Обсуждение: pg_dump and pg_restore and foreign keys
Hi,
I hope this is the correct place for this question.
I'm trying to restore a database where adding foreign key constraints takes most of the time. Does there exist a simple way to make either pg_dump or pg_restore handle them as "not valid", and defer the validation.
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> Tore Halvorsen || +052 0553034554
<demo> Tore Halvorsen || +052 0553034554
Tore Halvorsen <tore.halvorsen@gmail.com> writes:
> I'm trying to restore a database where adding foreign key constraints takes
> most of the time. Does there exist a simple way to make either pg_dump or
> pg_restore handle them as "not valid", and defer the validation.
No. It's kind of a neat idea perhaps, but it's not there. You could
* use parallel pg_restore to alleviate the pain, or
* use pg_restore's -l and -L switches to strip out the FKs altogether,
and then re-add them manually afterwards.
Or there's always
* hack the pg_dump source code to include NOT VALID. While a real
feature patch that made this optional would be a bit complicated,
doing it unconditionally should be a one-line change.
regards, tom lane
That would be appending it for "pg_catalog.pg_get_constraintdef(oid) AS condef" in getConstraints in pg_dump.c?
On Wed, Oct 27, 2021 at 3:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tore Halvorsen <tore.halvorsen@gmail.com> writes:
> I'm trying to restore a database where adding foreign key constraints takes
> most of the time. Does there exist a simple way to make either pg_dump or
> pg_restore handle them as "not valid", and defer the validation.
No. It's kind of a neat idea perhaps, but it's not there. You could
* use parallel pg_restore to alleviate the pain, or
* use pg_restore's -l and -L switches to strip out the FKs altogether,
and then re-add them manually afterwards.
Or there's always
* hack the pg_dump source code to include NOT VALID. While a real
feature patch that made this optional would be a bit complicated,
doing it unconditionally should be a one-line change.
regards, tom lane
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> Tore Halvorsen || +052 0553034554
<demo> Tore Halvorsen || +052 0553034554
Tore Halvorsen <tore.halvorsen@gmail.com> writes:
> That would be appending it for "pg_catalog.pg_get_constraintdef(oid) AS
> condef" in getConstraints in pg_dump.c?
No, you want to mess with the text printed by dumpConstraint().
regards, tom lane
Then I'll try that, thank you :)
On Wed, Oct 27, 2021 at 4:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tore Halvorsen <tore.halvorsen@gmail.com> writes:
> That would be appending it for "pg_catalog.pg_get_constraintdef(oid) AS
> condef" in getConstraints in pg_dump.c?
No, you want to mess with the text printed by dumpConstraint().
regards, tom lane
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> Tore Halvorsen || +052 0553034554
<demo> Tore Halvorsen || +052 0553034554
In case someone else needs to do this - I ended up with this change in pg_dump.c around lines 17080-17090
I guess a proper solution would check the already invalid foreign keys in a different way.
appendPQExpBuffer(q, " ADD CONSTRAINT %s %s%s;\n",
fmtId(coninfo->dobj.name),
coninfo->condef
, dopt->foreign_keys_not_valid && !strstr(coninfo->condef, "NOT VALID") ? " NOT VALID":"");
fmtId(coninfo->dobj.name),
coninfo->condef
, dopt->foreign_keys_not_valid && !strstr(coninfo->condef, "NOT VALID") ? " NOT VALID":"");
On Wed, Oct 27, 2021 at 4:08 PM Tore Halvorsen <tore.halvorsen@gmail.com> wrote:
Then I'll try that, thank you :)On Wed, Oct 27, 2021 at 4:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:Tore Halvorsen <tore.halvorsen@gmail.com> writes:
> That would be appending it for "pg_catalog.pg_get_constraintdef(oid) AS
> condef" in getConstraints in pg_dump.c?
No, you want to mess with the text printed by dumpConstraint().
regards, tom lane
--Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> Tore Halvorsen || +052 0553034554
--
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
<demo> Tore Halvorsen || +052 0553034554
<demo> Tore Halvorsen || +052 0553034554