Hi Robert,
Thanks for the response. Mine is 9.2.4 and the issue is specifically with cascading standby. I don’t think 8.3 had that feature.
Murthy
From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Burgholzer, Robert (DEQ)
Sent: Wednesday, April 02, 2014 3:29 PM
To: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: Cascade Standby - HELP!
Murthy,
I have experienced something similar to this issue, and recall that it had to do with log file difficulties. I tried my best to document this, but recall that I had problems working back through it, so please, note that this is a procedure that carries no guarantees. Note that the step-by-step calls a couple of custom scripts that I wrote (they are at the end of the page), AND this is on an 8.3 postgresql install:
Regards,
/r/b
My only response that might help you with this is that my setup is:
Server 1) primary production db server (5432)
Server 2) replicates production (5432) also primary for non-production use (6432)
Server 3) (offsite) replicates #2 (5432 -> 7432 internally and 6432 -> 6432) AND hosts internal databases
So:
1) Production
2) Replication + non production
3) Internal + replication of NON production + replication of PRODUCTION (via the cascaded server)
I would find that when replicating the PRODUCTION server internally (#3), I had not set wal_keep_segments high enough,
due to the lag of WAN replication, production and non production would replicate fine (gigabit backend), but sometimes when
a load of data was being updated/moved/deleted, the offsite (#3) would fall behind far enough that it could not recover
without a new pg base backup, in essence setting up replication again.
I solved this by upping the wal_keep_segments.
