Convert postgres database dumps

Postgres can dump databases in 2 different formats:

• PostgreSQL custom database dump
• Plain SQL dump

Both have their advantages and disadvantages. The later makes dumps better readable, so what can you do if your backup is using the first format?

First let’s check the source format of our dump:

linux # file postgres_src.dump
postgres_src.dump: PostgreSQL custom database dump - v1.14-0

Most online searches will result in: Restore the data to a new database and dump this new database in the desired format. That however requires a running postgres database and is quite annoying.

However if you skip the first page of search results, you’ll find that with Postgres 12 an newer versions, the option for converting data is built into pg_restore:

linux # pg_restore postgres_src.dump -f postgres_dst.dump
linux # file postgres_dst.dump
postgres_dst.dump: ASCII text

Now we’ve got a nice clean ASCII dump that can be analyzed easily.

In the beginning I was talking about the differences of the two formats, well one of them is size:

linux # ls -l postgres_src.dump postgres_dst.dump
-rw-r--r-- 1 root root 1710401 Sep  1 17:19 postgres_src.dump
-rw-r--r-- 1 root root 9636889 Sep  1 17:19 postgres_dst.dump

So my example the ASCII dump is about 5-6 times larger.