Postgres archive versions

Question

Trying to import a nightly production backup locally, I ran into this:

pg_restore: [archiver] unsupported version (1.14) in file header

Now, as to what to do in such a case, the question has been asked before or see here too.

I'm still left with some corollary questions though:

How can I check which version of the archive format a Postgres dump uses, from the command-line?
How do I know which Postgres versions support which versions of the archives format?

I see no obvious link between Postgres versions and Postgres archive formats, meanwhile searching Google or Postgresql.org for "1.14" did not help.

In my case:

While I can still import dumps produced by Heroku Postgres itself, the nightly export (produced by the Autobus add-on) produces archive versions I can't import locally.
Upgrading from Postgres 9.6.14 to 9.6.16 did not help. (I take it Autobus is using some Postgres version > 9.6.)

I'd like to know when they changed archive formats by looking up the info for the last X dumps… and from there what my options/requirements are for importing their dumps.

score 2 · Accepted Answer · edited Feb 12 '20 at 17:08

pg_restore -l filename will tell you the archive version of a PostgreSQL dump.

To know which PostgreSQL version uses which archive format, you'll have to look at the source: pg_backup_archiver.h, e.g.:

/* Historical version numbers (checked in code) */
#define K_VERS_1_0  MAKE_ARCHIVE_VERSION(1, 0, 0)
#define K_VERS_1_2  MAKE_ARCHIVE_VERSION(1, 2, 0)   /* Allow No ZLIB */
#define K_VERS_1_3  MAKE_ARCHIVE_VERSION(1, 3, 0)   /* BLOBs */
#define K_VERS_1_4  MAKE_ARCHIVE_VERSION(1, 4, 0)   /* Date & name in header */
#define K_VERS_1_5  MAKE_ARCHIVE_VERSION(1, 5, 0)   /* Handle dependencies */
#define K_VERS_1_6  MAKE_ARCHIVE_VERSION(1, 6, 0)   /* Schema field in TOCs */
#define K_VERS_1_7  MAKE_ARCHIVE_VERSION(1, 7, 0)   /* File Offset size in
                                                     * header */
#define K_VERS_1_8  MAKE_ARCHIVE_VERSION(1, 8, 0)   /* change interpretation
                                                     * of ID numbers and
                                                     * dependencies */
#define K_VERS_1_9  MAKE_ARCHIVE_VERSION(1, 9, 0)   /* add default_with_oids
                                                     * tracking */
#define K_VERS_1_10 MAKE_ARCHIVE_VERSION(1, 10, 0)  /* add tablespace */
#define K_VERS_1_11 MAKE_ARCHIVE_VERSION(1, 11, 0)  /* add toc section
                                                     * indicator */
#define K_VERS_1_12 MAKE_ARCHIVE_VERSION(1, 12, 0)  /* add separate BLOB
                                                     * entries */
#define K_VERS_1_13 MAKE_ARCHIVE_VERSION(1, 13, 0)  /* change search_path
                                                     * behavior */
#define K_VERS_1_14 MAKE_ARCHIVE_VERSION(1, 14, 0)  /* add tableam */

I recommend that you use a recent version of pg_restore: newer versions can read older archive formats.

Note that restoring a dump taken with an old version of pg_dump into a newer version of the database is not well supported and can produce surprising results.

Postgres archive versions

1 Answers1