I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a different role, and I want to keep their privileges separate in the transfer.

I tried following the documentation and ran these commands with my separate users (which have access to the corresponding PostgreSQL roles):

staging-user: pg_dump production_db > prod_db_file
production-user: psql --single-transaction staging_db < prod_db_file

But I got a couple of errors: must be owner of extension plpgsql and syntax error at or near. Besides, I'm not sure if this would have done what I wanted anyway. There are a lot of optional flags on the pg_dump and psql commands, and I don't know which ones I should use. (I've tried many combinations of flags and nothing has worked so far.)

Note: When I tried the above commands, my staging database already existed.

How do I move my production data to my staging database?

  • Were you restoring to an empty database? Sounds like you probably were not. I also strongly suggest using pg_dump -Fc and pg_restore. – Craig Ringer Jun 21 '15 at 23:40
  • @CraigRinger The database was empty but I had already run my migrations. Do I need to drop the database every time before I run pg_restore? Also, why is pg_restore superior to psql? – django09 Jun 22 '15 at 12:20
  • On first hand, you may use pg_dump -Ox production_db in order to avoid the ownership problem. Did you try to get the query issuing the syntax error at or near ? – DeadEye Jun 22 '15 at 12:49
  • pg_restore gives you the control to run the restore different ways from the same dump, it's not just a static SQL script. For example pg_restore --clean – Craig Ringer Jun 22 '15 at 13:02

Update Oct. 30, 2015: This answer is simpler and more accurate than my old answer, which I removed for the sake of brevity. To see the old answer, take a look at the edit history.

  1. As production user: pg_dump -Fc my_prod_db > prod_dump.db
  2. Drop and create staging database
  3. As staging user: pg_restore --no-owner --dbname my_staging_db path/to/prod_dump.db
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.