3

A good database backup is consistent, so the whole dump represents the state when the dumping was started. A typical strategy for this on MySQL is to lock the table, so no additional data can be written during the backup. But locking the database is a bad user experience (slow responses or even timeouts, if the dump takes lots of time). So I wonder, if Postgres has a method to make consistent backups without locking the tables.

(My current backup strategy with MySQL is to have a slave only for the backups, so the master can continue to serve the users requests while I can take a consistent dump. But that introduces other failure points, for example the replication might break)

2 Answers 2

3

locking and locking are two different things. Some locks don't cause any problems at all and you can use the database as usual (access share lock), while other locks have a major impact and stop your business.

The most simple way to make a backup of a PostgreSQL database, is pg_dump. We make daily backups of a multi TB database, during business hours processing at ~2500 tps.

Don't compare PostgreSQL with MySQL, two very different databases. Many hard to fix problems in MySQL are very simple in PostgreSQL. Making consistant backups is one of those things.

You don't need a slave to make a backup. It's handy, but not needed.

0

Running PostgreSQL over ZFS and taking live snapshot should solve this problem.

Of course that snapshot will have dirty transaction log, so you'll have to promote the snapshot to the clone (writable snapshot), then run separate PostgreSQL instance to clear/replay the logs, and then make a dump from the second instance. Finally, you should shutdown 2nd database server instance and delete the clone.

If you have not enough RAM to run two instances of PostgreSQL on the production server, then you can transfer database snapshot to the secondary server (with zfs send, zfs receive) and make a dump there.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .