From: Karl Denninger <karl@denninger.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Encoding error during database restore
Date: Thu, 4 Nov 2021 09:21:30 -0400
Message-ID: <c44dd634-1be3-231d-18e1-696e56e4740b@denninger.net> (raw)
In-Reply-To: <MWHPR0201MB3594D4A547E6E595C400BC3FC38C9@MWHPR0201MB3594.namprd02.prod.outlook.com>
References: <MWHPR0201MB3594D4A547E6E595C400BC3FC38C9@MWHPR0201MB3594.namprd02.prod.outlook.com>
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@denninger.net
/The Market Ticker/
/[S/MIME encrypted email preferred]/
Attachments:
smime.p7s (application/pkcs7-signature, 4.8K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: karl@denninger.net, pgsql-admin@lists.postgresql.org
Subject: Re: Encoding error during database restore
In-Reply-To: <c44dd634-1be3-231d-18e1-696e56e4740b@denninger.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox