BUG #14761: pg_dumpall --version is misleading (from jessie-pgdg postgresql-client-common)

From: vindrg(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: vindrg(at)gmail(dot)com
Subject: BUG #14761: pg_dumpall --version is misleading (from jessie-pgdg postgresql-client-common)
Date: 2017-07-25 13:23:33
Message-ID: 20170725132333.1464.39535@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14761
Logged by: Vincas Dargis
Email address: vindrg(at)gmail(dot)com
PostgreSQL version: 9.4.12
Operating system: Debian 8 amd64
Description:

We've discovered, that pg_dumpall produced dump of roles from 9.4 cluster
are not compatible with 9.4, i.e. cannot be restored due to NOBYPASSRLS
option in SQL file.

Here's details:

# pg_dumpall --version
pg_dumpall (PostgreSQL) 9.4.12

# apt-cache policy postgresql-client-9.4
postgresql-client-9.4:
Installed: 9.4.12-1.pgdg80+1
Candidate: 9.4.12-1.pgdg80+1
Version table:
*** 9.4.12-1.pgdg80+1 0
500 http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg/main amd64
Packages
100 /var/lib/dpkg/status
9.4.12-0+deb8u1 0
500 http://security.debian.org/ jessie/updates/main amd64 Packages
500 http://httpredir.debian.org/debian/ jessie/main amd64 Packages

Our backup script uses line similar to this to dump roles:

pg_dumpall -h 127.0.0.1 -p 5432 -U our_dumper_user -r --role="postgres" -l
our_db -v -f "/somewhere..."

Its produced SQL file contains lines like this:

ALTER ROLE <some_user> WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION NOBYPASSRLS PASSWORD '<some_password>';

Note NOBYPASSRLS that is available since 9.5. pg_dumpall reported 9.4.12
version, and dumped cluster itself in of 9.4.

It seems that it is actually dumped by 9.5 pg_dumpall available in another
packaged, installed to test 9.5 some time ago:

# dpkg -S pg_dumpall
postgresql-client-9.5: /usr/lib/postgresql/9.5/bin/pg_dumpall
postgresql-client-9.4: /usr/lib/postgresql/9.4/bin/pg_dumpall
postgresql-client-common: /usr/bin/pg_dumpall

So pg_dumpall --version is misleading. It actually produced dump as latest
version installed.

Dump is OK if I use 9.4 explicitly:
/usr/lib/postgresql/9.4/bin/pg_dumpall -h ...

As a side-question, how could I protect from these errors in future? I do
not see pg_dumpall having option to check for client/server version exact
match, or something like that...

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-07-25 14:47:20 Re: BUG #14761: pg_dumpall --version is misleading (from jessie-pgdg postgresql-client-common)
Previous Message nikhil.varangaonkar 2017-07-25 07:35:08 BUG #14760: Installation Failed/Interrupted