From: Paul Foerster <paul.foerster@gmail.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\))
Subject: libc to libicu via pg_dump/pg_restore?
Message-Id: <B0DB872D-B2A0-4323-9EA8-A7FB4567ACCB@gmail.com>
Date: Thu, 6 Feb 2025 10:04:21 +0100
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/B0DB872D-B2A0-4323-9EA8-A7FB4567ACCB%40gmail.com>
Precedence: bulk

Hi,

I have a problem which I don't understand. I have and do:


instance a, libc based, PostgreSQL 15.10:

mydb=3D# \l mydb
                                                          List of =
databases
 Name |  Owner  | Encoding |   Collate   |    Ctype    | ICU Locale | =
Locale Provider | Access privileges
=
------+---------+----------+-------------+-------------+------------+-----=
------------+-------------------
 mydb | my_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | =
libc            |

$ pg_dump -Fc -Z1 -b mydb -f mydb.dump.gz
$ ls -l mydb.dump.gz
-rw------- 1 postgres postgres 14660308577 Feb  6 08:45 mydb.dump.gz


instance b, libicu based, PostgreSQL 17.2:
$ psql postgres

# create database mydb;
# \l mydb
                                                              List of =
databases
 Name |   Owner  | Encoding | Locale Provider |   Collate   |    Ctype   =
 | Locale | ICU Rules | Access privileges
=
------+----------+----------+-----------------+-------------+-------------=
+--------+-----------+-------------------
 mydb | postgres | UTF8     | icu             | en_US.UTF-8 | =
en_US.UTF-8 | en-US  |           |

$ pg_restore -cC --if-exists --disable-triggers -d mydb mydb.dump.gz
pg_restore: error: could not execute query: ERROR:  cannot drop the =
currently open database
Command was: DROP DATABASE IF EXISTS mydb;
pg_restore: error: could not execute query: ERROR:  database "mydb" =
already exists
Command was: CREATE DATABASE mydb WITH TEMPLATE =3D template0 ENCODING =3D=
 'UTF8' LOCALE_PROVIDER =3D libc LOCALE =3D 'en_US.UTF-8';


pg_restore: error: could not execute query: ERROR:  insert or update on =
table "table_1" violates foreign key constraint "..._fk"
DETAIL:  Key (dokument_id)=3D(1000033680) is not present in table "...".
Command was: ALTER TABLE ONLY myschema.table
   ADD CONSTRAINT table_fk FOREIGN KEY (dokument_id) REFERENCES =
myschema.dokument(id);


pg_restore: error: could not execute query: ERROR:  insert or update on =
table "table_2" violates foreign key constraint "..._fk"
DETAIL:  Key (dokument_id)=3D(1000033740) is not present in table =
"dokument".
Command was: ALTER TABLE ONLY vostra2_str.nen_dokument
   ADD CONSTRAINT table_fk FOREIGN KEY (dokument_id) REFERENCES =
myschema.dokument(id);


I'm sorry, I sort of had to anonymize object names. But you should be =
able to get the gist of it. It's a dreaded message when importing. My =
goal is to export libc PostgreSQL 15 databases and import them into =
PostgreSQL 17 as libicu based databases to get away from glibc based =
sorting. I searched the net to find the "--disable-triggers" disable =
triggers when running pg_restore but the errors still occur.

What am I doing wrong or how can I better achieve that? Any help would =
be appreciated.

Thanks in advance.

Paul=