I was trying to migrate my Peertube instance and getting this error when trying to restore the dump on the new server

$ sudo -u postgres pg_restore -c -C -d postgres peertube_prod-dump.db
pg_restore: error: could not execute query: ERROR:  database "peertube_prod" does not exist
Command was: DROP DATABASE peertube_prod;
pg_restore: error: could not execute query: ERROR:  invalid locale name: "en_US.UTF-8"
Command was: CREATE DATABASE peertube_prod WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';

Solution:

This happen because your server hasn’t defined and generated locale en_US.UTF-8 yet, which is required by the command that you can see in the error log.

To check locale that your server has defined is by running

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

Step 1 – Define locale:

Edit /etc/locale.gen and comment out en_US.UTF-8

# en_SG.UTF-8 UTF-8
# en_US ISO-8859-1
# en_US.ISO-8859-15 ISO-8859-15
en_US.UTF-8 UTF-8
# en_ZA ISO-8859-1
# en_ZA.UTF-8 UTF-8
# en_ZM UTF-8

Step 2 – Generate locale:

Generate locale by running this command

$ sudo locale-gen

Step 3 – Verify:

Run previous command to verify. Make sure en_US.utf8 is there.

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

Step 4 – Restart PostgreSQL

$ sudo systemctl restart postgresql

You are now done.