Обсуждение: pg_dump / pg_restore version confusion
Hi,
An external supplier had a postgres v9.5 database which he dumped with a pg_dump v12 client in custom format using PgAdmin4.
Would a pg_restore with a v12 client into a postgres v9.6 database work and be officially supported?
Thanks, Markus
On Wed, Jul 29, 2020 at 10:53:34AM +0000, Zwettler Markus (OIZ) wrote: > Hi, > > > > An external supplier had a postgres v9.5 database which he dumped with a > pg_dump v12 client in custom format using PgAdmin4. > > > > Would a pg_restore with a v12 client into a postgres v9.6 database work and be > officially supported? Yes, you can always use a newer pg_dump on an older database, though the reverse is not recommended. In fact, if you are upgrading to PG 12, it is recommended to use pg_dump v12 to dump a Postgres database from an earlier version. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
And I can also do this restore: <quote> Would a pg_restore with a v12 client into a postgres v9.6 database work and be officially supported? </quote> -Markus > -----Ursprüngliche Nachricht----- > Von: Bruce Momjian <bruce@momjian.us> > Gesendet: Mittwoch, 29. Juli 2020 13:49 > An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> > Cc: pgsql-general <pgsql-general@lists.postgresql.org> > Betreff: Re: pg_dump / pg_restore version confusion > > On Wed, Jul 29, 2020 at 10:53:34AM +0000, Zwettler Markus (OIZ) wrote: > > Hi, > > > > > > > > An external supplier had a postgres v9.5 database which he dumped with > > a pg_dump v12 client in custom format using PgAdmin4. > > > > > > > > Would a pg_restore with a v12 client into a postgres v9.6 database > > work and be officially supported? > > Yes, you can always use a newer pg_dump on an older database, though the > reverse is not recommended. In fact, if you are upgrading to PG 12, it is > recommended to use pg_dump v12 to dump a Postgres database from an earlier > version. > > -- > Bruce Momjian <bruce@momjian.us> https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee
On Wed, Jul 29, 2020 at 12:33:56PM +0000, Zwettler Markus (OIZ) wrote: > And I can also do this restore: > > <quote> > Would a pg_restore with a v12 client into a postgres v9.6 database work and be officially supported? > </quote> Uh, good question. You should still use the version of pg_restore that you are loading _into_, not what you dumped from. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
I cannot use pg_restore v9.6 on a pg_dump v12 because otherwise: pg_restore: [archiver] unsupported version (1.14) in fileheader The external supplier did PG v9.5 database + pg_dump v12. I would have to do pg_restore v12 (as of pg_dump v12) into my PG v9.6. The version chain would be PG v9.5 => pg_dump v12 => pg_restore v12 => PG v9.6. That's why my question has been: would the whole chain work and is it supported? > -----Ursprüngliche Nachricht----- > Von: Bruce Momjian <bruce@momjian.us> > Gesendet: Mittwoch, 29. Juli 2020 14:55 > An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> > Cc: pgsql-general <pgsql-general@lists.postgresql.org> > Betreff: Re: pg_dump / pg_restore version confusion > > On Wed, Jul 29, 2020 at 12:33:56PM +0000, Zwettler Markus (OIZ) wrote: > > And I can also do this restore: > > > > <quote> > > Would a pg_restore with a v12 client into a postgres v9.6 database work and be > officially supported? > > </quote> > > Uh, good question. You should still use the version of pg_restore that you are > loading _into_, not what you dumped from. > > -- > Bruce Momjian <bruce@momjian.us> https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee
On 7/29/20 3:53 AM, Zwettler Markus (OIZ) wrote: > Hi, > > An external supplier had a postgres v9.5 database which he dumped with a > pg_dump v12 client in custom format using PgAdmin4. > > Would a pg_restore with a v12 client into a postgres v9.6 database work > and be officially supported? The best that could be said is it may work. Officially supported : https://www.postgresql.org/docs/12/app-pgdump.html "Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 8.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. " > > Thanks, Markus > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/29/20 6:38 AM, Zwettler Markus (OIZ) wrote: > I cannot use pg_restore v9.6 on a pg_dump v12 because otherwise: pg_restore: [archiver] unsupported version (1.14) in fileheader > > The external supplier did PG v9.5 database + pg_dump v12. > I would have to do pg_restore v12 (as of pg_dump v12) into my PG v9.6. > The version chain would be PG v9.5 => pg_dump v12 => pg_restore v12 => PG v9.6. > > That's why my question has been: would the whole chain work and is it supported? That is one way of attempting it, though it is not supported. You could also try pg_restore v12 on pg_dump file v9.6 to Postgres v9.6 database as well. With the caveats that I quoted in my previous post. Ideally you would have the supplier redo the dump of the database with a 9.6 version of pg_dump. > > > >> -----Ursprüngliche Nachricht----- >> Von: Bruce Momjian <bruce@momjian.us> >> Gesendet: Mittwoch, 29. Juli 2020 14:55 >> An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch> >> Cc: pgsql-general <pgsql-general@lists.postgresql.org> >> Betreff: Re: pg_dump / pg_restore version confusion >> >> On Wed, Jul 29, 2020 at 12:33:56PM +0000, Zwettler Markus (OIZ) wrote: >>> And I can also do this restore: >>> >>> <quote> >>> Would a pg_restore with a v12 client into a postgres v9.6 database work and be >> officially supported? >>> </quote> >> >> Uh, good question. You should still use the version of pg_restore that you are >> loading _into_, not what you dumped from. >> >> -- >> Bruce Momjian <bruce@momjian.us> https://momjian.us >> EnterpriseDB https://enterprisedb.com >> >> The usefulness of a cup is in its emptiness, Bruce Lee > > > -- Adrian Klaver adrian.klaver@aklaver.com
"Zwettler Markus (OIZ)" <Markus.Zwettler@zuerich.ch> writes:
> And I can also do this restore:
> <quote>
> Would a pg_restore with a v12 client into a postgres v9.6 database work and be officially supported?
> </quote>
It might work, but it's not completely guaranteed. Sometimes a new
pg_dump will use DDL syntax that doesn't exist in older versions,
even to describe constructs that do exist in older versions. This
isn't really common though.
I'd say try it and see. If the dump loads into 9.6 without errors then
it's probably fine (but you could dump again, with the same pg_dump
version, and diff the outputs to confirm).
regards, tom lane