We're currently running a master -> slave, slave, slave, slave set up using postgresql 9.2.8 with streaming and WAL-E/S3 for handling wal segments.
Should all of the replicas currently be "in recovery"? Running SELECT pg_is_in_recovery(); on all of them returns true, which is concerning. We can run queries on them (assuming they don't take longer than 30 seconds).
I'm trying to spin up another replica off of one of the existing slaves using WAL-E but I'm currently unable to do so given every replica is in recovery mode. I can't run pg_basebackup or use wal-e's backup features on the replicas.
Amy I missing something glaringly obvious? The only thing I can think of is we had an issue roughly 2 months ago where our hard drive filled up on our master, and it shut off. We were able to boot it, clear disk space, and continue streaming/replication from the master.
If I were to simply start up 3 postgresql servers and configure them in a 3 server chain (master -> slave -> slave) using streaming/archiving it would work properly with WAL-E, as I have done this. Just for some reason I'm unable to get our existing production replicas to stream/archive to any other server. Specifically the archive_command is NEVER run on any of the replicas (because it's stuck in recovery mode).
Does anyone have any suggestions on how I can further debug/diagnose this? I'm trying to find a solution without significant downtime to our production database (as I could always just re-import the DB to a new server and start the chain again, but this would take 12+ hours).
Here is the configuration details: https://gist.github.com/Geesu/1a696262e46ba9f0a24c As well as the local_backup_script.sh: https://gist.github.com/Geesu/3b8b35e108d8e2205da7
Thanks!