The best way is to pg_dump and pg_restore manually.

Dear ray, could you please help me with this, I have a production app with postgreSQL db and I have created staging env, however the postgres data are empty on staging, I was trying to do pgdump and pgrestore as you suggested but it wiped all my data from production. I have looked at the DATABASE_URL and DATABASE_PUBLIC_URL and they are exactly same between the production and staging env, is there a way I can have an exact seperate copy of the production in staging? Thanks

the fact that DATABASE_URL is identical between production and staging means they're pointing to the same database, which is why your restore wiped production. when you create a new environment in railway, you need to add a separate postgres service to that environment (click + New in your staging environment and add postgres). each environment should have its own database instance with unique credentials.

once you have a separate staging postgres, use reference variables like ${{Postgres.DATABASE_URL}} in your app service instead of hardcoding the url. that way each environment automatically gets its own database connection. then you can safely pg_dump from production and pg_restore to staging without them touching each other.

if you need to recover the wiped production data, check if railway has automatic backups enabled for your postgres service (Settings > Backups). you might be able to restore from a recent snapshot.

Thanks Yusuf for quick reply, yes I had daily backups so I was able to restore it. Could I please ask your help regarding pgdump and pgrestore, once I have seperate staging postgres instance, how exactly I can do pgdump and pgrestore, should I do this directly on railway or via ssh? Do I have to use DATABASE_URL or PUBLIC URL? thanks

no worries!! you'll do this from your local machine using the PUBLIC_URL since you can't ssh into railway's managed postgres directly. grab the public connection details from both databases in the railway dashboard.

first dump your production data locally:

pg_dump "postgresql://user:pass@productionhost.proxy.rlwy.net:port/railway" > backup.sql

then restore to your staging database:

psql "postgresql://user:pass@staginghost.proxy.rlwy.net:port/railway" < backup.sql

replace the connection strings with your actual DATABASE_PUBLIC_URL values from each environment. let me know how it goes!

Thanks Yusuf! you guys are awesome. Really appreciate your prompt response.