2

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.

7
  • You don't mention it explicitly: Are you trying to failover? Jun 3, 2014 at 14:46
  • yes, definitely :)
    – pawel7318
    Jun 3, 2014 at 14:55
  • Do the logs tell you something happening when you create the file?
    – dezso
    Jun 3, 2014 at 15:33
  • Nothing at all - I have tail -f on it and trying to play with the file. Whatever I do nothing happens.
    – pawel7318
    Jun 3, 2014 at 15:34
  • I run pg_ctlcluster 9.3 main promote and it worked but recovery.conf didn't changed its name to recovery.done.
    – pawel7318
    Jun 7, 2014 at 11:50

2 Answers 2

4
+100

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
1
  • Got it ! I did as you advised and I noticed 2 things: 1st: there was no trigger_file in the log and 2nd: it doesn't care if I change something in primary_conninfo - so I've started looking for another file and I found it in /var/lib/postgresql.. (mine was in /etc/postgres/..). Finally I found the reason: pg_basebackup -R which uses .pgpass to connect and than -R saves the credentials to new recovery.conf. It tricked me so much :) Thank you for help.
    – pawel7318
    Jun 10, 2014 at 7:13
-1

once the trigger is created on the master machine

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'

you need to create the file in the slave machine (replica) in-order to reconfigure itself to lunch your trigger

EDIT

lets check the configuration after the installation is completed:

1) you need a ssh key,if you don't have one you can generate one for testing, but it will give you certificate error later by using ssh-keygen

2)copy the certificate you had in step one to the other server: ssh-copy-id IP_address_of_opposite_server

3) create replica user by using psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ' enter_your_password';

**Note the following steps need to be done while the service is down if you want to do it at the sometime otherwise you can repeat the steps for replica machine (slave).

4)open PostgreSQL directory

5)edit (pg_hba.conf) file note: take a backup from (pg_hba.conf) first

6) add the following line but make sure you font add it to the bottom of the file: host replication rep IP_address_of_slave/32 md5

7) save and close

8) edit (postgresql.conf) note you need to take backup first

9) search for the following parameters and um-comment them

listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

10) save and close

11) start the master machine (main server that has the database make sure the replica service still down)

12) now you need to take backup from the master machine by using

psql -p $PORT -c "select pg_start_backup('$BACKUP_LABEL');"
rsync -cva --inplace --exclude=*pg_xlog* $PGDATA/ <IP_OF_OTHER_HOST>:$PGDATA/
psql -p $PORT -c "select pg_stop_backup();"

13) now open recovery.conf on the replica server

standby_mode = 'on'
primary_conninfo = 'host=master_IP_address port=[unter port number] user=rep password=[enter rep password]'
trigger_file = '/tmp/postgresql.trigger.port'

14) start the replica server

15) make sure the ports are opened in the firewall.

16) to open the turn over you need to touch the trigger file that mentioned in the recovery.conf

4
  • I have nothing like this on the master. recovery.conf is on the slave and the trigger file I'm creating there as well (with touch /var/lib/postgresql/9.3/main/failover_trigger). I don't get what do you mean writing 'once the trigger is created on the master machine'.
    – pawel7318
    Jun 9, 2014 at 7:55
  • Please check the edited part on my answer above. hope that will help you Jun 9, 2014 at 10:10
  • 1
    What you've wrote here is one more step-by-step tutorial how to set streaming replication - very similar to the one I followed. As I wrote in the Q replication works just fine and I can rollover with pg_ctl/pg_ctlcluster. What I would like to focus at is the reason why it doesn't care about the trigger file. I can provide any additional info that is necessary - just tell me what you need.
    – pawel7318
    Jun 9, 2014 at 11:19
  • Please use the the PostgreSQL user and its users Jun 9, 2014 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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