Subject: libc to libicu via pg_dump/pg_restore?

From: Paul Foerster <[email-masked]>
Date: Thu, 6 Feb 2025 10:04:21 +0100

Hi,

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

instance a, libc based, PostgreSQL 15.10:

mydb=# \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 = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = '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)=(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)=(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

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 6 Feb 2025 08:31:24 -0800

> Hi,

Comments inline.

>   Name |  Owner  | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges

I would start by doing:

1) Log into postgres database and do:

  a) DROP DATABASE mydb;
  b) CREATE DATABASE mydb <options>;

2) pg_restore -d mydb mydb.dump.gz

> DETAIL:  Key (dokument_id)=(1000033680) is not present in table "...".

Is dokument_id an integer field?

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

In a follow post you said:

"Everything works for all databases. Only this one has that problem."

Do you mean you made the same libc --> icu change on the other databases 
with no errors?

-- 
Adrian Klaver
[email-masked]

---

From: Paul Foerster <[email-masked]>
Date: Fri, 7 Feb 2025 07:32:28 +0100

Hi Adrian,

> On 6 Feb 2025, at 17:31, Adrian Klaver <[email-masked]> wrote:
> 

With create database <options> being "template template0", this is what my script does. But I need the -cC options for pg_restore to get ACLs back. Leaving out either one of them will not get me the ACLs back.

> Is dokument_id an integer field?

Yes, it's a bigint.

> Do you mean you made the same libc --> icu change on the other databases with no errors?

Yes, I have that PostgreSQL 15.10 cluster with locale provider libc with about 40 databases. I initdb'ed a new PostgreSQL 17.2 cluster with icu as locale provider and did a "create database ... template template0" for all about 40 databases. Then I did the mentioned pg_restore for each of them as a parallel background job in the shell.

The whole database cluster is about 1.2 TB in size so I have to find ways to restore as many databases in parallel as possible. However pg_restore only fails on this single database. All others in that database cluster work fine.

Cheers
Paul