Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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 '13 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 '13 at 16:32
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.