Hi Paul,
On 06/02/2025 12:20, Paul Foerster wrote:
Hi Guillaume,
On 6 Feb 2025, at 11:13, Guillaume Lelarge <guillaume.lela...@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
