Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I’m trying to set up backup strategy for a production postgreSQL DB. It will have large amount of data and it should be running 24 X 7. Could you recommend some backup & recovery strategies that can meet the following criteria?

  • Large amount data (about over 200GB).
  • Hot backup (online backup)
  • Minimum Impact on the DB performance.
  • Minimum restore time.
  • Allow PITR(Point-In-Time Recovery)
    1. Can we execute backup with above criteria on slave server of replication?
    2. If you know backup strategy using storage snapshot, Could you let me know?
share|improve this question
1  
the usual setup is (if I'm not mistaken) to create a slave using streaming replication and then to the backups on the slave (to minimize impact on the master). You might want to look at pgBarman and repmgr –  a_horse_with_no_name Feb 20 at 12:02
    
thanx @ a-horse-with-no-name I will look into it and then let you know. –  user234918 Feb 20 at 12:08
1  
Copy and pasted to serverfault.com/q/577033/102814. Please do not waste others' time on duplicating your questions between sites. Pick one. Most people will use tag searches that find questions across all of stack exchange anyway. –  Craig Ringer Feb 20 at 13:16

1 Answer 1

It sounds like you'd be best suited by using a physical base backup + WAL archiving, with regularly updated snapshots of the base backup. I strongly recommend taking regular dumps anyway.

Using newer PostgreSQL versions (9.2 and up, IIRC) you can take fresh base backups from a replica server so you don't have to disrupt the master.

File-system or logical volume level snapshot backups work fine with PostgreSQL so long as your snapshots are atomic. Restoring one is like starting PostgreSQL back up after unexpected power loss or OS reboot, not a big deal.

See also:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.