Table of Contents
In this article, we will see how to install pg_dump and pg_restore utility on Ubuntu 20.04 LTS (Focal Fossa). pg_dump is a very famous Postgresql utility to take database backup consistently even if the database is in use. It does not block user access to the database. However it comes with a limitation that it can only take backup of a single database. If you are planning to take backup of entire cluster then you need to use pg_dumpall utility. Similarly, pg_restore is also a Postgresql utility to restore the data from the archive created by the pg_dump.
Both of the utilities are extensively used by database administrators across the globe to backup and restore the data. pg_dump can take full, incremental and continuous backup quite easily. When used in combination with pg_restore, pg_dump provides a flexible archival and transfer mechanism. You cannot just restore the data using pg_restore but you can also rebuild the database using this utility. It is also easy to install both the utilities on any Linux distributions. Here we are going to look into the steps to install pg_dump and pg_restore on Ubuntu 20.04 LTS based systems.
How to Install pg_dump and pg_restore on Ubuntu 20.04 LTS (Focal Fossa)
Also Read: How to Install Juju Client on Ubuntu 20.04 LTS (Focal Fossa)
Step 1: Prerequisites
a) You should have a running Ubuntu 20.04 LTS Server.
b) You should have sudo or root access to run privileged commands.
c) You should have apt or apt-get utility available in your Server.
Step 2: Update Your Server
In the first step, you need to download and install all the available updates and then upgrade packages to the latest version by using sudo apt update && sudo apt upgrade command as shown below.
cyberithub@ubuntu:~$ sudo apt update && sudo apt upgrade
Hit:1 https://dl.google.com/linux/chrome/deb stable InRelease
Get:2 https://dl.winehq.org/wine-builds/ubuntu focal InRelease [8,041 B]
Hit:3 http://ppa.launchpad.net/flatpak/stable/ubuntu focal InRelease
Hit:4 https://download.sublimetext.com apt/stable/ InRelease
Hit:5 http://ppa.launchpad.net/juju/stable/ubuntu focal InRelease
Hit:6 http://ppa.launchpad.net/libreoffice/ppa/ubuntu focal InRelease
Hit:7 http://ppa.launchpad.net/mojo-maintainers/ppa/ubuntu focal InRelease
Get:8 https://dl.winehq.org/wine-builds/ubuntu focal/main amd64 Packages [407 kB]
Get:9 https://dl.winehq.org/wine-builds/ubuntu focal/main i386 Packages [408 kB]
Hit:10 http://in.archive.ubuntu.com/ubuntu focal InRelease
Get:11 http://in.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Get:12 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Get:13 http://in.archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB]
Get:14 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [2,544 kB]
Get:15 http://security.ubuntu.com/ubuntu focal-security/main i386 Packages [590 kB]
Get:16 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages [2,156 kB]
Get:17 http://in.archive.ubuntu.com/ubuntu focal-updates/main i386 Packages [820 kB]
Get:18 http://in.archive.ubuntu.com/ubuntu focal-updates/main Translation-en [430 kB]
Get:19 http://security.ubuntu.com/ubuntu focal-security/main Translation-en [348 kB]
...........................................................
Step 3: Install pg_dump and pg_restore
In the next step, you can install pg_dump and pg_restore utility from default Ubuntu repo by using sudo apt install postgresql-client postgresql-client-common libpq-dev command as shown below. This will download and install the packages along with all its dependencies.
cyberithub@ubuntu:~$ sudo apt install postgresql-client postgresql-client-common libpq-dev Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: postgresql-client-12 Suggested packages: postgresql-doc-12 postgresql-12 The following NEW packages will be installed: libpq-dev postgresql-client postgresql-client-12 postgresql-client-common 0 upgraded, 4 newly installed, 0 to remove and 2 not upgraded. Need to get 169 kB/1,222 kB of archives. After this operation, 4,644 kB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 libpq-dev amd64 12.14-0ubuntu0.20.04.1 [137 kB] Get:2 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 postgresql-client-common all 214ubuntu0.1 [28.2 kB] Get:3 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 postgresql-client all 12+214ubuntu0.1 [3,940 B] Fetched 169 kB in 2s (93.2 kB/s) Selecting previously unselected package libpq-dev. (Reading database ... 217571 files and directories currently installed.) Preparing to unpack .../libpq-dev_12.14-0ubuntu0.20.04.1_amd64.deb ... Unpacking libpq-dev (12.14-0ubuntu0.20.04.1) ... Selecting previously unselected package postgresql-client-common. Preparing to unpack .../postgresql-client-common_214ubuntu0.1_all.deb ... Unpacking postgresql-client-common (214ubuntu0.1) ... Selecting previously unselected package postgresql-client-12. Preparing to unpack .../postgresql-client-12_12.14-0ubuntu0.20.04.1_amd64.deb ... Unpacking postgresql-client-12 (12.14-0ubuntu0.20.04.1) ... Selecting previously unselected package postgresql-client. Preparing to unpack .../postgresql-client_12+214ubuntu0.1_all.deb ... Unpacking postgresql-client (12+214ubuntu0.1) ... Setting up postgresql-client-common (214ubuntu0.1) ... Setting up libpq-dev (12.14-0ubuntu0.20.04.1) ... Setting up postgresql-client-12 (12.14-0ubuntu0.20.04.1) ... update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-client (12+214ubuntu0.1) ... Processing triggers for man-db (2.9.1-1) ...
Step 4: Check Version
To check the installed version of pg_dump utility, you need to run pg_dump --version command as shown below.
cyberithub@ubuntu:~$ pg_dump --version pg_dump (PostgreSQL) 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1)
Similarly, to check the installed version of pg_restore utility, you need to use pg_restore --version command as shown below.
cyberithub@ubuntu:~$ pg_restore --version pg_restore (PostgreSQL) 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1)
Step 5: Using pg_dump and pg_restore
There are multiple ways to backup and restore PostgreSQL database. You can either login to database server and take backup locally or you can connect remotely to the DB Server and take backup in certain directory in various output formats such as tar, dump or in plain text SQL. For example - if you need to take backup of a database called cyberithub_db locally into the database server then you need to first login to the database using postgres account and run below command to take the dump in plain text SQL file.
postgres@ubuntu:~$ pg_dump cyberithub_db > cyberithub_db.sql
You can also take the dump in tar format by specifying t option with -F switch as shown below.
postgres@ubuntu:~$ pg_dump -F t cyberithub_db > cyberithub_db.tar
Similarly, if you are looking to take the backup in dump format then you need to specify c with -F switch as shown below.
postgres@ubuntu:~$ pg_dump -F c cyberithub_db > cyberithub_db.dump
Likewise, if you are planning to take backup by remotely connecting database server of IP 200.128.16.10 on default Port 5432 then you need to run pg_dump -U postgres -h 200.128.16.10 -p 5432 cyberithub_db > cyberithub_db.sql command as shown below.
cyberithub@ubuntu:~$ pg_dump -U postgres -h 200.128.16.10 -p 5432 cyberithub_db > cyberithub_db.sql
You can also take backup in some directory say example directory in our case by using below command.
postgres@ubuntu:~$ pg_dump -F d cyberithub_db -f example
If you are looking to restore the database from the dump you have taken using pg_dump then you need to use pg_restore -d cyberithub_db cyberithub_db.dump command as shown below.
postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.dump
Similarly, you can restore from tar dump using pg_restore -d cyberithub_db cyberithub_db.tar command as shown below.
postgres@ubuntu:~$ pg_restore -d cyberithub_db cyberithub_db.tar
If you saved your backup in some directory called example then you can restore the database from the directory by using pg_restore -d cyberithub_db example command as shown below.
postgres@ubuntu:~$ pg_restore -d cyberithub_db example
Step 6: Uninstall pg_dump and pg_restore
Once you are done using pg_dump and pg_restore command, you can choose to uninstall it from your System by using sudo apt remove postgresql-client postgresql-client-common libpq-dev command as shown below.
cyberithub@ubuntu:~$ sudo apt remove postgresql-client postgresql-client-common libpq-dev Reading package lists... Done Building dependency tree Reading state information... Done The following packages will be REMOVED: libpq-dev postgresql-client postgresql-client-12 postgresql-client-common 0 upgraded, 0 newly installed, 4 to remove and 2 not upgraded. After this operation, 4,644 kB disk space will be freed. Do you want to continue? [Y/n] Y (Reading database ... 217888 files and directories currently installed.) Removing libpq-dev (12.14-0ubuntu0.20.04.1) ... Removing postgresql-client (12+214ubuntu0.1) ... Removing postgresql-client-12 (12.14-0ubuntu0.20.04.1) ... Removing postgresql-client-common (214ubuntu0.1) ... Processing triggers for man-db (2.9.1-1) ...