In any modern PostgreSQL installation, there’s usually at least one streaming replication standby server feeding off your production master. In fact, there’s usually several. Being the good PostgreSQL administrator that you are, you’ve already enabled logging of replication commands in
log_replication_commands = on
But what you’re probably not doing is having each standby automatically identify itself in your logs. So how do you accomplish this? It’s simple: “abuse” the
application_name parameter in the standby’s
primary_conninfo = 'host=192.168.122.50 application_name=$(hostname -s)'
With this simple little trick, you can now look at your logs (or your pgBadger report of the logs) and see which standby is responsible for which log entry.
Simple and sweet.