Thread: Using PITR for creating Hot Standby
I am in a situation where we have to deploy a hot standby to a postgres db server. There is no custom tablespace and all data files are in $PGDATA I was thinking of using PITR []http://www.postgresql.org/docs/8.1/static/backup-online.html] to achieve that and here are my thoughts: 1. Continuously copy WAL files to the standby. 2. The standby is always in "recovery" mode, that is whenever it gets a WAL file, it recovers to that WAL file. 3. Always copy the "incomplete WAL" file to a different location. This is the "current" file. When the standby is activated, it checks to see if it has any complete WAL file to recover from? And if it has, it recovers to that file. At the end it recovers to the incomplete current file and the standby is now "recovered" up to a point in time. My concerns are as follows? 1. Is the above feasible? 2. What are the gotchas if somebody has already done that? 3. Is there something I can do more efficiently? I looked at SLONY and it can back up only tables with primary keys. The current schema on the primary does not have all the tables with primary keys and we cannot change the schema! Thanks in advance Dhaval Shah
Dhaval Shah wrote: > I am in a situation where we have to deploy a hot standby to a > postgres db server. There is no custom tablespace and all data files > are in $PGDATA > > I was thinking of using PITR > []http://www.postgresql.org/docs/8.1/static/backup-online.html] to > achieve that and here are my thoughts: Same here. > > 1. Continuously copy WAL files to the standby. Right. > 2. The standby is always in "recovery" mode, that is whenever it gets > a WAL file, it recovers to that WAL file. This I decided against, only because traffic does not warrant it. The idea at this point was to let the WAL files pile up in the offsite location and then work out a periodic schedule for running them, but it would be more like once/week than continuously. My thinking was that maintaining a 60-second failover is not really what I'm after. If the main server goes down, we would likely wait and hope for up to 10 minutes before starting to make DNS changes. During that time we could always run a recovery of the WAL files and have the standby easily ready in time.
Attachment
On 2/13/07, Dhaval Shah <dhaval.shah.m@gmail.com> wrote: > I am in a situation where we have to deploy a hot standby to a > postgres db server. There is no custom tablespace and all data files > are in $PGDATA > > I was thinking of using PITR > []http://www.postgresql.org/docs/8.1/static/backup-online.html] to > achieve that and here are my thoughts: > > 1. Continuously copy WAL files to the standby. > 2. The standby is always in "recovery" mode, that is whenever it gets > a WAL file, it recovers to that WAL file. > 3. Always copy the "incomplete WAL" file to a different location. This > is the "current" file. > > When the standby is activated, it checks to see if it has any complete > WAL file to recover from? And if it has, it recovers to that file. At > the end it recovers to the incomplete current file and the standby is > now "recovered" up to a point in time. > > My concerns are as follows? > > 1. Is the above feasible? > 2. What are the gotchas if somebody has already done that? > 3. Is there something I can do more efficiently? > > I looked at SLONY and it can back up only tables with primary keys. > The current schema on the primary does not have all the tables with > primary keys and we cannot change the schema! google pg_standby. it is up and coming contrib module that does exactly what you want. transferring wal files from 'a' to 'b' can be as simple as nfs mount, scp, you name it. merlin