I had to migrate a pretty big database cluster from one server with PostgreSQL 8.4 to a new server with 9.2 There we multiple apps using the databases, and the downtime had to be minimal. Luckily, the databases were split to multiple schemas, and there were only a few tables in a couple of schemas that were in active use at the time of the migration.
The pg_dump tool was very helpful in this operation. First, I set up ssh public key authentication between the postgres users from the source to the destination server. Then I ran the first full cluster dump to the new empty cluster. This can be done while users are actively using the source database, as long as you know which schemas, tables and sequences are in use, so that they can be copied again after shutting down applications:
|
1 |
postgres@source:~$ pg_dumpall | gzip | ssh destination 'gunzip | psql -e' |
The dump is gzipped and sent using ssh to the destination. Watching the run with top showed that gzip and ssh didn’t use much CPU compared to what the pg_dump and psql processes took. The -e option of psql tells you which queries exactly it is running.
At this point, there’s a snapshot of the source cluster in the destination, with all the metadata, users etc.
Next, after shutting down a particular application which uses a particular database, the following copies just that database:
|
1 |
postgres@source:~$ pg_dump --clean db1 | gzip | ssh destination 'gunzip | psql -e db1' |
The –clean option will actually drop all the objects in the destination database and re-create them. Note the destination database name in the psql command.
Another database was so big, that I had to copy individual schemas instead of the whole database, because that would have taken too much time. The following copies just one schema:
|
1 |
postgres@source:~$ pg_dump --clean --schema schema1 db2 | gzip | ssh destination 'gunzip | psql -e db2' |
If the whole schema is too big, this one can be used to copy a single table:
|
1 |
postgres@source:~$ pg_dump --clean --table schema2.table1 db2 | gzip | ssh destination 'gunzip | psql -e db2' |
The –clean table dump also drops and re-creates the sequences related to the table, so that your sequence numbers are kept intact.
With those commands, it is pretty easy to shut down an application, migrate the data, start up the particular app, and move to migrating the next one. Even if the apps use the same databases and schemas. Just be sure to know which app modifies which database objects. And remember that the schema and table specific dumps will not by default dump blobs.