DEV Community

Yujin
Yujin

Posted on • Originally published at jinyuz.dev on

Using pg_dump and pg_restore to backup and restore PostgreSQL database

So, I was trying to reproduce some issues and bugs that only happened in production. I needed an exact copy of the production database and run it locally.

Here’s how I did it using pg_dump and pg_restore.

$ pg_dump -U postgres -Fc -Z 9 -j 8 production.dump -d postgres
Enter fullscreen mode Exit fullscreen mode

Here’s the breakdown for the arguments:

  • U means username. It’s used to connect to your postgres database. In this case, my username is postgres.
  • F means format. It’s usually combined with c that means c ustom
  • Z means compress. It can have values from 0 to 9. 0 being no compression and 9 being the maximum compression. It’s supposed to make the dump file smaller. I used 9 here because I know that my database doesn’t contain much data and I want its size to be small as possible. You might be asking why not use 9 all the time? The answer to that is it takes a while to restore compressed dumps. So if you want to restore it faster, use a lower value.
  • j means jobs. It’s the number of jobs that we want to run. You can think of it as how many persons are going to dump your databse. More jobs means faster dump.
  • d means database. It’s the name of the database you want to dump. The name of the database I also want to dump is postgres.

After that, I copied it from my remote ssh machine to my local machine

$ scp yujinyuz@myremotesite:/usr/share/nginx/repo/production.dump ~/Downloads/
Enter fullscreen mode Exit fullscreen mode

And then restored it locally:

$ pg_restore -U postgres -Fc -j 8 production.dump -d postgres -c
Enter fullscreen mode Exit fullscreen mode

Notice that most of the arguments are almost the same except for the new -c.

  • c means clean. It will drop database objects before creating them. So, if I have existing tables in my local, it would just drop them and recreate it so I would have the exact copy of my production database.

That’s it! Hope that also helps you, too!

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay