2

Last week we had an incident with our master PostgreSQL server instance which got totally thrashed so, we had to switch to our slave as our one and only DB instance. After the initial chaos was controlled, we tried to create a new slave replica from scratch, according to instructions left by our DB provider who actually set up the first master-slave replica, but just yesterday I realized that the slave wasn't really replicating at all and, only has the contents of the initial base backup, so we have no redundancy right now in case of a new disaster!

On recovery.conf we have:

standby_mode = on
primary_conninfo = 'host=10.1.1.65 port=5432 user=replicador password=XXXXXXXX'

In /var/lib/postgresql.log (master) there are several messages like this:

2017-03-17 12:21:57 UTC [1969-1] replicador@[unknown] ERROR:  requested WAL segment 0000000100000B280000003A has already been removed

And in the replica's log file, many messages like:

2017-03-22 13:22:32 UTC [2827-1] LOG:  started streaming WAL from primary at B28/3A000000 on timeline 1
2017-03-22 13:22:32 UTC [2827-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000100000B280000003A has already been removed

Seems like the process wasn't fast enough to keep the master's pace. What parameters should I check/raise? Can I actually re-sync, or am I lost and once we'll have to do it all over again?

What could be missing? What additional info should I provide?

Environment:

select version()
PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

lsb_release -a
Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

uname -a
Linux ip-10-1-0-139 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux
  • Please check your server logs in master and replica. With Debian-packaged postgreSQL, they're inside /var/log/postgresql. – Daniel Vérité Mar 22 '17 at 13:15
  • Thanks @DanielVérité for pointing an obvious search location, which actually led to suspicious messages, as I've added to the question above – Gonzalo Vasquez Mar 22 '17 at 13:24
  • 1
    As it sounds, you have to turn WAL archiving on, to prevent removal of the segments. – dezso Jun 15 '17 at 7:28
  • @dezso Definitely sounds like a great approach, and in our case that we are working in AWS I guess S3 could be a suitable storage for such archive. Now arises another question: what to do with those files and, how to used to recreate a slave or help recovering from this question's describe scenario – Gonzalo Vasquez Jun 15 '17 at 16:32
  • 1
    Yes, S3 is a good place. Make it writable from both instances (for preparing a failover). Archive the segments by setting archive_command, and let the slave consume these via restore_command. Finally, archive_cleanup_command will help removing those that are already replayed, thus no longer needed. You could also look into github.com/wal-e/wal-e, or for a complete HA solution github.com/zalando/spilo (developed by the colleagues on the other side of my desk). – dezso Jun 15 '17 at 18:36
1

Your base backup looks old as current master (earlier slave) must be on timeline greater than 1 after getting promoted as master but your slave is on still timeline 1. You can either create a new slave from scratch after taking a new base backup from new master. Or you can try timeline switch in the current non replicating slave (not sure about it) refer it here http://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/

  • Can you please elaborate more on the "timeline" reference? I'm not aware of such concept – Gonzalo Vasquez Jun 15 '17 at 16:34
  • 1
    You can refer about timelines here postgresql.org/docs/9.1/static/… – sanyam jain Jun 16 '17 at 5:24
  • 1
    Try adding recovery_target_timeline='latest' in slaves' recovery.conf and restart your slave.It should automatically move the slave on latest timeline – sanyam jain Jun 16 '17 at 5:27
  • Great concept the timelines!! – Gonzalo Vasquez Jun 16 '17 at 15:46

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.