How backup huge pgsql ?
david.robillard at gmail.com
Mon Jan 14 07:55:32 PST 2008
> I want to known how can I make backup of huge postgresql database (huge mean ~ 2To).
> I can stop the access of the database during N>>1 hours.
> Any idea about this ?
I came around this particular problem by setting up a read only mirror
of an Oracle instance using Oracle DataGuard.
Of course the product is Oracle-specific, but the idea should apply to
PostgreSQL databases as well and it"s what we're in the process of
The idea is to setup an identical but read-only copy of the production
database on a seperate machine.
This read-only copy is kept in sync with the production database using
the various PostgreSQL High-Availability features (discussed here
postgresql.org/docs/8.2/static/high-availability.html) Such as a
Master-Slave Replication or a Synchronous Multi-Master Replication.
Say you're using a Master-Slave Replication. With this setup, you can
stop the Master-Slave replication before running the backup on the
read-only copy on the slave machine. This way you have a consistent
view of your data while you backup and the production database is
still online. Once your backup is over, you simply turn on the
replication again to update your slave's data with what has changed on
the master while the replication was offline. Simple and effective.
Beware, you will take a performance hit when you turn replication on.
What's more, since you now have a read-only database, you can use it
in your pre-production and test environments without any impact on
your production systems.
UNIX systems administrator & Oracle DBA
CISSP, RHCE & Sun Certified Security Administrator
Montreal: +1 514 966 0122
More information about the freebsd-questions