libc to libicu via pg_dump/pg_restore?

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

Hi Guillaume,

On 6 Feb 2025, at 11:13, Guillaume Lelarge <guillaume.lelarge@dalibo.com> wrote:

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.

This is intended because the dump contains a create database statement which creates the database with libc which is exactly what I do NOT want. I want it to be a libicu database. So I pre-create it as such and inhibit recreation by pg_restore by sitting on it with a session. So the first message about the database not being created is expected and can be ignored. This works fine for all databases so far.

My problem is the constraint violation which inhibits the foreign key contraints from being created.

Everything works for all databases. Only this one has that problem. And since I disabled triggers during restore, that shouldn't be a problem either.

Btw., the parent table contains the rows in question. So they are imported. I just can't make out why there is a problem.

Cheers,
Paul

Hi Paul,

On 06/02/2025 12:20, Paul Foerster wrote:

Hi Guillaume,

On 6 Feb 2025, at 11:13, Guillaume Lelarge <guillaume.lelarge@dalibo.com> wrote:

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.

This is intended because the dump contains a create database statement which creates the database with libc which is exactly what I do NOT want. I want it to be a libicu database. So I pre-create it as such and inhibit recreation by pg_restore by sitting on it with a session. So the first message about the database not being created is expected and can be ignored. This works fine for all databases so far.

You're right. Now I see the "create database" query in your previous
email. I should have been more careful, sorry for the noise.

My problem is the constraint violation which inhibits the foreign key contraints from being created.

Everything works for all databases. Only this one has that problem. And since I disabled triggers during restore, that shouldn't be a problem either.

Well, the doc says that --disable-triggers is only relevant for
data-only restore, which is not your use case. So you don't need it and
it won't help you.

Btw., the parent table contains the rows in question. So they are imported. I just can't make out why there is a problem.

Me neither. But another comment. You create the database, so there
should be no objects in it. Why do you use the -c, -C, and --if-exists
options? Try without them. On a new database, you should only need:

pg_restore -d mydb mydb.dump.gz

Less options, less weird behaviours.

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

On 2/6/25 01:04, Paul Foerster wrote:

Hi,

Comments inline.

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

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

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 "...".

Is dokument_id an integer field?

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);

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?

Thanks in advance.

Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Guillaume,

On 6 Feb 2025, at 15:51, Guillaume Lelarge <guillaume.lelarge@dalibo.com> wrote:

You're right. Now I see the "create database" query in your previous email. I should have been more careful, sorry for the noise.

No problem.

Well, the doc says that --disable-triggers is only relevant for data-only restore, which is not your use case. So you don't need it and it won't help you.

Yes, I found that out too. But it doesn't hurt. 🤣

Me neither. But another comment. You create the database, so there should be no objects in it. Why do you use the -c, -C, and --if-exists options? Try without them. On a new database, you should only need:

pg_restore -d mydb mydb.dump.gz

I need -C because I need ACLs to be recreated too. I tried with -C only, i.e. no -c but that doesn't work for some reason. The --if-exists is a script remnant of my past tries to suppress some messages. I'll try removing that as I rewrote my create database script which runs before importing.

Cheers,
Paul

On 2/6/25 09:37, Paul Foerster wrote:

Hi Guillaume,

On 6 Feb 2025, at 15:51, Guillaume Lelarge <guillaume.lelarge@dalibo.com> wrote:

You're right. Now I see the "create database" query in your previous email. I should have been more careful, sorry for the noise.

No problem.

Well, the doc says that --disable-triggers is only relevant for data-only restore, which is not your use case. So you don't need it and it won't help you.

Yes, I found that out too. But it doesn't hurt. 🤣

Me neither. But another comment. You create the database, so there should be no objects in it. Why do you use the -c, -C, and --if-exists options? Try without them. On a new database, you should only need:

pg_restore -d mydb mydb.dump.gz

I need -C because I need ACLs to be recreated too. I tried with -C only, i.e. no -c but that doesn't work for some reason. The --if-exists is a script remnant of my past tries to suppress some messages. I'll try removing that as I rewrote my create database script which runs before importing.

By ACL do you mean roles?

If so roles are global to the cluster not the database, so I am not
seeing -C being relevant.

If not you will need to be more specific about what you are referring to.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

On 6 Feb 2025, at 17:31, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

1) Log into postgres database and do:

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

2) pg_restore -d mydb mydb.dump.gz

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.

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 "...".

Is dokument_id an integer field?

Yes, it's a bigint.

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?

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

Hi Adrian,

On 6 Feb 2025, at 19:44, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

By ACL do you mean roles?

If so roles are global to the cluster not the database, so I am not seeing -C being relevant.

If not you will need to be more specific about what you are referring to.

I did a "pg_dumpall -r >roles.sql" on the originale database cluster and "psql -f roles.sql" on the new database cluster. So, roles are pre-created as is necessary.

No, I mean ACLs, like in "Access privileges" when doing a "\l".

Cheers,
Paul

Paul Foerster wrote:

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 "...".

Is dokument_id an integer field?

Yes, it's a bigint.

It's hard to imagine that the change of collation is related to the
failure to create that constraint.

When a value is present in the target table but the FK check does not
find it, often the cause is index corruption.
But if you've just imported that dump, the index on the target column
should be brand new.
Still, you may check it with pg_amcheck [1]https://www.postgresql.org/docs/current/app-pgamcheck.html or try rebuilding it
just in case.

[1]: https://www.postgresql.org/docs/current/app-pgamcheck.html

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/

I'm not sure why we are focused on the other errors - the database fails to
get dropped (or created), so future errors are to be expected.

pg_restore should be run with the --exit-on-error flag, and handle the
errors one by one as someone mentioned upthread.

I would use the --section=pre-data --section=data and --section=post-data
flags to pg_dump to create (for the pre-data) a small and editable file so
you can tweak the CREATE DATABASE as you please.

pg_dump mydb --clean --create --section=pre-data --file=mydb.pre.pg

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

On 2/7/25 06:53, Greg Sabino Mullane wrote:

I'm not sure why we are focused on the other errors - the database fails
to get dropped (or created), so future errors are to be expected.

It is already created before the pg_restore is run.

The OP says it works for ~40 other databases they are upgrading using
the same process.

Cheers,
Greg

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2/6/25 22:32, Paul Foerster wrote:

Hi Adrian,

On 6 Feb 2025, at 17:31, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

1) Log into postgres database and do:

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

2) pg_restore -d mydb mydb.dump.gz

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.

That does not make sense.

Are there ACLs(privileges) in the database at all?

What is the pg_dump command you are running?

Cheers
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Daniel,

On 7 Feb 2025, at 14:29, Daniel Verite <daniel@manitou-mail.org> wrote:

Still, you may check it with pg_amcheck [1] or try rebuilding it
just in case.

Thanks. I guess this is good advice. I will try that on Monday.

Cheers,
Paul

Hi Adrian,

sorry for the late answer. I'm just too busy.

On 7 Feb 2025, at 17:19, Adrian Klaver <adrian.klaver@aklaver.com> 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.

That does not make sense.

Are there ACLs(privileges) in the database at all?

What is the pg_dump command you are running?

I use this pg_dump command:

pg_dump -Fc -Z1 --quote-all-identifiers -b "${PGDATABASE}" -f ${dumpBase}/${clusterName}.${PGDATABASE}.dump.gz >${PGDATABASE}.out 2>${PGDATABASE}.err &

The command is embedded in a Bash script for loop that loops PGDATABASE over all database names inside the cluster and launches pg_dump as a background job. It then waits for all jobs to complete ("wait" command). dumpBase is just the destination directory.

If I don't use -cC, i.e. both, then the Access privileges will not be restored. Checking with \l just shows an empty field as usual for a newly created database. This happens at least with 17.2. I didn't check that with 17.3 yet.

I agree, from how I understood the docs I should be able to only use -C and not -c.

As for the data inconsistency, PostgreSQL is right. I found out that some clever person did a "alter table … disable trigger all" on a table and then manipulated data. That broke referential integrity. So, this case is closed.

Cheers,
Paul

On 2/13/25 08:17, Paul Foerster wrote:

Hi Adrian,

sorry for the late answer. I'm just too busy.

On 7 Feb 2025, at 17:19, Adrian Klaver <adrian.klaver@aklaver.com> 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.

That does not make sense.

Are there ACLs(privileges) in the database at all?

What is the pg_dump command you are running?

I use this pg_dump command:

pg_dump -Fc -Z1 --quote-all-identifiers -b "${PGDATABASE}" -f ${dumpBase}/${clusterName}.${PGDATABASE}.dump.gz >${PGDATABASE}.out 2>${PGDATABASE}.err &

The command is embedded in a Bash script for loop that loops PGDATABASE over all database names inside the cluster and launches pg_dump as a background job. It then waits for all jobs to complete ("wait" command). dumpBase is just the destination directory.

If I don't use -cC, i.e. both, then the Access privileges will not be restored. Checking with \l just shows an empty field as usual for a newly created database. This happens at least with 17.2. I didn't check that with 17.3 yet.

Per:

https://www.postgresql.org/docs/current/ddl-priv.html

"If the “Access privileges” column is empty for a given object, it means
the object has default privileges (that is, its privileges entry in the
relevant system catalog is null). Default privileges always include all
privileges for the owner, and can include some privileges for PUBLIC
depending on the object type, as explained above. The first GRANT or
REVOKE on an object will instantiate the default privileges (producing,
for example, miriam=arwdDxt/miriam) and then modify them per the
specified request. Similarly, entries are shown in “Column privileges”
only for columns with nondefault privileges. (Note: for this purpose,
“default privileges” always means the built-in default privileges for
the object's type. An object whose privileges have been affected by an
ALTER DEFAULT PRIVILEGES command will always be shown with an explicit
privilege entry that includes the effects of the ALTER.)"

From this:

1) It not unusual for the field to be blank.

2) \l only lists the privileges for the database object itself, not any
of it's contained objects.

In the original database are you executing explicit GRANTs on the
database object?

Do:

pg_restore -s -f db_name.sql ${PGDATABASE}.out

This will create a text version restore of the schema objects in the
dump file. Then search the file for GRANT statements.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

On 13 Feb 2025, at 17:40, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Per:

https://www.postgresql.org/docs/current/ddl-priv.html

"If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, miriam=arwdDxt/miriam) and then modify them per the specified request. Similarly, entries are shown in “Column privileges” only for columns with nondefault privileges. (Note: for this purpose, “default privileges” always means the built-in default privileges for the object's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)"

From this:

1) It not unusual for the field to be blank.

2) \l only lists the privileges for the database object itself, not any of it's contained objects.

In the original database are you executing explicit GRANTs on the database object?

Do:

pg_restore -s -f db_name.sql ${PGDATABASE}.out

This will create a text version restore of the schema objects in the dump file. Then search the file for GRANT statements.

${PGDATABASE}.out and ${PGDATABASE}.err are just log files of the backgrounded pg_dump command. The .out file is empty anyway and the .err file only contains the messages about the database being not be able to be dropped (-c) because I sit on it, which is explainable and correct.

What I mean is, in our environment there are four (application) roles having certain privileges, one of them being the database owner while the others have certain rights like reading or manipulating data, but no DDL. These four roles all have their privileges shown with \l in the access privileges column. Contrary to how I understand the documentation of pg_restore, they are restored only if I use -cC and they are not restored if I only use -C.

Cheers,
Paul

On 2/13/25 09:31, Paul Foerster wrote:

Hi Adrian,

On 13 Feb 2025, at 17:40, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Per:

https://www.postgresql.org/docs/current/ddl-priv.html

"If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, miriam=arwdDxt/miriam) and then modify them per the specified request. Similarly, entries are shown in “Column privileges” only for columns with nondefault privileges. (Note: for this purpose, “default privileges” always means the built-in default privileges for the object's type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)"

From this:

1) It not unusual for the field to be blank.

2) \l only lists the privileges for the database object itself, not any of it's contained objects.

In the original database are you executing explicit GRANTs on the database object?

Do:

pg_restore -s -f db_name.sql ${PGDATABASE}.out

This will create a text version restore of the schema objects in the dump file. Then search the file for GRANT statements.

${PGDATABASE}.out and ${PGDATABASE}.err are just log files of the backgrounded pg_dump command. The .out file is empty anyway and the .err file only contains the messages about the database being not be able to be dropped (-c) because I sit on it, which is explainable and correct.

Then run pg_restore -s -f db_name.sql against whatever is the dump file
produced by pg_dump -Fc -Z1 ...

It will create a plain text version of the schema definitions, no data
in the file db_name.sql. Then you can see if GRANTs are being done.

What I mean is, in our environment there are four (application) roles having certain privileges, one of them being the database owner while the others have certain rights like reading or manipulating data, but no DDL. These four roles all have their privileges shown with \l in the access privileges column. Contrary to how I understand the documentation of pg_restore, they are restored only if I use -cC and they are not restored if I only use -C.

From here:

https://www.postgresql.org/docs/current/app-psql.html

\l[+] or \list[+] [ pattern ]

List the databases in the server and show their names, owners,
character set encodings, and access privileges. If pattern is specified,
only databases whose names match the pattern are listed. If + is
appended to the command name, database sizes, default tablespaces, and
descriptions are also displayed. (Size information is only available for
databases that the current user can connect to.)

This only shows the information the actual database object not the
objects contained within it.

You will need to show your work:

1) What does \l show in the cluster you are dumping from?

2) What are the roles and what privileges are they being granted?

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

On 13 Feb 2025, at 19:05, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Then run pg_restore -s -f db_name.sql against whatever is the dump file produced by pg_dump -Fc -Z1 ...

It will create a plain text version of the schema definitions, no data in the file db_name.sql. Then you can see if GRANTs are being done.

I think, we're not talking about the same thing. I'm talking about access privileges on the database, i.e. connect, create, etc. Without a connect privilege, no schema privileges are relevant in the first place.

This only shows the information the actual database object not the objects contained within it.

Yes, this is what I am referring to, the access privileges on the database, not objects.

You will need to show your work:
1) What does \l show in the cluster you are dumping from?
2) What are the roles and what privileges are they being granted?

I'm not at work anymore and won't be until Monday (long weekend 🤣). So I don't have the exact case handy. However, I tried on my home database clusters (15.10 and 17.3). Seems, at least here at home, only using -C works. I don't know (yet) why it does not work at work.

Here's what I tried on my own clusters. Note the access privileges for "paul".

Source DB PostgreSQL 15.10
--------------------------

postgres=# \l mydb
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =Tc/postgres +
| | | | | | | postgres=CTc/postgres+
| | | | | | | paul=CTc/postgres
(1 row)

$ export PGDATABASE=mydb
$ pg_dump -Fc -Z1 --quote-all-identifiers -b "${PGDATABASE}" -f ${PGDATABASE}.dump.gz

No output, no error messages. Everything is fine.

Target DB PostgreSQL 17.3
-------------------------

postgres=# create role paul login;
CREATE ROLE
postgres=# create database mydb template template0;
CREATE DATABASE
postgres=# \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 | |
(1 row)

$ pg_restore -C -d mydb mydb.dump.gz
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: warning: errors ignored on restore: 1

postgres=# \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 | | =Tc/postgres +
| | | | | | | | postgres=CTc/postgres+
| | | | | | | | paul=CTc/postgres
(1 row)

So, "paul" again has CTc after pg_restore. That's what does not work at work. I'll have to figure out what's wrong there.

Cheers,
Paul

On 2/13/25 11:57, Paul Foerster wrote:

Hi Adrian,

I'm not at work anymore and won't be until Monday (long weekend 🤣). So I don't have the exact case handy. However, I tried on my home database clusters (15.10 and 17.3). Seems, at least here at home, only using -C works. I don't know (yet) why it does not work at work.

Here's what I tried on my own clusters. Note the access privileges for "paul".

So, "paul" again has CTc after pg_restore. That's what does not work at work. I'll have to figure out what's wrong there.

1) Verify the GRANT is in the dump file.

2) Check the Postgres log manually to see if there is an error that is
causing the GRANT to be skipped.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com