Re: How backup huge pgsql ?
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 N1 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 its 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 ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: How backup huge pgsql ?
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 N1 hours. Any idea about this ? pg_dump should work as usual. No need to stop database access since read-only access won't block anything else and it won't be blocked by other processes. That said, pg_dump:ing a database that is 2 terrabytes in size is going to take some significant time. If you want to maintain frequent backups you may want to look into using point in time recovery and WAL archiving. See: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html Also the very long transaction used for the backup will prevent vacuuming from freeing tuples for the duration of the backup. If you have tables that rely on very frequent vacuuming for performance, those may be affected. -- / Peter Schuller PGP userID: 0xE9758B7D or 'Peter Schuller [EMAIL PROTECTED]' Key retrieval: Send an E-Mail to [EMAIL PROTECTED] E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org signature.asc Description: This is a digitally signed message part.
Re: How backup huge pgsql ?
On Thu, Jan 10, 2008 at 11:10:58PM +0100, Albert Shih wrote: Hi all 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 N1 hours. Any idea about this ? Take a look at the PostgreSQL web site - they have a section in the docs dedicated to this subject, detailing several different strategies. I'm sure one of them will fit! http://www.postgresql.org/docs/8.2/static/backup.html (If you're running a diffrent version of PostgreSQL, check out the relevant section of the docs - accessible from http://www.postgresql.org/docs/manuals/) Dan -- Daniel Bye _ ASCII ribbon campaign ( ) - against HTML, vCards and X - proprietary attachments in e-mail / \ pgpFFxdXeXc0q.pgp Description: PGP signature
How backup huge pgsql ?
Hi all 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 N1 hours. Any idea about this ? Regards. -- Albert SHIH Observatoire de Paris Meudon SIO batiment 15 Heure local/Local time: Jeu 10 jan 2008 23:05:00 CET ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
