Re: 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

 

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.