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