Thread: Dump version issues
Hello. Was trying to import a database from a cloud deployment, and ran into this. Exported the database with: * pg_dump (PostgreSQL) 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) * RDS PostgreSQL 12.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit This produced a dump file of version 1.16, at least according to 'file'. Trying to import the same dump on another (Fedora) machine, tried: * pg_restore (PostgreSQL) 12.22 <- from PGDG * pg_restore (PostgreSQL) 16.8 <- from Fedora Both complain that they can't process the dump because dump version 1.16 is not supported. Both packages are latest from their respective repositories. I'm not sure whether the server has any say in the version of the dump file, I assume it doesn't. So, how come older software (according to versions) produces dump files with a greater version than the newer software can understand? Is this Ubuntu package maintainers messing things up? Given a pg_dump, it would be nice if its "-V" output would say which version of the dump it would produce, and a pg_restore - what's the max (and min, if that's a thing) version of the dump that it will accept. That would be just super-helpful in finding the right combination of tools. I ended up running PGDG's 16 pg_dump ((PostgreSQL) 16.8 (Ubuntu 16.8-1.pgdg22.04+1)) on Ubuntu, which produced a 1.15-0 dump. Thank you, Pawel.
Pawel Veselov <pawel.veselov@gmail.com> writes:
> Was trying to import a database from a cloud deployment, and ran into this.
> Exported the database with:
> * pg_dump (PostgreSQL) 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1)
> * RDS PostgreSQL 12.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
> 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
> This produced a dump file of version 1.16, at least according to 'file'.
Better take another look at which pg_dump you used. Archive version
1.16 was introduced in PG v17, according to a quick look at the source
code. pg_restore versions older than v17 are not going to understand
it.
> I'm not sure whether the server has any say in the version of the dump
> file, I assume it doesn't.
Nope, just pg_dump.
> So, how come older software (according to versions) produces dump
> files with a greater version
> than the newer software can understand? Is this Ubuntu package
> maintainers messing things up?
You'd have to ask them.
> Given a pg_dump, it would be nice if its "-V" output would say which
> version of the dump it would produce,
> and a pg_restore - what's the max (and min, if that's a thing) version
> of the dump that it will accept.
Hmm, maybe. The original thought was that the archive version would
seldom be a limiting factor: it describes the file format but not the
SQL inside the file, and that's often version-specific too. So in
general we don't promise that pg_dump version N will produce output
that you can use with pg_restore or server versions less than N,
whether they share the same archive version or not.
regards, tom lane
On 4/23/25 11:46, Pawel Veselov wrote: > Hello. > > Was trying to import a database from a cloud deployment, and ran into this. > > Exported the database with: > * pg_dump (PostgreSQL) 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) Are you sure about the above? Version 1.16 is what you get from a Postgres 17 dump file. If the client OS you are using is Ubuntu and you used the postgresql-common install it may dump using the latest installed version of pg_dump. If you have Postgres 17 installed then that will be used. To specify a pg_dump version do something like: pg_dump --cluster 16/main Assuming you have the default main cluster created. pg_lsclusters will help show what Postgres instances are installed and what the cluster names are. > * RDS PostgreSQL 12.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) > 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit > > This produced a dump file of version 1.16, at least according to 'file'. > > Trying to import the same dump on another (Fedora) machine, tried: > * pg_restore (PostgreSQL) 12.22 <- from PGDG > * pg_restore (PostgreSQL) 16.8 <- from Fedora > > Both complain that they can't process the dump because dump version > 1.16 is not supported. > Both packages are latest from their respective repositories. > > I'm not sure whether the server has any say in the version of the dump > file, I assume it doesn't. > > So, how come older software (according to versions) produces dump > files with a greater version > than the newer software can understand? Is this Ubuntu package > maintainers messing things up? > > Given a pg_dump, it would be nice if its "-V" output would say which > version of the dump it would produce, > and a pg_restore - what's the max (and min, if that's a thing) version > of the dump that it will accept. > That would be just super-helpful in finding the right combination of tools. > > I ended up running PGDG's 16 pg_dump ((PostgreSQL) 16.8 (Ubuntu > 16.8-1.pgdg22.04+1)) > on Ubuntu, which produced a 1.15-0 dump. > > Thank you, > Pawel. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 4/23/25 11:46, Pawel Veselov wrote: > Hello. > So, how come older software (according to versions) produces dump > files with a greater version > than the newer software can understand? Is this Ubuntu package > maintainers messing things up? Do: man postgresql-common to see how this handled. I have found that it is best to be explicit using the --cluster option. > > Thank you, > Pawel. > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Apr 23, 2025 at 9:13 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 4/23/25 11:46, Pawel Veselov wrote: > > Hello. > > > So, how come older software (according to versions) produces dump > > files with a greater version > > than the newer software can understand? Is this Ubuntu package > > maintainers messing things up? > > Do: > > man postgresql-common > > to see how this handled. > > I have found that it is best to be explicit using the --cluster option. Thank you, I would have never guessed. $ pg_dump -V -h x pg_dump (PostgreSQL) 17.4 (Ubuntu 17.4-1.pgdg22.04+2) $ pg_dump -V pg_dump (PostgreSQL) 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) (facepalm) I was using the latter checking what pg_dump version was actually being used. Wasn't aware of this pg_wrapper business. > Given a pg_dump, it would be nice if its "-V" output would say which > version of the dump it would produce Yeah, this wouldn't have helped a bit.
On 4/23/25 13:02, Pawel Veselov wrote: > On Wed, Apr 23, 2025 at 9:13 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > >> On 4/23/25 11:46, Pawel Veselov wrote: >>> Hello. >> >>> So, how come older software (according to versions) produces dump >>> files with a greater version >>> than the newer software can understand? Is this Ubuntu package >>> maintainers messing things up? >> >> Do: >> >> man postgresql-common >> >> to see how this handled. >> >> I have found that it is best to be explicit using the --cluster option. > > Thank you, I would have never guessed. > > $ pg_dump -V -h x > pg_dump (PostgreSQL) 17.4 (Ubuntu 17.4-1.pgdg22.04+2) > $ pg_dump -V > pg_dump (PostgreSQL) 12.20 (Ubuntu 12.20-0ubuntu0.20.04.1) > > (facepalm) > > I was using the latter checking what pg_dump version was actually being used. > Wasn't aware of this pg_wrapper business. If you do something like: ls -al /usr/bin/pg_dump you will find pg_dump is a sym link to: /usr/share/postgresql-common/pg_wrapper which is a Perl script that does the selection. The same holds for the other Postgres commands in /usr/bin/. FYI, psql will always resolve to latest version installed, --cluster will have no affect on it. > >> Given a pg_dump, it would be nice if its "-V" output would say which >> version of the dump it would produce > > Yeah, this wouldn't have helped a bit. -- Adrian Klaver adrian.klaver@aklaver.com