postgresql: pg_dump scenario 1 - simple table backup using pg_dump to script file and restore it using psql

This is a part 1 of the multi part blog series for pg_dump usage.

Objective: pg_dump scenario 1 - simple table backup using pg_dump to script file and restore it using psql

pg_dump notes:

Allows backup of the entire database in script or archive format.

Script format is plain text format.
Archive format can be restored only using pg_restore, this allows performing data selection & reordering.
Flexible Output file formats for archive are: Fc - custom format, Fd - directory format
Archive format supports - compession, parallel restore,select/reordering
User super user to perform backup/restore
If in case you want to restore with different owner, you have to use -O option in pg_dump
-t is used to mention the table to be backed up
-T exclude tables mentioned
-n include schemas
-N exclude schemas
-Z 0..9 [compression level] supported only for plain script and directory output,not for tar format
pg_dump (lower) -> server (higher) = OK
pg_dump (higher) -> server (lower) = NOK
Using pg_dump to backup the table pgtst_tbl2 and restore it in the same db and new db...

Example - plain script:
pg_dump -U pgtst_usr -d pgtst_db -t pgtst_schema.pgtst_tbl2 > /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

Actual output:

-bash-4.2$ pwd

/pgDATA/pgdump/plainscript

-bash-4.2$ pg_dump -U pgtst_usr -d pgtst_db -t pgtst_schema.pgtst_tbl2 > /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

Password:

>>> no response here, looks like a successful finish

-bash-4.2$ ls -altr

total 60

drwxr-xr-x. 3 postgres postgres    25 Oct 25 13:38 ..

drwxr-xr-x. 2 postgres postgres    41 Oct 25 13:39 .

-rw-r--r--. 1 postgres postgres 58691 Oct 25 13:39 pgtst_schema_pgtst_tbl2.sql

-bash-4.2$ view pgtst_schema_pgtst_tbl2.sql

-bash-4.2$

Let us now restore this table to a new db:

create database pgtst_db2;

postgres=> \l
                                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------
 pgtst_db  | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)
postgres=> \l+
                                                                                    List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   |  Si
ze   | Tablespace |                Description
-----------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------+----
-----+------------+--------------------------------------------
 pgtst_db  | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 208
9 MB | pg_default |
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |                       | 759
7 kB | pg_default | default administrative connection database
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +| 744
1 kB | pg_default | unmodifiable empty database
           |           |          |             |             |            |                 | postgres=CTc/postgres |
     |            |
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +| 751
3 kB | pg_default | default template for new databases
           |           |          |             |             |            |                 | postgres=CTc/postgres |
     |            |
(4 rows)

postgres=> create database pgtst_db2;
CREATE DATABASE

postgres=> \l
                                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+-----------+----------+-------------+-------------+------------+-----------------+-----------------------
 pgtst_db  | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 pgtst_db2 | pgtst_usr | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |           |          |             |             |            |                 | postgres=CTc/postgres
(5 rows)
postgres=>

psql -U pgtst_usr -d pgtst_db2 -f /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

this failed - reason, the schema isnt created, we just created the db.

Actual output:

-bash-4.2$ psql -U pgtst_usr -d pgtst_db2 -f /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql

Password for user pgtst_usr:

SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:32: ERROR:  schema "pgtst_schema" does not exist
LINE 1: CREATE TABLE pgtst_schema.pgtst_tbl2 (
                     ^
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:35: ERROR:  schema "pgtst_schema" does not exist
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:41: ERROR:  schema "pgtst_schema" does not exist
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1041: error: invalid command \.
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1049: ERROR:  syntax error at or near "1"
LINE 1: 1 T 1
        ^
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1051: ERROR:  schema "pgtst_schema" does not exist
psql:/pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql:1058: ERROR:  schema "pgtst_schema" does not exist

Now let us create the schema and retry the operation:

pgtst_db2=> \conninfo
You are connected to database "pgtst_db2" as user "pgtst_usr" via socket in "/var/run/postgresql" at port "5432".

pgtst_db2=> \d
Did not find any relations.

pgtst_db2=> select count(1) from pg_class;
 count
-------
   410
(1 row)

pgtst_db2=> select count(1) from pg_tables;
 count
-------
    68
(1 row)

pgtst_db2=> select distinct(schemaname) from pg_tables;
     schemaname
--------------------
 information_schema
 pg_catalog
(2 rows)
pgtst_db2=>

pgtst_db2=> create schema pgtst_schema; <
CREATE SCHEMA

pgtst_db2=>

Reattempt to load or restore the data:

-bash-4.2$ psql -U pgtst_usr -d pgtst_db2 -f /pgDATA/pgdump/plainscript/pgtst_schema_pgtst_tbl2.sql
Password for user pgtst_usr:
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 999
ALTER TABLE
ALTER TABLE
CREATE INDEX
-bash-4.2$

pgtst_db2=> select relname,reltype,relpages,reltuples,relhasindex from pg_class where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema');
      relname       | reltype | relpages | reltuples | relhasindex
--------------------+---------+----------+-----------+-------------
 pgtst_tbl2         |   24632 |       14 |       999 | t
 pgtst_tbl2_pkey    |       0 |        5 |       999 | f
 pgtst_tbl2_idx_age |       0 |        4 |       999 | f
(3 rows)

pgtst_db2=> analyze pgtst_schema.pgtst_tbl2;
ANALYZE

pgtst_db2=>

pgtst_db2=> select * from pg_stat_all_tables where schemaname='pgtst_schema';
 relid |  schemaname  |  relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_de
l | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum | last_autovacuum |
         last_analyze           |         last_autoanalyze         | vacuum_count | autovacuum_count | analyze_count | aut
oanalyze_count
-------+--------------+------------+----------+--------------+----------+---------------+-----------+-----------+---------
--+---------------+------------+------------+---------------------+--------------------+-------------+-----------------+--
--------------------------------+----------------------------------+--------------+------------------+---------------+----
---------------
 24630 | pgtst_schema | pgtst_tbl2 |        2 |         1998 |        0 |             0 |       999 |         0 |
0 |             0 |        999 |          0 |                   0 |                999 |             |                 | 2
022-10-25 13:53:11.996277+05:30 | 2022-10-25 13:50:57.759322+05:30 |            0 |                0 |             1 |
             1
(1 row)
pgtst_db2=>

Look there the autoanalyze is completed around the same time the data restore finished using psql.

Analyze is complete as well.

Thanks