public inbox for [email protected]help / color / mirror / Atom feed
Fixed port 65432 in pglogical_create_subscriber 5+ messages / 2 participants [nested] [flat]
* Fixed port 65432 in pglogical_create_subscriber @ 2020-09-25 04:18 William Ivanski <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: William Ivanski @ 2020-09-25 04:18 UTC (permalink / raw) To: pgsql-pkg-debian 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_subscrib... -- William Ivanski - 2ndQuadrant PostgreSQL Solutions for the Enterprise ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fixed port 65432 in pglogical_create_subscriber @ 2020-09-25 09:41 Michael Banck <[email protected]> parent: William Ivanski <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Michael Banck @ 2020-09-25 09:41 UTC (permalink / raw) To: William Ivanski <[email protected]>; +Cc: pgsql-pkg-debian Hi William, Am Freitag, den 25.09.2020, 01:18 -0300 schrieb William Ivanski: > 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. [...] > 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]. That patch has been around a long time, I think we had to introduce it in order to get the testsuite running; maybe it was just a shortcut. I agree that it's breaking the user experience though. I can see whether the testsuite got fixed/changed upstream in the meantime and we can drop the patch now. Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fixed port 65432 in pglogical_create_subscriber @ 2020-09-25 11:29 Michael Banck <[email protected]> parent: William Ivanski <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Michael Banck @ 2020-09-25 11:29 UTC (permalink / raw) To: William Ivanski <[email protected]>; +Cc: pgsql-pkg-debian 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='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: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fixed port 65432 in pglogical_create_subscriber @ 2020-09-26 07:16 William Ivanski <[email protected]> parent: Michael Banck <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: William Ivanski @ 2020-09-26 07:16 UTC (permalink / raw) To: Michael Banck <[email protected]>; +Cc: pgsql-pkg-debian 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 <[email protected]> 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='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: [email protected] > > 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fixed port 65432 in pglogical_create_subscriber @ 2020-09-28 08:13 Michael Banck <[email protected]> parent: William Ivanski <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Michael Banck @ 2020-09-28 08:13 UTC (permalink / raw) To: William Ivanski <[email protected]>; +Cc: pgsql-pkg-debian Hi William, Am Samstag, den 26.09.2020, 04:16 -0300 schrieb William Ivanski: > 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". Well, my case is "trying to get the TAP tests running even if another Postgres is listening on port 5432". Indeed you're right that one can use --postgresql-conf (Petr Jelinek also suggested that in the upstream bug I filed[1]), but the TAP tests reuse the same postgresql.conf for both publisher and subscriber, so that would need more changes anyway. Or maybe something more has changed and it's possible to run the pglogical TAP tests these days without a problem if there is another PostgreSQL listening on 5432 already, I'll need to investigate that when I get the time, but that is somewhat low priority. I've uploaded a new pglogical package now which backs out the problematic patch and sets PGPORT in the TAP tests environment before running pglogical_create_subscriber, that seems to work just as well (or at least just as badly as before). Cheers, Michael [1] https://github.com/2ndQuadrant/pglogical/issues/282 -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-09-28 08:13 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-09-25 04:18 Fixed port 65432 in pglogical_create_subscriber William Ivanski <[email protected]> 2020-09-25 09:41 ` Michael Banck <[email protected]> 2020-09-25 11:29 ` Michael Banck <[email protected]> 2020-09-26 07:16 ` William Ivanski <[email protected]> 2020-09-28 08:13 ` Michael Banck <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox