.::: Script Broadcast Query in EDB / PostgreSQL to Multple Database in Linux :::.

1. create database sample
create database db_klt_teguh;
create database db_klt_teguh_dmart;
create database db_klt_teguh_archive;

create database db_bgr_tri;
create database db_bgr_tri_dmart;
create database db_bgr_tri_archive;

create database db_lpg_raju;
create database db_lpg_raju_dmart;
create database db_lpg_raju_archive;

2. check all database
SELECT datname FROM pg_database ;

3. check specify database

SELECT datname FROM pg_database where datname not in('template0','template1','postgres')
and datname NOT LIKE '%dmart' AND datname NOT LIKE '%archive' AND datname LIKE 'db_%\_%';
 

4. run script if table empty
-bash-4.2$ sh script_broadcast_edb.sh insert_pembelian.sql
 

5. log script if table not empty
-bash-4.2$ sh script_broadcast_edb.sh insert_pembelian.sql
 

6. for lab delete table and run again script_broadcast_maria.sh
\c db_klt_teguh
delete from db_klt_teguh.public.pembelian;
\c db_bgr_tri
delete from db_bgr_tri.public.pembelian;
\c db_lpg_raju
delete from db_lpg_raju.public.pembelian;

\c db_klt_teguh
select * from db_klt_teguh.public.pembelian;
\c db_bgr_tri
select * from db_bgr_tri.public.pembelian;
\c db_lpg_raju
select * from db_lpg_raju.public.pembelian;
 
7. script broadcast for postgres

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 ".::: Broardcast Query to All database in '$Server' with IP $ip_address :::."
echo ""
echo "PostgreSQL Version : '$Server' $dbversion"
echo ""
#!/bin/bash
#!/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='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");

# script_broadcast_edb.sh file_name
echo "sintax >>> sh script_broadcast_edb.sh <sql_file>"
SQL_FILE=${1}
echo "sh script_broadcast_edb.sh '$SQL_FILE'"
echo ""
echo "Script will be apply to Multiple Database"
cat $SQL_FILE


#dbname=$(psql -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres') and datname NOT LIKE '%dmart' AND datname NOT LIKE '%archive' AND datname LIKE 'db_%\_%';");
dbname=$(psql -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres');");
for listdb in $dbname
do

echo
 datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
 echo "nama $listdb"
    echo "$datex --->>> Broadcast to database '$listdb' using $SQL_FILE Begin"
    psql "$listdb"  < "$SQL_FILE"
    
    datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
    echo "$datey --->>> Finish Broadcast to database '$listdb' using $SQL_FILE"

done;
 echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""

8. script broadcast for edb

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

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

infotgl=$(date)
echo ".::: Broardcast Query to All database in '$Server' with IP $ip_address :::."
echo ""
echo "PostgreSQL Version : '$Server' $dbversion"
echo ""
#!/bin/bash
#!/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='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");

# script_broadcast_edb.sh file_name
echo "sintax >>> sh script_broadcast_edb.sh <sql_file>"
SQL_FILE=${1}
echo "sh script_broadcast_edb.sh '$SQL_FILE'"
echo ""
echo "Script will be apply to Multiple Database"
cat $SQL_FILE


dbname=$(psql -d postgres -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres') and datname NOT LIKE '%dmart' AND datname NOT LIKE '%archive' AND datname LIKE 'db_%\_%';");
#dbname=$(psql -Atc "SELECT datname FROM pg_database where datname not in('template0','template1','postgres');");
for listdb in $dbname
do

echo
 datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
    echo "$datex --->>> Broadcast to database '$listdb' using $SQL_FILE Begin"
        psql -d "$listdb"  < "$SQL_FILE"

        datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
    echo "$datey --->>> Finish Broadcast to database '$listdb' using $SQL_FILE"

done;
 echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
-bash-4.2$