PostgreSQL – trigger_file Doesn’t Work

postgresqlreplication

I set streaming replication. Replication works just fine but it doesn't care about trigger_file. I have nothing fancy there except all what many of tutorials advices:

recovery.conf:

standby_mode = on
primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'

I've checked that the postgres user has access to this file, I've tried to change the location to /tmp, set file owner as postgres or root and nothing helped.

Any ideas ? Thanks in advance.

Best Answer

As a starting point to troubleshoot this, you may check what's read from recovery.conf with log_min_messages set to debug2 in postgresql.conf on the slave.

On server start, the trigger file should be shown in the log within a set of entries like this:

 DEBUG:  standby_mode = 'on'
 DEBUG:  primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
 DEBUG:  trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'
 LOG:  entering standby mode

If the trigger_file entry doesn't show up, the most plausible explanation would be that you're editing a recovery.conf at a wrong location.

If on the other hand it's found at startup, when later creating the trigger file to fail over, this entry should appear:

 LOG:  trigger file found: /var/lib/postgresql/9.3/main/failover_trigger
Related Question