Backup and Restore - 2

 
-bash-4.2$ psql -p 5455
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
Type "help" for help.
postgres=#
postgres=#
postgres=# show version();
ERROR:  syntax error at or near "("
LINE 1: show version();
                    ^
postgres=#
postgres=#
postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
postgres=#
postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)
postgres=#
postgres=# create schema test1;
CREATE SCHEMA
postgres=#
postgres=#
postgres=# create schema test2;
CREATE SCHEMA
postgres=#
postgres=#
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
postgres=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 test1  | postgres
 test2  | postgres
(3 rows)
postgres=#
postgres=# \dn+;
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
 test1  | postgres          |                                        |
 test2  | postgres          |                                        |
(3 rows)
postgres=#
postgres=# SELECT schema_name, schema_owner FROM information_schema.schemata;
    schema_name     |   schema_owner
--------------------+-------------------
 test2              | postgres
 test1              | postgres
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
(6 rows)
postgres=#
postgres=# SELECT nspname AS schema_name FROM pg_catalog.pg_namespace;
    schema_name
--------------------
 pg_toast
 pg_catalog
 public
 information_schema
 test1
 test2
(6 rows)
postgres=# create table test1.emp(no int,name varchar);
CREATE TABLE
postgres=#
postgres=#
postgres=# create table test1.dept(no int,name varchar);
CREATE TABLE
postgres=#
postgres=# create table test2.emp(no int,name varchar);
CREATE TABLE
postgres=#
postgres=# create table test2.dept(no int,name varchar);
CREATE TABLE
postgres=#
postgres=#
postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
 test1  | emp  | table | postgres
(2 rows)
postgres=#
postgres=# \dt test2.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test2  | dept | table | postgres
 test2  | emp  | table | postgres
(2 rows)
postgres=#
postgres=# insert into test1.emp value(1,'Amit'),(2,'Samik');
ERROR:  syntax error at or near "value"
LINE 1: insert into test1.emp value(1,'Amit'),(2,'Samik');
                              ^
postgres=# insert into test1.emp values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=#
postgres=# insert into test1.dept values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=# insert into test2.dept values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=# insert into test2.emp values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=#

-bash-4.2$
-bash-4.2$ cd postgre_backup/
-bash-4.2$
-bash-4.2$ ls -lrt
total 4
drwxrwxr-x 20 postgres postgres 4096 Jan 22 19:14 15_cluster_db1_bkp
-bash-4.2$
-bash-4.2$ mkdir backup
-bash-4.2$ ls -lrt
total 8
drwxrwxr-x 20 postgres postgres 4096 Jan 22 19:14 15_cluster_db1_bkp
drwxrwxr-x  2 postgres postgres 4096 Jan 24 09:47 backup
-bash-4.2$
-bash-4.2$ pwd
/backup/postgre_backup
-bash-4.2$
-bash-4.2$








1) Scenario 1 : Table Backup :-
-bash-4.2$
-bash-4.2$ cd /backup/postgre_backup/backup
-bash-4.2$
-bash-4.2$ pg_dump -p 5455 -d postgres -t test1.emp>emp.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 4
-rw-rw-r-- 1 postgres postgres 886 Jan 24 10:01 emp.sql
-bash-4.2$
postgres=#
postgres=# drop table test1.emp;
DROP TABLE

postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
(1 row)

If you take pg_dump with plain format, psql utility can be used to restore.

psql -f emp.sql -d postgres -o emp.log
-bash-4.2$
-bash-4.2$ psql -f emp.sql -d postgres -o emp.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 8
-rw-rw-r-- 1 postgres postgres 886 Jan 24 10:01 emp.sql
-rw-rw-r-- 1 postgres postgres 113 Jan 24 10:10 emp.log
-bash-4.2$
-bash-4.2$

-bash-4.2$ cat emp.log
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3

-bash-4.2$ psql
psql (15.5)
Type "help" for help.
postgres=#
postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
 test1  | emp  | table | postgres
(2 rows)


2) Scenario 2 : Table's structure backup :-
-bash-4.2$ pg_dump -d postgres -t test1.emp -s>emp_structure.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -rlt
total 4
-rw-rw-r-- 1 postgres postgres 740 Jan 24 10:15 emp_structure.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_structure.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.5
-- Dumped by pg_dump version 15.5
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: emp; Type: TABLE; Schema: test1; Owner: postgres
--
CREATE TABLE test1.emp (
    no integer,
    name character varying
);

ALTER TABLE test1.emp OWNER TO postgres;
--
-- PostgreSQL database dump complete
--

3) Scenario 3 : Take only data backup  :-
-bash-4.2$ pg_dump -d postgres -t test1.emp -a>emp_data.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 8
-rw-rw-r-- 1 postgres postgres 740 Jan 24 10:15 emp_structure.sql
-rw-rw-r-- 1 postgres postgres 635 Jan 24 10:17 emp_data.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_data.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.5
-- Dumped by pg_dump version 15.5
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;
--
-- Data for Name: emp; Type: TABLE DATA; Schema: test1; Owner: postgres
--
COPY test1.emp (no, name) FROM stdin;
1       Amit
2       Samik
3       Dhamik
\.

--
-- PostgreSQL database dump complete
--

4) Scenario 4 : Restore with Structure+Data  :-
postgres=#
postgres=# drop table test1.emp;
DROP TABLE
postgres=#
postgres=#
postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
(1 row)
postgres=#

-bash-4.2$
-bash-4.2$ psql -f emp_structure.sql -d postgres -o structurestore.log
-bash-4.2$
-bash-4.2$ psql -f emp_data.sql  -d postgres -o datastore.log
-bash-4.2$

-bash-4.2$ ls -lrt
total 16
-rw-rw-r-- 1 postgres postgres 740 Jan 24 10:15 emp_structure.sql
-rw-rw-r-- 1 postgres postgres 635 Jan 24 10:17 emp_data.sql
-rw-rw-r-- 1 postgres postgres 106 Jan 24 10:21 structurestore.log
-rw-rw-r-- 1 postgres postgres  80 Jan 24 10:22 datastore.log

postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
 test1  | emp  | table | postgres
(2 rows)


5) Scenario 5 : Take table backup in BIN format  :-
pg_dump -d postgres -Fc -f emp.bin -t test1.emp -v 2>emp.log
-bash-4.2$ pg_dump -d postgres -Fc -f emp.bin -t test1.emp -v 2>emp.log
-bash-4.2$


-bash-4.2$ cat emp.log
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "test1.emp"
6) Scenario 6 : Restore Table
pg_restore -Fc -d postgres -n test1 -t emp -c emp.bin -v 2>emp_restore.log
-bash-4.2$
-bash-4.2$ pg_restore -Fc -d postgres -n test1 -t emp -c emp.bin -v 2>emp_restore.log
-bash-4.2$

-bash-4.2$ cat emp_restore.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE emp
pg_restore: creating TABLE "test1.emp"
pg_restore: processing data for table "test1.emp"

7) Scenario 7 : Take table backup in tar format 
pg_dump -Ft -f emp.tar -d postgres -t test1.emp -v 2>emp_tar_bkp.log

-bash-4.2$ pg_dump -Ft -f emp.tar -d postgres -t test1.emp -v 2>emp_tar_bkp.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 40
-rw-rw-r-- 1 postgres postgres 2195 Jan 24 10:46 emp_tar_bkp.log
-rw-rw-r-- 1 postgres postgres 6656 Jan 24 10:46 emp.tar

8) Scenario 8 : Restore Table from tar backup
pg_restore -Ft -d postgres -n test1 -t emp emp.tar -c -v 2>emp_tar_restore.log
-bash-4.2$ pg_restore -Ft -d postgres -n test1 -t emp emp.tar -c -v 2>emp_tar_restore.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_tar_restore.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE emp
pg_restore: creating TABLE "test1.emp"
pg_restore: processing data for table "test1.emp"
-bash-4.2$


9) Scenario 8 : Take backup in Directory format 
pg_dump -Fd -f emp -d postgres -t test1.emp -v 2>emp_dir_bkp.log
-bash-4.2$ pg_dump -Fd -f emp -d postgres -t test1.emp -v 2>emp_dir_bkp.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
-rw-rw-r-- 1 postgres postgres 1945 Jan 24 10:53 emp_dir_bkp.log
drwx------ 2 postgres postgres 4096 Jan 24 10:53 emp




10) Scenario  : Restore from emp directory
pg_restore -Fd -d postgres -n test1 -t emp -c emp -v 2>emp_restore_from_dir.log
-bash-4.2$ pg_restore -Fd -d postgres -n test1 -t emp -c emp -v 2>emp_restore_from_dir.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_restore_from_dir.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE emp
pg_restore: creating TABLE "test1.emp"
pg_restore: processing data for table "test1.emp"
Note:- Only Directory format supports Parallel job
pg_dump -Ft -f emp.tar -d postgres -t test1.emp -j 5 -v 2>emp_bkp_using_parallel_job.log

11) Scenario Multiple tables backup at a time
pg_dump -Fc -d postgres -t test1.emp -t test1.dept -f multiple.bin -v 2>multiple_bkp.log
-bash-4.2$ pg_dump -Fc -d postgres -t test1.emp -t test1.dept -f multiple.bin -v 2>multiple_bkp.log
-bash-4.2$ ls -rlt
total 8
-rw-rw-r-- 1 postgres postgres 1993 Jan 24 11:06 multiple_bkp.log
-rw-rw-r-- 1 postgres postgres 1924 Jan 24 11:06 multiple.bin
-bash-4.2$


12) Multiple table restore
pg_restore -Fc -d postgres -n test1 -t emp -t dept multiple.bin -c -v 2>restore.log



13) Schema backup
pg_dump -Fc -d postgres -n test1 -f schema.bin -v 2>schema_bkp.log
14) Schema restore
pg_restore -Fc -d postgres -n test1 schema.bin -c -v 2>restore_schema.log
15) Restore single table from schema dumps
pg_restore -Fc -d postgres -n test1 -t dept schema.bin -c -v 2>rept_restore_Schema.log

16) Multiple table schema backup:-
pg_dump -Fc -d postgres -n test1 -n test2 -f multiple_schema_bkp.bin -v 2>multiple_schema_bkp.log

17) Take single DB backup
pg_dump -Fc -d postgres -f db.bin -v 2>dbbackup.log

18) Restore DB backup
pg_restore -Fc -d postgres db.bin -c -v 2>db_restore.log

19) Remap the database (backup taken for Raj database restore same dump as Ram database)
create database ram;
pg_restore -Fc ram db.bin -v 2>remap.log

20) Logical Full backup -
pg_dumpall>fullserverbkp.sql


21) Restore Full backup :-
psql -f fullserverbkp.sql -o fullserverbkp.log

22) Take Global object backup 
pg_dumpall -g>global.sql

Note: In real time we need to migrate complete server to another server , only logical backup can help.
take globals backup pg_dumpalll -g 
take backup database status using parallel job