Khusro Jaleel <mailing-lists(at)kerneljack(dot)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