Encoding error during database restore

From: Marc Fromm <frommm(at)wwu(dot)edu>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Encoding error during database restore
Date: 2021-11-03 21:03:28
Message-ID: MWHPR0201MB3594D4A547E6E595C400BC3FC38C9@MWHPR0201MB3594.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I need to migrate my databases from the centOS7 with postgres9 to Rocky8.4 with postgres10.

In the past, as postgres user, I initialized the database with "initdb --locale=C -D /var/lib/pgsql/data" using locale=C to overcome UTF8 errors when restoring the database backup.

To initialize the postgres10 database, tutorials recommended using, "postgresql-setup --initdb --unit postgresql" which does not allow --locale=C.
With out using --locale=C, when I import my databases I get the encoding errors.
psql -U postgres -f pg_dbs.bkp postgres
ERROR: encoding "LATIN1" does not match locale "en_US.UTF-8"
DETAIL: The chosen LC_CTYPE setting requires encoding "UTF8".

Is there an alternate way to set --locale=C using the "postgresql-setup --initdb --unit postgresql" command?

When I backup my databases using pg_dumpall (pg_dumpall -c -U postgres > pg_dbs.bkp), is there a way to strip the encoding or change the encoding to UTF8?

Thanks

Marc

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Scott Ribe 2021-11-03 22:24:18 q re removal/recycling of WAL files
Previous Message Nikhil Shetty 2021-11-03 09:17:41 Stuck Checkpoint Process