The Correct way to Backup PostgreSQL
Wednesday, September 19th, 2007Begin a full time DBA can make you realize how important backups are. Enterprise database systems like Oracle and Microsoft’s SQL Server provide excellent backup mechanisms for doing “live” backups of the databases that guarantee consistency and allow for point-in-time recovery.
In the open source world, however, we’re left with old skoul methods of doing backups such as cold backups, or even worse, text-based “dumps” of the databases. I’ll let you waste your time reading about how MySQL “recommends” you backup a database, and how PostgreSQL’s pg_dump works. These methods are very resource intensive (creating a bunch of “insert” statements and formatting everything as plain text) and can even lock our real working processes (in the case of MySQL) while it is doing the backup!