Re: PoC: pg_dump --filter-data <file> (like Oracle Where Clause on RMAN for specific tables)

Kirk Wolak <wolakk@gmail.com>

From: Kirk Wolak <wolakk@gmail.com>

To: Tom Lane <tgl@sss.pgh.pa.us>

Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Andrey Borodin <x4mmm@yandex-team.ru>, Nikolay Samokhvalov <nik@postgres.ai>, Pavel Stehule <pavel.stehule@gmail.com>, Pavlo Golub <Pavlo.Golub@cybertec.at>

Date: 2025-08-16T22:48:41Z

Lists: pgsql-hackers

On Fri, Aug 15, 2025 at 12:37 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Kirk Wolak <wolakk@gmail.com> writes:
> >   We have a handful of tables that have 90% of the data going back 30
> years.
> > We NEVER restore this data to Dev or Staging.  We used a special RMAN
> > backup where these tables had a "WHERE clause" applied to them during the
> > backup/dump process.
>
> Have you considered partitioning these tables by date and then not
> dumping the older partitions?  That would fit into existing
> functionality a lot better.
>

>                         regards, tom lane
>

I had not considered it until now.  But because we are talking about only
keeping 7 - 28 days of decades of data.
I would assume that window would require a bit of work in production to
maintain, making it a tough sell to the client.