Re: pg_dump / pg_restore option - Mailing list pgsql-general

From Adrian Klaver
Subject Re: pg_dump / pg_restore option
Date
Msg-id 0418b5a3-255a-a11c-bff3-1f48aa5878ab@aklaver.com
Whole thread Raw
In response to pg_dump / pg_restore option  ("Luiz Hugo Ronqui" <lronqui@tce.sp.gov.br>)
List pgsql-general
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



pgsql-general by date:

Previous
From: "Luiz Hugo Ronqui"
Date:
Subject: pg_dump / pg_restore option
Next
From: Brajendra Pratap Singh
Date:
Subject: Kerberos-Postgresql implementation for user authentication