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?

It seems that you are trying to copy DB from PostgreSQL 13. PostgreSQL 12 haven't 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. — Abelisto
– Abelisto, Commented Feb 4, 2021 at 19:57
Thanks @Abelisto! Saved the day!!! Removing -C and creating the database manually worked perfectly, and it didn't have anything to do with the way I was inputting the password. — rodolfo_r
– rodolfo_r, Commented Feb 4, 2021 at 22:14

Codrut · Accepted Answer · 2021-03-10 13:24:07Z

20

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.

answered Mar 10, 2021 at 13:24

Codrut

4936 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rodolfo_r Over a year ago

Awesome, @Codrut!

Jeremy John · Accepted Answer · 2021-03-18 22:06:32Z

7

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.

answered Mar 18, 2021 at 22:06

Jeremy John

13.8k2 gold badges18 silver badges17 bronze badges

Comments

rodolfo_r · Accepted Answer · 2021-02-12 00:00:13Z

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:

Destination db password
Hit enter
Origin db password
Hit enter

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related