2

I'm using PostgreSQL 9.1 with streaming replication for failover and WAL for archiving.

If I use the archive_command configuration directive on my standby server it will always fail (because the WAL segments it is trying to archive are already archived by master), so I'm using an empty archive_command on standby server.
The problem is when my master server is down and the standby server becomes the new master archiving stops. Is there any way to know in archive_command script if server is running in master mode?

I can make an archive_command script that returns 0 if file with name of segment I'm trying to archive already exists, but I'm not sure if this is right behavior for archive_command.

Improve this question

2 Answers 2

Reset to default
4

Your instincts are correct - sort of.

Write an archive command that determines if the local Postgres server is currently the master (easy way: Connect and do SELECT pg_is_in_recovery(); -- If it's true, you're on a slave).

If you detect that you're on the master server, archive segments normally.
If you detect that you're on a slave server do nothing and exit gracefully.

The above trick works because you can't connect to a master that's in (crash) recovery -- The only time you will be able to connect to a Postgres server that is in recovery mode is when you've connected to a slave with streaming replication.
After failover your slave will be a master (and no longer be in recovery mode), so your WAL archiving scripts will be able to do their thing.

Improve this answer
2
  • I found, that WAL operations are disabled in recovery mode(standby server is running in endless recovery), so changing script isn't necessary - standby just won't ever launch it. But you have shown me interesting function, thank you :)
    – Selivanov Pavel
    May 17, 2013 at 11:35
  • 1
    @SelivanovPavel I don't expect WAL segments will ever be generated by a slave (because like you said the slaves are in "endless recovery" until they get triggered to become Master), but Just In Case that ever changes I'd perform the check anyway. (As an aside it's also a good check for your monitoring system to detect that a failover happened and alert you)
    – voretaq7
    May 17, 2013 at 16:32
0

Everything is OK: PostgreSQL on stanby server is running in recovery mode, so WAL opetations are disabled.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .