Date: Mon, 14 Jan 2008 10:55:30 -0500 From: "David Robillard" <david.robillard@gmail.com> To: Albert.Shih@obspm.fr Cc: FreeBSD Questions <freebsd-questions@freebsd.org> Subject: Re: How backup huge pgsql ? Message-ID: <226ae0c60801140755h154f0a96r1f50ab88a02a7193@mail.gmail.com>
next in thread | raw e-mail | index | archive | help
> 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 installing here. 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. HTH, David -- David Robillard UNIX systems administrator & Oracle DBA CISSP, RHCE & Sun Certified Security Administrator Montreal: +1 514 966 0122
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?226ae0c60801140755h154f0a96r1f50ab88a02a7193>