Robert Haas: Why pg_dump Is Amazing

I wrote a blog post a couple of weeks ago entitled Is pg_dump a Backup Tool?. In that post, I argued in the affirmative, but also said that it's probably shouldn't be your primary backup mechanism. For that, you probably shouldn't directly use anything that is included in PostgreSQL itself, but rather a well-maintained third-party backup tool such as barman or pgbackrest. But today, I want to talk a little more about why I believe that pg_dump is both amazingly useful for solving all kinds of PostgreSQL-related problems and also just a great piece of technology.

The core value proposition of pg_dump is that the output is human-readable text. You'll get DDL commands that you can use to recreate your database objects, and you'll get COPY commands (or INSERT s, if you so request) that you can use to reload your table data. That is not really an advantage if you're just trying to back up and restore an entire database cluster, because converting all of your data from PostgreSQL's internal formats into text and back again is going to use a bunch of CPU resources. If you instead take and restore a physical backup, you can avoid all of that overhead.

But what if you're trying to do something else? There are lots of situations where a physical backup doesn't help you at all. Because all of your data is stored in PostgreSQL's internal formats, you can only use that data with a compatible version of PostgreSQL. So, if you're hoping to get your data into a system other than PostgreSQL, or if you're hoping to get your data into a different major version of PostgreSQL, that physical backup is not helping. In fact, even if you're just switching to a different CPU architecture, that physical backup is probably not helping, either: values such as integers and floating point numbers are represented using the CPU's native format, and if that is different, then the on-disk format is incompatible.

Leave a Comment