Обсуждение: [ADMIN] Standby: Use WAL instead of Streaming for now
This afternoon we've experienced an odd network issue where streaming replication and WAL file rsync performance are very poor. The standby is currently over 2 hours behind when normally it's under a minute.
Oddly, our NetApp mirror replication performance is just fine. We mirror our WAL archive location so I could just copy the WAL files from the mirror to our standby host very quickly.
The problem now is how do I tell the standby to use the WALs and not streaming replication? Do I just comment out primary_conninfo? Can I then do a pg_reload_conf() or do I need to restart the standby DB? None of those things?
My searches so far for this kind of thing seem to focus on stopping recovery altogether, which isn't what I want. I just want it to use the local WAL files for now until we get the networking issue resolved.
On Tue, Aug 29, 2017 at 4:08 PM, Don Seiler <don@seiler.us> wrote:
The problem now is how do I tell the standby to use the WALs and not streaming replication? Do I just comment out primary_conninfo? Can I then do a pg_reload_conf() or do I need to restart the standby DB? None of those things?
One clarification, I copied them to the defined archive directory on the standby, not directly to the pg_xlog directory (where the streaming replication seems to be writing its data).
Don Seiler
www.seiler.us
www.seiler.us
Sorted it out thanks to the folks in IRC. I didn't change any configs, just killed the sender process on the primary via pg_terminate_backed() on the PID of the sender (as seen in pg_stat_replication). This caused recovery on the standby immediately fall back to WAL recovery, where it quickly restored and recovered all the WAL files I had copied over. Once it finished with those, it opened another streaming connection. So I'll just rinse/repeat this process until our networking issues are resolved.