<div dir="ltr"><div dir="ltr"><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hi Andreas, thanks for your email.</div><div><br></div><div>Hmm... that sucks! :(</div><div><br></div><div>Did you perform the same/similar steps as I did? Just wanna make sure they're correct.</div><div><br></div><div>Cheers,</div><div>Lucas</div></div></div></div></div></div></div></div></div></div></div></div><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, 17 Apr 2021 at 23:49, Andreas Wahler <<a href="mailto:andreas.wahler@wamtek.net">andreas.wahler@wamtek.net</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>-hi Lucas,</p>
<p>I faced these locks during adding a new sync to bucardo, too.
During this step it helps a lot if no / less traffic is on the
database.</p>
<p>Best,<br>
Andreas<br>
</p>
<div>Am 17.04.2021 um 13:08 schrieb Lucas
Possamai:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Anyone able to help with my previous email, please?
<div><br>
</div>
<div>I've just tried the process again, and I got into the same
outcome. The steps I followed are below:</div>
<div><br>
</div>
<div>---</div>
<div>
<ol>
<li>Created bucardo username on source and target dbs</li>
<li>because I've done this before, I've done some cleanup
before repeating the process</li>
<ol>
<li>bucardo remove sync test_sync</li>
<li>bucardo remove dbgroup test_db_group</li>
<li>bucardo remove relgroup test_db_group</li>
<li>bucardo remove all tables</li>
<li>bucardo remove all sequences</li>
<li>bucardo remove db source_db_ec2</li>
<li>bucardo remove target_db_rds</li>
<li>AS POSTGRES</li>
<ol>
<li>drop schema bucardo cascade;</li>
</ol>
</ol>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
add db source_db_ec2 dbname=test_db host=<a href="http://pgsql-source.example.com" target="_blank">pgsql-source.example.com</a>
user=bucardouser pass=password</p>
</li>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica"></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
add db target_db_rds dbname=test_db host=<a href="http://pgsql-target-rds.example.com" target="_blank">pgsql-target-rds.example.com</a>
user=bucardo pass=password</p>
</li>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica"></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
add table public.* db=source_db_ec2
relgroup=test_db_group</p>
</li>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica"></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
add table junk.* db=source_db_ec2 relgroup=test_db_group</p>
</li>
<li>
<p style="margin:0px;font:12px Helvetica">bucardo
add sequence public.* db=source_db_ec2
relgroup=test_db_group</p>
</li>
<li>
<p style="margin:0px;font:12px Helvetica">bucardo
add sequence junk.* db=source_db_ec2
relgroup=test_db_group</p>
</li>
<li>
<p style="margin:0px;font:12px Helvetica">
</p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
add dbgroup test_db_group source_db_ec2:source
target_db_rds:target</p>
</li>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica"></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
add sync test_sync relgroup=test_db_group
dbs=test_db_group autokick=1</p>
</li>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
validate test_sync</p>
</li>
<li>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:12px;line-height:normal;font-family:Helvetica">bucardo
start</p>
</li>
</ol>
</div>
<div>When checking the status of the sync</div>
<div>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Last
good<span>
</span>: Apr 17, 2021 10:43:25 (time to run: 1s)</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Rows
deleted/inserted<span>
</span>: 0 / 0</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Sync
name<span>
</span>: test_sync</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Current
state<span>
</span>: Good</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Source
relgroup/database : test_db_group / source_db_ec2</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Tables
in sync <span>
</span>: 209</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Status
<span>
</span>: Active</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Check
time <span>
</span>: None</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Overdue
time <span>
</span>: 00:00:00</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Expired
time <span>
</span>: 00:00:00</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Stayalive/Kidsalive<span> </span>: Yes
/ Yes</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Rebuild
index<span>
</span>: No</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Autokick
<span>
</span>: Yes</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Onetimecopy<span> </span>:
No</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Post-copy
analyze<span> </span>:
Yes</span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">Last
error:<span>
</span>:<span> </span></span></p>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">======================================================================</span></p>
</div>
<div><br>
</div>
<div>... everything looks normal</div>
<div><br>
</div>
<div>Bucardo logs:</div>
<div>(371540) [Sat Apr 17 10:42:26 2021] MCP Active syncs: 1<br>
(371540) [Sat Apr 17 10:42:26 2021] MCP Entering main loop<br>
(371542) [Sat Apr 17 10:42:26 2021] VAC New VAC daemon.
PID=371542<br>
(371542) [Sat Apr 17 10:42:26 2021] VAC Connected to database
"source_db_ec2" with backend PID of 81591<br>
(371540) [Sat Apr 17 10:42:26 2021] MCP Created VAC 371542<br>
(371544) [Sat Apr 17 10:42:26 2021] CTL New controller for
sync "test_sync". Relgroup is "test_db_group", dbs is
"test_db_group". PID=371544<br>
(371544) [Sat Apr 17 10:42:26 2021] CTL stayalive: 1
checksecs: 0 kicked: 1<br>
(371544) [Sat Apr 17 10:42:26 2021] CTL kidsalive: 1
onetimecopy: 0 lifetimesecs: 0 (NULL) maxkicks: 0<br>
(371544) [Sat Apr 17 10:42:26 2021] CTL Database
"source_db_ec2" backend PID: 81592<br>
(371544) [Sat Apr 17 10:42:26 2021] CTL Database
"target_db_rds" backend PID: 15208<br>
(371540) [Sat Apr 17 10:42:26 2021] MCP Created controller
371544 for sync "test_sync". Kick is 1<br>
(371546) [Sat Apr 17 10:42:27 2021] KID (test_sync) New kid,
sync "test_sync" alive=1 Parent=371544 PID=371546 kicked=1<br>
</div>
<div><br>
</div>
<div>Then, my DB servers stop processing queries from the
application. I couldn't find anything on the PG logs.</div>
<div><br>
</div>
<div>Can someone please tell me if the above steps are correct?</div>
<div><br>
</div>
<div>Cheers</div>
<div>---</div>
<div><br>
</div>
<div><br clear="all">
<div>
<div dir="ltr">
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div>---<br>
</div>
<div>Kind regards,</div>
<b><br>
</b></div>
<div dir="ltr"><b>Lucas Possamai</b></div>
<div><a href="http://lpossamai.me" target="_blank">lpossamai.me</a><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Tue, 13 Apr 2021 at 10:31,
Lucas Possamai <<a href="mailto:drum.lucas@gmail.com" target="_blank">drum.lucas@gmail.com</a>> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">Hi all,
<div><br>
</div>
<div>When executing the command below, my source db
collapsed. Looked like lots of locks and waits were
happening.</div>
<div><br>
</div>
<div>
<p style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:11px;line-height:normal;font-family:Monaco;color:rgb(242,242,242);background-color:rgba(0,0,0,0.85)"><span style="font-variant-ligatures:no-common-ligatures">bucardo
add sync test_sync relgroup=test_db_group
dbs=test_db_group autokick=0</span></p>
</div>
<div><br>
</div>
<div>The steps I'm doing are:</div>
<div>
<ul>
<li>PG DUMP the database from source</li>
<li>Create roles and apply database permissions to
target</li>
<li>Import the Database dump to the target DB (RDS)</li>
<li>Enable session_replication_role on Target</li>
<li>Bucardo add source DB</li>
<li>Bucardo add target DB</li>
<li>bucardo add tables</li>
<ul>
<li>bucardo add table public.* db=source_db
relgroup=test_db_group<br>
</li>
</ul>
<li>bucardo add sequences</li>
<ul>
<li>bucardo add sequence public.* db=source_db
relgroup=test_db_group<br>
</li>
</ul>
<li>bucardo add dbgroup</li>
<ul>
<li>bucardo add dbgroup test_db_group source_db:source
target_db:target<br>
</li>
</ul>
<li>bucardo add sync</li>
<ul>
<li>bucardo add sync test_sync relgroup=test_db_group
dbs=test_db_group autokick=0<br>
</li>
</ul>
</ul>
<div><br>
</div>
<div>Are those locks expected when adding the sync on the
source db? Any other recommendations?</div>
<div><br>
</div>
<div>Source DB is PostgreSQL 9.2</div>
<div>Target DB is PostgreSQL 12.4</div>
<div>Bucardo version is: 5.6.0</div>
<div><br>
</div>
<div>Cheers,</div>
<div>
<div dir="ltr">
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div>---<br>
</div>
<div>Kind regards,</div>
<b><br>
</b></div>
<div dir="ltr"><b>Lucas Possamai</b></div>
<div><a href="http://lpossamai.me" target="_blank">lpossamai.me</a><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
Bucardo-general mailing list
<a href="mailto:Bucardo-general@bucardo.org" target="_blank">Bucardo-general@bucardo.org</a>
<a href="https://bucardo.or" target="_blank">https://bucardo.or</a></pre>
</blockquote>
<br>
</div>
_______________________________________________<br>
Bucardo-general mailing list<br>
<a href="mailto:Bucardo-general@bucardo.org" target="_blank">Bucardo-general@bucardo.org</a><br>
<a href="https://bucardo.org/mailman/listinfo/bucardo-general" rel="noreferrer" target="_blank">https://bucardo.org/mailman/listinfo/bucardo-general</a><br>
</blockquote></div></div>