Re: pg_dump / pg_restore option

On 7/8/20 12:27 PM, Luiz Hugo Ronqui wrote:
> 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?

Why not just restore the dump file as is and then partition the tables?

>
> Thanks in advance!
>
> Regards
>
> Luiz Hugo Ronqui
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com