Migrating a database from one PostgreSQL server to another cleanly

Question

I have a Rails app server that uses Postgres for the DB backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the server was in production by doing the following:

1. As user postgres: pg_dump databasename > databasename.sql
2. scp the databasename.sql file over to the new server
3. As user postgres pn the new server enter psql and create the database and alter ownership
4. As user postgres run psql databasename < databasename.sql

All of this will occur after I shutdown Nginx/Passenger so all sessions will be terminated and no more data can come in/go out of the system. The only thing running will be postgres which no one can connect to directly.

Does this sound like a reasonable plan to migrate the database from one server to another? The database is 12MB in size, probably 30 tables, and has maybe 50K records so it's pretty small.

Any advice on how to do this cleanly and to make sure my data is "sane" would be great.

Answer 1

This is a reasonable plan if a planned maintenance of a few minutes up to an hour maybe is no big thing for you.

The database is real small and should be imported within seconds on the new server.

If the downtime is an issue for you, you should consider replicating the server with Slony (http://slony.info/) and then make the replicating slave finally the new master.