Обсуждение: PSQL 9.1 Streaming Replication Windows 2008 64 bit Servers
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.
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.
On Monday, February 27, 2012 5:34:12 am chinnaobi wrote: > 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: > > please check my configuration once, may be i am wrong somewhere.. > > > recovery.conf: in share folder On thing I see is above. recovery.conf should be in data directory for standby. Questions: The clusters are running on separate machines? Outside of the replication mechanism can you access each cluster from the other? In other words a user on the standby machine can log into the primary cluster and/or a user on the primary can log into the standby cluster? To check the status of replication you can use the system view pg_stat_replication: http://www.postgresql.org/docs/9.1/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE -- Adrian Klaver adrian.klaver@gmail.com
Hi Adrian, Thanks for the reply streaming is working after placing the recovery.conf in standby data folder. I add an extra configuration in the recovery.conf : archive_cleanup_command='pg_archivecleanup \\\\10.1.18.16\\DB_Stream_Share\\ %r' The standby server log file says error as below : 2012-02-28 10:59:01 MYT FATAL: the database system is starting up 2012-02-28 10:59:01 MYT LOG: database system was shut down in recovery at 2012-02-28 10:58:59 MYT 2012-02-28 10:59:01 MYT LOG: entering standby mode 2012-02-28 10:59:01 MYT LOG: restored log file "000000010000000000000027" from archive 2012-02-28 10:59:01 MYT LOG: redo starts at 0/27000020 2012-02-28 10:59:01 MYT LOG: consistent recovery state reached at 0/28000000 2012-02-28 10:59:01 MYT LOG: database system is ready to accept read only connections 2012-02-28 10:59:01 MYT LOG: unexpected pageaddr 0/20000000 in log file 0, segment 40, offset 0 2012-02-28 10:59:02 MYT LOG: streaming replication successfully connected to primary 'pg_archivecleanup' is not recognized as an internal or external command, operable program or batch file. 2012-02-28 11:04:01 MYT WARNING: archive_cleanup_command "pg_archivecleanup \\NOBE-HGG-SRV-04\DB_Stream_Share\ %r": return code 1 'pg_archivecleanup' is not recognized as an internal or external command, operable program or batch file. 2012-02-28 11:09:01 MYT WARNING: archive_cleanup_command "pg_archivecleanup \\NOBE-HGG-SRV-04\DB_Stream_Share\ %r": return code 1 'pg_archivecleanup' is not recognized as an internal or external command, operable program or batch file. 2012-02-28 11:14:01 MYT WARNING: archive_cleanup_command "pg_archivecleanup \\NOBE-HGG-SRV-04\DB_Stream_Share\ %r": return code 1 Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/PSQL-9-1-Streaming-Replication-Windows-2008-64-bit-Servers-tp5518918p5520733.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.