pg_dumpall - restoration problem

From: Tony Bazeley <tonyb(at)tonyb(dot)id(dot)au>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_dumpall - restoration problem
Date: 2024-04-06 23:05:15
Message-ID: 3625230.PrqTZ78sOi@apn53
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)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(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2024-04-07 01:05:44 Re: pg_dumpall - restoration problem
Previous Message Adrian Klaver 2024-04-06 20:55:16 Re: Moving delta data faster