Re: Logical replication existing data copy

From: Erik Rijkers <er(at)xs4all(dot)nl>
To: Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Logical replication existing data copy
Date: 2017-02-16 09:36:58
Message-ID: 56f3ec6f1989c738a0fa865b13d25761@xs4all.nl
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2017-02-16 00:43, Petr Jelinek wrote:
> On 13/02/17 14:51, Erik Rijkers wrote:
>> On 2017-02-11 11:16, Erik Rijkers wrote:
>>> On 2017-02-08 23:25, Petr Jelinek wrote:
>>>
>>>> 0001-Use-asynchronous-connect-API-in-libpqwalreceiver-v2.patch
>>>> 0002-Always-initialize-stringinfo-buffers-in-walsender-v2.patch
>>>> 0003-Fix-after-trigger-execution-in-logical-replication-v2.patch
>>>> 0004-Add-RENAME-support-for-PUBLICATIONs-and-SUBSCRIPTION-v2.patch
>>>> 0001-Logical-replication-support-for-initial-data-copy-v4.patch
>>>
>>> This often works but it also fails far too often (in my hands). I
>
> Could you periodically dump contents of the pg_subscription_rel on
> subscriber (ideally when dumping the md5 of the data) and attach that
> as
> well?

I attach a bash script (and its output) that polls the 4 pgbench table's
md5s and the pg_subscription_rel table, each second, while I run the
pgbench_derail2.sh (for that see my earlier mail).

pgbench_derail2.sh writes a 'header' into the same output stream (search
for '^===' ).

The .out file reflects a session where I started pgbench_derail2.sh
twice (it removes the publication and subscription at startup). So
there are 2 headers in the attached cb_20170216_10_04_47.out. The first
run ended in a succesful replication (=all 4 pgbench tables
md5-identical). The second run does not end correctly: it has (one of)
the typical faulty end-states: pgbench_accounts, the copy, has a few
less rows than the master table.

Other typical endstates are:
same number of rows but content not identical (for some, typically < 20
rows).
mostly pgbench_accounts and pgbench_history are affected.

(I see now that I made some mistakes in generating the timestamps in the
.out file but I suppose it doesn't matter too much)

I hope it helps; let me know if I can do any other test(s).

Attachment Content-Type Size
cb_20170216_10_04_47.out text/plain 46.1 KB
cb.sh text/x-shellscript 2.5 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bernd Helmle 2017-02-16 10:07:06 Re: LWLock optimization for multicore Power machines
Previous Message Amit Langote 2017-02-16 09:12:12 Re: UPDATE of partition key