Re: Logical replication existing data copy

On 2017-01-18 14:46, Petr Jelinek wrote:

> 0001-Logical-replication-support-for-initial-data-copy-v2.patch

Applies and builds fine on top of the previous 5 patches.

Two problems:

1. alter_subscription.sgml has an unpaired <command>-tag, which breaks
the doc-build:
This is the offending patch-line:
+ <command>CREATE SUBSCRIPTION</command> with <command>COPY
DATA<command>

2. Running the below (a version of the earlier pgbench_derail.sh) I have
found that
create subscription sub1 .. with (disabled); and then alter
subscription sub1 enable;
cannot be run immediately, consecutively. The error is avoided when the
two
commands are separated (for instance, below in separate psql- calls).

I don't understand why this is but it is reliably so.

The error(s):
2017-01-18 17:26:56.126 CET 24410 LOG: starting logical replication
worker for subscription "sub1"
2017-01-18 17:26:56.132 CET 26291 LOG: logical replication apply for
subscription sub1 started
2017-01-18 17:26:56.139 CET 26291 LOG: starting logical replication
worker for subscription "sub1"
2017-01-18 17:26:56.145 CET 26295 LOG: logical replication sync for
subscription sub1, table pgbench_accounts started
2017-01-18 17:26:56.534 CET 26295 ERROR: duplicate key value violates
unique constraint "pgbench_accounts_pkey"
2017-01-18 17:26:56.534 CET 26295 DETAIL: Key (aid)=(1) already exists.
2017-01-18 17:26:56.534 CET 26295 CONTEXT: COPY pgbench_accounts, line
1
2017-01-18 17:26:56.536 CET 21006 LOG: worker process: logical
replication worker 41015 sync 40991 (PID 26295) exited with exit code 1
2017-01-18 17:26:56.536 CET 26291 LOG: starting logical replication
worker for subscription "sub1"
2017-01-18 17:26:56.542 CET 26297 LOG: logical replication sync for
subscription sub1, table pgbench_branches started
2017-01-18 17:26:57.015 CET 26297 ERROR: duplicate key value violates
unique constraint "pgbench_branches_pkey"
2017-01-18 17:26:57.015 CET 26297 DETAIL: Key (bid)=(1) already exists.
2017-01-18 17:26:57.015 CET 26297 CONTEXT: COPY pgbench_branches, line
1
2017-01-18 17:26:57.017 CET 21006 LOG: worker process: logical
replication worker 41015 sync 40994 (PID 26297) exited with exit code 1
2017-01-18 17:26:57.017 CET 26291 LOG: starting logical replication
worker for subscription "sub1"
2017-01-18 17:26:57.023 CET 26299 LOG: logical replication sync for
subscription sub1, table pgbench_history started
2017-01-18 17:26:57.487 CET 26299 LOG: logical replication
synchronization worker finished processing
2017-01-18 17:26:57.488 CET 26291 LOG: starting logical replication
worker for subscription "sub1"
2017-01-18 17:26:57.491 CET 26301 LOG: logical replication sync for
subscription sub1, table pgbench_tellers started
2017-01-18 17:26:57.948 CET 26301 ERROR: duplicate key value violates
unique constraint "pgbench_tellers_pkey"
2017-01-18 17:26:57.948 CET 26301 DETAIL: Key (tid)=(1) already exists.
2017-01-18 17:26:57.948 CET 26301 CONTEXT: COPY pgbench_tellers, line 1
etc, etc.

#!/bin/sh

# assumes both instances are running, initially without publication or
subscription

unset PGSERVICEFILE PGSERVICE PGPORT PGDATA PGHOST
env | grep PG

PGDATABASE=testdb

# clear logs
echo >
/home/aardvark/pg_stuff/pg_installations/pgsql.logical_replication/logfile.logical_replication
echo >
/home/aardvark/pg_stuff/pg_installations/pgsql.logical_replication2/logfile.logical_replication2

port1=6972
port2=6973

function cb()
{
# display the 4 pgbench tables' accumulated content as md5s
# a,b,t,h stand for: pgbench_accounts, -branches, -tellers, -history
for port in $port1 $port2
do
md5_a=$(echo "select * from pgbench_accounts order by aid"|psql
-qtAXp$port|md5sum|cut -b 1-9)
md5_b=$(echo "select * from pgbench_branches order by bid"|psql
-qtAXp$port|md5sum|cut -b 1-9)
md5_t=$(echo "select * from pgbench_tellers order by tid"|psql
-qtAXp$port|md5sum|cut -b 1-9)
md5_h=$(echo "select * from pgbench_history order by hid"|psql
-qtAXp$port|md5sum|cut -b 1-9)
cnt_a=$(echo "select count(*) from pgbench_accounts"|psql -qtAXp
$port)
cnt_b=$(echo "select count(*) from pgbench_branches"|psql -qtAXp
$port)
cnt_t=$(echo "select count(*) from pgbench_tellers" |psql -qtAXp
$port)
cnt_h=$(echo "select count(*) from pgbench_history" |psql -qtAXp
$port)
printf "$port a,b,t,h: %6d %6d %6d %6d" $cnt_a $cnt_b $cnt_t
$cnt_h
echo -n " $md5_a $md5_b $md5_t $md5_h"
if [[ $port -eq $port1 ]]; then echo " master"
elif [[ $port -eq $port2 ]]; then echo " replica"
else echo " ERROR"
fi
done
}

echo "
drop table if exists pgbench_accounts;
drop table if exists pgbench_branches;
drop table if exists pgbench_tellers;
drop table if exists pgbench_history;" | psql -X -p $port1 \
&& echo "
drop table if exists pgbench_accounts;
drop table if exists pgbench_branches;
drop table if exists pgbench_tellers;
drop table if exists pgbench_history;" | psql -X -p $port2 \
&& pgbench -p $port1 -qis 1 \
&& echo "
alter table pgbench_history add column hid serial primary key;
-- alter table pgbench_history replica identity full;
" | psql -1p $port1 \
&& pg_dump -F c -p $port1 \
-t pgbench_accounts \
-t pgbench_branches \
-t pgbench_tellers \
-t pgbench_history \
| pg_restore -p $port2 -d testdb

echo "$(cb)"

sleep 2

echo "$(cb)"

echo "create publication pub1 for all tables;" | psql -p $port1 -aqtAX

# this demostrates the bug:
echo "create subscription sub1 connection 'port=${port1}' publication
pub1 with (disabled);
alter subscription sub1 enable; " | psql -p $port2 -aqtAX
# like this (create and alter together in a single psql-call) fails;
# with the commands separated (to send them separately to psql suffices)
there is no problem.

#------------------------------------

#exit
#echo "$(cb)"
#echo "-- pgbench -c 1 -T 10 -P 5 -n (short run, first)"
# pgbench -c 1 -T 10 -P 5 -n