0

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
1
  • 2
    Not directly related to your problem, but why are you reading the manual for version 8.1 if you are using 12?
    – user1822
    Apr 19, 2022 at 10:20

1 Answer 1

3

The 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.

The 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 pg_dumpall --version

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 postgresql-client-12 package.

1
  • yes , you are right .. the postgres client we were using was version 14. Thanks a lot
    – Biju Soman
    Apr 20, 2022 at 5:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.