Replicate a PostgreSQL database into another server using just one command.

Replicate a PostgreSQL database into another server using just one command

July 22, 2018 - 1 minute read -
postgresql sql migrate

Sometines 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 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 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 constains 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;"

Note: Test it using backup database or backup your current db first.