[MASSMAIL]pg_dumpall - restoration problem - Mailing list pgsql-general

From Tony Bazeley
Subject [MASSMAIL]pg_dumpall - restoration problem
Date
Msg-id 3625230.PrqTZ78sOi@apn53
Whole thread Raw
Responses Re: pg_dumpall - restoration problem
List 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@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




pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Moving delta data faster
Next
From: Tom Lane
Date:
Subject: Re: pg_dumpall - restoration problem