We need to migrate our existing database server (PG 12) to a new server.
I followed this [https://www.postgresql.org/docs/8.1/app-pg-dumpall.html] article and created the dump as
pg_dumpall > alldb.sql
then copied the file
alldb.sql to the new system and tried to restore using
psql -f alldb.sql postgres
and it failed with the following error messages
psql:allbackup.sql:314: ERROR: option “locale” not recognized LINE 1:
…3103″ WITH TEMPLATE = template0 ENCODING = ‘UTF8’ LOCALE = ‘…
^ psql:allbackup.sql:317: ERROR: database “XXXXX” does not exist
psql:allbackup.sql:319: error: connect: FATAL: database “XXXXX”
does not exist
What could be the issue?
- Ubuntu 20.04
- Postgres version 12
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
CREATE DATABASE statement did not accept a
LOCALE parameter until PostgreSQL version 13. It fails in your case because the target server is version 12.
pg_dumpall command put that parameter because its version is newer than 12, so it considers that you’re going to restore into a version newer than 12. You can check the version with
The solution is to use the same version of
pg_dumpall than your target server. On Ubuntu, you might have several versions installed. Check if you have
/usr/lib/postgresql/12/bin/pg_dumpall, and if you do, call it directly with that full path.
If it’s not installed, it can be installed with the
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0