pg_dump and pg_restore and foreign keys

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

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

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

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