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. Jun 21, 2015 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, 2015 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 ?
    – Eric Ly
    Jun 22, 2015 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 Jun 22, 2015 at 13:02

2 Answers 2


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

In addition to basic dump/restore, to have an automated robust refresh process, you can use Database Lab Engine by Postgres.ai https://postgres.ai/docs/, https://gitlab.com/postgres-ai/database-lab. It takes the data from the source in some way (in addition to "logical" copying, dump/restore, you can copy the data directory physically with pg_basebackup or any other way or restore from archives created by a backup tool such as WAL-G or pgBackRest), and then you can start using "thin cloning" that allow getting many independent copies of the database "for free" (no extra costs or extra wait time) thanks to copy-on-write (CoW) provided by either ZFS or LVM (you choose). In the case of "physical" provisioning, it is also possible to configure the "continuous refresh" option: the data is constantly updated from the source, and you can use thin clones based on different points in time.

In many cases, when we decide to copy production data to a non-production environment, we might want or need to obfuscate the data. This is also possible, with such tools as, for example, postgresql_anonymizer https://postgresql-anonymizer.readthedocs.io/en/stable/, https://gitlab.com/dalibo/postgresql_anonymizer. It is possible to use it with Database Lab Engine as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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