hot standby PSQL 9.1 Windows 2008 Servers

From: chinnaobi <chinnaobi(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: hot standby PSQL 9.1 Windows 2008 Servers
Date: 2012-05-14 12:18:34
Message-ID: 1336997914627-5708637.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi All,

I have implemented hot standby for PostgreSQL with a group of two Primary
and Standby in windows 2008 servers.

Currently below are the settings:

1. Archiving is enabled on primary, stored on network storage.
2. Asynchronous Streaming replication from primary to standby.
wal-senders=5, wal_keep_segments=32.
3. I am checking heartbeat of primary from standby, when it is down i am
restarting service by changing postgresql.conf and recovery.conf settings.

I do base backup only first time on standby when it is going to be
replicated. when ever primary goes down, standby becomes primary and
primary becomes standby when primary comes up. When primary becomes standby
I am restoring data from WAL archive and start postgres service streaming
replication to connect to primary.

This setup is working.

I have tested for few days in my network with huge data input to the primary
and restarting the servers multiple times. I have observed these below
specific errors in standby server and standby server is not replicating in
anymore.

*1. Invalid primary and secondary checkpoints. Very rare But happend
2. contrecord is requested by 0/DF000020 -- Major one
3. record with zero length at 0/DF000078, invalid record length at
0/DF000078*

These errors are shown up when primary switching to standby by recovering
data from archive and standby stops replicating. *I am not able to start the
service for that I should kill all the process of postgres and then do base
backup and then start service on stnadby.*

I don't have any prior experience working with postgreSQL. Please tell me

what are the most reliable settings for Hot standby with streaming
replication to work ? two 2008 servers and 1 network storage available.
How to avoid above errors ??

--
View this message in context: http://postgresql.1045698.n5.nabble.com/hot-standby-PSQL-9-1-Windows-2008-Servers-tp5708637.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Janes 2012-05-14 12:22:12 Re: Why do we still have commit_delay and commit_siblings?
Previous Message Stephen Frost 2012-05-14 11:57:15 Re: Gsoc2012 idea, tablesample