How to backup PostgreSQL database on Ubuntu 18

Introduction

Data loss can be a significant set back to you especially with the government sanctions on imposed fine for user data loss. To mitigate damages resulting from either a physical catastrophe or an attack by malicious hackers, regular backup is deemed necessary as a way of disaster recovery plan. With PostgreSQL, there are fundamentally different approaches to conducting back up of data, and these include:

File system backup which involves directly making copies of the files that PostgreSQL uses to store data in the database.
Continuous archiving and point-in-time recovery which involves logs backed up and restored when needed, and database restored into consistency by replaying the entries made during the last checkpoint.
SQL dump which involves generating the commands that are used to create the database and saving them into a file. When this file is run, the SQL commands will be used to recreate the database again on a simple template.

In this guide, you learn how to make a backup and conducting a restoration on a database using SQL dump.

Before you Start

In order to achieve this task, the following conditions need to be met:

A nonroot user with sudo privileges on a VPS or a Dedicated Server running Ubuntu 18.04 operating system.
PostgreSQL database software package installed and configured on your VPS. You can learn How To Install PostgreSQL Database Server On Ubuntu 18.04.
A sample database or specific schemas/tables which we will use to backup and restore (optional since we will create our sample database to backup and restore).

Steps

pg_dumpis the official PostgreSQL database utility that is used for database backup. You are going to create your own sample database so as to back it up and restore.

$ sudo -i -u postgres psql

# CREATEDATABASE sampledb;
# c sampledb;
# CREATETABLE sampleTable(
# sampledb(# tableID SERIAL PRIMARY KEY,
# sampledb(# firstname VARCHAR,
# sampledb(# lastname VARCHAR,
# sampledb(# age INT);

CREATETABLE

# CREATETABLE sampleTable2(
# table2ID SERIAL PRIMARY KEY,
# tableID INTREFERENCES sampleTable(tableID),
# location VARCHAR,
# monthsINT);

CREATETABLE

Backup

Exit your database and create your backup in a simple plain text format.

$ pg_dump sampledb > sampledb.sql

When you view the dumped data in the sampledb.sql file, it should look similar to the following extract of the whole file.

...
-- PostgreSQL database dump
--

-- Dumped from database version 10.5 (Ubuntu 10.5-0ubuntu0.18.04)
-- Dumped by pg_dump version 10.5 (Ubuntu 10.5-0ubuntu0.18.04)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
...

You may decide to dump the data in your VPS a file format that is deemed more suitable for larger databases which is the compressed method. You can use the following command forthe same. You can use the following file to compress the file to a compressed binary format.

$ pg_dump -Fc sampledb > sampledb.bak

You may also use the following command to backup your database in a tarball file format.

$ pg_dump -Ft sampledb > sampledb.tar

Restore

You will now drop the database form the PostgreSQL command line view and exit.

# DROPDATABASE sampledb;

DROPDATABASE

# q

Pg_restoreis the database utility provided by PostgreSQL to restore your database. Please note you must create the database that you will restore your data in.

$ createdb sampledb

If it exists from a template, you can use the following command if your database is in a .bak file format.

$ pg_restore -sampledb -Fc sampledb.bak

If it is in a .tar file format, then use the following command:

$ pg_restore -d sampledb -Ft sampledb.tar

Conclusion

You have successfully backed up and restored your database using the pg_dumpand pg_restore database utilities. Please note that this method only backs up one database at a time and does not back up the different roles that are assigned to it since it is considered not to be within the scope of the database. If you want to backup every database including the various roles you can use thepg_dumpalldatabase utility in the format below: