psql - 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?

question from:https://stackoverflow.com/questions/66052693/copying-postgresql-local-to-remote-database-both-with-password-error-option

Answer 1

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