Re: Logical replication existing data copy

On 24/03/17 12:32, Petr Jelinek wrote:

> On 24/03/17 00:14, Mark Kirkwood wrote:
>> On 24/03/17 02:00, Peter Eisentraut wrote:
>>> On 3/21/17 21:38, Peter Eisentraut wrote:
>>>> This patch is looking pretty good to me, modulo the failing pg_dump
>>>> tests.
>>>>
>>>> Attached is a fixup patch. I have mainly updated some comments and
>>>> variable naming for (my) clarity. No functional changes.
>>> Committed all that.
>>>
>> Testing now this patch is in, I'm unable to create a subscription:
>>
>> (master)
>>
>> bench=# CREATE PUBLICATION pgbench
>> FOR TABLE pgbench_accounts , pgbench_branches,
>> pgbench_tellers, pgbench_history
>> WITH (PUBLISH INSERT, PUBLISH UPDATE, PUBLISH DELETE);
>>
>> (slave)
>>
>> bench=# CREATE SUBSCRIPTION pgbench
>> CONNECTION 'port=5447 user=postgres dbname=bench'
>> PUBLICATION pgbench
>> WITH (COPY DATA);
>> ERROR: duplicate key value violates unique constraint
>> "pg_subscription_rel_srrelid_srsubid_index"
>> DETAIL: Key (srrelid, srsubid)=(0, 16389) already exists.
>>
>> This is a pair of freshly initdb'ed instances, the master has a size 100
>> pgbench schema.
>>
>> I'm guessing this is a different bug from the segfault also reported
>>
> Yes, I also forgot to check if the table actually exists on subscriber
> when fetching them in CREATE SUBSCRIPTION (we have check during
> replication but not there).
>
> Attached patches should fix both issues.
>
>

Yep, does seem to.

I note that (probably intensional) specifying 'COPY DATA' does not
'CREATE TABLES' for you...ok, I probably didn't read ...something
somewhere...but anyway, after creating the tables it all seems to work.
Nice.

However one minor observation - as Michael Banck noted - the elapsed
time for slave to catch up after running:

$ pgbench -c8 -T600 bench

on the master was (subjectively) much longer than for physical streaming
replication. Is this expected?

regards

Mark