Re: [GENERAL] PostgreSQL Point In Time Recovery
Alan Hodgson wrote Well, yeah. The point was that you possibly could run it for a while to catch up without taking a new base backup if you desired. You should also keep copies of it for PITR. Something like this - delayed replication http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html might help. I could say lag by 12 hours, or 1 transactions... -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775997.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
Before going through something like delayed replication, you really want to consider using zfs or lvm and taking regular snapshots on your hot or warm standby. In the event of the accidental table drop, you can just roll back to the snapshot prior and then do PITR from there. Greg Haase On Fri, Oct 25, 2013 at 11:14 PM, Jayadevan maymala.jayade...@gmail.comwrote: Alan Hodgson wrote Well, yeah. The point was that you possibly could run it for a while to catch up without taking a new base backup if you desired. You should also keep copies of it for PITR. Something like this - delayed replication http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html might help. I could say lag by 12 hours, or 1 transactions... -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775997.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
Jeff Janes wrote I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. Cheers, Jeff That makes sense. So we take a new base backup once we feel Hey , recovery may take time. Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775872.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
Alan Hodgson wrote That's basically what warm standby's do, isn't it? As long as they keep recovery open it should work. A warn standby will be almost in sync with the primary, right? So recovery to point-in-time (like 10 AM this morning) won't be possible. We need a base, but it shouldn't be so old that it takes hours to catchup- that was my thought. As John mentioned, looking at the WAL/transaction numbers, time to recover etc need to be looked at. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775874.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
On Thursday, October 24, 2013 11:13:34 PM Jayadevan wrote: Alan Hodgson wrote That's basically what warm standby's do, isn't it? As long as they keep recovery open it should work. A warn standby will be almost in sync with the primary, right? So recovery to point-in-time (like 10 AM this morning) won't be possible. We need a base, but it shouldn't be so old that it takes hours to catchup- that was my thought. As John mentioned, looking at the WAL/transaction numbers, time to recover etc need to be looked at. Well, yeah. The point was that you possibly could run it for a while to catch up without taking a new base backup if you desired. You should also keep copies of it for PITR. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
On Wed, Oct 23, 2013 at 9:10 PM, Jayadevan M maymala.jayade...@gmail.comwrote: Hi, I went through http://www.postgresql.org/docs/9.3/static/continuous-archiving.html and set up the archiving process. With this approach, if my database crashes after a couple of weeks after the base backup is taken, recovering would mean replaying the WAL logs for about 2 weeks, right? To avoid that, what is the standard process followed - take a base backup every day or once a week? Regards, Jayadevan I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. Cheers, Jeff
Re: [GENERAL] PostgreSQL Point In Time Recovery
On 10/24/2013 9:47 AM, Jeff Janes wrote: I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. hey, silly idea formed on half a cup of coffee if that base backup is in the form of a copy of the data directory (as opposed to tar.gz or something), could you 'update' it by pointing an instance of postgres at it, and then playing the WAL archive into it, then shutting that instance down? or would it be impossible to synchronize the ongoing new WAL's from the master with the timeline of this? -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Point In Time Recovery
On Thursday, October 24, 2013 11:31:38 AM John R Pierce wrote: On 10/24/2013 9:47 AM, Jeff Janes wrote: I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. hey, silly idea formed on half a cup of coffee if that base backup is in the form of a copy of the data directory (as opposed to tar.gz or something), could you 'update' it by pointing an instance of postgres at it, and then playing the WAL archive into it, then shutting that instance down? or would it be impossible to synchronize the ongoing new WAL's from the master with the timeline of this? That's basically what warm standby's do, isn't it? As long as they keep recovery open it should work. You can also use rsync to take your base backup - just update the rsync copy. That's what I do (and keep a separate tarball of that rsync copy, made on the backup host). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Point In Time Recovery
Hi, I went through http://www.postgresql.org/docs/9.3/static/continuous-archiving.html and set up the archiving process. With this approach, if my database crashes after a couple of weeks after the base backup is taken, recovering would mean replaying the WAL logs for about 2 weeks, right? To avoid that, what is the standard process followed - take a base backup every day or once a week? Regards, Jayadevan
Re: [GENERAL] PostgreSQL Point In Time Recovery
On 10/23/2013 9:10 PM, Jayadevan M wrote: I went through http://www.postgresql.org/docs/9.3/static/continuous-archiving.html and set up the archiving process. With this approach, if my database crashes after a couple of weeks after the base backup is taken, recovering would mean replaying the WAL logs for about 2 weeks, right? To avoid that, what is the standard process followed - take a base backup every day or once a week? before you decide on what interval is appropriate for new base backups, its probably a good idea to test how long the restore actually takes on your hardware, with your WAL volume... then decide. restoring the base backup files takes some time, then playing the WAL takes more time, you need to decide what your acceptable tolerance for restore time is, and decide on backup intervals appropriately. I like to keep two sets of base backups with their WAL logs, so I wait til the newest is done before purging the one done two intervals ago. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
