HOT standby on windows not working

From: CS_DBA <cs_dba(at)consistentstate(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: HOT standby on windows not working
Date: 2014-04-10 22:15:01
Message-ID: 534717E5.20400@consistentstate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Crawford 2014-04-10 23:40:44 Re: openssl heartbleed
Previous Message Jan Wieck 2014-04-10 21:50:24 Re: Linux vs FreeBSD