Replicate your PostgreSQL database into another server using just one command.
Levi Velázquez

Levi Velázquez @levivm

About: Director of Engineering at MO Technologies. Best way to learn is teaching :) Dev.to community moderator.

Joined:
Aug 10, 2018

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

Publish Date: Aug 25 '18
62 4

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
Enter fullscreen mode Exit fullscreen mode

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;"
Enter fullscreen mode Exit fullscreen mode

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

Comments 4 total

  • rhymes
    rhymesAug 25, 2018

    Nice tip!

    You just reminded me of one more. If you have a big database you can save some time:

    • using serial dumping with a custom format: pg_dump -Fc | pg_restore (instead of using SQL which is the default)
    • using parallel restore with:
    pg_dump -Fc DB_NAME -f DUMP_FILE.gz && pg_restore -d TARGET_DB -j NUM_CORES DUMP_FILE.gz
    

    or

    pg_dump -Fd DB_NAME DUMP_FOLDER && pg_restore -d TARGET_DB -j NUM_CORES DUMP_FOLDER
    
    • using both parallel dumping and parallel restore:
    pg_dump -Fd -j NUM_CORES DB_NAME -f DUMP_FOLDER && pg_restore -d TARGET_DB -j NUM_CORES DUMP_FOLDER
    

    Hope it helps :D

  • Mazharul islam
    Mazharul islamJul 1, 2019

    thanks

  • andreasneuman
    andreasneumanApr 20, 2020

    Thanks, nice tips! There is one more possible solution of data migration, I suggest using dbForge Studio for PostgreSQL with its advanced built-in features for moving PostgreSQL data between a source and target systems, it can significantly assist teams in their PostgreSQL data migration efforts

Add comment