Re: Fixed port 65432 in pglogical_create_subscriber

Hi Michael,

Thanks for your quick response and for testing without the patch.

I tested with upstream pglogical, and it works, I'll explain.

For pglogical to start Postgres on port 5433 on the subscriber, I need to
set "port = 5433" in the file I specified in "--postgresql-conf", then it
works as I need. pglogical_create_subscriber will copy this file to the
path specified in "-D" (PGDATA), and this file will be used when
pglogical_create_subscriber starts Postgres itself during the procedure. As
in Debian/Ubuntu we generally don't keep postgresql.conf and pg_hba.conf
files under PGDATA, after pglogical_create_subscriber finishes (I recommend
using "-s" or "--stop" so pglogical_create_subscriber stops Postgres at the
end of the procedure), it is safe to delete those files from PGDATA.

In your case, it seems you are expecting the subscriber to start on port
65432, as that's what you specified in "--subscriber-dsn". So you need to
set "port = 65432" in the file that you specified in "--postgresql-conf".

Best regards,

On Fri, Sep 25, 2020 at 8:29 AM Michael Banck <michael(dot)banck(at)credativ(dot)de>
wrote:

> Hi,
>
> Am Freitag, den 25.09.2020, 01:18 -0300 schrieb William Ivanski:
> > 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.h
> >
> > postgres(at)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.
>
> If I take out that patch, pglogical_create_subscriber seems to start
> postgres on the default port (5432), without looking at subscriber-dsn,
> so I'm unsure it would work for your 5433 port. Did you test it with
> upstream pglogical and/or how is this supposed to work?
>
> I get a process with
> "/usr/lib/postgresql/12/bin/pglogical_create_subscriber -D
> /tmp/regresscheck.R5THTZ/tmp_check/tmp_backupdir --subscriber-
> name=test_subscriber --subscriber-
> dsn=host=/tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir user=super
> port=65432 dbname=postgres --provider-
> dsn=host=/tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir user=super
> port=65431 dbname=postgres --drop-slot-if-exists -v --hba-conf=regress-
> pg_hba.conf --postgresql-
> conf=/tmp/regresscheck.R5THTZ/tmp_check/tmp_datadir/postgresql.conf"
>
> but in /tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir the sockets are:
>
> .s.PGSQL.5432 .s.PGSQL.5432.lock .s.PGSQL.65431 .s.PGSQL.65431.lock
>
> and the t/010_pglogical_create_subscriber.pl TAP test hangs.
>
>
> Michael
>
> --
> Michael Banck
> Projektleiter / Senior Berater
> Tel.: +49 2166 9901-171
> Fax: +49 2166 9901-100
> Email: michael(dot)banck(at)credativ(dot)de
>
> credativ GmbH, HRB Mönchengladbach 12080
> USt-ID-Nummer: DE204566209
> Trompeterallee 108, 41189 Mönchengladbach
> Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
>
> Unser Umgang mit personenbezogenen Daten unterliegt
> folgenden Bestimmungen: https://www.credativ.de/datenschutz
>
>

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