Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rtF6Y-00DPc6-Q6 for pgsql-general@arkaria.postgresql.org; Sat, 06 Apr 2024 23:05:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rtF6W-000Y4W-S7 for pgsql-general@arkaria.postgresql.org; Sat, 06 Apr 2024 23:05:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rtF6W-000Y3z-Fj for pgsql-general@lists.postgresql.org; Sat, 06 Apr 2024 23:05:44 +0000 Received: from docker01.sa.apana.org.au ([203.14.158.64] helo=mail.sa.apana.org.au) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rtF6M-001BDN-2K for pgsql-general@lists.postgresql.org; Sat, 06 Apr 2024 23:05:40 +0000 From: Tony Bazeley Authentication-Results: mail.sa.apana.org.au; auth=pass smtp.mailfrom=tonyb@tonyb.id.au To: pgsql-general@lists.postgresql.org Subject: pg_dumpall - restoration problem Date: Sun, 07 Apr 2024 08:35:15 +0930 Message-ID: <3625230.PrqTZ78sOi@apn53> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="nextPart47754494.dbBhxLGFpU" Content-Transfer-Encoding: 7Bit X-Spamd-Bar: -- List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --nextPart47754494.dbBhxLGFpU Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="utf-8" 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 --nextPart47754494.dbBhxLGFpU Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

I've a problem with restoring a cluster created with pg_dump_all from 14= =2E8

( p= g_dumpall >pgall.out and then psql -f pgall.out postgres).

pga= ll.out was recovered after a hardware failure on the hosting machine.

Att= empting to restore to postgresql-16 results in errors

.

.

.

psql:/tmp/pgall.out:5172242: error: inval= id command \N
psql:/tmp/pgall.out:5172243: error: inval= id command \N
psql:/tmp/pgall.out:5172244: error: invalid command \N =
psql:/tmp/pgall.out:5172245: error: invalid command \N
psql:/tm= p/pgall.out:5172246: error: invalid command \N
psql:/tmp/pgall.out:51= 72247: error: invalid command \N
psql:/tmp/pgall.out:5172248: error: = invalid command \N
psql:/tmp/pgall.out:5172249: error: invalid comman= d \N
psql:/tmp/pgall.out:5660594: error: out of memory
Which str= ikes 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 r= ead in and the error below appears in the log file

 

2024-= 04-05 22:17:15.418 ACDT [6565] postgres@tonbaz ERROR: =C2=A0collation "= ;pg_catalog.C.UTF-8" for encoding "UTF8" does not exist at c= haracter 366

202= 4-04-05 22:17:15.418 ACDT [6565] postgres@tonbaz STATEMENT: =C2=A0CREATE TA= BLE public.bug_line (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0id integer NOT NULL,
=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0routenm character varying(254),
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0type ch= aracter varying(254),
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0status character varying(254),
=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0the_geom public.geometry= (LineString,28354),
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0category text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0code text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0src text,
=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0name text,
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0timing text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0refplan2= 015 integer,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0comments text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0descrip text,
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0class text COLLATE pg_catalog."C.U= TF-8"
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0);

= =C2=A0Thinking it might be something specifi= c to postgres16 I installed a copy of the postgres14  (14.11) with fai= lure in exactly the same way.

I don't understand the class text COLLATE pg_catalog."C.UTF-8"=   syntax, but

sel= ect * from pg_collation shows a C.UTF8 but no C.UTF-8


Any help on how to proceed would be most appreciated


TIA

Ton= y Bazeley




--nextPart47754494.dbBhxLGFpU--