On Sat, Dec 30, 2017 at 09:19:05AM -0500, Robert Blayzor wrote:
> On Dec 30, 2017, at 12:38 AM, Justin Pryzby <pry...@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..