Thread: [HACKERS] issue about the streaming replication

Hi,

I make a test to see how postgresql handle replication diverge problem:

a) setup two pg cluster A and B
b) run A as master, B as salve, using streaming replication
c) insert some data into table foobar on A, shutdown the network
between A and B at the meantime, which ends up some data would be
missing on B
d) A crashes
e) promote B as new master, insert some data into table foobar on B
f) now data on A and B diverge

When I restart A as new slave, it reports below error in log:
record with incorrect prev-link

And worse is, when I shutdown B and promotes A as master again, it
fails to startup:
LOG:  database system was shut down in recovery
FATAL:  invalid memory alloc request size 2281725952

what's this error and why?

I think this diverge scenario is common, because it's likely the
master would crash due to some hardware issue (e.g. power off) which
would cause some committed transaction has not yet synced to slave,
while the slave would be promoted to new master and accepts new
transactions, then how to recover the old master? Moreover, how to
recover the data on old master which is missing on new master?

Regards,
Jinhua Luo

On Sun, Mar 12, 2017 at 5:24 PM, Jinhua Luo <luajit.io@gmail.com> wrote:
> I think this diverge scenario is common, because it's likely the
> master would crash due to some hardware issue (e.g. power off) which
> would cause some committed transaction has not yet synced to slave,
> while the slave would be promoted to new master and accepts new
> transactions, then how to recover the old master? Moreover, how to
> recover the data on old master which is missing on new master?

pg_rewind (https://www.postgresql.org/docs/9.6/static/app-pgrewind.html)
has been designed with exactly this scenario in mind, aka recycling a
past master as a slave to a promoted node. Have you looked at it? What
you are trying to do is much likely going to create corruptions on
your systems, so I am not surprised that you see inconsistency
failures, what you are seeing is pretty much the tip of hte iceberg.
-- 
Michael

Thanks for your information. Now I know the pg_rewind tool.

But why PG does not recover the diverge automatically?

There exists two options at least,  analogy to what "git merge" does:

a) the old master find out and rewind itself to the common base of the
new master in the WAL history before applying new WAL segments from
new master.

b) maybe with logical replication, two masters could merge their data.
if conflict exists, uses the new version. That means data committed on
the old master could survive the crash, which may be important for
some business.

The pg_rewind helps to do option a. But how about option b?


2017-03-12 19:20 GMT+08:00 Michael Paquier <michael.paquier@gmail.com>:
> On Sun, Mar 12, 2017 at 5:24 PM, Jinhua Luo <luajit.io@gmail.com> wrote:
>> I think this diverge scenario is common, because it's likely the
>> master would crash due to some hardware issue (e.g. power off) which
>> would cause some committed transaction has not yet synced to slave,
>> while the slave would be promoted to new master and accepts new
>> transactions, then how to recover the old master? Moreover, how to
>> recover the data on old master which is missing on new master?
>
> pg_rewind (https://www.postgresql.org/docs/9.6/static/app-pgrewind.html)
> has been designed with exactly this scenario in mind, aka recycling a
> past master as a slave to a promoted node. Have you looked at it? What
> you are trying to do is much likely going to create corruptions on
> your systems, so I am not surprised that you see inconsistency
> failures, what you are seeing is pretty much the tip of hte iceberg.
> --
> Michael