0

I'm new to postgres and I'm trying to configure table level logical replication(PUBLICATION/SUBSCRIPTION) on postgres-12.

I have updated postgresql.conf file and updated wal_level=replica. Also, then created 2 databases(test1 and test2). Here is what , I'm doing:

test1=# create table t1(a int primary key, b int);
CREATE TABLE
test1=# insert into t1 values(1, 1);
INSERT 0 1
test1=# create publication my_pub for table t1;
CREATE PUBLICATION

test2=# CREATE TABLE t1(a int primary key, b int);
CREATE TABLE
test2=# CREATE SUBSCRIPTION my_sub CONNECTION 'host=localhost port=5432 dbname=test2 user=postgres 
password=password' PUBLICATION my_pub;
invalid integer value "CONNECTION" for connection option "port"
Previous connection kept

When creating subscription I'm getting this message: invalid integer value "CONNECTION" for connection option "port" Previous connection kept and I'm unable to add this table into replication. In my postgresql.conf file as well I have mentioned 5432 port only. However, I'm unable to understand why it's saying invalid integer value

4
  • 1
    I can't reproduce this. Are test1 and test2 located in the same database instance?
    – jjanes
    Aug 17 '20 at 1:16
  • test1 and test2 belongs to same cluster Aug 22 '20 at 17:33
  • 1
    If I run the non-prompt parts of the above with two databases in the same cluster, I very reproducibly get an internal, undetected deadlock. Just as the docs say you will.
    – jjanes
    Aug 22 '20 at 19:54
  • Thanks @jjanes - I got it working when tried from 2 separate clusters. Aug 23 '20 at 20:22
0

It seems to me that what you really must have done is something like:

\c CREATE SUBSCRIPTION my_sub CONNECTION ...

The arguments to \c are positional, and the 4th position belongs to the port.

I see no other way your message could include 'Previous connection kept', which is exclusively generated by attempts to use \c or \connect

2
  • no @jjanes - I didn't run it with \c. Do you have any other method to make it working for me? Aug 22 '20 at 18:21
  • Maybe you don't think you did that, but there is no other way to have gotten that error message. In the absence of a reproducible test case, I maintain that what you actually did is not what you think you did.
    – jjanes
    Aug 22 '20 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.