Re: Using PITR for creating Hot Standby

On 2/13/07, Dhaval Shah <dhaval(dot)shah(dot)m(at)gmail(dot)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