Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Multile Schema Backup & Restore,Backup Script,Restoring Script,Backup & Restore Prerequest & PostRequest

Currently we want to take the backup what are the schema name having kafi and nijam.
PreRequest:

  • checking table rows count of particular table.
  • Check the schema's size.
  • check the available  backup location size.

1.List the schema whoose name start with kafi AND nijam:
mhrorsin=# \dn+ kafi*
                                      List of schemas
         Name          |      Owner      |         Access privileges          | Description
-----------------------+-----------------+------------------------------------+-------------
 kafi                  | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz+                 |
                       |                 | umhrorsin=UC/benz                  |
 kafi_audit            | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz+                 |
                       |                 | umhrorsin=UC/benz                  |
 kafi_ech              | benz            |                                    |
 kafi_his              | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz+                 |
                       |                 | umhrorsin=UC/benz                  |
 kafi_ideal            | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz                  |
 kafi_os               | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz                  |
 kafi_rollback         | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz                  |
 kafi_wadi             | benz            | benz=UC/benz+                      |
                       |                 | emutation=UC/benz+                 |
                       |                 | umhrorsin=UC/benz                  |
(8 rows)
mhrorsin=# \dn nijam*
                                      List of schemas
         Name          |      Owner      |         Access privileges        
-----------------------+-----------------+--------------------------------
 nijam                 | benz            | benz=UC/benz+                    
                       |                 | emutation=UC/benz     
                       |                 | umhrorsin=UC/benz       
 nijam_audit           | benz            | benz=UC/benz+
                       |                 | emutation=UC/benz     
                       |                 | umhrorsin=UC/benz       
 nijam_ech             | benz            |                                    
 nijam_his             | benz            | benz=UC/benz+
                       |                 | emutation=UC/benz     
                       |                 | umhrorsin=UC/benz      
 nijam_ideal           | benz            | benz=UC/benz+
                       |                 | emutation=UC/benz       
 nijam_os              | benz            | benz=UC/benz+
                       |                 | emutation=UC/benz       
 nijam_rollback        | benz            | benz=UC/benz+
                       |                 | emutation=UC/benz       
 nijam_wadi            | benz            | benz=UC/benz+
                       |                 | emutation=UC/benz      
                       |                 | umhrorsin=UC/benz      
(8 rows)   
                         (OR)
select nspname from pg_catalog.pg_namespace  where nspname like 'kafi%';
select nspname from pg_catalog.pg_namespace  where nspname like 'nijam%';

mhrorsin=# select nspname from pg_catalog.pg_namespace  where nspname like 'kafi%';
        nspname
-----------------------
 kafi
 kafi_wadi
 kafi_ech
 kafi_ideal
 kafi_os
 kafi_rollback
 kafi_his
 kafi_audit
(8 rows)
mhrorsin=# select nspname from pg_catalog.pg_namespace  where nspname like 'nijam%';
        nspname
-----------------------
 nijam_ech
 nijam_wadi
 nijam_ideal
 nijam_os
 nijam_rollback
 nijam_his
 nijam
 nijam_audit
(8 rows)  
2.check schemas size and percentage of database usage:
SELECT schema_name, 
    pg_size_pretty(sum(table_size)::bigint) as "disk space",
    (sum(table_size) / pg_database_size(current_database())) * 100
        as "percent"
FROM (
     SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
     FROM   pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace 
             ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

      schema_name      | disk space |            percent
-----------------------+------------+--------------------------------
 information_schema    | 96 kB      |     0.000054350853113262904000
 nijam                 | 14 GB      |         8.16790508595202191400
 nijam_audit           | 796 MB     |         0.46133457046313496800
 nijam_ech             | 392 kB     |     0.000221932650212490191300
 nijam_his             | 1975 MB    |         1.14482372378897261700
 nijam_ideal           | 0 bytes    | 0.0000000000000000000000000000
 nijam_os              | 1050 MB    |         0.60879296419717666500
 nijam_rollback        | 0 bytes    | 0.0000000000000000000000000000
 nijam_wadi            | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_dod          | 3612 MB    |         2.09389832085276944600
 mhrorsin_dod_audit    | 534 MB     |         0.30929258811654143200
 mhrorsin_dod_ech      | 392 kB     |     0.000221932650212490191300
 mhrorsin_dod_his      | 1528 MB    |         0.88575585318684554600
 mhrorsin_dod_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_dod_os       | 2567 MB    |         1.48843434640878013500
 mhrorsin_dod_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_dod_wadi     | 0 bytes    | 0.0000000000000000000000000000
 kafi                  | 15 GB      |         9.06973520394711496400
 kafi_audit            | 1554 MB    |         0.90120508318429052700
 kafi_ech              | 392 kB     |     0.000221932650212490191300
 kafi_his              | 1151 MB    |         0.66721560205617484800
 kafi_ideal            | 0 bytes    | 0.0000000000000000000000000000
 kafi_os               | 991 MB     |         0.57474668395947689400
 kafi_rollback         | 0 bytes    | 0.0000000000000000000000000000
 kafi_wadi             | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud          | 18 GB      |        10.53531501662176804700
 mhrorsin_kud_audit    | 1173 MB    |         0.68013298814609366500
 mhrorsin_kud_ech      | 392 kB     |     0.000221932650212490191300
 mhrorsin_kud_his      | 1647 MB    |         0.95489466758467539900
 mhrorsin_kud_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud_os       | 637 MB     |         0.36909664349216838100
 mhrorsin_kud_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud_wadi     | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal          | 21 GB      |        12.37762776765100295000
 mhrorsin_mal_audit    | 2211 MB    |         1.28192375076717829200
 mhrorsin_mal_ech      | 392 kB     |     0.000221932650212490191300
 mhrorsin_mal_his      | 6009 MB    |         3.48385797989583607600
 mhrorsin_mal_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal_os       | 636 MB     |         0.36877506761124824200
 mhrorsin_mal_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal_wadi     | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_saw          | 15 GB      |         8.92249874286328575700
 mhrorsin_saw_audit    | 3435 MB    |         1.99160548605584922300
 mhrorsin_saw_ech      | 392 kB     |     0.000221932650212490191300
 mhrorsin_saw_his      | 5540 MB    |         3.21155114732287005000
 mhrorsin_saw_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_saw_os       | 3209 MB    |         1.86050216987114022100
 mhrorsin_saw_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_saw_wadi     | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_vai          | 5748 MB    |         3.33262220187042260700
 mhrorsin_vai_audit    | 263 MB     |         0.15265342944411774300
 mhrorsin_vai_ech      | 392 kB     |     0.000221932650212490191300
 mhrorsin_vai_his      | 708 MB     |         0.41034894100513492500
 mhrorsin_vai_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_vai_os       | 91 MB      |         0.05281997075057266600
 mhrorsin_vai_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_vai_wadi     | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_ven          | 9233 MB    |         5.35257618706259787300
 mhrorsin_ven_audit    | 1004 MB    |         0.58221086778703166600
 mhrorsin_ven_ech      | 392 kB     |     0.000221932650212490191300
 mhrorsin_ven_his      | 2249 MB    |         1.30397208018012527700
 mhrorsin_ven_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_ven_os       | 786 MB     |         0.45580437115886046700
 mhrorsin_ven_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_ven_wadi     | 0 bytes    | 0.0000000000000000000000000000
 pg_catalog            | 223 MB     |         0.12911498080831546700
 pg_toast              | 26 GB      |        15.63575759609193930800
 public                | 96 kB      |     0.000054350853113262904000
 rcis_uni              | 179 MB     |         0.10364707688699235800
(69 rows)
3. Writing script for taking multiple schema backup:
vi /home/postgres/eds/devkan1.sh

#!/bin/sh
# The script sets environment variables helpful for PostgreSQL

export PATH=/opt/PostgreSQL/9.3/bin:$PATH
export PGDATA= /data/emutsin_15/
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/opt/PostgreSQL/9.3/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/9.3/share/man

pg_dump -Fc -n 'nijam*' -n 'kafi*'  mhrorsin > /backup/devkan1.dump
                    (OR)
WITHOUT COMPRESS 60GB DATA SHOULD COME 27GB DUMP TASK WILL COMPLETE WITHIN 2 HOURS if you use compress option dump size will be around 2GB

pg_dump  -n 'nijam*' -n 'kafi*'  mhrorsin > /backup/devkan1.dump
SCHEDULING CRONTAB JOB:
list out the crontab jobs first before adding or editing jobs.
bash-4.1$ crontab -l 

--check date before schedule crontab job
bash-4.1$ date
Sat Dec 23 00:15:47 IST 2017

bash-4.1$ crontab -e  
20 0 23 * * sh /home/postgres/eds/devkan1.sh   
4.RESTORING THE BACKUP:
Create new database if database not exists
--Before setting the crontab just check the date
bash-4.2$ date
Sat Oct 21 08:24:39 IST 2017

--AT CRONTAB:crontab -e
26 11 21 * * sh /home/postgres/eds/devkanRESTORE.sh

----devkanRESTORE.sh----
export PATH=/opt/PostgreSQL/9.6/bin:$PATH
export PGDATA=/home/data/
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/opt/PostgreSQL/9.6/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/9.6/share/man
pg_restore  -d mhrorsin  /home/backup/devkan1.dump


           (OR)
--WITHOUT COMPRESS OPTION
psql  -d mhrorsin  /home/backup/devkan1.dump  
5.check schema growth at every time while Restoring dump using following script
PostRequest:

  • Check the data directoy size.
  • check the data growth while restoring.
  • check the wall log genereation if generated more wall log just compress and move the archives.
  • after restored count the particular table rows.
  • check the schema size and analyze that.

SELECT schema_name, 
    pg_size_pretty(sum(table_size)::bigint) as "disk space",
    (sum(table_size) / pg_database_size(current_database())) * 100
        as "percent"
FROM (
     SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
     FROM   pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace 
             ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name  
After 5 minutes you will see your schema growth like folowing
      schema_name      | disk space |            percent
-----------------------+------------+--------------------------------
 information_schema    | 96 kB      |     0.000138821907070813026700
 nijam                 | 5777 MB    |         8.55368533155198315400
 nijam_audit           | 168 kB     |     0.000242938337373922796700
 nijam_ech             | 232 kB     |     0.000335486275421131481100
 nijam_his             | 0 bytes    | 0.0000000000000000000000000000
 nijam_ideal           | 0 bytes    | 0.0000000000000000000000000000
 nijam_os              | 64 kB      |     0.000092547938047208684400
 nijam_rollback        | 0 bytes    | 0.0000000000000000000000000000
 nijam_wadi            | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud          | 17 GB      |        26.26843654356752415800
 mhrorsin_kud_audit    | 1161 MB    |         1.71979519574676718100
 mhrorsin_kud_ech      | 392 kB     |     0.000566856120539153192200
 mhrorsin_kud_his      | 1679 MB    |         2.48660113643691473600
 mhrorsin_kud_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud_os       | 637 MB     |         0.94273957091789126400
 mhrorsin_kud_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud_wadi     | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal          | 20 GB      |        30.90205729476163316400
 mhrorsin_mal_audit    | 2226 MB    |         3.29560893687658945100
 mhrorsin_mal_ech      | 392 kB     |     0.000566856120539153192200
 mhrorsin_mal_his      | 6065 MB    |         8.98051642182570960700
 mhrorsin_mal_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal_os       | 636 MB     |         0.94191820796772228700
 mhrorsin_mal_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal_wadi     | 0 bytes    | 0.0000000000000000000000000000
 pg_catalog            | 73 MB      |         0.10881323815900561100
 pg_toast              | 10 GB      |        15.64645518705975362000
 rcis_uni              | 63 MB      |         0.09390145164114911100
(28 rows) 
Note:After backup and Restoring process is completed, comment or remove crontab job which one scheduled for taking backup and restore.

Using top commond you can see whether operation is done or not For Example
THIS IS FOR RESTORING IS RUNNING:
bash-4.2$ top -c
top - 07:31:20 up 12 days,  6:21,  3 users,  load average: 2.12, 0.83, 0.43
Tasks: 285 total,   2 running, 274 sleeping,   0 stopped,   9 zombie
%Cpu(s): 15.9 us,  1.7 sy,  0.0 ni, 74.7 id,  7.7 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 65558836 total, 46535024 free,  1530976 used, 17492836 buff/cache
KiB Swap: 32899068 total, 32899068 free,        0 used. 59015724 avail Mem

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 71355 postgres  20   0 4540212 353000 342888 R  76.4  0.5   1:45.36 postgres: postgres mhrorsin [local] COPY
 71352 postgres  20   0  150152   6088   1848 S  24.3  0.0   0:31.93 pg_restore -d mhrorsin /home/backup/devkan1.dump
 71557 postgres  20   0 4543712 197244 183332 S  17.6  0.3   0:38.23 postgres: autovacuum worker process   mhrorsin
 69087 postgres  20   0 4526684 4.095g 4.089g D   1.3  6.5   3:19.39 postgres: checkpointer process
THIS IS FOR RESTORING IS COMPLETED:
bash-4.2$ top -c
top - 07:41:17 up 12 days,  6:31,  3 users,  load average: 1.75, 1.90, 1.22
Tasks: 283 total,   2 running, 272 sleeping,   0 stopped,   9 zombie
%Cpu(s): 19.9 us,  1.4 sy,  0.0 ni, 66.9 id, 11.8 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 65558836 total, 24838252 free,  1630616 used, 39089968 buff/cache
KiB Swap: 32899068 total, 32899068 free,        0 used. 58915852 avail Mem

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 71355 postgres  20   0 4548204 3.558g 3.543g R  99.7  5.7   9:16.06 postgres: postgres mhrorsin [local] COPY
 68871 root      20   0       0      0      0 S   1.3  0.0   0:02.96 [kworker/u384:2]
 69087 postgres  20   0 4526684 4.095g 4.089g S   0.7  6.5   3:26.77 postgres: checkpointer process
 69089 postgres  20   0 4521216  34848  33920 S   0.7  0.1   1:59.08 postgres: wal writer process
 72568 postgres  20   0 4573856 139244  86304 S   0.7  0.2   0:23.34 postgres: autovacuum worker process   mhrorsin
 64173 root      20   0       0      0      0 S   0.3  0.0   0:00.50 [kworker/7:1]
 73442 postgres  20   0 4573152  99960  48932 S   0.3  0.2   0:13.27 postgres: autovacuum worker process   mhrorsin
 74675 postgres  20   0  157876   2528   1632 R   0.3  0.0   0:00.03 top -c
     1 root      20   0  191392   4100   2176 S   0.0  0.0   2:59.04 /usr/lib/systemd/systemd --switched-root --system --deserialize 21
     2 root      20   0       0      0      0 S   0.0  0.0   0:00.31 [kthreadd]
6.Verify the schema after Restoration:
SELECT schema_name, 
    pg_size_pretty(sum(table_size)::bigint) as "disk space",
    (sum(table_size) / pg_database_size(current_database())) * 100
        as "percent"
FROM (
     SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
     FROM   pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace 
             ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

      schema_name      | disk space |            percent
-----------------------+------------+--------------------------------
 information_schema    | 96 kB      |     0.000091168390280551593400
 nijam                 | 13 GB      |        12.62837191649116506100
 nijam_audit           | 764 MB     |         0.74327309385976700300
 nijam_ech             | 392 kB     |     0.000372270926978919006300
 nijam_his             | 1974 MB    |         1.91941370477159297100
 nijam_ideal           | 0 bytes    | 0.0000000000000000000000000000
 nijam_os              | 1050 MB    |         1.02119233426417182300
 nijam_rollback        | 0 bytes    | 0.0000000000000000000000000000
 nijam_wadi            | 0 bytes    | 0.0000000000000000000000000000
 kafi                  | 15 GB      |        14.47390123830622767800
 kafi_audit            | 1495 MB    |         1.45345965941355049000
 kafi_ech              | 392 kB     |     0.000372270926978919006300
 kafi_his              | 1144 MB    |         1.11257345078871137000
 kafi_ideal            | 0 bytes    | 0.0000000000000000000000000000
 kafi_os               | 991 MB     |         0.96408293511926296200
 kafi_rollback         | 0 bytes    | 0.0000000000000000000000000000
 kafi_wadi             | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud          | 17 GB      |        17.25124748244709470700
 mhrorsin_kud_audit    | 1161 MB    |         1.12943960299061341500
 mhrorsin_kud_ech      | 392 kB     |     0.000372270926978919006300
 mhrorsin_kud_his      | 1679 MB    |         1.63302340143695356600
 mhrorsin_kud_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud_os       | 637 MB     |         0.61912453839522587100
 mhrorsin_kud_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_kud_wadi     | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal          | 20 GB      |        20.29428120796305921600
 mhrorsin_mal_audit    | 2226 MB    |         2.16432239052858140200
 mhrorsin_mal_ech      | 392 kB     |     0.000372270926978919006300
 mhrorsin_mal_his      | 6065 MB    |         5.89776673827330641500
 mhrorsin_mal_ideal    | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal_os       | 636 MB     |         0.61858512541939927400
 mhrorsin_mal_rollback | 0 bytes    | 0.0000000000000000000000000000
 mhrorsin_mal_wadi     | 0 bytes    | 0.0000000000000000000000000000
 pg_catalog            | 91 MB      |         0.08819782023057695400
 pg_toast              | 16 GB      |        15.86913468579393255100
 rcis_uni              | 63 MB      |         0.06166781865893644000
(36 rows)
mhrorsin=# \dn nijam*
             List of schemas
         Name   |      Owner
----------------+-----------------
 nijam          | benz
 nijam_audit    | benz
 nijam_ech      | benz
 nijam_his      | benz
 nijam_ideal    | benz
 nijam_os       | benz
 nijam_rollback | benz
 nijam_wadi     | benz
(8 rows)
mhrorsin=# \dn kafi*
             List of schemas
         Name  |      Owner
---------------+-----------------
 kafi          | benz
 kafi_audit    | benz
 kafi_ech      | benz
 kafi_his      | benz
 kafi_ideal    | benz
 kafi_os       | benz
 kafi_rollback | benz
 kafi_wadi     | benz
(8 rows)
8.
After succesfully completed your task, just check the rows count of some particular schema of particular table equal to same as restored schemas table's row count
EXAMPLE:
--Before Taking Backup:

select count(*) from Table_name;

 --After Restored schema:

select count(*) from Table_name;
Note:count should be same

Note:

  • Size will be Reduced becouse dead tubles was cleared(unused spaced)while Restoring.
  • At the time of Restoring More wall Log willbe generated if Archive enabled Just compress & move the archives to backup Location

Job Was Done! ok bye!...

No comments:

Post a Comment