Khusro Jaleel <mailing-li...@kerneljack.com> wrote:
> On 02/15/2012 12:58 PM, Vladimir Rusinov wrote:
>>
>> pg_dump won't block writes, thanks to MVCC. It may increase bloat
>> and it will block DDL operations (ALTER TABLE/etc), but if your
>> database is relatively small but have high load and you need
>> frequent backups, this may be a way to go.
 
> Thanks Vladimir. Would a simple script with 'pg_start_backup' and 
> 'pg_stop_backup' and an rsync job or tar job in between would
> work equally well? I thought that was the better way to do it,
> rather than pg_dump?
 
The PITR style backup you describe doesn't cause bloat or block DDL,
and if you archive the WAL files you can restore to any point in
time following the pg_stop_backup.  pg_dump just gives you a
snapshot as of the start of the dump, so if you use that you would
need to start a complete dump every 30 minutes.  With PITR backups
and WAL archiving you could set your archvie_timeout to force timely
archiving (or use streaming replication if you are on 9.0 or later)
and effectively dump incremental database *activity* to stay
up-to-date.
 
Now, if 30 minutes of activity is more than the size of the
database, pg_dump could, as Vladimir says, still be a good
alternative.
 
-Kevin

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Reply via email to