Need guidance: Adding PgBouncer to existing production PostgreSQL without data loss

garysfez
PROOP

20 days ago

Hi Railway Team,

I'm running a production e-commerce platform on Railway with a PostgreSQL database (Postgres-Fw71) that's currently experiencing "too many clients" errors due to connection pooling issues.

Current situation:

  • PostgreSQL 16 database with ~6 months of production data

  • Active users making purchases daily

  • Multiple services connecting (Next.js app + PM2 processes + cron jobs)

  • Connection limit being exceeded causing service disruptions

What I want to do:

Add PgBouncer as a connection pooler between my application and existing PostgreSQL database

Critical requirements:

  • Zero data loss - this database contains customer orders, payments, and transaction history

  • Minimal downtime - need to prevent users from writing data during the transition

  • Use existing PostgreSQL - I do NOT want to create a new database or migrate data

My questions:

  1. Should I use the "PgBouncer for Railway" template from the Extensions menu, or is there a better approach?

  1. Will deploying PgBouncer create a NEW PostgreSQL database or can it connect to my existing Postgres-Fw71 instance?

  1. Step-by-step procedure:

  • When should I put my application in maintenance mode?

  • How do I ensure PgBouncer connects to my EXISTING database?

  • What's the exact order of operations to switch from direct connection to PgBouncer?

  • How do I test that everything works before bringing the site back online?

  1. Configuration:

  • What PgBouncer settings do you recommend for a production Next.js app with Prisma?

  • Should I use transaction, session, or statement pool mode?

  • What values for DEFAULT_POOL_SIZE and MAX_CLIENT_CONN?

  1. Rollback plan:

  • If something goes wrong, what's the fastest way to rollback to direct PostgreSQL connection?

  • Will my data remain intact during rollback?

  1. Database backup:

  • Should I create a snapshot before proceeding?

  • What's the recommended backup procedure for a database this size?

Current environment:

  • Service: Postgres-Fw71 (PostgreSQL 16.10)

  • App: kepler (Next.js with Prisma ORM)

  • Current DATABASE_URL: Direct connection to PostgreSQL

  • Connection limit issues started appearing this week

I want to do this correctly the first time to avoid any customer-facing issues. Could you please provide the exact step-by-step procedure?

Thank you for your help!

Best regards,

Gary

Solved$40 Bounty

2 Replies

Railway
BOT

20 days ago

Hey there! We've found the following might help you get unblocked faster:

If you find the answer from one of these, please let us know by solving the thread!

brody
EMPLOYEE

20 days ago

This thread has been marked as public for community involvement, as it does not contain any sensitive or personal information. Any further activity in this thread will be visible to everyone.

Status changed to Open brody • 20 days ago

crisog
PROTop 10% Contributor

20 days ago

Hey!

If you don't feel sure about any step of the following steps, please ask.

Adding a pooler in front of your Postgres shouldn't put your data at risk. However, I'd suggest you do a backup before proceeding. Click your database service then,


Please go to your project, right click the canvas to deploy a template and choose this one.

Once you've done that, don't deploy the template yet. We'll need to configure it so that it can connect to your Postgres database.

If your database service is simply called Postgres and the variables show up like in the picture (note the values have a gray background), you can deploy the template as-is.


If it doesn't, for example,

You'll want to use the variable reference helper, and manually select all of the proper variables referencing your Postgres database (make sure they look like in the picture)


After you are done, it should look like the image below:


Now let's click "Deploy Template" and once it's finished deploying it should look like this:

Then click the PgBouncer service to access the connection variables,


With those URLs you can connect to your database (it uses the same credentials as your original except the internal port is 6432 instead of 5432)

Try connecting to your DB manually and make sure all data is accessible before switching your production app database URL.

Status changed to Solved brody • 20 days ago

Loading...