Copying postgresql local to remote database (both with password) - ERROR: option "locale" not recognized

Question

Working with Postgres 12 / Windows 10.

Trying to copy a remote database to localhost with the following command:

pg_dump -C -h remotehost -p 5432 -U postgres remotedb | psql -h localhost -p 5432 -U postgres localdb

CMD requests for password 2x.

Password for user postgres: Password:

I input localhost first, hit ENTER, then input remotehost and hit ENTER again.

This is the error I get in return:

SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
ERROR:  option "locale" not recognized
LINE 1: ...ting" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = '...
                                                             ^
ERROR:  database "remotedb" does not exist
\connect: FATAL:  database "remotedb" does not exist
pg_dump: error: could not write to output file: Broken pipe

• How to solve 1st error 'option "locale" not recognized"?
• Is the 2nd error related to how I input the passwords? How should I work when both databases request for passwords?

Answer 1

You don't need to create the db manually.
You can do in a one-liner by using sed to replace LOCALE with LC_COLLATE:

Your command should look like this:
Note! This works only if you use script (plain-text) file format for backups

pg_dump -C -h remotehost -p 5432 -U postgres remotedb |  sed 's/LOCALE/LC_COLLATE/' | psql -h localhost -p 5432 -U postgres localdb

Explanation:

Script dumps are plain-text files containing the SQL commands required to reconstruct the database. When you add -C flag to pg_dump the dump file will contain the following statement:

• Postgres 12 and older

CREATE DATABASE yourdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

• Postgres 13

CREATE DATABASE yourdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8';

By using sed we substitute the LOCALE word with LC_COLLATE in the pg_dump stream so psql will be able to restore the db locally.
This works even if LC_CTYPE = 'en_US.UTF-8' is missing.

Answer 2

After testing Abelisto's suggestion I found answers for both questions:

Answer to question 1

As informed by Abelisto, postgres 12 does not have locale option for create database, while postgres 13 does.

postgres 12: postgresql.org/docs/12/sql-createdatabase.html

postgres 13: postgresql.org/docs/13/sql-createdatabase.html

Then, creating the database manually on the destination db and removing -C from the command solved it. Here is the final command:

pg_dump -h remotehost -p 5432 -U postgres remotedb | psql -h localhost -p 5432 -U postgres localdb

An observation is that I had postgres 12 & 13 installed but psql path was setup for postgres 13. Hence, no matter if I was trying to pg-dump between postgres 12 databases, I would get the locale error, since psql was using postgres 13 to run the command.

Answer to question 2

The process for inputing both passwords was correct:

1. Destination db password
2. Hit enter
3. Origin db password
4. Hit enter

Answer 3

This was caused by me using the 13.x of pg_restore connecting to dbs that were 11.x (target) and 10.x (source).

So I just installed pg_restore 10.x.

brew install postgresql@10
/usr/local/opt/postgresql@10/bin/pg_restore --all-the-args-here

Next time I'll be more careful with the --create and --clean arguments to pg_restore.