Howto Byte-level "offline" backup of postgresql database

Question

I am trying to love PostGreSQL. I'm pushing it at my company. I want us to adopt it for more and more projects. But I am personally stumped by a backup/restore issue. I keep thinking, if this was MS-SQL, this would not be a problem...

We cannot restore backups of our database - not with pg_dump and pg_restore. It fails for numerous reasons. I've searched quite a bit - hours and hours - with no remedy. Much of the database does get restored, but key parts do not.

As many know, in MS-SQL, one can disconnect a database, and make a copy of the MDB and LDB files, and then re-connect it. I can then take those two copied files to any other computer and re-connect them, and barring perhaps missing user accounts, in our experience, we've had no problem.

But pg_dump and pg_dumpall (which mostly just ends up iteratively calling pg_dump) dump the SQL commands to re-create the database. Not the byte-for-byte copy of the state of the database. Given that restoring our database by running the commands pg_dump spits out to create it does not work, is there any alternative closer to the byte-level-copy solution from the MS-SQL world that I can use?

What is the goal? Aside from the obvious (being able to restore the database in case of failure), I'm trying to deploy a Vagrant development environment. So we have one master copy of the database that our database developer sets up in her virtual development environment. Then myself and others want to be able to get periodic snapshots of the database after major modifications, and load the snapshots into the Vagrants on our machines.

IF we could easily backup and restore the database, this would not be a problem. It would work just fine. I'd rather not have to copy an entire virtual machine to share database changes.

The only thing I tried different from futzing with pg_dump[all] and pg_restore was SymmetricDS, but it broke the database... perhaps through misconfiguration, or perhaps because it is trying to do the same kind of stuff pg_dump does. Not sure.

I suspect I'll get questions about why the pg_restore fails. So to clarify briefly, it has to do with custom data types, operators, and functions installed by 3rd party software, and us, into interdependent schemas (ArcSDE and PostGIS) not getting created in the right order as pg_dump thinks they should be created. I also believe (though cannot prove) that the search_path as set at the start of a pg_dump backup is wrong, which doesn't help the restore process already hampered by schema interdependencies.

Answer 1

You can do a physical/file level backup on PostgreSQL, albeit it is not standard practice (I'll try very hard to make pg_dump work; have you asked on the mailing list?)

As another option, have you considered a replicated setup, with "live" point-in-time backup/recovery?

Anyway, to take a physical backup with minimal downtime you had to:

1. stop PostgreSQL
2. take a LVM snapshot inside your virtual machine
3. restart PostgreSQL
4. mount the LVM snapshot in a suitable directory inside your virtual machine
5. make a copy the entire database cluster directory (eg: /var/lib/pgsql)
6. unmount and delete the LVM snapshot

As taking a snapshot is very fast, this will minimize the downtime.

To restore the database on another PostgreSQL server, do the following:

1. stop PostgreSQL
2. delete/rename the database cluster directory (eg: mv /var/lib/pgsql /var/lib/pgsql_original)
3. restore your copy of the database cluster directory (eg: mv mycopy /var/lib/pgsql)
4. if SELINUX is enabled, relabel the new directory (eg: `restorecon -RF /var/lib/pgsql)
5. restart PostgreSQL

Please note that this kind of backups can be used only between the same PostgreSQL version and the same arch (ie: you can not restore a i386/32 bit backup on a x86_64/64 bit machine).