pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dumpall - restoration problem
2+ messages / 2 participants
[nested] [flat]
* pg_dumpall - restoration problem
@ 2024-04-06 23:05 Tony Bazeley <tonyb@tonyb.id.au>
0 siblings, 1 reply; 2+ messages in thread
From: Tony Bazeley @ 2024-04-06 23:05 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I've a problem with restoring a cluster created with pg_dump_all from 14.8
( pg_dumpall >pgall.out and then psql -f pgall.out postgres).
pgall.out was recovered after a hardware failure on the hosting machine.
Attempting to restore to postgresql-16 results in errors
.
.
.
psql:/tmp/pgall.out:5172242: error: invalid command \N
psql:/tmp/pgall.out:5172243: error: invalid command \N
psql:/tmp/pgall.out:5172244: error: invalid command \N
psql:/tmp/pgall.out:5172245: error: invalid command \N
psql:/tmp/pgall.out:5172246: error: invalid command \N
psql:/tmp/pgall.out:5172247: error: invalid command \N
psql:/tmp/pgall.out:5172248: error: invalid command \N
psql:/tmp/pgall.out:5172249: error: invalid command \N
psql:/tmp/pgall.out:5660594: error: out of memory
Which strikes me as a bit strange as it's a lightly loaded 32GB machine and a 8.5GB dump
file
On examination, all tables up to the table bug_line below appear to be read in and the
error below appears in the log file
2024-04-05 22:17:15.418 ACDT [6565] postgres@tonbaz ERROR: collation
"pg_catalog.C.UTF-8" for encoding "UTF8" does not exist at character 366
2024-04-05 22:17:15.418 ACDT [6565] postgres@tonbaz STATEMENT: CREATE TABLE
public.bug_line (
id integer NOT NULL,
routenm character varying(254),
type character varying(254),
status character varying(254),
the_geom public.geometry(LineString,28354),
category text,
code text,
src text,
name text,
timing text,
refplan2015 integer,
comments text,
descrip text,
class text COLLATE pg_catalog."C.UTF-8"
);
Thinking it might be something specific to postgres16 I installed a copy of the
postgres14 (14.11) with failure in exactly the same way.
I don't understand the class text COLLATE pg_catalog."C.UTF-8" syntax, but
select * from pg_collation shows a C.UTF8 but no C.UTF-8
Any help on how to proceed would be most appreciated
TIA
Tony Bazeley
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_dumpall - restoration problem
@ 2024-04-07 01:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tony Bazeley <tonyb@tonyb.id.au>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-04-07 01:05 UTC (permalink / raw)
To: Tony Bazeley <tonyb@tonyb.id.au>; +Cc: pgsql-general@lists.postgresql.org
Tony Bazeley <tonyb@tonyb.id.au> writes:
> I've a problem with restoring a cluster created with pg_dump_all from 14.8
> ( pg_dumpall >pgall.out and then psql -f pgall.out postgres).
> ...
> Attempting to restore to postgresql-16 results in errors
> 2024-04-05 22:17:15.418 ACDT [6565] postgres@tonbaz ERROR: collation
> "pg_catalog.C.UTF-8" for encoding "UTF8" does not exist at character 366
> I don't understand the class text COLLATE pg_catalog."C.UTF-8" syntax, but
> select * from pg_collation shows a C.UTF8 but no C.UTF-8
I take it you are trying to restore onto a different OS platform with
different locale naming conventions. The easiest way to deal with it
probably is to edit the dump file and change "C.UTF-8" to "C.UTF8"
everywhere. (Manually editing an 8G dump file might be no fun, but
"sed" should make short work of it.)
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-07 01:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-06 23:05 pg_dumpall - restoration problem Tony Bazeley <tonyb@tonyb.id.au>
2024-04-07 01:05 ` Tom Lane <tgl@sss.pgh.pa.us>
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