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

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)

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

StackExchange Database Administrators Q#294812, answer score: 5