Re: pg_dump --comment?

On May 28, 2004, at 10:48 AM, Andrew Dunstan wrote:

> A better answer to this particular problem might be incremental dumps,
> though :-)
>

Oh would incremental dumps be ever so hungrily accepted by ever so many
shops. I had imagined that PITR transaction log archiving would allow
one to perform an equivalent to "repay initial full data dump", then
foreach transaction log, replay. If no writes have occurred, then the
transaction log would be small / nongrowing, right?

For example, we perform a full dump hourly, scp to a backup db box who
then imports it in its entirety, giving us, on average, a half-hour's
worth of potential data lossage at any given moment in time if the
primary box goes horribly bad. With current database size / machine
speed, etc., this process takes ~8 minutes, so we're not sweating it.
But we know that the vast majority of the data did not change in the
past hour, so the majority of that work was not truly necessary.

With PITR log archiving, could we kick off this transfer + replay
activity on the second box to ultimately just ship deltas? I suspect
that we could not get such fixed-time guarantees anymore, since any
individual transaction log file would not become full until, well,
enough writes have occurred to push it over the edge, so our 'data
hanging in the wind not yet on the backup box' becomes a function of
'most recent N Kbytes of changed data'. By lowering transaction segment
size (or whatever controls the size of individual transaction log
files) we could force archiving to occur more / less frequently
according to write traffic, right? Poor man's (very) async replication.

If all of this is correct, kudos to the forward-thinkers involved with
PITR and the abstract archive transaction log protocol. If my proposed
usage is flawed, then negative kudos to my puny mind.

----
James Robinson
Socialserve.com