Logical Backups : Single Table, Multiple Tables, Single Database, Logical Backup Options, Usage

PG Dump of Single Database

POSTGRES-pg_dump(single database)
PostgreSQL pg_dump is a database tool that helps you make automatic, consistent backups. For example, you can back up offline and online databases. The utility creates a set of SQL statements and processes them against the database instance to create a dump file that can use to restore the database later.
We can use the pg_dump command to backup your PostgreSQL database. Even if others are accessing the database, pg_dump will still back it up, and it will not block others from reading or writing to it.

Requirements:
A server running Linux operating system with PostgreSQL installed.
A root password is setup on your server.
A brief explanation of all available options is shown below:
  • -U : Specify the PostgreSQL username.
  • -W : Force the pg_dump command to ask for a password.
  • -F : Specify the format of the output file.
  • -f : Specify the output file.
  • -p : Plain text SQL script.
  • -c : Specify the custom formate.
  • -d : Specify the directory format.
  • -t : Specify tar format archive file.

Backup a Single PostgreSQL Database :
Step 1:list the database and check tables
    postgres=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
    | | | | | postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
    | | | | | postgres=CTc/postgres
    tsdb_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    tsdb_db_1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    (5 rows)
    postgres=# \dt;
    List of relations
    Schema | Name | Type | Owner
    --------+----------+-------+----------
    public | tab_tsdb | table | postgres
    public | tsdb | table | postgres
    (2 rows)
Step 2: Create folder for backup 
    -bash-4.2$ pwd

    /var/lib/pgsql
    -bash-4.2$ mkdir backup_database
    -bash-4.2$ chmod 700 backup_database/
    -bash-4.2$ chown -R postgres. backup_database/
    -bash-4.2$ ls
    backup_database
    -bash-4.2$ cd backup_database/
    -bash-4.2$ ls
    -bash-4.2$
Step 3: Taking backup 
    -bash-4.2$ cd /usr/pgsql-13/bin
    -bash-4.2$ pwd
    /usr/pgsql-13/bin
    -bash-4.2$ ./pg_dump -p 5432 -U postgres -d postgres >
    /var/lib/pgsql/backup_database/db.sql
    -bash-4.2$
Step 4: checking the content of backup 
    -bash-4.2$ pwd
    /var/lib/pgsql
    -bash-4.2$ ls
    13 backup_database backup_schema backup_schema_table backup_table
    -bash-4.2$ cd backup_database/
    -bash-4.2$ ls

    db.sql
    -bash-4.2$ cat db.sql
    --
    -- PostgreSQL database dump
    -- Dumped from database version 13.12
    -- Dumped by pg_dump version 13.12

    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 xmloption = content;
    SET client_min_messages = warning;
    SET row_security = off;

    --
    -- Name: sch_tsdb; Type: SCHEMA; Schema: -; Owner: postgres
    CREATE SCHEMA sch_tsdb;
    ALTER SCHEMA sch_tsdb OWNER TO postgres;
    SET default_tablespace = '';

    SET default_table_access_method = heap;
    -- Name: tab_tsdb; Type: TABLE; Schema: public; Owner: postgres
    CREATE TABLE public.tab_tsdb (
    id integer,
    name text
    );
    ALTER TABLE public.tab_tsdb OWNER TO postgres;

    --
    -- Name: tsdb; Type: TABLE; Schema: public; Owner: postgres
    --

    CREATE TABLE public.tsdb (
    id integer,
    name text,
    age integer
    );
    ALTER TABLE public.tsdb OWNER TO postgres;
    -- Name: tsdb1; Type: TABLE; Schema: sch_tsdb; Owner: postgres
    CREATE TABLE sch_tsdb.tsdb1 (
    id integer,
    name text
    );

    ALTER TABLE sch_tsdb.tsdb1 OWNER TO postgres;
    -- Data for Name: tab_tsdb; Type: TABLE DATA; Schema: public; Owner:
    postgres
    COPY public.tab_tsdb (id, name) FROM stdin;
    1 ts
    2 ts
    3 ts
    4 ts
    \.

    -- Data for Name: tsdb; Type: TABLE DATA; Schema: public; Owner: postgres
    --

    COPY public.tsdb (id, name, age) FROM stdin;
    1 TSDB 1
    2 TSDB 2
    3 TSDB 3
    4 TSDB 4
    5 TSDB 5
    -- Data for Name: tsdb1; Type: TABLE DATA; Schema: sch_tsdb; Owner: postgres
    COPY sch_tsdb.tsdb1 (id, name) FROM stdin;
    1 tsdb
    2 tsdb

    3 tsdb
    4 tsdb
    5 tsdb
    \.
    -- PostgreSQL database dump complete
    --
Step 5: beforing restoring we need to create a new database 
    postgres=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
    | | | | | postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
    | | | | | postgres=CTc/postgres
    tsdb_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    tsdb_db_1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    (5 rows)

    postgres=# create database db_tsdb;

    CREATE DATABASE
    postgres=# \c db_tsdb;
    You are now connected to database "db_tsdb" as user "postgres".
    db_tsdb=# \dt;
    Did not find any relations.
Step 6: Restore to New Database: 
    -bash-4.2$ pwd
    /usr/pgsql-13/bin
    -bash-4.2$ ./psql -p 5432 -U postgres -d db_tsdb -f
    /var/lib/pgsql/backup_database/db.sql
    SET
    SET
    SET
    SET
    SET
    set_config
    ------------

    (1 row)

    SET
    SET
    SET

    SET
    CREATE SCHEMA
    ALTER SCHEMA
    SET
    SET
    CREATE TABLE
    ALTER TABLE
    CREATE TABLE
    ALTER TABLE
    CREATE TABLE
    ALTER TABLE
    COPY 4
    COPY 5
    COPY 5
    -bash-4.2$
Step 7: connect to new database and check values 
    postgres=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    db_tsdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
    | | | | | postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
    | | | | | postgres=CTc/postgres
    tsdb_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    tsdb_db_1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    (6 rows)

    postgres=# \c db_tsdb;
    You are now connected to database "db_tsdb" as user "postgres".
    db_tsdb=# \dt;
    List of relations
    Schema | Name | Type | Owner
    --------+----------+-------+----------
    public | tab_tsdb | table | postgres
    public | tsdb | table | postgres
    (2 rows)

    db_tsdb=# select * from tab_tsdb;
    id | name
    ----+------

    1 | ts
    2 | ts
    3 | ts
    4 | ts
    (4 rows)

    db_tsdb=#

PG_DUMP OF SINGLE TABLE FROM A DATABASE

PostgreSQL pg_dump is a database tool that helps you make automatic, consistent backups. For example, you can back up offline and online databases. The utility creates a set of SQL statements and processes them against the database instance to create a dump file that can use to restore the database later.
We can use the pg_dump command to backup your PostgreSQL database. Even if others are accessing the database, pg_dump will still back it up, and it will not block others from reading or writing to it.
    Requirements :
  • A server running Linux operating system with PostgreSQL installed.
  • A root password is setup on your server.
  • A brief explanation of all available options is shown below:
  • -U : Specify the PostgreSQL username.
  • -W : Force the pg_dump command to ask for a password.
  • -F : Specify the format of the output file.
  • -f : Specify the output file.
  • -p : Plain text SQL script.
  • -c : Specify the custom formate.
  • -d : Specify the directory format.
  • -t : Specify tar format archive file.
Step 1: check for table we are going to backup 
    postgres=# \dt;
    List of relations
    Schema | Name | Type | Owner
    --------+------+-------+----------
    public | tsdb | table | postgres
    (1 row)
Step 2: create a folder for saving the backup file 
    -bash-4.2$ pwd
    /var/lib/pgsql
    -bash-4.2$ mkdir backup_table
    -bash-4.2$ ls
    13 backup_table
    -bash-4.2$
Step 3:Taking Backup of table 
    -bash-4.2$ pwd
    /usr/pgsql-13/bin
    -bash-4.2$ ./pg_dump -p 5432 -U postgres -d postgres -t tsdb >
    /var/lib/pgsql/backup_table/table.sql
Step 4:check in the folder if the backup file has been created 
    -bash-4.2$ cd backup_table/
    -bash-4.2$ ls
    table.sql
    -bash-4.2$ cat table.sql
    --
    -- PostgreSQL database dump
    --

    -- Dumped from database version 13.12
    -- Dumped by pg_dump version 13.12

    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 xmloption = content;
    SET client_min_messages = warning;
    SET row_security = off;

    SET default_tablespace = "";

    SET default_table_access_method = heap;

    --
    -- Name: tsdb; Type: TABLE; Schema: public; Owner: postgres
    --

    CREATE TABLE public.tsdb (
    id integer,
    name text,
    age integer
    );
    ALTER TABLE public.tsdb OWNER TO postgres;

    --
    -- Data for Name: tsdb; Type: TABLE DATA; Schema: public; Owner: postgres
    --

    COPY public.tsdb (id, name, age) FROM stdin;
    1 TSDB 1
    2 TSDB 2
    3 TSDB 3
    4 TSDB 4
    5 TSDB 5
    \.

    --
    -- PostgreSQL database dump complete.
Step 5:restoring the backup to another database
Check Database: 
    postgres=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres

    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    tsdb_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    (4 rows)
Step 6: connect to database where we have to restore 
    postgres=# \c tsdb_db;
    You are now connected to database "tsdb_db"; as user "postgres".
    tsdb_db=# \dt;
    Did not find any relations.
    tsdb_db=#
Step 7:restore the file to the desired location using 'psql' command 
    -bash-4.2$ pwd
    /usr/pgsql-13/bin
    -bash-4.2$ ./psql -p 5432 -U postgres -d tsdb_db -f /var/lib/pgsql/backup_table/table.sql
    SET
    SET
    SET
    SET
    SET
    set_config
    ------------

    (1 row)

    SET

    SET
    SET
    SET
    SET
    SET
    CREATE TABLE
    ALTER TABLE
    COPY 5
    -bash-4.2$
Step 8:check for the table in database 
    tsdb_db=# \dt;
    List of relations
    Schema | Name | Type | Owner
    --------+------+-------+----------
    public | tsdb | table | postgres
    (1 row)


(pg_backrest : Third party tool for backup)