Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kM4SD-0007ZZ-MX for pgsql-pkg-debian@arkaria.postgresql.org; Sat, 26 Sep 2020 07:17:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kM4SB-0004ym-RV for pgsql-pkg-debian@arkaria.postgresql.org; Sat, 26 Sep 2020 07:17:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kM4SB-0004yf-FK for pgsql-pkg-debian@lists.postgresql.org; Sat, 26 Sep 2020 07:17:07 +0000 Received: from mail-il1-x143.google.com ([2607:f8b0:4864:20::143]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kM4S4-0002nG-9b for pgsql-pkg-debian@postgresql.org; Sat, 26 Sep 2020 07:17:06 +0000 Received: by mail-il1-x143.google.com with SMTP id s88so4710402ilb.6 for ; Sat, 26 Sep 2020 00:17:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=uN08J+OOntKezAnIGJNjZDj9emZJNG1IpHII8YrM6ig=; b=Z0q1sVdUv3fKve3+YPqsiojHsDtrEfQrnRuuAb6xZTT2w/GbfIdht7ftuVnjHMSweL 7pDhGLLRdLiSqHd06IZ9heYeYAnU1moqggb6dARZfFt5T7BdRgqF7PTWPGiTPu7KLTL4 XsDir3r+h8VJqM5zgK1QrG6KVgeCBccSPJfjvtuRy5ARE45uYv47r+dyogB6Fli+pocC W9qiL0zck4lfR1rSSyOC9gJWaoE8ilTAWvSsUKlv0ml2/mlNrVqFWR5nYQqjpVoiV8HO ueFaeUH7HaX8GnR/8n5gTLFfMfD9DvocAXNkrcSuA9mbXnpUWUO4cm8DSfkYjIboK71L Ob3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=uN08J+OOntKezAnIGJNjZDj9emZJNG1IpHII8YrM6ig=; b=XWiLgaCNmpdw3xSeoC9nBFCXJwu6QR7jf3xnba1pPSjvRg4/VWTR6QJ76N5gS6WFSF GcFV+2hAMQaKilCu5ITZNXYBMKsdULjp3OnCSonxmGNrZvZT/x+IOq+dW8TldBXLx0Ov yiKfsYCpDEm5skhi68namoRkWN5x8+I7ksDzqDQAEhocujzYo3J23fMNSQYuolF5DRHr fANCBqRn+7BL7ps6DeDRD+cHCoaLzQdylOfqohUSGXN2tR9VHPr4Wilg63H0y1LXxuRT otgQ/0kFFqLKnESmO/3vEjmCtwZADOlenO8782RX96dL+p0DhAXOGkahwvfnesB4Pb/C 6Ztw== X-Gm-Message-State: AOAM5313bOx1v7OusIFJAap5rGUHosdecuD3pIVntMzPnbPqXPxBvwtI brbv99eLDJCXd0qMriGf0+dnbq2ula2nHC2MIjAMxObhxh3t2A== X-Google-Smtp-Source: ABdhPJzg2wRQsAQRBjiLxAWIkdOdFkbvPLLnCpdpy9Y5AjbngleaTQ+v14lc06nzqWTtJK9j6HVrOYxpLSOlVdQBKgU= X-Received: by 2002:a92:d5c5:: with SMTP id d5mr3288451ilq.204.1601104619151; Sat, 26 Sep 2020 00:16:59 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: William Ivanski Date: Sat, 26 Sep 2020 04:16:48 -0300 Message-ID: Subject: Re: Fixed port 65432 in pglogical_create_subscriber To: Michael Banck Cc: pgsql-pkg-debian@postgresql.org Content-Type: multipart/alternative; boundary="00000000000012bb7a05b0323882" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000012bb7a05b0323882 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D 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 =3D 65432" in the file that you specified in "--postgresql-conf". Best regards, On Fri, Sep 25, 2020 at 8:29 AM Michael Banck 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@subserver ~ $ > /usr/lib/postgresql/9.4/bin/pglogical_create_subscriber \ > > > -D /var/lib/postgresql/9.4/main/ -n subserver \ > > > --subscriber-dsn=3D'host=3D127.0.0.1 port=3D5433 dbname=3Dmydb > user=3Dpglogical' \ > > > --provider-dsn=3D'host=3D192.168.0.100 port=3D5432 dbname=3Dmydb > user=3Dpglogical' \ > > > --hba-conf=3D/etc/postgresql/9.4/main/pg_hba.conf \ > > > --postgresql-conf=3D/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 end= s] > > > > 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=3Dtest_subscriber --subscriber- > dsn=3Dhost=3D/tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir user=3Dsupe= r > port=3D65432 dbname=3Dpostgres --provider- > dsn=3Dhost=3D/tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir user=3Dsupe= r > port=3D65431 dbname=3Dpostgres --drop-slot-if-exists -v --hba-conf=3Dregr= ess- > pg_hba.conf --postgresql- > conf=3D/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.banck@credativ.de > > credativ GmbH, HRB M=C3=B6nchengladbach 12080 > USt-ID-Nummer: DE204566209 > Trompeterallee 108, 41189 M=C3=B6nchengladbach > Gesch=C3=A4ftsf=C3=BChrung: Dr. Michael Meskes, J=C3=B6rg Folz, Sascha He= uer > > Unser Umgang mit personenbezogenen Daten unterliegt > folgenden Bestimmungen: https://www.credativ.de/datenschutz > > --=20 William Ivanski - 2ndQuadrant PostgreSQL Solutions for the Enterprise --00000000000012bb7a05b0323882 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Michael,

Thanks for your quick=C2=A0= response and for testing without the patch.

I test= ed with upstream pglogical, and it works, I'll explain.

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

In y= our case, it seems you are expecting the subscriber to start on port 65432,= as that's what you specified in "--subscriber-dsn". So you n= eed to set "port =3D 65432" in the file that you specified in &qu= ot;--postgresql-conf".

Best regards,

On = Fri, Sep 25, 2020 at 8:29 AM Michael Banck <michael.banck@credativ.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@subserver ~ $ /usr/lib/postgresql/9.4/bin/pglogical_create_su= bscriber \
> >=C2=A0 -D /var/lib/postgresql/9.4/main/ -n subserver \
> >=C2=A0 --subscriber-dsn=3D'host=3D127.0.0.1 port=3D5433 dbname= =3Dmydb user=3Dpglogical' \
> >=C2=A0 --provider-dsn=3D'host=3D192.168.0.100 port=3D5432 dbna= me=3Dmydb user=3Dpglogical' \
> >=C2=A0 --hba-conf=3D/etc/postgresql/9.4/main/pg_hba.conf \
> >=C2=A0 --postgresql-conf=3D/etc/postgresql/9.4/main/postgresql.con= f \
> >=C2=A0 --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 a= ccept connections ...
>
> Removing old pglogical configuration ...
> Waiting for PostgreSQL to shutdown ...
> Initializing pglogical on the subscriber node:
> Waiting for PostgreSQL to accept connections ................[never en= ds]
>
> 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=3Dtest_subscriber --subscriber-
dsn=3Dhost=3D/tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir user=3Dsuper<= br> port=3D65432 dbname=3Dpostgres --provider-
dsn=3Dhost=3D/tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir user=3Dsuper<= br> port=3D65431 dbname=3Dpostgres --drop-slot-if-exists -v --hba-conf=3Dregres= s-
pg_hba.conf --postgresql-
conf=3D/tmp/regresscheck.R5THTZ/tmp_check/tmp_datadir/postgresql.conf"=

but in /tmp/regresscheck.R5THTZ/tmp_check/tmp_socketdir the sockets are:
.s.PGSQL.5432=C2=A0 .s.PGSQL.5432.lock=C2=A0 .s.PGSQL.65431=C2=A0 .s.PGSQL.= 65431.lock

and the t/010_pglogical_create_subscriber.pl TAP test h= angs.


Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:=C2=A0 +49 2166 9901-100
Email: micha= el.banck@credativ.de

credativ GmbH, HRB M=C3=B6nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M=C3=B6nchengladbach
Gesch=C3=A4ftsf=C3=BChrung: Dr. Michael Meskes, J=C3=B6rg Folz, Sascha Heue= r

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz


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