Archival process of partition tables with filtering few rows from tables.
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. 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 ?.
Appreciate your replies.
Thanks.
On 29 March 2019 05:13:31 CET, github kran <githubkran@gmail.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