Re: Archival process of partition tables with filtering few rows from tables.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: Archival process of partition tables with filtering few rows from tables.

Andreas Kretschmer-3
On 29 March 2019 05:13:31 CET, github kran <[hidden email]> wrote:
>Hello Team,
>
>We are using PostgreSQL Version 9.6 and planning to archive our
>partition
>tables containing about 300 - 500 million rows . We have around ~ 50
>partition tables to be archived to a new
>cold path PostgreSQL database , version 10.6.

Consider Version 11 instead, much better features for partitioning.


 We have a requirement to

>filter few rows before exporting this data from these tables as we dont
>want to archive those rows .
>
> What is a better approach to export and restore these tables ?.
>
>- Does COPY Command with a filter query to filter few rows using select
>   works better ?.
>   - pg_dump with filtering these rows ?.
> - Can i able to export my indexes , check constraints , constraints ?.
>   - Any other options ?.

Yeah, consider logical replication using pg_logical from us with row filtering.



Regards, Andreas


--
2ndQuadrant - The PostgreSQL Support Company