Thread: HOT standby on windows not working

Hi All;

We're setting up a HOT standby on Windows 2000 server and PostgreSQL 9.2

We do this:

1) stop both servers

2) copy the master data directory to the slave

3) edit the master postgresql.conf file as follows:
Modify the following listen_address = ‘*’
wal_level = hot_standby
max_wal_senders = 3


4) edit the master pg_hba.conf file and add this line:
host replication all 192.168.91.136/32 trust

where 192.168.91.136 is the IP of the slave


5) edit the postgresql.conf file on the slave as follows:
hot_standby = on


6) create a recovery.conf with the following contents:
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.91.165’

where 192.168.91.165 is the IP of the master

7) start the standby

8) start the master


The standby simply comes online, almost like it is ignoring the
recovery.conf file.
Here's a tail of the log from the slave when we start it:

2014-04-10 15:45:24 MDT [3756]: [1-1] LOG: database system was
interrupted; last known up at 2014-04-10 16:36:17 MDT
2014-04-10 15:45:24 MDT [3756]: [2-1] LOG: database system was not
properly shut down; automatic recovery in progress
2014-04-10 15:45:24 MDT [3756]: [3-1] LOG: record with zero length at
0/8000080
2014-04-10 15:45:24 MDT [3756]: [4-1] LOG: redo is not required
2014-04-10 15:45:24 MDT [3592]: [1-1] LOG: database system is ready to
accept connections
2014-04-10 15:45:24 MDT [3572]: [1-1] LOG: autovacuum launcher started



Any thoughts why this is not working?

I've also tried this approach:


1) Master postgresql.conf file
Modify the following settings:
listen_address = ‘*’
wal_level = hot_standby
max_wal_senders = 3


2) Modify Master pg_hba.conf file:
hostssl replication al 192.168.91.136/32 trust

3) RESTART MASTER DATABASE

4) Slave postgresql.conf file
hot_standby = on

5) Create a recovery.conf file on the slave as follows:
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.91.165’


6) Execute a ‘pg_start_backup’ on the master.
SELECT pg_start_backup (‘date’, true);

7) Copy the data files to the standby.
a) Copied the master data directory to the slave desktop
b) removed postgresql.conf and pg_hba.conf from the data directory (the
copy on the slave desktop)
c) removed the pg_xlog dir from the data directory (the copy on the
slave desktop)


8) Execute a ‘pg_stop_backup’ on the master.
SELECT pg_stop_backup ();

9) Copy the Write Ahead Log (WAL) files (the data/pg_xlog directory)
from the master to the standby

10) start the standby database

We get the same behavior (i.e. the slave comes fully online, not just
into recovery / standby mode) no matter which approach we use...

Thoughts?

Thanks in advance...

On Fri, Apr 11, 2014 at 8:15 AM, CS_DBA <cs_dba@consistentstate.com> wrote:
> Hi All;
>
> We're setting up a HOT standby on Windows 2000 server and PostgreSQL 9.2
>
> We do this:
> I've also tried this approach:
>
>
> 1) Master postgresql.conf file
> Modify the following settings:
> listen_address = '*'
> wal_level = hot_standby
> max_wal_senders = 3
>
>
> 2) Modify Master pg_hba.conf file:
> hostssl replication al 192.168.91.136/32 trust
>
> 3) RESTART MASTER DATABASE

Use the pg_basebackup utility to take the backup directory and change
the the conf files.

> 4) Slave postgresql.conf file
> hot_standby = on
>
> 5) Create a recovery.conf file on the slave as follows:
> standby_mode = 'on'
> primary_conninfo = 'host=192.168.91.165'
>
> 6) start the standby database

Try with the above approach.

Regards,
Hari Babu
Fujitsu Australia