Connection terminated unexpectedly in pg module after a few minutes

[BEpresent]

Describe the Bug

Description:

When deploying on railway.app and also running locally, the application starts successfully and operates as expected initially. However, after a few minutes, it throws an "Unhandled 'error' event" and terminates the connection unexpectedly. Both instances of the issue seem to originate from the pg module, which is used for PostgreSQL interactions. So it happens both locally Connection terminated unexpectedly, and on railway.app (error code 57P01). Sorry if these are two different issues, but both seem to originate from pg ?

Error Details:

On railway.app (Node.js v18.17.1):

error: terminating connection due to administrator command
severity: 'FATAL',
code: '57P01',
file: 'postgres.c',
line: '3221',
routine: 'ProcessInterrupts'
Locally (Node.js v20.8.0):

Locally I get

node:events:492
      throw er; // Unhandled 'error' event

Error: Connection terminated unexpectedly
    at Connection.<anonymous> (.../node_modules/pg/lib/client.js:132:73)
    at Object.onceWrapper (node:events:628:28)
    at Connection.emit (node:events:514:28)
    at Socket.<anonymous> (.../node_modules/pg/lib/connection.js:63:12)
    at Socket.emit (node:events:526:35)
    at TCP.<anonymous> (node:net:337:12)
Emitted 'error' event on Client instance at:
    at Client._handleErrorEvent (.../node_modules/pg/lib/client.js:341:10)
    at Connection.<anonymous> (.../node_modules/pg/lib/client.js:150:16)
    at Object.onceWrapper (node:events:628:28)
    [... lines matching original stack trace ...]
    at TCP.<anonymous> (node:net:337:12)

Observations:
The application works as intended initially, but the described issue arises only after a few minutes of operation.

This here is the error log on railway.app:

node:events:492

throw er; // Unhandled 'error' event

^

error: terminating connection due to administrator command

at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:287:98)

at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:126:29)

at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:39:38)

at TLSSocket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42)

at TLSSocket.emit (node:events:514:28)

at addChunk (node:internal/streams/readable:324:12)

at readableAddChunk (node:internal/streams/readable:297:9)

at Readable.push (node:internal/streams/readable:234:10)

at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)

Emitted 'error' event on Client instance at:

at Client._handleErrorEvent (/app/node_modules/pg/lib/client.js:341:10)

at Client._handleErrorMessage (/app/node_modules/pg/lib/client.js:352:12)

at Connection.emit (node:events:514:28)

at /app/node_modules/pg/lib/connection.js:117:12

at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:40:17)

at TLSSocket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42)

[... lines matching original stack trace ...]

at Readable.push (node:internal/streams/readable:234:10) {

length: 116,

severity: 'FATAL',

code: '57P01',

detail: undefined,

hint: undefined,

position: undefined,

internalPosition: undefined,

internalQuery: undefined,

where: undefined,

schema: undefined,

table: undefined,

column: undefined,

dataType: undefined,

constraint: undefined,

file: 'postgres.c',

line: '3221',

routine: 'ProcessInterrupts'

}

Node.js v18.17.1

error Command failed with exit code 1.

To Reproduce

My payload.config.ts

import path from "path";

import { postgresAdapter } from "@payloadcms/db-postgres";
import { lexicalEditor } from "@payloadcms/richtext-lexical"; // beta

import { buildConfig } from "payload/config";
import { webpackBundler } from "@payloadcms/bundler-webpack";
import Users from "./collections/Users";
import Post from "./collections/Post";
import { Media } from "./collections/Media";

export default buildConfig({
  admin: {
    user: Users.slug,
    bundler: webpackBundler(), //webpackBundler(), viteBundler()
  },
  editor: lexicalEditor({}), // or slateEditor({})
  collections: [Users, Post, Media],
  typescript: {
    outputFile: path.resolve(__dirname, "payload-types.ts"),
  },
  graphQL: {
    // schemaOutputFile: path.resolve(__dirname, "generated-schema.graphql"),
    disable: true,
  },
  db: postgresAdapter({
    pool: {
      connectionString: process.env.DATABASE_URI,
    },
  }),
});

Payload Version

^2.0.0

Adapters and Plugins

postgresAdapter, webpackBundler

[joschan21]

Got the same issue!

[jmikrut]

Hey @BEpresent and @joschan21 — this is a problem with your database, and is not something related to Payload itself. It appears your connection is closing for some reason. Is your database hosted on Railway? I would bring this up with them or see if you can Google the issue unrelated to Payload.

I am happy to continue to help here, but there is nothing actionable on Payload's side, so I will convert this to a discussion instead.

Thank you!

[joschan21]

Hi @jmikrut, thanks for your answer!

I deployed this project to Railway with a Postgres database provided by Neon. One weird thing I've noticed is that this error happens both locally and in production every exact 5 minutes.

Great to know that this is not Payload related. Switching over to Mongo which seems to work just fine, I wonder if this is related to either Railway or Neon then.

[emmittjames]

Neon automatically suspends a compute after 5 minutes of inactivity which is why it happens after 5 minutes exactly. You can read more about it here https://neon.tech/docs/connect/connection-errors#terminating-connection-due-to-administrator-command

[BEpresent]

Hi @jmikrut, thanks for your answer!

I deployed this project to Railway with a Postgres database provided by Neon. One weird thing I've noticed is that this error happens both locally and in production every exact 5 minutes.

Great to know that this is not Payload related. Switching over to Mongo which seems to work just fine, I wonder if this is related to either Railway or Neon then.

Thanks @jmikrut for the response. I got this using a Vercel hosted Postgres database (that uses neon as a provider), and locally using a local docker Postgres database. Currently I would probably also switch back to Mongo db if there are issues with the pg library.

Usually after a few minutes the connection is lost and I have to restart the app. The error code 57P01 suggests some pooling related issue but I need to look further into this, so it is likely pg related and not Payload related.

[steveninety]

Was anyone able to solve this? Dealing with it right now in production (Railway)

[BEpresent]

I got it working with a normal postgres db on railway, not with a serverless db (like neon, vercel). My hypothesis would be that serverless dbs cause this issue. Not sure if support for Supabase has been added but vanilla postgres worked.

[steveninety]

I got it working with a normal postgres db on railway, not with a serverless db (like neon, vercel). My hypothesis would be that serverless dbs cause this issue. Not sure if support for Supabase has been added but vanilla postgres worked.

Mine is a normal postgress db on railway and got the issue, so it has to be something else than serverless causing this...

[paulpopus]

On friday we pushed several fixes to the pg adapter package, are you seeing these issues on the latest version? @steveninety

[steveninety]

Just upgraded and will update here within 1-2 days if the issue persists. You can assume that no update means that everything's working properly now :)

[steveninety]

Update: The issue persists despite updgrading to the latest version of payload and the pg adapter package.

node:events:491

throw er; // Unhandled 'error' event

^

Error: Connection terminated unexpectedly

at Connection.<anonymous> (/home/node/app/node_modules/pg/lib/client.js:132:73)

at Object.onceWrapper (node:events:627:28)

at Connection.emit (node:events:513:28)

at Socket.<anonymous> (/home/node/app/node_modules/pg/lib/connection.js:63:12)

at Socket.emit (node:events:513:28)

at TCP.<anonymous> (node:net:313:12)

Emitted 'error' event on Client instance at:

at Client._handleErrorEvent (/home/node/app/node_modules/pg/lib/client.js:341:10)

at Connection.<anonymous> (/home/node/app/node_modules/pg/lib/client.js:150:16)

at Object.onceWrapper (node:events:627:28)

[... lines matching original stack trace ...]

at TCP.<anonymous> (node:net:313:12)

For now I just make Railway restart the app after the container stops - but obviously that's just a dirty workaround.

[AshtarCodes]

I have this same issue while using AWS Aurora Postgres. I had to increase the idle timeout and get rid of the connection timeout (not ideal since there is no error when failing to connect, just hangs forever.)

const postgresConfig: PostgresAdapterArgs = {
  // pool config options are passed to node-postgres: https://node-postgres.com/apis/pool
  pool: {
    ...connectionPoolOptions,
    // node-postgres: max clients the pool should contain. default is 10.
    max: 10,
    /** number of milliseconds to wait before timing out when connecting a new client
     *  by default this is 0 which means no timeout
     */
    connectionTimeoutMillis: 0,
    /** number of milliseconds a client must sit idle in the pool and not be checked out
     *  before it is disconnected from the backend and discarded
     *  default is 10000 (10 seconds) - set to 0 to disable auto-disconnection of idle clients
     */
    idleTimeoutMillis: 1000 * 60,
    allowExitOnIdle: false
  },
};