pg_restore: error: could not execute query: ERROR: option “locale” not recognized

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply