How to easily clone a production postgresql database to staging

Question

I have data in a production database but want to test out some new features so I've set up a staging environment on the same server and want to use the data from the production database with my staging app.

How can I replicate the data from my rails_production database to my rails_staging database, they both have the same owner (postgres) and share the same password.

Solution

The easiest way is to use a backup using pg_dump and then restore it to the new database by piping that output into psql. If you can connect to both boxes you can also pipe directly from pg_dump into psql without using the local filesystem.

This method is described here:

Copying PostgreSQL database to another server

Comments

[raphael_turtle]

they're both on the same server

[Zeki]

OK, so pg_dump source | psql destination should pipe the data from source to destination.

[BAE]

@Zehi, how to make sure that everyhing is OK even some errors (network issue) happened? How to check whether the two databases are the same after migration?

[Spyros]

Just be careful with that because if for example you are sending emails from staging and you have real user emails in staging DB you will spam testing emails to real users. So, it is suggested to anonymize sensitive data for a lot of reasons (GDPR, your own protection if you have some kind of data leak on staging etc)