Quick Tip: PostgreSQL Dump Database and Restore PostgreSQL Database

Tags
4 October 2022
Complete Guide for CTO & IT Directors
Microservices under X-Ray Three books image Download free ebook

Making regular backups is an essential aspect of working with the PostgreSQL database in any production environment. No matter the size, you must take precautions to address potential data loss, and regular backups of the entire database are the best thing you can do. That way, in case of any problems with the database, such as data loss or corruption, you can simply load data from the latest version of the PostgreSQL backup file.

PostgreSQL includes native tools that can allow to easily dump, manage, and restore a PostgreSQL database.

How to dump a PostgreSQL database

PostgreSQL provides the pg_dump which is a utility that helps you efficiently do a backup of your database. It will generate an SQL database file which will make it easy to restore data should you need it.

To back up a PostgreSQL database you have to log in to your database server. Then switch to the Postgres user account and run pg_dump in the following manner:

pg_dump database_name > database_name.sql

It is generally a good habit to employ a system of database name building, as it’s good to be able to know what each individual database is. A good idea is to always include the date of the backup file so that when you want to restore PostgreSQL database you will know at a glance which one simply from the name of the database.

Changing the format of the PostgreSQL data dump

PostgreSQL database dump is, by default, a plain SQL script file, but this is not the only format that’s available as you can also do a custom format dump. You can specify the output file format with -F option (c = custom format archive file, d = directory format archive, t = tar file).

As an example you can do it like that:

pg_dump -F c database_name > database_name.tar

or:

pg_dump -F c database_name > database_name.dump

Specifying directory for the database dump

When dumping data in the directory output format, you can use -f flag to specify the target directory (instead of a file). Please mind, that the specified directory that the pg_dump will create must not exist. You can dump data into a specified directory by using the following command:

pg_dump -F d database_name -f database_directory	

Dumping all PostgreSQL databases

You can back up more than one database at a time. You can use pg_dumpall to dump all PostgreSQL databases.

pg_dumpall > all_databases.sql

Restoring a PostgreSQL Database

If you want to restore the desired database, you can use pg_restore (restores a PostgreSQL database from an archive created in one of the other formats such as tar or directory) or psql (restores the text files that were created by pg_dump).

The following command is the example of psql use:

psql database_name < database_name.sql

A custom format dump is not a SQL script file, so it must be restored with pg_restore. You use it like that:

pg_restore -d database_name database_name.dump 

or

pg_restore -d database_name database_name.tar

or like this for directory format archive

pg_restore -d database_name database_directory

pg_dump and psql utilities as shown.

Dealing with large PostgreSQL databases

If you want to back up a large database and you wish to get a smaller output file, you can compress the dump. However, you will have to filter the output format with a compression tool, e.g. gzip.

pg_dump database_name | gzip > database_name.gz

If your database is extensively large, you can do a parallel dump by dumping number_of_jobs simultaneously. We use -j flag for this:

pg_dump -F d -j 8 -f database_namedir

Please remember that though a parallel dump reduces the time of the database dump it will inevitably increase the load on the database server.

Latest Posts
invision vs figma

InVision vs. Figma: Key Features, Differences, and Similarities.

Figma and InVision rank among the best UI design tools. Learn about their key features and how they can speed up front-end development. It’s hard to overstate the importance of UI design in web and mobile development. To be successful a digital product needs to be an eye-pleaser and a UX gem, in addition to […]

/
flutter language logo

Flutter: the number one framework for building cross-platform apps

Flutter is an SDK for building fast apps for different platforms. It comes with comprehensive development tools and streamlines designing high-performance UIs. Find out why Flutter is a top-quality and cost-effective alternative to native app development. Flutter is Google’s open-source toolkit for developing high-fidelity cross-platform applications. It allows you to create native software for Android […]

/
django hosting

Hosting for Django? Here’s what you need to know.

Django is a robust web framework for Python that enables programmers to swiftly build web apps. But once you’ve built your application, the next step is to get it online and available to the world. That’s where hosting comes in. In this article, we will explore the various options available for hosting Django applications. Types […]

/
front end technologies

Top-ranking front-end technologies. Best tools to speed up UI development in 2023

Flawless UI is a must if your app is headed for success. Designing a high-quality front-end can be costly and time-consuming, but not so much if you’re able to choose the right toolset for your product. Find out the best front-end development options to consider in 2023. Back-end powers the functionality of the system, while […]

/
nodejs books

Top 7 Node.js Books for Both Beginner and Professional Developers

Looking to advance your expertise in Node.js? If you’re tired of chaotic online resources, try good old-fashioned books. They will provide you with structured knowledge and give you a clear understanding of even the most complex programming concepts. Node.js is an asynchronous, event-driven, backend JavaScript runtime designed to develop scalable network applications. As a cross-platform […]

/
AR in fashion

AR in Fashion. Key Benefits and Real Use Cases

AR technology has already taken root in many industries. One of them is fashion. Clothing, beauty, and retail brands have long been leveraging AR solutions to attract customers and boost sales. According to recent estimates, the number of mobile augmented reality (AR) users worldwide will reach 1.7 billion by 2024 – a huge rise from […]

/
Related posts
invision vs figma

InVision vs. Figma: Key Features, Differences, and Similarities.

Figma and InVision rank among the best UI design tools. Learn about their key features and how they can speed up front-end development. It’s hard to overstate the importance of UI design in web and mobile development. To be successful a digital product needs to be an eye-pleaser and a UX gem, in addition to […]

/
flutter language logo

Flutter: the number one framework for building cross-platform apps

Flutter is an SDK for building fast apps for different platforms. It comes with comprehensive development tools and streamlines designing high-performance UIs. Find out why Flutter is a top-quality and cost-effective alternative to native app development. Flutter is Google’s open-source toolkit for developing high-fidelity cross-platform applications. It allows you to create native software for Android […]

/
django hosting

Hosting for Django? Here’s what you need to know.

Django is a robust web framework for Python that enables programmers to swiftly build web apps. But once you’ve built your application, the next step is to get it online and available to the world. That’s where hosting comes in. In this article, we will explore the various options available for hosting Django applications. Types […]

/
front end technologies

Top-ranking front-end technologies. Best tools to speed up UI development in 2023

Flawless UI is a must if your app is headed for success. Designing a high-quality front-end can be costly and time-consuming, but not so much if you’re able to choose the right toolset for your product. Find out the best front-end development options to consider in 2023. Back-end powers the functionality of the system, while […]

/
ruby on rails

Ruby on Rails Company Use Case: 14 Businesses that Use Ruby on Rails

Ruby on Rails is a web application framework written in Ruby, a dynamic and object-oriented programming language. It is intended to make web application programming simpler by assuming that all developers have the prerequisites for getting started. Rails is released under the MIT License and is maintained by David Heinemeier Hansson. Rails is known for […]

/
agile roles and responsibilities

Agile Roles and Responsibilities. Who’s Who in Agile Projects?

Agile teams aren’t like traditional project groups. They embrace self-organization and collaborate closely to achieve business goals. So, how many Agile roles are there, and what are their key responsibilities? Read on to find out. Agile is a set of management practices based on an iterative, time-boxed approach to software development. It originated in 2001 […]

/
Talk with experts

We look forward to hearing from you to start expanding your business together.

Email icon [email protected] Phone icon +1 (888) 413 3806