Connection Error: Connection ended unexpectedly

i recently deleted and created a new database and now keep getting a connection terminated error. this might’ve been happening before but the frequency was low enough as it was not a very active site. now that i’m looking to release this officially, i’m having these errors - which seem to be preventing users from signing up.

Jun 21 11:06:21 AM  Connection Error: Connection ended unexpectedly
Jun 21 01:08:23 PM  Connection Error: Connection ended unexpectedly

i’ve rebuilt and cleared the cache on my node api and it mostly works but i’m getting random disconnects when trying to use my setup. previous to replacing the db, it was working perfectly.

in the database i’m seeing these logs right when trying to create a user. it successfully creates in the db but gives a ERR_BAD_RESPONSE error.

i replaced the username / db name, which should be obvious

Jun 21 11:06:08 AM  [4-1] user=postgres,db=postgres,app=psql,client=::1LOG:  disconnection: session time: 0:00:00.099 user=postgres database=postgres host=::1 port=56964
Jun 21 11:06:19 AM  [1-1] user=[unknown],db=[unknown],app=[unknown],client=::1LOG:  connection received: host=::1 port=57064
Jun 21 11:06:19 AM  [2-1] user=postgres,db=postgres,app=[unknown],client=::1LOG:  connection authenticated: identity="postgres" method=md5 (/pgdata/dpg-camfmd441ls38dhedjlg-a/pg_hba.conf:10)
Jun 21 11:06:19 AM  [3-1] user=postgres,db=postgres,app=[unknown],client=::1LOG:  connection authorized: user=postgres database=postgres application_name=psql SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
Jun 21 11:06:19 AM  [4-1] user=postgres,db=postgres,app=psql,client=::1LOG:  disconnection: session time: 0:00:00.008 user=postgres database=postgres host=::1 port=57064
Jun 21 11:06:21 AM  [1-1] user=[unknown],db=[unknown],app=[unknown],client=10.204.185.56LOG:  connection received: host=10.204.185.56 port=48862
Jun 21 11:06:21 AM  [2-1] user={{user}},db={{app_name_db}},app=[unknown],client=10.204.185.56LOG:  connection authenticated: identity={{user}} method=md5 (/pgdata/dpg-camfmd441ls38dhedjlg-a/pg_hba.conf:8)
Jun 21 11:06:21 AM  [3-1] user={{user}},db={{app_name_db}},app=[unknown],client=10.204.185.56LOG:  connection authorized: user={{user}} database={{app_name_db}} SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
Jun 21 11:06:28 AM  [1-1] user=[unknown],db=[unknown],app=[unknown],client=::1LOG:  connection received: host=::1 port=57178
Jun 21 11:06:29 AM  [2-1] user=postgres,db=postgres,app=[unknown],client=::1LOG:  connection authenticated: identity="postgres" method=md5 (/pgdata/dpg-camfmd441ls38dhedjlg-a/pg_hba.conf:10)
Jun 21 11:06:29 AM  [3-1] user=postgres,db=postgres,app=[unknown],client=::1LOG:  connection authorized: user=postgres database=postgres application_name=psql SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256)
Jun 21 11:06:29 AM  [4-1] user=postgres,db=postgres,app=psql,client=::1LOG:  disconnection: session time: 0:00:00.105 user=postgres database=postgres host=::1 port=57178

not sure what to do, both my API and my DB are on paid plans

ok, well, i had 2 issues happening.

1 was a missing env on my api (lol), and the other was a knex setting i added to my config file.

i added the pool object and acquireConnectionTimeout. i might trim it back down since i think it’s just the timeout property that was missing for this.

shoutout to render support for the suggestion: Strapi v4, postgres, Error "Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?" · Issue #11860 · strapi/strapi · GitHub

production: {
    client: "pg",
    connection: DATABASE_URL,
    migrations: {
      directory: "./migrations",
      tableName: "knex_migrations",
    },
    pool: {
      min: 0,
      max: 7,
      acquireTimeoutMillis: 300000,
      createTimeoutMillis: 300000,
      destroyTimeoutMillis: 50000,
      idleTimeoutMillis: 300000,
      reapIntervalMillis: 10000,
      createRetryIntervalMillis: 2000,
      propagateCreateError: false,
    },
    acquireConnectionTimeout: 60000,
  },

Two quick follow up questions:

  1. How did you figure out that your problem was caused by a Timeout?

  2. Where is pool used?

I’m asking because I have a possibly related problem Server closed the connection unexpectedly

i hit up render support and they shared me that github link. i wasn’t using strapi, but was using postgres and knex so i read thru the thread and tried it. seems like a common issue right now w/postgres and some providers, like render.

https://github.com/strapi/strapi/issues/11860#issuecomment-1151480439

the only thing that i think mattered for my setup was the acquireConnectionTimeout.

pool is being used in my Knex config file that holds my db settings

Just wanted to add some clarity for any others running into this issue.

I had the same issues as OP. I was using knex + pg to connect my node app to my postgres db on Render. The fix for me was to configure my knex connection pool to have a min of 0.

The knex creator posted about this here: Bug: Connection terminated unexpectedly (postgres) · Issue #3523 · knex/knex · GitHub

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.