Migration of actual database across is something that was not working at all last time I tried it, and that I have managed to make work perfectly this time around.
In order to migrate a database from an older version of Postgres to a newer version, you need to do a dump and restore of the data. Unfortunatly, the pg_dump tool on Postgres 7.3 doesn't dump Schemas in a form that Postgres 8.1 will restore, and so if you do this you end up with all of your tables and functions in the Public schema instead of the schemas you put them in.
However, there is a simple way around this, and one that works very easy and even removes a step from the migration.
Before, I was attempting to do the migration as follows:
This assumes that you have already created the database and users prior to importing the data.
oldserver$ pg_dump -f database.sql database
newserver$ scp oldserver:database.sql .
newserver$ psql -d database -f database.sql
The new method that I have been using, that seems so far to work perfectly, is as follows:
newserver$ pg_dump -f database.sql -h oldserver -U graham database
newserver$ psql -d database -f database.sql
In order to do this, there are a few things that need to be done first. Namely,
# Allow newserver to access oldserver. I did this by just putting a whitelist entry in the firewall on oldserver.
# Allow newserver to access the Postgres databases on oldserver. This is done by editing pg_hba.conf
# Allow the user to log in remotely. This is done by the settings in pg_hba.conf, and by making sure the user has a password you know.
The trick here is that the pg_dump that comes with Postgres 8.1 knows how to dump data from an older version of Postgres, but it dumps it in a format that the newer version is able to import properly.
