How to Backup and Restore a PostgreSQL Database

In this blog, you will learn how to backup and restore a PostgreSQL database. Enjoy!

1. Introduction

Some time ago, I needed to backup a PostgreSQL database from a production server in order to be able to fix a problem which was difficult to reproduce in the test environment. It appeared that I could not find the answer very quickly by means of a Google search. After a while, I managed to find out the commands I needed to use and it seemed to me a good idea to share this knowledge.

In order to test the commands, I will be using a database I created for a previous blog. The schema is quite basic and is shown here for information purposes only, it is not relevant for the remainder of the post. The database is called myjhipsterplanet and that is the one you will backup and restore in this post.

2. Prerequisites

Prerequisites needed for this blog:

Ubuntu 22.04 is used;
Basic knowledge of Docker and Docker Compose is needed;
Basic PostgreSQL knowledge is needed, PostgreSQL 14.5 is used.

3. Preparation

In this section, a database is prepared which you will use to backup and restore. Skip this section if you just want to know how to use the backup and restore commands.

The database backup and Docker Compose file used in this section are available at GitHub.

The Docker Compose file will start a PostgreSQL database containing a basic setup where the default admin user postgres is replaced with a user mypostgresqldumpplanet. The PostgreSQL data is mounted to directory ~/docker/volumes/postgresqldata.

version: '3.8'

services:

mypostgresqldumpplanet-postgresql:

image: postgres:14.5

volumes:

- ~/docker/volumes/postgresqldata/:/var/lib/postgresql/data/

environment:

- POSTGRES_USER=mypostgresqldumpplanet

- POSTGRES_PASSWORD=

- POSTGRES_HOST_AUTH_METHOD=trust

ports:

- 127.0.0.1:5432:5432

Execute the following command from the root of the repository in order to start a PostgreSQL Docker container.

$ docker compose -f postgresql.yml up -d

[+] Running 2/2

Network mypostgresqldumpplanet_default                                Created                                                                                                                                     0.1s

Container mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1  Started                                                                                                                                     0.4s

Copy the database dump file from the root of the repository to the mounted PostgreSQL directory.

$ sudo cp 2023-04-01-original.dump ~/docker/volumes/postgresqldata/

Now you need to open a bash shell inside the container. This can be done with the docker exec command. Inside the container the linux user postgres has to be used, therefore the -u argument needs to be passed.

$ docker exec -it -u postgres mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1 bash

postgres@4934dd659171:/$

Now you have access to bash inside the container. You need to restore the database from the dump by means of pg_restore.

$ pg_restore -U mypostgresqldumpplanet -d postgres -C /var/lib/postgresql/data/2023-04-01-original.dump

pg_restore: while PROCESSING TOC:

pg_restore: from TOC entry 3382; 1262 16384 DATABASE myjhipsterplanet myjhipsterplanet

pg_restore: error: could not execute query: ERROR: role "myjhipsterplanet" does not exist

Command was: ALTER DATABASE myjhipsterplanet OWNER TO myjhipsterplanet;

...

The parameters used are explained:

-U mypostgresqldumpplanet: the admin database user.
-d postgres: you need to provide a database where pg_restore can connect to. Because this is an empty instance of PostgreSQL, you use the postgres database which is always available with the -d argument.
-C: Because you have an empty PostgreSQL database, you need to provide the argument -C in order that pg_restore (the backup restore tool) will create the database for you.
/var/lib/postgresql/data/2023-04-01-original.dmp: The file location of the database dump. The database dump is accessible via the mount in directory /var/lib/postgresql/data/.

As you can see in the output, the restore was not completely successful because of a missing role.

Enter the database, connect to database postgres with user mypostgresqldumpplanet.

$ psql -d postgres -U mypostgresqldumpplanet

Create the missing role.

postgres=# CREATE ROLE myjhipsterplanet;

CREATE ROLE

Check which databases exist with the \l command.

postgres=# \l

List of databases

Name          |         Owner          | Encoding |  Collate   |   Ctype    |                 Access privileges

------------------------+------------------------+----------+------------+------------+---------------------------------------------------

|                        |          |            |            | mypostgresqldumpplanet=CTc

/mypostgresqldumpplanet

|                        |          |            |            | mypostgresqldumpplanet=CTc

/mypostgresqldumpplanet

(5 rows)

Note that the database myjhipsterplanet is created. However, not everything was created correctly, so drop the database.

postgres=# DROP DATABASE myjhipsterplanet;

DROP DATABASE

Use the quit command to exit the postgres prompt.

Try the restore command again and this time the restore is successful.

Connect via psql to the database.

Use the \c command to use the myjhipsterplanet database.

postgres=# \c myjhipsterplanet

You are now connected to database "myjhipsterplanet" as user "mypostgresqldumpplanet".

Verify that the customer table contains data.

myjhipsterplanet=# select * from customer;

id | customer_name | company_id

----+-------------------------------+------------

1 | Assistant |

2 | Market |

3 | program payment User-friendly |

4 | Team-oriented |

5 | index |

6 | auxiliary experiences |

7 | Sahara |

8 | Kong deposit |

9 | indexing Ball |

10 | users |

(10 rows)

In order to be able to verify the database will be restored correctly later on, you change one of the customer records.

myjhipsterplanet=# UPDATE customer SET customer_name = 'Assistent1' WHERE id = 1;

4. Backup Database

In order to backup the database, pg_dump is used. The official documentation of pg_dump can be found at the PostgreSQL website. Also, help information can be retrieved with the following command:

postgres@4934dd659171:/$ pg_dump --help

If you are running PostgreSQL inside a Docker container, you need to have access to a bash shell inside the container. First, you need to retrieve the name of the Docker container.

$ docker ps

CONTAINER ID   IMAGE           COMMAND                  CREATED         STATUS         PORTS                      NAMES

9dea76770a2e postgres:14.5 "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 127.0.0.1:5432->5432/tcp mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1

Open a bash shell inside the container, as described above:

$ docker exec -it -u postgres mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1 bash

postgres@4934dd659171:/$

There are four formats for creating a backup, each of them are described in the next sections.

4.1 Plain Text SQL Backup

By default, pg_dump will create a plain text SQL backup. Do not use this default if you want to use the backup to restore with pg_restore.

postgres@4934dd659171:/$ pg_dump -f /var/lib/postgresql/data/2023-04-01-plaintext.sql -U mypostgresqldumpplanet myjhipsterplanet

The parameters explained:

-f /var/lib/postgresql/data/2023-04-01-plaintext.sql: The name of the backup file;
-U mypostgresqldumpplanet: The name of the PostgreSQL admin user, often this will be postgres;
myjhipsterplanet: The name of the database you want to backup.

4.2 Custom Backup

The PostgreSQL custom backup format. Compressed by default and most likely the best option to use for creating the backup.

postgres@4934dd659171:/$ pg_dump -F c -f /var/lib/postgresql/data/2023-04-01-custom.dump -U mypostgresqldumpplanet myjhipsterplanet

The arguments are similar as for the plain text SQL backup, only here you need to add the parameter -F c in order to choose for the custom backup format.

4.3 Directory Backup

The directory backup format. Compressed by default and creates a directory with one file for each table and blob being dumped. This format also supports parallel dumps.

postgres@4934dd659171:/$ pg_dump -F d -f /var/lib/postgresql/data/2023-04-01-directory -U mypostgresqldumpplanet myjhipsterplanet

The arguments are similar as for the plain text SQL backup, only here you need to add the parameter -F d in order to choose for the directory backup format.

Some websites explaining how to create PostgreSQL backups do not use the -f parameter for specifying the output file. Instead, they redirect the output of the pg_dump command to a file with the greater-than-sign (>). However, this will not work with the directory backup format as you will encounter the following error:

postgres@4934dd659171:/$ pg_dump -F d -U mypostgresqldumpplanet myjhipsterplanet > /var/lib/postgresql/data/2023-04-01-directory/

bash: /var/lib/postgresql/data/2023-04-01-directory/: Is a directory

4.4 Tar Backup

The tar backup format. Does not support compression but extracting the tar leads to a valid directory format.

postgres@4934dd659171:/$ pg_dump -F t -f /var/lib/postgresql/data/2023-04-01-tar.tar -U mypostgresqldumpplanet myjhipsterplanet

The arguments are similar as for the plain text SQL backup, only here you need to add the parameter -F t in order to choose for the tar backup format.

5. Restore Database

If you follow the steps in this blog, you first need to restore the changed customer record to its original value. Connect to the database and change the record again.

myjhipsterplanet=# UPDATE customer SET customer_name = 'Assistent' WHERE id = 1;

In order to backup the database, pg_restore is used. The official documentation of pg_restore can be found at the PostgreSQL website. Also, help information can be retrieved with the following command:

postgres@4934dd659171:/$ pg_restore --help

5.1 Plain Text SQL Restore

As mentioned before, this will not work with pg_restore. The following error will be shown:

postgres@4934dd659171:/$ pg_restore -U mypostgresqldumpplanet -d myjhipsterplanet -c /var/lib/postgresql/data/2023-04-01-plaintext.sql

pg_restore: error: input file appears to be a text format dump. Please use psql.

5.2 Custom Restore

The custom restore works as a charm:

postgres@4934dd659171:/$ pg_restore -U mypostgresqldumpplanet -d myjhipsterplanet -c /var/lib/postgresql/data/2023-04-01-custom.dump

The parameters explained:

-U mypostgresqldumpplanet: The name of the PostgreSQL admin user, often this will be postgres;
–d myjhipsterplanet: The name of the database you want to restore;
-c: In order to clean the database objects before restoring them;
/var/lib/postgresql/data/2023-04-01-custom.dump: The name of the backup file.

Connect to the database and show the customer with id 1.

postgres@4934dd659171:/$ psql -d postgres -U mypostgresqldumpplanet

postgres=# \c myjhipsterplanet

myjhipsterplanet=# select * from customer where id = 1;

id | customer_name | company_id

----+---------------+------------

1 | Assistent1 |

(1 row)

As you can see, the value is Assistent1, the value from the backup.

5.3 Directory Restore

The directory restore can be done as follows:

postgres@4934dd659171:/$ pg_restore -U mypostgresqldumpplanet -d myjhipsterplanet -c /var/lib/postgresql/data/2023-04-01-directory/

The parameters are similar as for the custom format. The pg_restore command will figure out for itself which format is used in the backup file.

5.4 Tar Restore

And for completeness, the tar restore.

postgres@4934dd659171:/$ pg_restore -U mypostgresqldumpplanet -d myjhipsterplanet -c /var/lib/postgresql/data/2023-04-01-tar.tar

6. What Is The Mounted Docker Volume?

When you are running PostgreSQL as a Docker container, the directory /var/lib/postgresql/data will be mounted to a host directory. How can you find this directory? First, retrieve the name of the Docker container.

$ docker ps

CONTAINER ID   IMAGE           COMMAND                  CREATED       STATUS       PORTS                      NAMES

4934dd659171 postgres:14.5 "docker-entrypoint.s…" 3 hours ago Up 3 hours 127.0.0.1:5432->5432/tcp mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1

The Docker inspect command will dump the mounts of the container, and here you will find the mounted volumes.

$ docker inspect mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1

...

"Mounts": [

{

"Type": "bind",

"Source": "/home/<user>/docker/volumes/postgresqldata",

"Destination": "/var/lib/postgresql/data",

"Mode": "rw",

"RW": true,

"Propagation": "rprivate"

}

],

...

7. Clean Up

If you followed the steps in this blog, you need to do some cleanup.

Stop the PostgreSQL Docker container with the following command executed from the root of the Git repository.

$ docker compose -f postgresql.yml down

[+] Running 2/2

Container mypostgresqldumpplanet-mypostgresqldumpplanet-postgresql-1 Removed

Network mypostgresqldumpplanet_default Removed

8. Conclusion

In this post, you learned how to backup and restore a PostgreSQL database. Interesting information how to optimize backups can be found here.

1. Introduction

2. Prerequisites

3. Preparation

4. Backup Database

4.1 Plain Text SQL Backup

4.2 Custom Backup

4.3 Directory Backup

4.4 Tar Backup

5. Restore Database

5.1 Plain Text SQL Restore

5.2 Custom Restore

5.3 Directory Restore

5.4 Tar Restore

6. What Is The Mounted Docker Volume?

7. Clean Up

8. Conclusion

Share this:

Recommend

Rambus预计今年末会有更快的GDDR6量产，速率将达到24Gbps

ChatGPT streaming with ruby-openai, Rails 7, Hotwire, Turbostream, Sidekiq and T...

How to Create a GraalVM Docker Image

ChatGPT Cheat Sheet For Data Science

reset-button.css

Peter Schiff's Dramatic Shift: A Bitcoin Skeptic Embraces Ordinals And NFTs – Cr...

Elon Musk reveals reason behind Dogecoin investment here is our reason behind Tr...

Microsoft Dev Box due this summer

PostgreSQL 16 advances query parallelism

Caterham EV Seven 将在古德伍德速度节上首次亮相

About Joyk