Type for search...
codete Postgre SQL Dump and Restore Database Step by Step Instruction 1 main 084e559037
Codete Blog

PostgreSQL – Dump and Restore Database: Step by Step Instruction

Patryk Grudniewski av 569835c2f8

18/01/2022 |

5 min read

Patryk Grudniewski

PostgreSQL Dump and Restore Database operations may seem quite simple and uncomplicated yet a step-by-step instruction may be all we need to make the process go smooth and undisturbed. Indeed, many software developers are not sure about it and searching for phrases such as „PSQL Dump”, „backup Postgres database”, „Postgres import Dump”, or „restore Postgres database from Dump” is something common.

PostgreSQL, originally named Postgres, is a free and open-source object-relational database management system that supports both relational and non-relational querying. It was created at the University of California, Berkley as a successor of the Ingress project. Currently, it is maintained by a worldwide team of volunteers and published by PostgreSQL Global Development Group Regents of the University of California.

It provides various operations, but one we will discuss in this article is dumping the database to file and then restoring it.

 

In detail, the following threads will be discussed:
    1.     PostgreSQL Restore & Dump – what are they?
    2.     Postgres Dump Database process – how to do it right
    3.     Postgres Restore from Dump – instructions     
    4.     PostgreSQL Dump & Restore Database operations – now you know how to do it!
 

PostgreSQL Restore & Dump – what are they?

Imagine a case where you are migrating your application from one server room to another or you just want to have a backup of your database and restore it from that dump in case of emergency. Or, imagine a situation where you have a data-related bug in your application and you want to replicate it in your local environment.

And this is exactly what Dump & Restore database operations were designed for. They provide the possibility to copy databases from one PostgreSQL instance to another: Dump is an operation that allows you to copy the whole content of your database to a text file and Restore is an operation that loads such dump SQL files to a database.
 

Postgres Dump Database process – how to do it right

pg_dump is a binary that provides the possibility of dumping a database. It is really simple to use:

pg_dump my_database > my_dump.sql

In the above example, my_database is the name of the database we want to dump and my_dump.sql is the name of the file our dump will be written at. But, as you probably can see, the above does not specify a user name, password, or connection details like hostname and port. It will connect to a localhost database instance with the username same as the name of the user who called it. In most cases, it’s not something we are interested in but, instead, we are dumping databases located on some remote servers. To achieve that we should use command-line options which allows us to control connection details:

pg_dump -h my.host.com -p 1234 -U my_user my_database > my_dump.sql

pg_dump binary also provides various formatters for output data. The above examples will use the default formatter which is formatting output as a plain SQL script file. But in the case of huge databases, we would like to compress data or split a single SQL script file into smaller files. It is what the -F command-line option was invented for. It accepts the following values:

  • plain which is the default value and formats data as a single SQL script file,
  • directory which generates a directory that contains SQL script file for each database table,
  • tar which generates a tar archive.

Postgres Restore from Dump – instructions

Restoring the database from Dump is as simple as dumping that data. PostgreSQL provides pg_restore command line command which is a sibling command to pg_dump. To restore data from the Dump run the following shell command:

pg_restore -h another.host.com -p 4321 - U another_user -d another_database my_dump.sql

You can see the only difference is the -d flag which specifies the name of the database we are restoring to. It is useful when we have dumped data without the -C flag (which adds a create database statement to our dump).

Also, have in your minds that pg_restore should be run with the same formatter as pg_dump was run with.

PostgreSQL Dump & Restore Database operations – now you know how to do it!

Now you see it’s simple! There are two shell binaries provided together with PostgreSQL which allows us to Dump and Restore the database. You can do this by running the following script in your shell:

#! /bin/bash

pg_dump -h my.host.com -p 1234 -U my_user my_database > my_dump.sql

pg_restore -h another.host.com -p 4321 - U another_user -d another_database my_dump.sql

However, if you want to explore this topic more, some interesting resources offer relevant, and comprehensive information. For more details you can check out PostgreSQL documentation and reference pages, looking for these two commands:

Undoubtedly, the „PSQL Restore Dump” is an important issue as PostgreSQL is a very popular and commonly appreciated database. In the latest edition of the StackOverflow Developer Survey, PostgreSQL has been dubbed „the most wanted” database, as well as the second „most loved” one, only being barely beaten by Redis.

What are the reasons for this success? Well, PostgreSQL is often praised for its speed, versatility, and security as well as enterprise-class features such as function overloading or table inheritance. In general, it is believed to be perfect for many use cases, with building even very complex and demanding applications topping the list.

And what are your experiences with PostgreSQL? Are you, too, this database’s avid supporter? Have you experienced any problems with the process of dumping the database to file or restoring it?

Rated: 5.0 / 1 opinions
Patryk Grudniewski av 569835c2f8

Patryk Grudniewski

Software Developer at Codete. 8+ years of commercial experience, 20+ years of programming passion.

Our mission is to accelerate your growth through technology

Contact us

Codete Przystalski Olechowski Śmiałek
Spółka Komandytowa

Na Zjeździe 11
30-527 Kraków

NIP (VAT-ID): PL6762460401
REGON: 122745429
KRS: 0000696869

Offices
  • Kraków

    Na Zjeździe 11
    30-527 Kraków

  • Lublin

    Wojciechowska 7E
    20-704 Lublin

  • Berlin

    Wattstraße 11
    13355 Berlin

Copyright 2022 Codete