Postgrest calls fail with "Database connection lost..." but no connection issues are captured in stdout

[nathan-rice]

Environment

• PostgreSQL version: 10.6 on AWS RDS
• PostgREST version: 5.0
• Operating system: Ubuntu 16.04

Description of issue

Database seems to work fine, can psql in and run queries no problem. Postgrest starts up just fine with no errors connecting to the database reported, but when any rest call is made it fails with a 503 and the error {"message":"Database connection lost, retrying the connection."}.

According to RDS performance insights, the following query is causing some issues:

with view_columns as (
        select
            c.oid as view_oid,
            a.attname::information_schema.sql_identifier as column_name
        from pg_attribute a
        join pg_class c on a.attrelid = c.oid
        join pg_namespace nc on c.relnamespace = nc.oid
        where
            not pg_is_other_temp_schema(nc.oid)
            and a.attnum > 0
            and not a.attisdropped
            and (c.relkind = 'v'::"char")
            and nc.nspname not in ('information_schema',...

[steve-chavez]

Try upgrading postgrest, several performance optimizations were made since 5.0.

[nathan-rice]

I tried a 6.0 upgrade, the problem seems to be persistent. Is there some way I can get more detailed debugging information out of postgres regarding what was happening when the connection dropped?

[steve-chavez]

A different query is used starting from 5.2(no with view_columns as ...) make sure to double check the upgrade, if there's an issue it should be a different one.

Regarding debugging, see http://postgrest.org/en/v5.2/admin.html#database-logs. Not sure if the same config params are exposed in RDS, but there should be a way to get more detailed logging.

[steve-chavez]

This is the kind of error I get when something is wrong with the connection:

./postgrest support/configs/config
Listening on port 3000
Attempting to connect to the database...
{"details":"could not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (::1) and accepting\n\tTCP/IP connections on port 5432?\ncould not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (127.0.0.1) and accepting\n\tTCP/IP connections on port 5432?\n","code":"","message":"Database connection error"}
Attempting to reconnect to the database in 0 seconds...
{"details":"could not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (::1) and accepting\n\tTCP/IP
 connections on port 5432?\ncould not connect to server: Connection refused\n\tIs the server running on host \"localhost\" (127.0.0.1) a
nd accepting\n\tTCP/IP connections on port 5432?\n","code":"","message":"Database connection error"}
Attempting to reconnect to the database in 1 seconds...

There should be some error when you try to connect to RDS.

[nathan-rice]

I don't get any errors in the postgrest stdout log. The problem resolved after another engineer went through and rebuilt the ec2 instance from scratch, so unfortunately we don't know what change fixed it, or what exactly postgrest didn't like.

It might be helpful to have higher levels of debug output verbosity for this type of situation.

[steve-chavez]

Definitely taking note of this for #540, thanks for the feedback.