Thread: Encoding error during database restore
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
@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;} 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
What's wrong with using "initdb" for Postgres10? It still works perfectly-well.
I have some legacy stuff in-field with ISO-8859-1 characters in the data and they will not load (nor will pg_upgrade work) into UTF8, which is the current default. Rolling forward to -10 by using "initdb" first and then using pg_upgrade worked just fine.
Yes, converting them would be a good idea at some point, but that point is not today.
Attachment
@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}
Is there an alternate way to set --locale=C using the “postgresql-setup --initdb --unit postgresql” command?
have you tried using 'SQL_ASCII' instead of 'C' for the locale?
-- Paul Paul Smith Computer Services support@pscs.co.uk - 01484 855800
On Thu, 2021-11-04 at 15:58 +0000, Paul Smith wrote: > On 03/11/2021 21:03, Marc Fromm wrote: > > Is there an alternate way to set --locale=C using the “postgresql-setup --initdb --unit postgresql” command? > > have you tried using 'SQL_ASCII' instead of 'C' for the locale? That is very bad advice. Never use SQL_ASCII! Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com