Is there a way to determine the actual database hostname to which PostgREST is connected?

[christopheraranda]

I am using Amazon Aurora PostgreSQL 15.8 and have one PostgREST pod pointed at the primary read-write endpoint and another PostgREST pod pointed at a read-only replica endpoint. However, when I configured the read-only PostgREST pod according to the documentation, PostgREST returns the following error when starting up:

29/Jan/2025:15:23:47 +0000: Failed to query the PostgreSQL version. {"code":"PGRST000","details":"connection to server at ****, port **** failed: session is not read-only\nconnection to server at ****, port **** failed: session is not read-only\n","hint":null,"message":"Database connection error. Retrying the connection."}

For security reasons, I have removed the hostname, IP address and port, but the values are for the primary read-write endpoint.

I ran select pg_is_in_recovery(); on the read-write primary, and it returned false.
I ran select pg_is_in_recovery(); on the read-only replica, and it returned true.

I'm using libpq environment variables to set configuration for PostgREST and tried the following combinations.

• PGHOST=$read_only,$primary PGTARGETSESSIONATTRS=read-only < failed
• PGHOST=$read_only,$primary < succeeded
• PGHOST=$read_only PGTARGETSESSIONATTRS=read-only < failed
• PGHOST=$read_only < succeeded (this is the current configuration)

My DBAs have confirmed that the read-only replica is indeed a read-only replica. However, when making GET requests to the PostgREST read-only pod, we confirmed that the primary read-write endpoint is receiving the request which is leading me to suspect that it might be a networking issue.

What does PostgREST do to determine whether the host is a read-only replica?

Is there a way to determine the actual database hostname/address to which PostgREST is connected? The only messages that I see in the logs are schema loaded messages, and I have PGRST_LOG_LEVEL set todebug in PostgREST.

[wolfgangwalther]

This doesn't look like a PostgREST issue to me. We are not doing anything special here.

What happens when you connect with psql and PGHOST=$read_only PGTARGETSESSIONATTRS=read-only? Can you run a simple query like the following?

SELECT current_setting('server_version_num')::integer, current_setting('server_version'), version();

[wolfgangwalther]

I connected to the read-only replica and ran the following commands using psql

You're not showing the exact same thing as you do with PostgREST, I think. IIUC, you run PostgREST with environment variables, but you're not doing the same for psql. Could that make a difference?

Hm, maybe not for psql, but potentially for us:

postgrest/src/PostgREST/Config.hs

Lines 561 to 579 in 5619a52

	-- | Adds `target_session_attrs=read-write` to the connection string. This allows using PostgREST listener when multiple hosts are specified in the connection string.
	--
	-- >>> addTargetSessionAttrs "postgres:///postgres?host=/dir/0kN/socket_replica_24378,/dir/0kN/socket"
	-- "postgres:///postgres?host=/dir/0kN/socket_replica_24378,/dir/0kN/socket&target_session_attrs=read-write"
	--
	-- >>> addTargetSessionAttrs "postgresql://host1:123,host2:456/somedb"
	-- "postgresql://host1:123,host2:456/somedb?target_session_attrs=read-write"
	--
	-- >>> addTargetSessionAttrs "postgresql://host1:123,host2:456/somedb?fallback_application_name=foo"
	-- "postgresql://host1:123,host2:456/somedb?fallback_application_name=foo&target_session_attrs=read-write"
	--
	-- adds target_session_attrs despite one existing
	-- >>> addTargetSessionAttrs "postgresql://host1:123,host2:456/somedb?target_session_attrs=read-only"
	-- "postgresql://host1:123,host2:456/somedb?target_session_attrs=read-only&target_session_attrs=read-write"
	--
	-- >>> addTargetSessionAttrs "host=localhost port=5432 dbname=postgres"
	-- "host=localhost port=5432 dbname=postgres target_session_attrs='read-write'"
	addTargetSessionAttrs :: Text -> Text
	addTargetSessionAttrs dbUri = addConnStringOption dbUri "target_session_attrs" "read-write"

I assume you must set target_session_attrs=read-only via db-uri and not via env variable, because I don't think we'll deal with this case well.

[christopheraranda]

Yes, I was connecting using psql from a postgres docker container. I was also able to connect from pgAdmin using the same connection parameters and target_session_attrs=read-only and got the same results using psql.

However, all of our PostgREST connection settings do come from libpq environment variables (including PGTARGETSESSIONATTRS) which I thought was supported according to the documentation.

I can also try connecting PostgREST by setting the PGRST_DB_URI environment variable instead of setting libpq environment variables and see if that makes a difference.

I'm leaning towards this being a networking issue and not a problem with PostgREST since we also have a QA environment that's configured according the instructions in the documentation (also using libpq environment variables), and it's functioning normally.

Also, when setting PGHOST to just the read-only replica, the primary read-write database is still receiving the requests even though PostgREST shouldn't even be aware of its existence. Unfortunately, it's been a challenge to troubleshoot what PostgREST is doing under the hood when connecting.

[wolfgangwalther]

Unfortunately, it's been a challenge to troubleshoot what PostgREST is doing under the hood when connecting.

With the exception of setting target_session_attrs=read-write as mentioned above we are just using libpq. Thus, we are not doing anything different than, let's say, psql.

[christopheraranda]

I stood up a PostgREST container locally and was able to successfully connect to the replica in question. As initially suspected, this was not a PostgREST or database problem, but a problem with our networking config in Istio.

Thanks for the discussion! PostgREST has been my favorite part of our stack. I will close this as resolved.

[steve-chavez]

Unfortunately, it's been a challenge to troubleshoot what PostgREST is doing under the hood when connecting.
With the exception of setting target_session_attrs=read-write as mentioned above we are just using libpq

The above is documented on the "Note" in https://docs.postgrest.org/en/v12/references/listener.html#listener-on-read-replicas. For more visibility, maybe at startup we could log that we force read-write.