public inbox for [email protected]
help / color / mirror / Atom feedRe: libc to libicu via pg_dump/pg_restore?
5+ messages / 3 participants
[nested] [flat]
* Re: libc to libicu via pg_dump/pg_restore?
@ 2025-02-06 11:20 Paul Foerster <[email protected]>
2025-02-06 14:51 ` Re: libc to libicu via pg_dump/pg_restore? Guillaume Lelarge <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Foerster @ 2025-02-06 11:20 UTC (permalink / raw)
To: Guillaume Lelarge <[email protected]>; +Cc: [email protected]
Hi Guillaume,
> On 6 Feb 2025, at 11:13, Guillaume Lelarge <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-06 11:20 Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
@ 2025-02-06 14:51 ` Guillaume Lelarge <[email protected]>
2025-02-06 17:37 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Guillaume Lelarge @ 2025-02-06 14:51 UTC (permalink / raw)
To: [email protected]
Hi Paul,
On 06/02/2025 12:20, Paul Foerster wrote:
> Hi Guillaume,
>
>> On 6 Feb 2025, at 11:13, Guillaume Lelarge <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-06 11:20 Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-06 14:51 ` Re: libc to libicu via pg_dump/pg_restore? Guillaume Lelarge <[email protected]>
@ 2025-02-06 17:37 ` Paul Foerster <[email protected]>
2025-02-06 18:44 ` Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Foerster @ 2025-02-06 17:37 UTC (permalink / raw)
To: Guillaume Lelarge <[email protected]>; +Cc: [email protected]
Hi Guillaume,
> On 6 Feb 2025, at 15:51, Guillaume Lelarge <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-06 11:20 Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-06 14:51 ` Re: libc to libicu via pg_dump/pg_restore? Guillaume Lelarge <[email protected]>
2025-02-06 17:37 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
@ 2025-02-06 18:44 ` Adrian Klaver <[email protected]>
2025-02-07 06:35 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-02-06 18:44 UTC (permalink / raw)
To: Paul Foerster <[email protected]>; Guillaume Lelarge <[email protected]>; +Cc: [email protected]
On 2/6/25 09:37, Paul Foerster wrote:
> Hi Guillaume,
>
>> On 6 Feb 2025, at 15:51, Guillaume Lelarge <[email protected]> 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
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: libc to libicu via pg_dump/pg_restore?
2025-02-06 11:20 Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-06 14:51 ` Re: libc to libicu via pg_dump/pg_restore? Guillaume Lelarge <[email protected]>
2025-02-06 17:37 ` Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-06 18:44 ` Re: libc to libicu via pg_dump/pg_restore? Adrian Klaver <[email protected]>
@ 2025-02-07 06:35 ` Paul Foerster <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Paul Foerster @ 2025-02-07 06:35 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Guillaume Lelarge <[email protected]>; [email protected]
Hi Adrian,
> On 6 Feb 2025, at 19:44, Adrian Klaver <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-07 06:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-06 11:20 Re: libc to libicu via pg_dump/pg_restore? Paul Foerster <[email protected]>
2025-02-06 14:51 ` Guillaume Lelarge <[email protected]>
2025-02-06 17:37 ` Paul Foerster <[email protected]>
2025-02-06 18:44 ` Adrian Klaver <[email protected]>
2025-02-07 06:35 ` Paul Foerster <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox