.::: Script Backup Full All Database in PostgreSQL EDB :::.

corelation with http://teguhth.blogspot.com/2023/08/backup-and-restore-postgresql-edb.html

 

 1. check data from table
select * from teguhth.public.pembelian;
select * from edb.public.barang;
 

2. run backup
su - postgres
sh backup_edb_full_script.sh

-bash-4.2$ sh backup_edb_full_script.sh

.::: Backup Full All database using custom in 'teguhth' with IP 10.10.10.8/24 :::.

PostgreSQL Version : 'teguhth' PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

Backup begin Mon Feb 19 13:46:48 WIB 2024

Backup Full for database 'teguhth' in /data/edb/edbbackup/20240219/teguhth start at 20240219_134648
Finish Backup Full for database 'teguhth' in /data/edb/edbbackup/20240219/teguhth at 20240219_134648
Backup Full for database 'edb' in /data/edb/edbbackup/20240219/edb start at 20240219_134648
Finish Backup Full for database 'edb' in /data/edb/edbbackup/20240219/edb at 20240219_134648
Backup Full for database 'tgh' in /data/edb/edbbackup/20240219/tgh start at 20240219_134648
Finish Backup Full for database 'tgh' in /data/edb/edbbackup/20240219/tgh at 20240219_134648

Backup finish Mon Feb 19 13:46:48 WIB 2024

Copyright by           : Teguh Triharto
Website                : https://www.linkedin.com/in/teguhth

-bash-4.2$
 

3. delete data from table and check 

delete from teguhth.public.pembelian;

delete from edb.public.barang;

 

4. restore database

su - postgres
gunzip -c /data/edb/edbbackup/20240219/teguhth/[FULL]_teguhth_20240219_134648.sql.gz | psql teguhth
gunzip -c /data/edb/edbbackup/20240219/edb/[FULL]_edb_20240219_134648.sql.gz | psql edb

su - postgres
gunzip -c /data/edb/edbbackup/20240219/teguhth/[FULL]_teguhth_20240219_134648.sql.gz | psql teguhth
gunzip -c /data/edb/edbbackup/20240219/edb/[FULL]_edb_20240219_134648.sql.gz | psql edb

-bash-4.2$ gunzip -c /data/edb/edbbackup/20240219/teguhth/[FULL]_teguhth_20240219_134648.sql.gz | psql teguhth
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "barang" already exists
ALTER TABLE
ERROR:  relation "customer" already exists
ALTER TABLE
ERROR:  relation "pasok" already exists
ALTER TABLE
ERROR:  relation "pembelian" already exists
ALTER TABLE
ERROR:  relation "suplier" already exists
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "barang_pkey"
DETAIL:  Key (kode_barang)=(ELK-01) already exists.
CONTEXT:  COPY barang, line 1
ERROR:  duplicate key value violates unique constraint "customer_pkey"
DETAIL:  Key (kode_customer)=(J-0001) already exists.
CONTEXT:  COPY customer, line 1
ERROR:  duplicate key value violates unique constraint "pasok_pkey"
DETAIL:  Key (kode_pasok, kode_barang, kode_suplier)=(PAS-E001  , ELK-01, EJ-01) already exists.
CONTEXT:  COPY pasok, line 1
COPY 15
ERROR:  duplicate key value violates unique constraint "suplier_pkey"
DETAIL:  Key (kode_suplier)=(EJ-01) already exists.
CONTEXT:  COPY suplier, line 1
ERROR:  multiple primary keys for table "barang" are not allowed
ERROR:  multiple primary keys for table "customer" are not allowed
ERROR:  multiple primary keys for table "pasok" are not allowed
ERROR:  multiple primary keys for table "pembelian" are not allowed
ERROR:  multiple primary keys for table "suplier" are not allowed
ERROR:  constraint "pasok_kode_barang_fkey" for relation "pasok" already exists
ERROR:  constraint "pasok_kode_suplier_fkey" for relation "pasok" already exists
ERROR:  constraint "pembelian_kode_barang_fkey" for relation "pembelian" already exists
ERROR:  constraint "pembelian_kode_customer_fkey" for relation "pembelian" already exists
-bash-4.2$

-bash-4.2$ gunzip -c /data/edb/edbbackup/20240219/edb/[FULL]_edb_20240219_134648.sql.gz | psql edb
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "barang" already exists
ALTER TABLE
COPY 6
ERROR:  multiple primary keys for table "barang" are not allowed
-bash-4.2$

5. check after restore

select * from teguhth.public.pembelian;
select * from edb.public.barang;

 

6. script to backup all database
backup_edb_full_script.sh

 
-bash-4.2$ cat backup_edb_full_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

dbversion=$(psql -Atc "select version();");

infotgl=$(date)
echo ".::: Backup Full All database using custom in '$Server' with IP $ip_address :::."
echo ""
echo "PostgreSQL Version : '$Server' $dbversion"
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
echo "Backup begin $infotgl"
echo ""
 backuppath="/data/edb/edbbackup";

dbname=$(psql -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres');");

for listdb in $dbname
do

mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";
echo "Backup Full for database '$listdb' in $path start at $year$month$day$underscore$hour$min$sec"


 pg_dump $listdb | gzip -c > "$path"/[FULL]_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;
echo "Finish Backup Full for database '$listdb' in $path at $year$month$day$underscore$hour$min$sec"

done;
echo ""
echo "Backup finish $infotgl"
echo ""

echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
##[root@teguhth backup]#

-bash-4.2$