2

My problem is same as the one asked in below topic

Streaming Replication Failover - how to point second slave at new master?

Currently I am working on Postgresql 9.5, I have one master and two slave servers, after master crashed and one of the slaves is assigned as new master, I want to change the master of other slave without full base backup.

I tried the steps written in answer, but it does not seem to work, can someone please guide me on solving this problem?

I am writing some details of my configuration, in case it helps.

standby_mode = on
primary_conninfo = 'host=10.10.10.160 port=5432 user=repuser password=*****'
restore_command = 'cp /archivedir/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

The 10.10.10.160 is the IP address of new master server.

Also /archivedir folder seems empty in both master and slave servers, is this normal? The below settings are present in the postgresql.conf:

wal_level = hot_standby 
archive_mode = on
archive_command = 'test ! -f /archivedir/%f && cp %p /archivedir/%f'
max_wal_senders = 3
hot_standby = on
Improve this question
7
  • Have you checked cascading replication as an option?
    – András Váczi
    Commented Aug 10, 2016 at 13:34
  • In cascading replication, after master crashes and one of the slaves steps up to be new master, does replication with new master and remaing slave preserve?
    – zibidyum
    Commented Aug 10, 2016 at 13:40
  • This is what cascading is about. Of course, it is you who promotes the master (unless you are using Patroni).
    – András Váczi
    Commented Aug 10, 2016 at 14:06
  • I checked the cascading replication, I think it does not completely suitable for my needs. Please correct me if I get this wrong. In my scenario, which is one master two slaves, if I implement cascading replication architecture and upstream server goes down then the downstream server will lose its connectivity with master. So in that case, I will need to get full backup for two slave servers in order to achieve the state before failover. I don't want to do that, reassigning master if possible, suits my needs better. Thank you very much for your hint, I will keep that in my mind.
    – zibidyum
    Commented Aug 10, 2016 at 14:22
  • No, you got it wrong. In cascading replication, there is a cascade like M -> S1 ->S2, S2 following S1, not the master. Streaming replication is about being able to fail over without extra steps - so, once you have a functioning replication (normally with WAL archiving), you can be sure that S1 can be promoted when M fails and S2 still follows S1. No need to rebuild anything (except the failed member).
    – András Váczi
    Commented Aug 10, 2016 at 14:30

1 Answer 1

Reset to default
0

I manage to solve it with dezso's help.

I used cascading replication for this. Current architecture is like M -> S1 -> S2.

Master: 10.10.10.146, S1: 10.10.10.130, s2: 10:10:10:160,

First of all I created repuser in all three systems with below command.

sudo -u postgres createuser -U postgres repuser -P -c 5 --replication

It will ask you to assign a password, type anthing you like and repuser will be created. We will use this user for replication related operations.

On Master server current configuration is:

postgresql.conf

listen_addresses = 'localhost, 10,10,10,146'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /here i wrote full directory starting from root/%f && cp %p /here i wrote full directory starting from root/%f'
max_wal_senders = 3

pg_hba.conf

# Allow replication connections
host     replication     repuser         10.10.10.130/32        md5
host     replication     repuser         10.10.10.160/32        md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

After this configurations restart master server.

Now we need to get full backup from master to S1. First stop the postgresql service on S1. Then remove the data directory (directory is var/lib/pgsql/9.5/data for my system). After removing run full backup command:

sudo -u postgres pg_basebackup -h 10.10.10.146 -D /var/lib/pgsql/9.5/data -U repuser -v -P --xlog-method=stream

This will copy the data folder to S1 server. Now open postgresql.conf in S1, and make below changes

listen_addresses = 'localhost, 10,10,10,130'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /full directory starting from root/%f && cp %p /full directory starting from root/%f'
max_wal_senders = 3
hot standby=on

Open pg_hba.conf and make below changes:

# Allow replication connections
host     replication     repuser         10.10.10.160/32        md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

And finally create a recovery.conf file under data folder. recovery.conf should be like:

standby_mode=on
primary_conninfo='host=10.10.10.146 port=5432 user=repuser password=password_of_repuser'
trigger_file='/full directory starting from root/any_folder_name'

For example if you write trigger_file = '/tmp/trigger_failover' in recovery.conf file, then create a folder under /tmp directory named trigger_failover, S1 will start failover process and will start to serve as master server.

After above configurations start S1 server and now M and S1 should work as master and slave. Next we need to configure S2 server. Steps are pretty much same as configuring S1.

First stop the postgresql service on S2. Then remove the data directory as we did in S1. In my case S2 has same folder architecture with S1, so data folder is also present in /var/lib/pgsql/9.5/data directory. Now run below command this time we will get full backup from S1 server.

sudo -u postgres pg_basebackup -h 10.10.10.30 -D /var/lib/pgsql/9.5/data -U repuser -v -P --xlog-method=stream

No we will change configuration files in S2. In postgresql.conf make below changes:

listen_addresses = 'localhost, 10,10,10,160'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /full directory starting from root/%f && cp %p /full directory starting from root/%f'
max_wal_senders = 3
hot standby=on

And in recovery.conf make below changes:

standby_mode=on
primary_conninfo='host=10.10.10.160 port=5432 user=repuser password=password_of_repuser'
recovery_target_timeline='latest'

And you are finished. Now start postgresql service in S2 and see replication mechanism work in M, S1 and S2.

By this architecture:

  • if M goes down, you can trigger S1 and make it work as new master and S1 will have replication with S2.

  • If S1 goes down you can easily point S2 to M just changing
    primary_conninfo command in recovery.conf file in S2 and you will have
    replication with M and S2.

  • And finally if S2 goes down M will still have replication with S1.

Improve this answer
3
  • An issue I am having is that my slave is already in recovery mode so I cant create a role on it. cannot execute CREATE ROLE in a read-only transaction.
    – Joseph Persico
    Commented Feb 16, 2017 at 5:04
  • why do you need to create role?
    – zibidyum
    Commented Feb 20, 2017 at 14:17
  • That is the error I get when I execute: sudo -u postgres createuser -U postgres repuser -P -c 5 --replication The solution was to promote the server back to master. And then execute the command above.
    – Joseph Persico
    Commented Feb 20, 2017 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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