pg_dump / pg_restore option

From: "Luiz Hugo Ronqui" <lronqui(at)tce(dot)sp(dot)gov(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Subject: pg_dump / pg_restore option
Date: 2020-07-08 19:27:47
Message-ID: !&!AAAAAAAAAAAYAAAAAAAAAN2bnnMKn0ZDlzDJ3VbPTBvCgAAAEAAAAGcjPr+cx2tFjXjkW6/kPzgBAAAAAA==@tce.sp.gov.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all!

I'm experimenting with options to upgrade databases from older (v9.x) to
more recent (probably v11) versions of PostgreSql and, partitioning some
large tables during the process.

The idea was:

1) To make a dump of the old database, wich I've done with the custom
format

2) To restore the section "pre-data" on the new instalation

3) To make the desired changes, creating the partitions structures
(quite simple: by ranges of "year" columns)

4) To restore the section "data"

5) To restore the section "post-data"

It all went well until step 5, but the creation of FOREIGN KEYS on the
partitioned tables raised errors, because of the "ALTER TABLE ONLY <name>
ADD CONSTRAINT..." syntax of pg_dump output file.

As it is needed that the partitions do exist in the previous step, that
"ONLY" clause creates a situation that needs some more scripts to overcome.

I've checked that the pg_dump v11 generates different versions of commands
for partitioned and non-partitioned tables.

Is there a parameter to suppress that keyword, or some other option I coud
use?

Thanks in advance!

Regards

Luiz Hugo Ronqui

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-07-08 19:34:58 Re: pg_dump / pg_restore option
Previous Message Thomas Kellerer 2020-07-08 17:07:20 Re: Is this a bug in pg_current_logfile() on Windows?