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:

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:

$ pg_dumpall > dumpfile

Check out these top 3 Linux hosting services

HostArmada
$2.49 /mo
Starting price
Visit HostArmada
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.5
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.0
Kamatera
$4.00 /mo
Starting price
Visit Kamatera
Rating based on expert review
  • User Friendly
    3.5
  • Support
    3.0
  • Features
    3.9
  • Reliability
    4.0
  • Pricing
    4.3
Hostens
$2.00 /mo
Starting price
Visit Hostens
Rating based on expert review
  • User Friendly
    4.5
  • Support
    3.8
  • Features
    4.3
  • Reliability
    4.5
  • Pricing
    4.8
  • Click this link and all your queries to best hosting will end.

How to Install Ruby on Rails on Ubuntu 18

In this article, we will be setting up a Ruby on Rails development environment o
4 min read
Arvind Singh
Arvind Singh
Hosting Expert

How To Install PostgreSQL Database Server On Ubuntu 18.04?

This tutorial will help you install PostgreSQL; an advanced open source database
3 min read
David Malcom
David Malcom
Author

How to Set Up Replication on PostgreSQL on Ubuntu 18.04 VPS or Dedicated Server

High availability and load balancing are important concepts in database manageme
4 min read
Vladimir Rakov
Vladimir Rakov
Hosting Expert

How to Install PostgreSQL on an Ubuntu VPS Running Nginx

Brief description Having a remote database system with a client library, one ma
2 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.