December 9th, 2013, 10:29 AM
Is pg_dump sufficient for an online backup tool
I am using postgresql 9.2 running on Ubuntu. I have a single postgres cluster on the server which contains several small databases. I want to script a method to extract a single database and move it to a new server by recreating the database from backup.
I can see several options using pg_start_backup and pg_stop_backup using a replication method but I don't want to replicate the entire cluster. Is it possible to use pg_dump to make an online copy of the source database without stopping the source database at all, and perhaps freeze transactions of a single database to ensure consistency?
I ultimately want to be able to make online checkpoints of an entire database (not cluster) and restore the database to a point in time either to a new server or to replace it where it was originally.
December 10th, 2013, 01:49 AM
You can just pg_dump it, you will get a backup of the current state of the database. The only thing to note is that making the dump may place locks that reduce the usability of your database, the dump must be consistent so two related tables may not change together if one of them has already been processed during the current dump.
You can use --serializable-deferrable to make dump use a serializable transaction; effectively ignoring changes that are being made while the dump is running.
pg_start_backup and pg_stop_backup are meant to be used for file-based backups, they bring the transaction logs into a state from which they can recover if you use a filesystem backup. After a servercrash you would restore the files from your backup and start the server, pgsql would recognise the state of the WAL files and startup with data from the point of the pg_start_backup, ignoring (and not recovering from) any incomplete transactions that got into the WAL files after the start command. But that's not really relevant unless the database in question requires extremely fast backups or the database is too large to put into a single SQL file (taking note that restoring from an SQL file can take quite some time for a sizable database)