Sometimes we need to replicate our production postgreSQL database to development/staging database in order to test new features with the existing data.
The common way is to dump the database to file, then using scp transfer the file to the new server and create the database from that file.
But, we can archive that without creating an intermediate file, just using a single command, using a single pipe.
pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname
If you development database already contains data, you should drop it first in order to avoid errors creating duplicated data.
psql -h localhost -U localuser dbname -c "drop schema public cascade; create schema public;"
I found this command very helpful because it's quite simple.
Note:
Test it using backup database or backup your current db first.
If you like my content or it was helpful, you can motivate me to write more content by buying me a cofee
Top comments (4)
Nice tip!
You just reminded me of one more. If you have a big database you can save some time:
pg_dump -Fc | pg_restore
(instead of using SQL which is the default)or
Hope it helps :D
Thx, nice to know it.
thanks
Some comments may only be visible to logged-in visitors. Sign in to view all comments.