Debezium - RDS Postgres 9.6.3

priscila...@99taxis.com

unread,

Oct 25, 2017, 5:35:51 PM10/25/17

to debezium

Hi, guys!

I'm trying to test wal2json plugin implemented for Jiri Pechanec, but our team is having a problem with user replication in RDS 9.6.3.

Error: No permission to create it.

The problem is that Debezium needs a user replication.

Do you know if your team have a similar problem and if positive, how did they solve this problem? The AWS Support no help us.

Regards,

Priscila

Jiri Pechanec

unread,

Oct 30, 2017, 2:36:59 AM10/30/17

to debezium

Hi,

I think I need more details. Could you please deascribe the steps you are doing so I can follow you up?

Thanks

J.

priscila...@99taxis.com

unread,

Oct 31, 2017, 7:15:34 AM10/31/17

to debezium

Hi, Jiri.

We are testing the not official 0.7.0 version of debezium.

Following the debezium documentation, we are trying set a user replication in Postgres RDS.

CREATE USER debezium REPLICATION;

The current user belongs to rds_superuser group.

payments=> \du

List of roles

Role name | Attributes | Member of

-----------------+------------------------------------------------------------+-------------------------------------

corpPayments | Create role, Create DB | {rds_superuser,rds_replication} | Password valid until infinity |

rds_replication | Cannot login | {}

rds_superuser | Cannot login | {rds_replication,pg_signal_backend}

rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | Password valid until infinity |

rdsrepladmin | No inheritance, Replication | {} | Password valid until infinity |

I know that it isn't a problem to debezium exactly, but we contact AWS Support and they tell to us that Postgres RDS is not prepared to create a user with replication permission.

So, with this information I decided to ask you:

1) If you have the same problem with RDS, and how do you make it works?

2) However, if did you have a succeeded with 0.7.0v debezium with Postgres RDS (postgres version that supports a WAL and plugin wal2json)?

Thanks,

Priscila

Jiri Pechanec

unread,

Oct 31, 2017, 7:21:19 AM10/31/17

to debezium

Hi Priscilla,

could you please try to use the default admin user? I think this is what I've used to test it.

Also, do you have logical decoding enabled for the RDS instance?

J.

priscila...@99taxis.com

unread,

Oct 31, 2017, 7:45:28 AM10/31/17

to debezium

We tried access with rdsadmin user, because it have a full access and Replication permission, but is impossible to know the password:

rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | Password valid until infinity |

We also tried to create a user that is member of replication group, but we did not succeed:

corpPayments | Create role, Create DB | {rds_superuser,rds_replication} | Password valid until infinity |

rds_superuser | Cannot login | {rds_replication,pg_signal_backend}

So, our team don't know what we can do to make debezium works.

Jiri Pechanec

unread,

Oct 31, 2017, 8:25:11 AM10/31/17

to debezium

I've used the user that you can create when the new instance is created

debezium=> \du

List of roles

Role name | Attributes | Member of

-------------------+------------------------------------------------+-------------------------------------

debezium | Create role, Create DB +| {rds_superuser}

| Password valid until infinity |

pg_signal_backend | Cannot login | {}

rds_replication | Cannot login | {}

rds_superuser | Cannot login | {rds_replication,pg_signal_backend}

rdsadmin | Superuser, Create role, Create DB, Replication+| {}

| Password valid until infinity |

rdsrepladmin | No inheritance, Cannot login, Replication | {}

I've used this one it worked nicely.

J.

priscila...@99taxis.com

unread,

Oct 31, 2017, 9:16:45 AM10/31/17

to debezium

Ok. We set our database with the same configs that you:

psql (9.6.5, server 9.6.3)
Type "help" for help.

debezium=> \du
                                                   List of roles
    Role name    |                         Attributes                         |              Member of

-----------------+------------------------------------------------------------+-------------------------------------

 debezium        | Create role, Create DB                                    +| {rds_superuser}
                 | Password valid until infinity                              |

 rds_replication | Cannot login                                               | {}
 rds_superuser   | Cannot login                                               | {rds_replication,pg_signal_backend}

rdsadmin | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}

                 | Password valid until infinity                              |
 rdsrepladmin    | No inheritance, Cannot login, Replication                  | {}

 test            | Cannot login                                               | {}
 testuser        |                                                            | {rds_superuser}

In after, we register a connector in or Kafka Connect with infos:

{
    "name": "payments-connector",
    "config": {
        "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
        "tasks.max": "1",
        "database.hostname": "hostname.rds.amazonaws.com",
        "database.port": "5432",
        "database.user": "debezium",
        "database.password": "password",
        "database.dbname" : "dbname",
        "database.server.name": "debezium_server",
        "database.history.kafka.bootstrap.servers": "kafka:9092",
	"plugin.name": "wal2json",
	"database.sslmode": "disable",
	"slot.name": "debezium_slot",
	"snapshot.mode": "never"
    }
}

In the first connection, we can watching the connector to recover the values from WAL but, if we make a update in a register on postgres database we receives this error in log connector:

2017-10-31 12:49:59,488 ERROR  ||  Exception thrown while calling task.commit()   [org.apache.kafka.connect.runtime.WorkerSourceTask]
org.apache.kafka.connect.errors.ConnectException: org.postgresql.util.PSQLException: Database connection failed when writing to copy
    at io.debezium.connector.postgresql.RecordsStreamProducer.commit(RecordsStreamProducer.java:143)
    at io.debezium.connector.postgresql.PostgresConnectorTask.commit(PostgresConnectorTask.java:154)
    at org.apache.kafka.connect.runtime.WorkerSourceTask.commitSourceTask(WorkerSourceTask.java:383)
    at org.apache.kafka.connect.runtime.WorkerSourceTask.commitOffsets(WorkerSourceTask.java:330)
    at org.apache.kafka.connect.runtime.SourceTaskOffsetCommitter.commit(SourceTaskOffsetCommitter.java:108)
    at org.apache.kafka.connect.runtime.SourceTaskOffsetCommitter.access$000(SourceTaskOffsetCommitter.java:45)
    at org.apache.kafka.connect.runtime.SourceTaskOffsetCommitter$1.run(SourceTaskOffsetCommitter.java:82)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: Database connection failed when writing to copy
    at org.postgresql.core.v3.QueryExecutorImpl.flushCopy(QueryExecutorImpl.java:942)
    at org.postgresql.core.v3.CopyDualImpl.flushCopy(CopyDualImpl.java:23)
    at org.postgresql.core.v3.replication.V3PGReplicationStream.updateStatusInternal(V3PGReplicationStream.java:176)
    at org.postgresql.core.v3.replication.V3PGReplicationStream.forceUpdateStatus(V3PGReplicationStream.java:99)
    at io.debezium.connector.postgresql.connection.PostgresReplicationConnection$1.flushLSN(PostgresReplicationConnection.java:213)
    at io.debezium.connector.postgresql.RecordsStreamProducer.commit(RecordsStreamProducer.java:138)
    ... 13 more
Caused by: java.net.SocketException: Broken pipe (Write failed)
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at org.postgresql.core.PGStream.flush(PGStream.java:553)
    at org.postgresql.core.v3.QueryExecutorImpl.flushCopy(QueryExecutorImpl.java:939)
    ... 18 more

We think that it happens on error in the replication user. When we test with mysql RDS, it works well.

I know that had a issue opened about this error: https://issues.jboss.org/browse/DBZ-273

Do you know something about this?

Jiri Pechanec

unread,

Nov 1, 2017, 5:49:14 AM11/1/17

to debezium

Hi,

I am not sure if I understand what does it mean "make a update in a register". Have you reconfigured the connector? Or have you tried to restart it?