pg_restore could not execute query: ERROR: invalid locale name: "en_US.UTF-8"

Question

I use pg_restore on Windows 10 with a dump file made on Linux.

I search on the web but I don't find answer.

[NEW] : I install Ubuntu on my computer to use pg_restore but when I send

pg_restore -d mydatabase /home/user/Documents/dumpfile.dump

the command line is blocked.

Someone has this issue ?

Answer 1

Your new ubuntu installation hasn't defined the en_US.UTF-8 locale yet. So, when you're trying to restore the dumpfile, the dumpfile attempts to do something like:

CREATE DATABASE <database> WITH TEMPLATE = ... LC_COLLATE = 'en_US.UTF-8'...

But, 'en_US.UTF-8' is not defined by your new ubuntu server. First, you can verify this:

# list all "known" locales. In my case, on new Ubuntu 20, I get:
$ locale -a
C
C.UTF-8
POSIX

Edit existing /etc/locale.gen file, which contains the list of possible locales. Most locales will be commented out. These will not be defined, so, un-comment the line with 'en_US.UTF-8'.

Run (as root) locale-gen.

root# locale-gen
Generating locales (this might take a while)...
  en_US.UTF-8... done
Generation complete.

Notice it's now a configured locale:

$ locale -a
C
C.UTF-8
POSIX
en_US.utf8

(Yes, it is lower case utf8, not a problem)

Restart your postgres server (so it sees the new locale -- you do not need to restart the ubuntu server itself), and you restore show now work.