From: Justin Pryzby <pryzby@telsasoft.com>
To: Robert Blayzor <rblayzor.bulk@inoc.net>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: partitioning an existing table - efficient pg_dump
Date: Sat, 30 Dec 2017 12:42:47 -0600
Message-ID: <20171230184247.GH4172@telsasoft.com> (raw)
In-Reply-To: <942C6568-41A7-46EC-B391-D523CC36B3AB@inoc.net>
References: <D8C4F7BD-FECE-4ABB-9B16-5A764ADBD5DF@inoc.net>
<20171230053821.GE4172@telsasoft.com>
<942C6568-41A7-46EC-B391-D523CC36B3AB@inoc.net>
On Sat, Dec 30, 2017 at 09:19:05AM -0500, Robert Blayzor wrote:
> On Dec 30, 2017, at 12:38 AM, Justin Pryzby <pryzby@telsasoft.com> wrote:
> > BTW depending on your requirements, it may be possible to make pg_dump much
> > more efficient. For our data, it's reasonable to assume that a table is
> > "final" if its constraints exclude data older than a few days ago, and it can
> > be permanently dumped and excluded from future, daily backups, which makes the
> > backups smaller and faster, and probably causes less cache churn, etc. But I
> > imagine you might have different requirements, so that may be infeasible, or
> > you'd maybe have to track insertions, either via p
>
> The idea is only only keep a # of months available for searching over a period of months. Those months could be 3 or more, up to a year, etc. But being able to just drop and entire child table for pruning is very attractive. Right now the average months data is about 2-3 million rows each. Data is just inserted and then only searched. Never updated…
>
> I also like the idea of skipping all this older data from a PGdump. We archive records inserted into these tables daily into cold storage. ie: export and compressed. So the data is saved cold. We dump the DB nightly also, but probably would make sense to skip anything outside of the newest child table. Just not sure how to make that happen, yet….
For us, I classify the tables as "partitioned" or "not partitioned" and
subdivide "partitioned" into "recent" or "historic" based on table names; but
if you design it from scratch then you'd have the opportunity to keep a list of
partitioned tables, their associated date range, date of most recent insertion,
and most recent "final" backup.
This is the essence of it:
snap= ... SELECT pg_export_snapshot();
pg_dump --snap "$snap" -T "$ptnreg" -f nonpartitioned.new
pg_dump --snap "$snap" -t "$recent" -f recent.new
loop around historic partitioned tables and run "final" pg_dump if it's been
INSERTed more recently than it's been dumped.
remove any "final" pg_dump not included in any existing backup (assuming you
keep multiple copies on different rotation).
Note that pg_dump -t/-T is different from "egrep" in a few special ways..
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, rblayzor.bulk@inoc.net, pgsql-performance@lists.postgresql.org
Subject: Re: partitioning an existing table - efficient pg_dump
In-Reply-To: <20171230184247.GH4172@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox