Update staging data with production data in Postgresql 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 &gt; prod_db_file
production-user: psql --single-transaction staging_db &lt; 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?
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.


As production user: pg_dump -Fc my_prod_db &gt; prod_dump.db
Drop and create staging database
As staging user: pg_restore --no-owner --dbname my_staging_db path/to/prod_dump.db