Search code examples
postgresqlpglogical

Setting up pglogical over ssh tunnel, connection reset by peer

I am setting up a pglogical replication between an on-premise PostgreSQL 10.14 (publisher and AWS RDS Postgresql 10.16 (subscriber), but after setting up a subscription, I only get to connection resets. Can anyone spot what I'm doing wrong?

Network setup:

On-premise postgres < jumphost > <-- ssh tunnel --> AWS RDS
internal-ip                          10.2.0.8       db01.some.tld

I have verified that the ssh tunnel is working:

  • I can connect to the on-premise postgres from AWS using psql connecting to the tunnel
  • I can even connect to the on-premise postgres from AWS RDS when using postgres_fwd

Pglogical setup:
on-premise:

  • create node with dsn host=10.2.0.8 dbname=... user=... password=...

AWS:

  • create node with dsn host=db01.some.tld dbname=... user=... password=...
  • create subscription with dsn host=10.2.0.8 dbname=... user=... password=...

Once that is done:

  • pglogical processes are running on both the provider and subscriber
  • but I get to the following errors: AWS:

LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection with an open transaction

On-premise:

LOG: unexpected EOF on client connection with an open transaction

I have tried fiddling around with logging parameters, but couldn't get to anything useful. Similarly I couldn't get anything helpful from the ssh tunnel logs.

Any idea what I could be doing wrong?

Solution

  • Figured it out. In this case:

    The connection was fine. However, there was an issue with the schema of one table on the replica, causing the replication to die.