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

On 29 March 2019 05:13:31 CET, github kran <githubkran(at)gmail(dot)com> 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