0

I've had success in the past with MySQL's delayed replication feature which allows you to stop replication, inspect the bin log to find a bad query and SLAVE UNTIL just before said query and skip over it.

Since Postgresql introduced the min_recovery_apply_delay setting I was hoping to achieve the same thing in Postgres, however, I keep reading articles which stop at the point at which you stop replication. So now you have data x hours old... how do you get back up and running? A guide similar to what I wrote for MySQL would be ideal

Edit: from a lot of searching around I've found the omnipitr and pghoard tools along with the recovery target settings. In particular the recovery_target_xid setting which will allow me to recover to an exact query. The only missing piece of the puzzle now is I'm not sure how to tell postgresql to skip this bad query and continue recovery after that point.

Jason
  • 163
  • 1
  • 1
  • 7

2 Answers2

0

From asking some more experienced PostgreSQL people than myself and doing further reading it seems you can't actually skip over queries like you can in MySQL. When you resume after recovering a new "timeline" is created, "Back to the Future" style.

Maybe PostgresSQL will come up with a save way of achieving this in the future but for now it seems you are limited to recovering up to a point in time but all data written after this is lost without significant manual work.

Jason
  • 163
  • 1
  • 1
  • 7
0

I am maybe wrong but for postgresql streaming replication you have to set archiving on master (see in docu https://www.postgresql.org/docs/current/static/continuous-archiving.html). Master saves old XLOGS (WAL logs) in given directory (and you need to delete them later periodically because master does not do it automatically so far). These archived xlogs are necessary otherwise postgresql replication would now survive over some longer networking problems etc. So is this what you mean?

It is similar like with MySQL if replica starts after some time of being down or unreachable and binary logs are no longer present on master you must recreate replica. Otherwise if binary logs are still available replica will sync itself automatically (unless slave was stopped or force not to start in config file).

JosMac
  • 131
  • 2