Home / server / Questions / 508243
In Process
Selivanov Pavel
Asked: 2013-05-17 03:19:33 +0800 CST

PostgreSQL WAL archiving on secondary server

  • 772

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.

postgresql

2 Answers

  1. 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.

    • 4

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

    • 0