incremental backup of postgres database? - Mailing list pgsql-admin

From Palle Girgensohn
Subject incremental backup of postgres database?
Date
Msg-id 83540000.1044632843@rambutan.pingpong.net
Whole thread Raw
List pgsql-admin
Hi!

What would be the best suggestion for incremental backup of a rather large
database, where the bulk data volume consists of large objects. Since
backup will be transmitted over a 2 Mbit/s internet line, we need to
minimize the data flow for each nightly backup. The compressed database
dump file, when dumped with pg_dump -F c -b, is roughly 2.5 GB, whereas a
dump without large objects is roughly is only 2% that size. I can live with
having to transfer the BLOB-less dump every night, but not several
gigabytes of data...

So, I will either need to find  a way to get the latest data (I have
timestamps for all LOBS) and somehow get it to a file in a restorable
format... One simple way would be to select all new blobs into a temp file
and copy that table to a backup file

or

replicate the database in real time to the backup site, using one of the
replication projects? How robust are the replication systems today? What
will happen if the 2Mb/s line fails temporarily?

Perhaps there are other ideas for incremental backup of postgres databases?
Your input would be much appreciated.

Thanks
Palle


pgsql-admin by date:

Previous
From: Jerry Asher
Date:
Subject: Re: Sql Management Tool to download
Next
From: Leslie
Date:
Subject: noatime.