Message-ID: <2256966c-446f-4607-a47d-04b895b0747b@dalibo.com>
Date: Thu, 6 Feb 2025 11:13:12 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: libc to libicu via pg_dump/pg_restore?
To: pgsql-general@lists.postgresql.org
References: <B0DB872D-B2A0-4323-9EA8-A7FB4567ACCB@gmail.com>
Content-Language: fr
From: Guillaume Lelarge <guillaume.lelarge@dalibo.com>
Organization: Dalibo
In-Reply-To: <B0DB872D-B2A0-4323-9EA8-A7FB4567ACCB@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/2256966c-446f-4607-a47d-04b895b0747b%40dalibo.com>
Precedence: bulk

Hi,

On 06/02/2025 10:04, Paul Foerster wrote:
> 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.
> 

You probably don't need --disable-triggers. You should fix errors in the 
order they appear. The first one is on the drop of the database:

ERROR:  cannot drop the currently open database

pg_restore can't drop the database because it's connected to the 
database. When you use -c and -C options, you can't connect to the 
database you want to restore to. You have to connect to another 
database, such as postgres, so that it can do the drop and the create. 
After both are done, it will connect to the just-created database to do 
the restore step.

Look at the pg_restore man page 
(https://www.postgresql.org/docs/current/app-pgrestore.html). It says on 
the --create option:

When this option is used, the database named with -d is used only to 
issue the initial DROP DATABASE and CREATE DATABASE commands. All data 
is restored into the database name that appears in the archive.

Regards.


-- 
Guillaume Lelarge
Consultant
https://dalibo.com