Logical PostgreSQL Backup – Import

June 16, 2021

Logical PostgreSQL Backup – Import

Logical (SQL DUMP) Database Backup (Export/Import) is an extract of some or all data or structure of objects within the database. This is a dump in the form of an SQL command that can be executed on the server in order to recreate database objects.

Advantage:

1) Flexibility: Dump files are OS-independent.
2) Cross Version Compatibility: Take backup from Higher Version and import in Lower Version or vice versa.
3) Consistent backup at the time pg_dump begins.
4) Selective restoration options are possible.

Disadvantage:

1) Slower restore compare to Physical backup.
2) No Point-In-Time-Recovery (PITR) possible with Logical Backup.
3) Will not allow DDL during backup.
4) Incremental backup is not possible.

Tools (Utilities) for Logical Backup Restore:

psql – Used only to restore for backup taken in plain text format generated by pg_dump and pg_dumpall tools.

pg_restore – Is utility for restoring a postgresql from a dumpfile created by pg_dump in one of non-plain text format. The dumpfile also allows pg_restore to be selective about what to restore.

In the previous blog, we have already created dump files using pg_dump & pg_dumpall Click here to read more. We will use the same dumps to perform the import. For import we will use the 2nd PostgreSql cluster created on Port: 5433 Click here to read more.

[root@test-machine02 ~]# cd /u01/backup
[root@test-machine02 backup]#
[root@test-machine02 backup]# ls -ltr
total 19808
-rw-r--r--. 1 postgres postgres 2766115 Jun 10 15:15 dvdrental.sql
-rw-r--r--. 1 postgres postgres 2843136 Jun 10 15:23 dvdrental_tar
-rw-r--r--. 1 postgres postgres  664040 Jun 10 15:24 dvdrental_custom
drwx------. 2 postgres postgres    4096 Jun 10 15:24 dvdrental_dir
-rw-r--r--. 1 postgres postgres   57919 Jun 10 15:35 table_bkp.sql
-rw-r--r--. 1 postgres postgres   40011 Jun 10 15:39 schema-only.sql
-rw-r--r--. 1 postgres postgres 2726593 Jun 10 15:40 data-only.sql
-rw-r--r--. 1 postgres postgres 2771911 Jun 10 16:08 dropobject_dvdrental.sql
-rw-r--r--. 1 postgres postgres 2766729 Jun 10 16:08 drop_dvdrental.sql
-rw-r--r--. 1 postgres postgres 2843136 Jun 10 16:15 drop_dvdrental.tar
-rw-r--r--. 1 postgres postgres 2769994 Jun 10 16:44 dumpall.sql
-rw-r--r--. 1 postgres postgres     627 Jun 13 10:33 schema_bkp.sql
[root@test-machine02 backup]#

Help Command: Use option –help to get full available options for both psql & pg_restore command.

-bash-4.2$ psql --help
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit

-bash-4.2$ pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t       backup file format (should be automatic)

Single DB Backup Import: Use the below command to perform Single DB Full backup import using psql. Before attempting to import target database should be created beforehand.

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/dvdrental.sql
Password:
psql: error: FATAL:  database "dvdrental" does not exist
-bash-4.2$
-bash-4.2$

postgres=# create database dvdrental;
CREATE DATABASE
postgres=#

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/dvdrental.sql
Password:
CREATE SCHEMA
ALTER SCHEMA
CREATE TYPE

postgres=# \connect dvdrental
Password:
You are now connected to database "dvdrental" as user "postgres".
dvdrental=#
dvdrental=# \dt
 public | actor         | table | postgres
 public | address       | table | postgres
 
dvdrental=# select count(*) from actor;
 count
-------
   200
(1 row)

Error for Non-Plain Text Format: You will receive the below error if try to import non-plain text formats like tar, custom, or directory format using psql instead of pg_restore.

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/dvdrental_tar
Password:
psql:/u01/backup/dvdrental_tar:6: ERROR:  syntax error at or near "toc"
LINE 1: toc.dat    'G',
        ^
psql:/u01/backup/dvdrental_tar:7: ERROR:  syntax error at or near ""
LINE 1:  CONSTRAINT year_check CHECK (((VALUE >= 1901) AND (VALUE ...
        ^
psql:/u01/backup/dvdrental_tar:15: ERROR:  syntax error at or near ""
LINE 1:     LANGUAGE sql IMMUTABLE

Table Backup Import: Use the below command to perform Table backup import using psql.

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/table_bkp.sql
Password:
-bash-4.2$

Schema Only Backup Import: Use the below command to perform Schema Only backup import using psql.

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/schema-only.sql
Password:
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE TYPE
ALTER TYPE

dvdrental=# \dn
   List of schemas
   Name    |  Owner
-----------+----------
 public    | postgres
 test2_new | postgres
(2 rows)

dvdrental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | autovac_test  | table | postgres
 public | category      | table | postgres
dvdrental=# select count(*) from actor;
 count
-------
     0
(1 row)

Data-Only Backup Import: Use the below command to perform Data Only backup import using psql.

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/data-only.sql
COPY 200
COPY 109
COPY 600
dvdrental=# select count(*) from actor;
 count
-------
   200
(1 row)

dvdrental=#

Drop Object Command Backup Import: Use the below command to perform Drop Object backup import using psql.

-bash-4.2$ psql -U postgres -p 5433 -W  -d dvdrental -f /u01/backup/dropobject_dvdrental.sql
Password:
DROP VIEW
DROP VIEW
DROP TABLE
DROP SEQUENCE
DROP TABLE
DROP SEQUENCE

Drop Database & Object Command Backup Import: If you created backup with option -C -c. Before importing make sure you created dependent Tablespace if Source DB is using non-default Tablespace otherwise Create Database command will fail to create.

-bash-4.2$ psql -U postgres -p 5433 -W  -d postgres -f /u01/backup/drop_dvdrental.sql
Password:

SET
psql:/u01/backup/drop_dvdrental.sql:24: ERROR:  tablespace "myts02" does not exist
ALTER DATABASE

postgres=# create tablespace myts02  location '/u01/psql3_tbls';
CREATE TABLESPACE
postgres=#
 
postgres=#  \db
           List of tablespaces
    Name    |  Owner   |    Location
------------+----------+-----------------
 myts01     | postgres | /u01/psql3_tbls
 pg_default | postgres |
 pg_global  | postgres |
(3 rows)


-bash-4.2$  psql -U postgres -p 5433 -W  -d postgres -f /u01/backup/drop_dvdrental.sql
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE TYPE
ALTER TYPE

Full Cluster Backup Import: Use the below command to perform Full Cluster backup import using psql.

-bash-4.2$  psql -U postgres -p 5433 -W  -d postgres -f /u01/backup/dumpall.sql
Password:
SET
SET
SET

Single DB Backup Import: For pg_restore use only non-plain format to import. Create the Target DB before attempting an import. Use the below command to perform Single DB backup import using pg_restore.

-bash-4.2$ pg_restore -U postgres -p 5433 -W  -d postgres /u01/backup/dumpall.sql
pg_restore: error: input file appears to be a text format dump. Please use psql.
-bash-4.2$

-bash-4.2$
-bash-4.2$ pg_restore -U postgres -p 5433 -W  -d dvdrental  /u01/backup/dvdrental_tar
Password:
pg_restore: error: connection to database "dvdrental" failed: FATAL:  database "dvdrental" does not exist
-bash-4.2$

postgres=#
postgres=# create database dvdrental;
CREATE DATABASE
postgres=#

-bash-4.2$ pg_restore -U postgres -p 5433 -W  -d dvdrental /u01/backup/dvdrental_tar
Password:
-bash-4.2$

Schema Definition Import: Use option –section in pg_restore to define which level of import you want to perform pre-data (Schema Definition), data (Data Only), or post-data (Constraints Creation).

-bash-4.2$ pg_restore -U postgres -p 5433 -W  --section=pre-data -d dvdrental /u01/backup/dvdrental_tar
Password:
-bash-4.2$

-bash-4.2$ pg_restore -U postgres -p 5433 -W  --section=data -d dvdrental /u01/backup/dvdrental_tar
Password:
-bash-4.2$

-bash-4.2$ pg_restore -U postgres -p 5433 -W  --section=post-data -d dvdrental /u01/backup/dvdrental_tar
Password:
-bash-4.2$

Table Level Import: Use option -t to perform Table Level import using pg_restore. Please note we are doing only a single Table import from the entire DB backup.

-bash-4.2$  pg_restore -U postgres -p 5433 -W  -t actor  -v -d dvdrental /u01/backup/dvdrental_tar
pg_restore: connecting to database for restore
Password:
pg_restore: creating TABLE "public.actor"
pg_restore: processing data for table "public.actor"
-bash-4.2$

Schema Level Import: Use option -n to perform Schema Level import using pg_restore. Please note we doing only a single schema import from the entire DB backup.

-bash-4.2$  pg_restore -U postgres -p 5433 -W  -n public  -v -d dvdrental /u01/backup/dvdrental_tar
pg_restore: connecting to database for restore
Password:
pg_restore: creating TYPE "public.mpaa_rating"
pg_restore: creating DOMAIN "public.year"
pg_restore: creating FUNCTION "public._group_concat(text, text)"
pg_restore: creating FUNCTION "public.film_in_stock(integer, integer)"

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles

BACKUP, IMPORT, POSTGRESQL