PostgreSQL Multiple Schema Backup & Restore
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!…
Tag:backup, Postgres schema, postgresql
Login with Google