Received-SPF: pass (loft7616.startdedicated.de: connection is authenticated)
Date: Fri, 29 Mar 2019 06:42:52 +0100
In-Reply-To: 
 <CACaZr5SFcUNkRBGwNLRnoCD46RwpAoLTsgm09E9yKO9P2=vTZQ@mail.gmail.com>
References: 
 <CACaZr5SFcUNkRBGwNLRnoCD46RwpAoLTsgm09E9yKO9P2=vTZQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=utf-8
Content-Transfer-Encoding: quoted-printable
Subject: Re: Archival process of partition tables with filtering few rows from
 tables.
To: pgsql-sql@lists.postgresql.org,github kran
 <githubkran@gmail.com>,pgsql-general
 <pgsql-general@postgresql.org>,pgsql-sql@postgresql.org
From: Andreas Kretschmer <andreas@a-kretschmer.de>
Message-ID: <304BE2C2-7064-4AD7-99CE-C0723D648E1D@a-kretschmer.de>
Precedence: bulk

On 29 March 2019 05:13:31 CET, github kran <githubkran@gmail=2Ecom> wrote:
>Hello Team,
>
>We are using PostgreSQL Version 9=2E6 and planning to archive our
>partition
>tables containing about 300 - 500 million rows =2E We have around ~ 50
>partition tables to be archived to a new
>cold path PostgreSQL database , version 10=2E6=2E

Consider Version 11 instead, much better features for partitioning=2E


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

Yeah, consider logical replication using pg_logical from us with row filte=
ring=2E


Regards, Andreas


--=20
2ndQuadrant - The PostgreSQL Support Company