2

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 postgresql@13-main.service
● postgresql@13-main.service - PostgreSQL Cluster 13-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; 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 postgresql@12-main.service
● postgresql@12-main.service - PostgreSQL Cluster 12-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; 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/postgresql@12-main.service
           ├─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?

1
  • Some things are done with sudo and some are done without it. Maybe you are finding different binaries depending on sudo usage?
    – jjanes
    Jun 26 at 0:15
4

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

1
  • If you're running on a Mac and have postgresql installed via Homebrew there's no wrapper, so instead the workaround is to install version 12 (or other) like brew install postgresql@12, then calling `/usr/local/Cellar/postgresql@12/12.7/bin/pg_dump Jul 22 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.