4

I am absolutely new to postgres 9.1 replication mechanisms.

I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when the master server fails, using the trigger file mechanism the standby takes over, and accumulates additional data (say now has x+y number of records).

Now when the Original Master Server comes up, it still has x number of records (which is now the new master). Is there a way to fetch only the delta, i.e. newly added 'y' number of records from the standby and restart as Master.

Or do I have to always take entire base backup?

8
  • 2
    Look into pg_rewind. In general, you can't be sure that the old master didn't have updates that didn't make it to its slave, so you need to "rewind" the old master first. See for example hlinnaka.iki.fi/2015/03/23/pg_rewind-in-postgresql-9-5 – Colin 't Hart May 5 '15 at 8:51
  • 1
    @Colin'tHart this won't help them until they upgrade to the not-yet-released 9.5 ;) – dezso May 5 '15 at 9:01
  • Yes, but versions for as far as back as at least 9.3 exist at github.com/vmware/pg_rewind – Colin 't Hart May 5 '15 at 9:03
  • The other option is to rsync your original master from (a snapshot of) the slave. This will probably be quicker than backing up the slave and restoring to the original master, but slower than the pg_rewind solution. – Colin 't Hart May 5 '15 at 9:04
  • 2
    @dezso I don't think it's ever possible to set the old master as a slave without restore/rewind. – Jack Douglas May 5 '15 at 9:11
0

During the process there is a good chance that the process fails requesting wall files that have never existed in the old time line. That does not help quickly restoring the requested redundancy.

The safest method is to just create a fresh new replica.

So if your requirement is to always have at least one valid replica, make sure you have two of them. This gives you time to rebuild the former primary as a new replica and still keep operating at your requested redundancy level.

There was a bug that is solved in 9.5.22 that caused premature recycling of wall files during crash recovery.

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.