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