Thread: Clarification on pg_basebackup

PostgreSQL 9.3

Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created?  There are two scenarios.  The first scenario listed below makes sense using the –X option.  Bus scenario #2 has me a little confused.  I don’t want to cause any issues when restoring.

1)      Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter.  When a failure occurs you restore the database to that exact point in time.

2)      You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup.  Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last?  Or does it matter.  Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup? 

Thanks for your clarification.  This is a hard scenario to test.

Thanks,

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

PostgreSQL 9.3

Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created?  There are two scenarios.  The first scenario listed below makes sense using the –X option.  Bus scenario #2 has me a little confused.  I don’t want to cause any issues when restoring.

1)      Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter.  When a failure occurs you restore the database to that exact point in time.

2)      You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup.  Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last?  Or does it matter.  Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup? 

Thanks for your clarification.  This is a hard scenario to test.

Thanks,

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

Jason,

Thanks so much.  That makes sense.  That was what I was trying to communicate.  OPIT vs APIT. 

So if I am doing a APIT I can do a pg_basebackup each night without the –X option.  I save all WAL files.  If I need to restore during the day I then only have to restore from the last basebackup and apply all of the WAL files from that time forward.  Did I get that correct?    

On September 23, 2014 at 9:18:37 AM, Campbell, Lance (lance@illinois.edu) wrote:

PostgreSQL 9.3

Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created?  There are two scenarios.  The first scenario listed below makes sense using the –X option.  Bus scenario #2 has me a little confused.  I don’t want to cause any issues when restoring.

 

1)      Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter.  When a failure occurs you restore the database to that exact point in time.

2)      You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup.  Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last?  Or does it matter.  Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup? 

 

Thanks for your clarification.  This is a hard scenario to test.

 

Thanks,

 

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

 

 

This transmission contains confidential and privileged information intended solely for the party identified above. If you receive this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the sender at (303) 386-3955 or by return e-mail to the sender.

Jason,

Thanks so much.  That makes sense.  That was what I was trying to communicate.  OPIT vs APIT. 

So if I am doing a APIT I can do a pg_basebackup each night without the –X option.  I save all WAL files.  If I need to restore during the day I then only have to restore from the last basebackup and apply all of the WAL files from that time forward.  Did I get that correct?    

On September 23, 2014 at 9:18:37 AM, Campbell, Lance (lance@illinois.edu) wrote:

PostgreSQL 9.3

Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created?  There are two scenarios.  The first scenario listed below makes sense using the –X option.  Bus scenario #2 has me a little confused.  I don’t want to cause any issues when restoring.

 

1)      Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter.  When a failure occurs you restore the database to that exact point in time.

2)      You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup.  Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last?  Or does it matter.  Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup? 

 

Thanks for your clarification.  This is a hard scenario to test.

 

Thanks,

 

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

 

 

This transmission contains confidential and privileged information intended solely for the party identified above. If you receive this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the sender at (303) 386-3955 or by return e-mail to the sender.