Event triggers don't work on read replicas

[steve-chavez]

Problem

See https://stackoverflow.com/questions/29931136/postgresql-replica-where-i-can-create-triggers/29932655#29932655.

This means that PostgREST NOTIFY reloading won’t work in read replicas.

Previously, I thought this was only a problem with just LISTEN/NOTIFY on read replicas (#2781), but it's worse.

Solution

Enable a configurable connection string for the LISTEN channel.

db-channel-uri = "postgres://authenticator:mysecretpassword@localhost:5433/postgres?parameters=val"

Similar to the https://postgrest.org/en/v12/references/configuration.html#db-uri. By default it will use the same value as the db-uri.

This way the LISTEN channel can point to the primary instead of the replica and notify reloading can work normally.

---

• This should also close LISTEN/NOTIFY doesn't work on read replicas #2781
• Testing is not possible right now because we lack the tooling. See Missing tests - recovery tests #1766). We can note it as a TODO in that issue.

[wolfgangwalther]

Previously, I thought this was only a problem with just LISTEN/NOTIFY on read replicas (#2781), but it's worse.

In which way is it worse than what you thought before? It's exactly that, no?

Enable a configurable connection string for the LISTEN channel.

Does this really make sense as long as we don't have support for read replica connections to route reads to, in the first place? How does this come up in practice without?

[steve-chavez]

Does this really make sense as long as we don't have support for read replica connections to route reads to, in the first place? How does this come up in practice without?

It makes sense in a setup where you have a PostgREST on the primary and with a replica that has its own PostgREST too.

Yeah ideally it would be a single PostgREST with #2429 but that's not coming anytime soon. This would be a much easier to implement alternative in the meantime.

It doesn't add much complexity too. Once we have read replica support I guess we could drop the config as well.

[wolfgangwalther]

How do both db-uri and db-channel-uri and the PG_ environment variables interact?

IMHO, this will quickly become very annoying to configure. For example, in a kubernetes setup, it's much more practical to pass all the database config options via separate environment variables - including the host name, which could already be available in a secret to consume, provided by your favorite pg operator. But now I have to pass those in a db-uri format. Meh.

How about using PostgreSQL's "Multiple Hosts" feature in libpq?

We could do the following:

• Tell the user to provide the hosts as a comma separated list, i.e. read-only.host,primary.host. This should also work with PG_HOST, which the k8s pg operator could then provide as a list, too, depending on how many read-only nodes there are.
• Use target_session_attrs=read-write (or primary, not sure) for the listener connection.

This should make postgrest pick the first connection, which is read-only, for all regular connections, but use the primary node for the listener.

Advantages:

• A more "postgres native" approach.
• Gives us a head start to implement read only replicas via the same mechanism - i.e. flag some of our pool connections as read-only or read-write and pass this parameter to target_session_attrs, then put the respective requests primarily on those connections.
• Most importantly: Should give some kind of connection error for the listener connection, when the user only provides read only nodes, but still tries to enable the listener. Much better default than right now, where it just silently doesn't work.

[steve-chavez]

@wolfgangwalther Not sure I get your idea. So I've just tried it multiple hosts and target_session_attrs and they work like this:

# having only one pg listening on 5432 this will succeed, since libpq will try the second host
psql postgresql://user:pass@localhost:5433,localhost:5432/postgres

# this will now fail since our pg is read-write and we tell libpq to only use a read-only session
psql postgresql://user:pass@localhost:5433,localhost:5432/postgres?target_session_attrs=read-only

connection to server at "localhost" (::1), port 5432 failed: session is not read-only

target_session_attrs is always applied to all hosts.

Use target_session_attrs=read-write (or primary, not sure) for the listener connection.
This should make postgrest pick the first connection, which is read-only, for all regular connections, but use the primary node for the listener.

Is the above true? So on the instance having a read replica + postgrest, we'd have:

PG_HOST="postgresql://user:pass@readonly.host,readwrite.host/postgres?target_session_attrs=read-write"

Then all connections will be routed to the readwrite.host, not just the listener connection. Is it possible to apply the target_session_attrs for just one host? It doesn't seem so.

I'm not seeing how is possible to solve this without 2 db-uris, one for the pool and another one for the listener.

[steve-chavez]

Use target_session_attrs=read-write (or primary, not sure) for the listener connection.

Oh, perhaps you meant to dynamically inject that target_session_attrs=read-write to the internal listener db-uri? Like what we do for the fallback_application_name?

Yeah, that could work! Great idea! 💡

(PG_HOST/db-uri would not contain target_session_attrs, but if it's specified.. I guess we would need to force read-write)

[wolfgangwalther]

Oh, perhaps you meant to dynamically inject that target_session_attrs=read-write to the internal listener db-uri? Like what we do for the fallback_application_name?

That's exactly what I meant to say :)