Re: Encoding error during database restore

On 11/3/2021 17:03, Marc Fromm wrote:
>
> 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.

--
Karl Denninger
karl(at)denninger(dot)net
/The Market Ticker/
/[S/MIME encrypted email preferred]/