Copying postgresql local to remote database (both with password) - ERROR: option "locale" not recognized
Feb 4
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?
postgresql1 answer
Accepted answer · original discussion
Mar 10
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.
2 question comments
Use comments to ask for clarification. Post a solution as an answer.
Feb 4
locale option in the create database statement: postgresql.org/docs/12/sql-createdatabase.html but 13 have: postgresql.org/docs/13/sql-createdatabase.html Remove -C option from pg_dump and create database by hands.Feb 4
-C and creating the database manually worked perfectly, and it didn't have anything to do with the way I was inputting the password.