Aurora Postgres Logical Replication in Java

Question

My goal is to stream the Postgres WAL inside a system that runs on the JVM. I am using Aurora RDS Postgres.

If I try to run the following:

```
    PGReplicationStream stream =
        pgConnection.getReplicationAPI()
            .replicationStream()
            .logical()
            .withSlotName("test_slot")
            .withStartPosition(lsn)
            .start();
```

I get the error:

```
; (err) ERROR: syntax error at or near "START_REPLICATION"
; (err)   Position: 1
```

Postgres logs the following command:

```
:ERROR:  syntax error at or near "START_REPLICATION" at character 1
:STATEMENT:  START_REPLICATION SLOT test_slot LOGICAL 0/40DCC70;
```

This does look like the command the jdbc driver is trying to write. (as seen [here](https://github.com/pgjdbc/pgjdbc/blob/f61fbfe7b72ccf2ca0ac2e2c366230fdb93260e5/pgjdbc/src/main/java/org/postgresql/core/v3/replication/V3ReplicationProtocol.java#L91-L96))

START_REPLICATION does seem to be a command Postgres supports. Is it that Aurora RDS does not support this? If so, is there an alternative way to do this?

stopa · Accepted Answer

The problem was that I was using the wrong `connection`. I had a HikariCP, and thought I could just take one of the connections from it.

But what you need to do, is to create a specific 'replication' connection. Something like:

```
    Properties props = new Properties();
    PGProperty.USER.set(props, "user");
    PGProperty.PASSWORD.set(props, "pass");
    PGProperty.ASSUME_MIN_SERVER_VERSION.set(props, "9.4");
    PGProperty.REPLICATION.set(props, "database");
    PGProperty.PREFER_QUERY_MODE.set(props, "simple");

Connection con = DriverManager.getConnection(url, props);
```

Aurora Postgres Logical Replication in Java

Relevant content