MIME-Version: 1.0
From: William Ivanski <william.ivanski@2ndquadrant.com>
Date: Fri, 25 Sep 2020 01:18:30 -0300
Message-ID: 
 <CAG7+Lq__342Y7GMzhmmspPRmE=SuPRcEvPhEsMJcg9o6PjWt8A@mail.gmail.com>
Subject: Fixed port 65432 in pglogical_create_subscriber
To: pgsql-pkg-debian@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009fec7c05b01b9ca8"
Precedence: bulk

--0000000000009fec7c05b01b9ca8
Content-Type: text/plain; charset="UTF-8"

Hi,

Operating system is Ubuntu 18.04, the issue is with package
postgresql-9.4-pglogical. I didn't test with > 9.4.

I've been trying to convert a PostgreSQL 9.4 physical standby into a
logical subscriber using pglogical_create_subscriber. It can't initialize
pglogical on the subscriber server because it can't connect to the local
PostgreSQL database after a restart, so it hangs.

As you can see below (sensitive information redacted), I have configured to
start local PostgreSQL database on port 5433. File
/etc/postgresql/9.4/main/postgresql.conf confirms port is set to 5433.


postgres@subserver ~ $
/usr/lib/postgresql/9.4/bin/pglogical_create_subscriber \
>  -D /var/lib/postgresql/9.4/main/ -n subserver \
>  --subscriber-dsn='host=127.0.0.1 port=5433 dbname=mydb user=pglogical' \
>  --provider-dsn='host=192.168.0.100 port=5432 dbname=mydb user=pglogical'
\
>  --hba-conf=/etc/postgresql/9.4/main/pg_hba.conf \
>  --postgresql-conf=/etc/postgresql/9.4/main/postgresql.conf \
>  --drop-slot-if-exists -s -v
pglogical_create_subscriber: starting ...
Getting information for database mydb ...
Creating replication slot in database mydb ...
Creating restore point "pglogical_create_subscriber_59e1d7bc" on remote
node ...
Bringing subscriber node to the restore point ...
Waiting for PostgreSQL to accept connections ....
Waiting for PostgreSQL to become primary...Waiting for PostgreSQL to accept
connections ...

Removing old pglogical configuration ...
Waiting for PostgreSQL to shutdown ...
Initializing pglogical on the subscriber node:
Waiting for PostgreSQL to accept connections ................[never ends]


pglogical_create_subscriber started the local PostgreSQL instance on port
65432, but it's expecting to connect to port 5433, so it will never be able
to connect.

I checked pglogical source code on [1] and I was not able to find any
explanation for this fixed port 65432. Then I found the pglogical source
code is being patched to start Postgres with a fixed port 65432 here [2].


[1] https://github.com/2ndQuadrant/pglogical
[2]
https://salsa.debian.org/postgresql/pglogical/-/blob/master/debian/patches/pglogical_create_subscriber_set_port.patch

-- 
William Ivanski - 2ndQuadrant
PostgreSQL Solutions for the Enterprise

--0000000000009fec7c05b01b9ca8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>Operating system is Ubun=
tu 18.04, the issue is with package postgresql-9.4-pglogical. I didn&#39;t =
test with &gt; 9.4.</div><div><br></div><div>I&#39;ve been trying to conver=
t a PostgreSQL 9.4 physical standby into a logical subscriber using pglogic=
al_create_subscriber. It can&#39;t initialize pglogical on the subscriber s=
erver because it can&#39;t connect to the local PostgreSQL database after a=
 restart, so it hangs.</div><div><br></div><div>As you can see below (sensi=
tive information redacted), I have configured to start local PostgreSQL dat=
abase on port 5433. File /etc/postgresql/9.4/main/postgresql.conf confirms =
port is set to 5433.</div><div><br></div><div><br></div><div>postgres@subse=
rver ~ $ /usr/lib/postgresql/9.4/bin/pglogical_create_subscriber \<br>&gt; =
=C2=A0-D /var/lib/postgresql/9.4/main/ -n subserver \<br>&gt; =C2=A0--subsc=
riber-dsn=3D&#39;host=3D127.0.0.1 port=3D5433 dbname=3Dmydb user=3Dpglogica=
l&#39; \<br>&gt; =C2=A0--provider-dsn=3D&#39;host=3D192.168.0.100 port=3D54=
32 dbname=3Dmydb user=3Dpglogical&#39; \<br>&gt; =C2=A0--hba-conf=3D/etc/po=
stgresql/9.4/main/pg_hba.conf \<br>&gt; =C2=A0--postgresql-conf=3D/etc/post=
gresql/9.4/main/postgresql.conf \<br>&gt; =C2=A0--drop-slot-if-exists -s -v=
<br>pglogical_create_subscriber: starting ...<br>Getting information for da=
tabase mydb ...<br>Creating replication slot in database mydb ...<br>Creati=
ng restore point &quot;pglogical_create_subscriber_59e1d7bc&quot; on remote=
 node ...<br>Bringing subscriber node to the restore point ...<br>Waiting f=
or PostgreSQL to accept connections ....<br>Waiting for PostgreSQL to becom=
e primary...Waiting for PostgreSQL to accept connections ...<br><br>Removin=
g old pglogical configuration ...<br>Waiting for PostgreSQL to shutdown ...=
<br>Initializing pglogical on the subscriber node:<br>Waiting for PostgreSQ=
L to accept connections ................[never ends]<br></div><div><br></di=
v><div><br></div><div>pglogical_create_subscriber started the local Postgre=
SQL instance on port 65432, but it&#39;s expecting to connect to port 5433,=
 so it will never be able to connect.</div><div><br></div><div>I checked pg=
logical source code on [1] and I was not able to find any explanation for t=
his fixed port 65432. Then I found the pglogical source code is being patch=
ed to start Postgres with a fixed port 65432 here [2].</div><div><br></div>=
<div><br></div><div>[1]=C2=A0<a href=3D"https://github.com/2ndQuadrant/pglo=
gical">https://github.com/2ndQuadrant/pglogical</a></div><div>[2] <a href=
=3D"https://salsa.debian.org/postgresql/pglogical/-/blob/master/debian/patc=
hes/pglogical_create_subscriber_set_port.patch">https://salsa.debian.org/po=
stgresql/pglogical/-/blob/master/debian/patches/pglogical_create_subscriber=
_set_port.patch</a></div><div><br></div>-- <br><div dir=3D"ltr" class=3D"gm=
ail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div><di=
v dir=3D"ltr"><div><div dir=3D"ltr">William Ivanski - 2ndQuadrant<div><span=
 style=3D"font-size:small">PostgreSQL Solutions for the Enterprise</span><b=
r></div></div></div></div></div></div></div></div>

--0000000000009fec7c05b01b9ca8--