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.klaver@aklaver.com