Hi all,
I am very new to psql wanted to implement streaming replication on Windows 2008 64 bit Servers installed with PSQL 9.1. Unfortunately the standby server not even showing anything in the log regarding the replication as below:
standby log:
2012-02-27 19:30:23 MYT LOG: database system was interrupted; last known up at 2012-02-27 19:28:08 MYT
2012-02-27 19:30:23 MYT LOG: database system was not properly shut down; automatic recovery in progress
2012-02-27 19:30:23 MYT LOG: consistent recovery state reached at 0/21000058
2012-02-27 19:30:23 MYT LOG: redo starts at 0/21000020
2012-02-27 19:30:23 MYT LOG: record with zero length at 0/210000B0
2012-02-27 19:30:23 MYT LOG: redo done at 0/21000058
2012-02-27 19:30:23 MYT LOG: database system is ready to accept connections
2012-02-27 19:30:24 MYT LOG: autovacuum launcher started
2012-02-27 19:30:50 MYT LOG: incomplete startup packet
2012-02-27 19:35:51 MYT LOG: incomplete startup packet
2012-02-27 19:40:52 MYT LOG: incomplete startup packet
2012-02-27 19:45:53 MYT LOG: incomplete startup packet
2012-02-27 19:50:54 MYT LOG: incomplete startup packet
2012-02-27 19:55:55 MYT LOG: incomplete startup packet
primary log:
2012-02-27 19:13:02 MYT LOG: database system was shut down at 2012-02-27 19:13:01 MYT
2012-02-27 19:13:02 MYT LOG: database system is ready to accept connections
2012-02-27 19:13:02 MYT LOG: autovacuum launcher started
please check my configuration once, may be i am wrong somewhere..
1. Configured PSQL on primary server as below.
Primary:
pg_hba.conf:
host replication all 10.1.18.0/24 trust
host all all 10.1.18.0/24 trust
postgresql.conf:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy %p D:\\temp\\Obi_TempFiles\\DB_Stream_Share\\%f'
max_wal_senders = 5
wal_keep_segments = 5
The above D:\\temp\\Obi_TempFiles\\DB_Stream_Share\\ location is shared and can be accessed by the standby server (everyone has been given permissions).
2. Started backup using select pg_start_backup('back');
archived the data folder from primary and then copied to Standby server data folder. removed postmaster.pid and contents of pg_xlog (only files and kept the archive_status folder inside).
Stopped the backup using select pg_stop_backup();
3. Configured standby server as below:
pg_hba.conf:
same as above
Standby:
postgresql.conf:
wal_level = hot_standby
#archive_mode = on
#archive_command = 'copy %p D:\\temp\\Obi_TempFiles\\DB_Stream_Share\\%f'
#max_wal_senders = 5
#wal_keep_segments = 5
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
recovery.conf: in share folder
restore_command = 'copy \\\\10.1.18.16\\DB_Stream_Share\\%f %p'
standby_mode = 'on'
primary_conninfo = 'host=10.1.18.16 port=5432 user=postgres'
trigger_file = 'D:\\temp\\Ob-TempFiles\\Failover.txt'
I am able to see the WAL archives in the shared folder but the standby server is not able to start replication at all, I tried by checking another way the replication status in standby server
command : select pg_last_xlog_receive_location() returned 0 rows.
select pg_last_xlog_replay_location() returned 0 rows
I suspect that in the standby server streaming replication hasnt started at all...
Have spent nearly 2 days on knowing all these .. please help.. :)
View this message in context: PSQL 9.1 Streaming Replication Windows 2008 64 bit Servers
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
