pg_restore: error: could not execute query: ERROR: option "locale" not recognized

Question

I'm getting the following error when I'm trying to restore my dump file to another (local) PostgreSQL server (running on Debian GNU/Linux 10.10):

pg_restore: error: could not execute query: ERROR:  option "locale" not recognized

because it indeed has the following line:

CREATE DATABASE "REMOTE_DB" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_GB.UTF-8';

And yes, I know that PostgreSQL 12 CREATE DATABASE command doesn't have a LOCALE option.

What I don't understand:

• the version of PostgreSQL itself I'm using on my local server, is reported as 12.7
• the version of pg_dump I'm using on my local server is reported as 12.7
• the version of pg_restore I'm using on my local server is reported as 12.7
• the remote PostgreSQL database whose dump I've created using pg_dump is version 12.7, not 13.

Let me verify these one by one:

My local server:

$ sudo -u postgres psql -c 'select version();'
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

My local pg_dump version:

$ pg_dump --version
pg_dump (PostgreSQL) 12.7 (Debian 12.7-1.pgdg100+1)

My local pg_restore version:

$ pg_restore --version
pg_restore (PostgreSQL) 12.7 (Debian 12.7-1.pgdg100+1)

Let's check the PostgreSQL version of the REMOTE database:

$ psql --host=REMOTE_HOST_IP_ADDRESS --dbname=REMOTE_DB --username=DB_USER -c 'select version();'
Password for user DB_USER:
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

This is how I created the dump file:

$ pg_dump --verbose \
  --create \
  --clean \
  --if-exists \
  --format=custom \
  --compress=5 \
  --host=REMOTE_HOST_IP_ADDRESS --dbname=REMOTE_DB --username=DB_USER \
  --schema=public --table=TABLE_NAME\
  > db.dump

And this is how I tried to restore it on the local server that's running PostgreSQL 12:

$ sudo -u postgres \
pg_restore --verbose \
  --create \
  --clean \
  --jobs=8 \
  --format=custom \
  --dbname=postgres \
  db.dump

PostgreSQL 13 is installed but it's not running on this local server:

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 13-main
   Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
   Active: failed (Result: protocol) since Thu 2021-06-24 07:59:24 UTC; 1 day 5h ago
  Process: 562 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 13-main start (code=exited, status=1/FAILURE)

PostgreSQL 12 is running:

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 12-main
   Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
   Active: active (running) since Thu 2021-06-24 07:59:26 UTC; 1 day 5h ago
  Process: 556 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
 Main PID: 595 (postgres)
    Tasks: 9 (limit: 36863)
   Memory: 235.7M
   CGroup: /system.slice/system-postgresql.slice/[email protected]
           ├─595 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
           ├─678 postgres: 12/main: checkpointer
           ├─679 postgres: 12/main: background writer
           ├─680 postgres: 12/main: walwriter
           ├─681 postgres: 12/main: autovacuum launcher
           ├─682 postgres: 12/main: stats collector
           ├─683 postgres: 12/main: TimescaleDB Background Worker Launcher
           ├─684 postgres: 12/main: logical replication launcher
           └─685 postgres: 12/main: TimescaleDB Background Worker Scheduler

Long story short, my question is:

• why do I get this unsupported LOCALE option in the dump file generated by pg_dump, while the REMOTE PostgreSQL version, as well as local pg_dump version is reported as 12.7 and not 13?

What else should I check?

Answer 1

the version of PostgreSQL itself I'm using on my local server, is reported as 12.7

But still it's pg_dump 13.x that created the dump with the LOCALE argument to CREATE DATABASE.

/usr/bin/pg_dump as installed by Debian/Ubuntu packages is a wrapper that tries to determine which version of PostgreSQL it should talk to, and then it executes the actual binary inside /usr/lib/postgresql/$VERSION/bin/. This is meant to support multiple installations and versions of PostgreSQL on the same host, even if only one is running in your case.

$ ls -l /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37 Aug 14  2020 /usr/bin/pg_dump -> ../share/postgresql-common/pg_wrapper

See pg_wrapper for the manpage.

Plausible explanation

When executing pg_dump --version, it somehow picks up the version of your active cluster (12) and launches the corresponding pg_dump.

When executing pg_dump --host ..., it doesn't know which version of PostgreSQL the remote host is running, and somehow chooses version 13 by default.

Solution

As a workaround, you may invoke directly /usr/lib/postgresql/12/bin/pg_dump instead of pg_dump