pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedEncoding error during database restore
4+ messages / 4 participants
[nested] [flat]
* Encoding error during database restore
@ 2021-11-03 21:03 Marc Fromm <frommm@wwu.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Marc Fromm @ 2021-11-03 21:03 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Encoding error during database restore
@ 2021-11-04 13:21 Karl Denninger <karl@denninger.net>
parent: Marc Fromm <frommm@wwu.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Karl Denninger @ 2021-11-04 13:21 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Encoding error during database restore
@ 2021-11-04 15:58 Paul Smith <paul@pscs.co.uk>
parent: Marc Fromm <frommm@wwu.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Paul Smith @ 2021-11-04 15:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
--
Paul
Paul Smith Computer Services
support@pscs.co.uk - 01484 855800
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Encoding error during database restore
@ 2021-11-05 05:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Paul Smith <paul@pscs.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2021-11-05 05:14 UTC (permalink / raw)
To: Paul Smith <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-11-05 05:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-03 21:03 Encoding error during database restore Marc Fromm <frommm@wwu.edu>
2021-11-04 13:21 ` Karl Denninger <karl@denninger.net>
2021-11-04 15:58 ` Paul Smith <paul@pscs.co.uk>
2021-11-05 05:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
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