PostgreSQL 9.2.6 – Ignoring Archive_Command Issue

postgresqlpostgresql-9.2replication

I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between.

My replication from a -> b is working perfectly through WAL-E. I specify the S3 bucket and access information and a uploads to S3, and b pulls down segments it may need if streaming falls behind.

I'm having an issue trying to get b to upload segments to S3 for use by server c. I've updated my postgresql.conf on b to be:

wal_level = hot_standby
checkpoint_segments = 10
archive_mode = on
archive_command = '/etc/postgresql/9.2/main/local_backup_script.sh "%p" "%f"'
archive_timeout = 3600
max_wal_senders = 5
wal_keep_segments = 4096
hot_standby = on

My local_backup_script.sh is as follows:

#!/bin/bash

echoerr() { echo "$@" 1>&2; }

FAIL=0

`envdir /etc/wal-e.d/env-rep-a /usr/local/bin/wal-e wal-push $1` & pid_1=$!

echoerr "Spawned replication process $pid_1"

wait $pid_1 || let "FAIL+=1"

if [ "$FAIL" == "0" ];
then
echoerr "Replication success $1 $2"
else
echoerr "Replication failed $1 $2"
fi

exit "$FAIL"

I am using a different S3 bucket from b -> c so they don't step on each other. I've also verified that wal-e upload works to this bucket by manually calling the script from the pg_xlog directory.

But postgresql NEVER executes archive_command, and I'm not at a loss as to why. I even just tried to print something to STDERR (so it goes to the log), nothing.

Few other points:
1) /var/lib/postgresql/9.2/archive directory is empty (on b)
2) the script permissions have been set properly (owned by postgres and chmod +x)

Anyone have any suggestions on what may be wrong?

Best Answer

Streaming replicas don't run archive_command because they do not generate WAL. They just replay the master server's WAL.

You can cascade streaming replicas, where one replica acts as a master for the next. But for fallback WAL archiving they both use the master's WAL stream directly.

So just configure the second replica to use the master's WAL stream.