3

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.

Improve this question
2
  • or stackoverflow.com/questions/1237725/… Commented Oct 2, 2014 at 22:23
  • 1
    The selected duplicate isn't a great choice; it's specific to PgAdmin and uses templates, which isn't the best way to do it. You can't copy a database as a template while it has connections. Commented Oct 3, 2014 at 0:45

1 Answer 1

Reset to default
7

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

they're both on the same server
OK, so pg_dump source | psql destination should pipe the data from source to destination.
@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?
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)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.