How To Backup and Restore PostgreSQL Database using pg_dump and pg_restore

I’ve just received a report about a bug on project I’ve been working with. The problem was the development and the production environments databases wasn’t synced so I couldn’t reproduce the error. Now you probably think, well? Just sync them and reproduce the error. The problem is that I’m not a database guy… But I’m a problem solver, so I solved my problem.

Dump the database

pg_dump -U db_username db_name  — format tar > /home/iktw/dump.tar

Copy the database dump from remote

scp username@remote:/home/iktw/dump.tar ~/dbdumps/

Update the following line in /etc/postgresql/9.x/main/pg_hba.conf

local all all peer
local all all md5

Restore the database

pg_restore -U db_username -c -n public -d db_name ~/dbdumps/dump.tar
Like what you read? Give Mohammed Hammoud a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.