Re: Enquiry about setting Up PostgreSQL 8.4 PIRT

On Friday, January 22, 2016 03:39:37 PM Caesar Olima wrote:
> Dear all,
>
> We are using Postgres 8.4 in Windows environment in our setting. I have been
> tasked with setting up a Postgres PITR. Having gone through the
> documentation, I have bumped into certain issues that hasn't made my setup
> sucessful. What I have done is setup 2 servers. First, I modified main
> server's postgresql.conf by enabling archive_mode and puttiing the command
> to perform the actual archiving, and this is all good.
>
> I have further created a windows scheduled task that copies the archived WAL
> files to a remote server(the Postgres slave server). I have also installed
> Grsync on the Master server to help me copy files from main server to slave
> even as the main is being used, as previous copy command on Windows cmd
> always displayed errors of along the lines of "cannot access file, file
> being used by another process".
>
> After running select pg_start_backup('Master_Base_Backup); on the main
> server, I launch Grsync to copy the main server's data dir to the slave
> server. When this process completes, I run select pg_stop_backup(); on the
> main server again.
>
> I then copy all the data from Master server to the slave's data dir
> (deleting all files in pg_xlog and postmaster.pid and modifying
> postmaster.conf appropriately). I have created the needed recovery.conf (as
> mentioned on the documentation). On restarting the slave, the server
> restarts and hopefully restores the WAL files, I say hopefully because, NO
> more WAL files are copied from the archive directory to pg_xlog on the
> slave. And on creating a table in the Master server, it is not reflected on
> the slave server.

You seem to be confusing two concepts.

A PITR backup is a way to generate a good backup of a running database that
can later be restored to a consistent state for data recovery purposes.

Doing that restoration does not automatically setup ongoing replication.

You can use a PITR base backup as the starting point for replication, but you
also need to do other things on the slave - at a minimum setting standby_mode
= on, and setting trigger_file, both in recovery.conf, in order to get that
working. If you want streaming replication, and you probably do, there are
additional changes on both the master and slave needed to support that.

You also seem to indicate that you're doing two separate copies of the master
to the slave, which is probably wrong, but that might just be unclear
communication.

Unfortunately I can't help you specifically with the commands needed to make
this work on Windows, but hopefully if you separate the restore concept and
the replication concepts you can figure out where things went wrong.