2021-09-30

PostgreSQL Checking Current config (loaded on RAM)

#!/bin/bash
#/postgres/pgscript/postgres-pg_settings.sh
#LastUpdate: #14:30 2021.08.02
################################################
#su - postgres -c 'psql -h 127.0.0.1 -p 5432'
################################################
echo "===================="
echo "Checking Current config (loaded on RAM):"
su - postgres -c "psql -h 127.0.0.1 -d postgres -c 'select name,setting from pg_settings order by name asc;'"

#THE_END


#RESULT:
root@srv145:/postgres/pgscript# ./postgres-pg_settings.sh
====================
Checking Current config (loaded on RAM):
                  name                  |             setting              
----------------------------------------+----------------------------------
 allow_system_table_mods                | off
 application_name                       | psql
 archive_command                        | (disabled)
 archive_mode                           | off
 archive_timeout                        | 0
 array_nulls                            | on
 authentication_timeout                 | 60
 autovacuum                             | on
 autovacuum_analyze_scale_factor        | 0.1
 autovacuum_analyze_threshold           | 50
 autovacuum_freeze_max_age              | 200000000
 autovacuum_max_workers                 | 3
 autovacuum_multixact_freeze_max_age    | 400000000
 autovacuum_naptime                     | 60
 autovacuum_vacuum_cost_delay           | 20
 autovacuum_vacuum_cost_limit           | -1
 autovacuum_vacuum_scale_factor         | 0.2
 autovacuum_vacuum_threshold            | 50
 autovacuum_work_mem                    | -1
 backend_flush_after                    | 0
 backslash_quote                        | safe_encoding
 bgwriter_delay                         | 200
 bgwriter_flush_after                   | 64
 bgwriter_lru_maxpages                  | 100
 bgwriter_lru_multiplier                | 2
 block_size                             | 8192
 bonjour                                | off
 bonjour_name                           | 
 bytea_output                           | hex
 check_function_bodies                  | on
 checkpoint_completion_target           | 0.9
 checkpoint_flush_after                 | 32
 checkpoint_timeout                     | 300
 checkpoint_warning                     | 30
 client_encoding                        | UTF8
 client_min_messages                    | notice
 cluster_name                           | postgres
 commit_delay                           | 0
 commit_siblings                        | 5
 config_file                            | /postgres/pgdata/postgresql.conf
 constraint_exclusion                   | partition
 cpu_index_tuple_cost                   | 0.005
 cpu_operator_cost                      | 0.0025
 cpu_tuple_cost                         | 0.01
 cursor_tuple_fraction                  | 0.1
 data_checksums                         | on
 data_directory                         | /postgres/pgdata
 data_directory_mode                    | 0700
 data_sync_retry                        | off
 DateStyle                              | ISO, MDY
 db_user_namespace                      | off
 deadlock_timeout                       | 1000
 debug_assertions                       | off
 debug_pretty_print                     | on
 debug_print_parse                      | off
 debug_print_plan                       | off
 debug_print_rewritten                  | off
 default_statistics_target              | 500
 default_tablespace                     | 
root@srv145:/postgres/pgscript# 

PostgreSQL check config file location

#!/bin/bash
#/postgres/pgscript/postgres-pgdata-LOC.sh
#LastUpdate: #11:35 2021.09.18
################################################
#su - postgres -c 'psql -h 127.0.0.1 -p 5432'
################################################

echo "===================="
echo "PostgreSQL [pgdata] location:..."
su - postgres -c "psql -h 127.0.0.1 -d postgres -c 'show data_directory;'"


echo "===================="
echo "PostgreSQL [config file] location:..."
#SELECT name,setting,category FROM pg_settings WHERE category like '%Locations%';
su - postgres -c "psql -h 127.0.0.1 -d postgres -c \"SELECT name,setting,category FROM pg_settings WHERE category like '%Locations%';\""
#THE_END

#RESULT:
root@srv145:/postgres/pgscript# ./postgres-pgdata-LOC.sh
====================
PostgreSQL [pgdata] location:...
  data_directory  
------------------
 /postgres/pgdata
(1 row)

====================
PostgreSQL [config file] location:...
       name        |             setting              |    category    
-------------------+----------------------------------+----------------
 config_file       | /postgres/pgdata/postgresql.conf | File Locations
 data_directory    | /postgres/pgdata                 | File Locations
 external_pid_file |                                  | File Locations
 hba_file          | /postgres/pgdata/pg_hba.conf     | File Locations
 ident_file        | /postgres/pgdata/pg_ident.conf   | File Locations
(5 rows)

How to Run PostgreSQL Query Using Bash Script and Command-Line

#!/bin/bash
#/opt/script/postgres-query-from-shell.sh
#LastUpdate: #11:54 2021.08.02
#----------------------------------------
#su - postgres -c 'psql -h 127.0.0.1'
#su -c "psql -d database_name -c \"SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'\"" postgres
su - postgres -c "psql -h 127.0.0.1 -d dvdrental -c 'select film_id,title,description from film order by film_id asc limit 5'"



#!/bin/bash
#/opt/script/postgres-query-from-shell-update-load-test.sh
#LastUpdate: #14:13 2021.08.02
#----------------------------------------
#su - postgres -c "psql -h 127.0.0.1 -d dvdrental -c 'select film_id,title,description from film order by film_id asc limit 5'"
#update film set title='Ace Goldfinger-201' where film_id=2
for var_temp in {1..1000}
do
   echo "[DATE: $(date +'%H:%M:%S_%6N')]--------------VALUE: $var_temp"
   su - postgres -c "psql -h 127.0.0.1 -d dvdrental -c \"update film set title='Ace Goldfinger-$var_temp' where film_id=2\""
done




#REF:
for var01 in {1..1000}
do
   echo "Welcome $var01 times"
done

DO
$do$
BEGIN 
    FOR i IN 1..25 LOOP
        -- COMMAND
    END LOOP;
END
$do$;

How to Run MySQL/MariaDB Query Using Bash Script and Command-Line

#!/bin/bash
#/opt/lampp/script/mysql-stats-AbortedConnection.sh
#LastUpdate: #16:09 2021.09.30
#####################################
source /opt/lampp/LAMPP_VERSION.txt
#####################################
#/opt/lampp/bin/mysql
MYSQL_BIN=$LAMPP_HOME/bin/mysql
#$MYSQL_BIN -u root -p'xxx' -hlocalhost < mysql-AbortedConnection.sql

MySQL_Username="root"
MySQL_Password="xxx"
MySQL_DB="mysql"

#__________[SCRIPT]:BEGIN
cat >temp.sql <<'SQL_STATEMENTS'

use pureftpd;
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
show variables like "Wait_timeout";
show variables like "connect_timeout";

SQL_STATEMENTS
$MYSQL_BIN -u $MySQL_Username -p$MySQL_Password $MySQL_DB -e "SOURCE temp.sql"

\rm -f temp.sql
#__________[SCRIPT]:END

#THE_END

#RESULT:
# +------------------+-------+
# | Variable_name    | Value |
# +------------------+-------+
# | Aborted_connects | 1     |
# +------------------+-------+
# +---------------+-------+
# | Variable_name | Value |
# +---------------+-------+
# | wait_timeout  | 60    |
# +---------------+-------+
# +-----------------+-------+
# | Variable_name   | Value |
# +-----------------+-------+
# | connect_timeout | 10    |
# +-----------------+-------+

#REF: #https://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script/8055745
#https://www.nitendratech.com/database/sql-query-bash-script/

2021-09-29

Docker Remove Compeletely

#!/bin/bash
#/opt/script/docker-remove-compeletely.sh
#LastUpdate: #15:13 2021.09.29
#############################################
echo "#############################################"
echo "STOP ALL RUNNING CONTAINER:"
echo "docker container stop $(docker container ls -aq)"
sleep 5
docker container stop $(docker container ls -aq)


echo "#############################################"
echo "DELETE ALL CONTAINER:"
echo "docker container rm $(docker container ls -aq)"
sleep 5
docker container rm $(docker container ls -aq)

echo "#############################################"
echo "DELETE ALL IMAGES:"
echo "docker image prune -a"
sleep 5
docker image prune -a


echo "#############################################"
echo "DELETE ALL DOCKER DATA ON SERVER:"
sleep 5

dpkg -l | grep -i docker
sudo apt-get purge -y docker-engine docker docker.io docker-ce docker-ce-cli
sudo apt-get autoremove -y --purge docker-engine docker docker.io docker-ce

sudo apt-get purge -y docker-ce docker-ce-cli containerd.io
sudo apt-get remove docker docker-engine docker.io containerd runc

sudo \rm -rf /var/lib/docker
sudo \rm -rf /var/lib/containerd

find / -name "*docker*" -exec \rm -rf {} \;


echo "#############################################"
echo "DELETE DOCKER NETWORK INTERFACE:"
echo "ip link delete docker0"
sleep 5
ip link delete docker0


echo "#############################################"
echo "CHECKING EXISTED DOCKER on SERVER:"
echo "find / -name docker"
echo "find / -name docker.io"
sleep 5


#THE_END

#REF:
##https://askubuntu.com/questions/935569/how-to-completely-uninstall-docker

2021-08-10

LOAD BALANCING MICROSOFT SQL SERVER WITH HAPROXY

LOAD BALANCING MICROSOFT SQL SERVER WITH HAPROXY

In case you are told it isn’t possible, I can confirm that it is in fact possible to load balance requests to databases using HAProxy. Here are the specifics.

First, the databases in question are SQL 2012 Web Edition databases. They get populated by replication from a SQL 2012 Standard Edition publisher. Reads are sent to these replicated databases, but all writes go to the master database.

Here is the entire HAProxy config for a basic database load balancing listener, taking connections from a list of approved addresses and distributing them to three servers, depending on whether they appear to be up.

listen sql-db
    bind *:1433
    mode tcp
    balance leastconn
    acl db_white_list src 0.0.0.0 1.1.1.1 2.2.2.2 3.3.3.3
    tcp-request connection reject if !db_white_list
    option log-health-checks
    server DB-1 4.4.4.1:1433 check port 1433 inter 1000
    server DB-2 4.4.4.2:1433 check port 1433 inter 1000
    server DB-3 4.4.4.3:1433 check port 1433 inter 1000

You can get more advanced here – you could perform more advanced checks using SQL to make sure you have more than just a connection to a port – but you get the idea.

What does this cost?

Unlike HTTP load balancing, you will actually start to see CPU usage on your HAProxy server. You will also see a big jump in network traffic, as web pages are usually smaller than the data you might query to generate them. You’ll need to make sure this isn’t a major problem. You’ll also see a little latency as it will take slightly longer to get your data as there is something else in the middle.

2021-07-22

MONITOR ROUTE TABLE IN LINUX

MONITOR ROUTE TABLE IN LINUX

#!/bin/bash
#!/bin/sh -e
#/opt/script/routing-monitor-stats.sh
##############################################
#LastUpdate: 2021.07.23 8:28:04
##############################################
# Check ROUTING status every minute:
# * * * * * /opt/script/routing-monitor-stats.sh >> /opt/log/routing-monitor-stats/routing-monitor-stats.log 2>&1
##############################################
now1="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
##############################################
COMMAND1="ip route"
SUBNET1="192.168.202"
##############################################
mkdir -p /opt/log/routing-monitor-stats/
cd /opt/log/routing-monitor-stats/

VAR1=$($COMMAND1 | grep "$SUBNET1" | awk '{print $1}');
if [ -z "$VAR1" ]
then
      /opt/script/route-2-dbzone.sh
      echo "[$now1]: Subnet [$SUBNET1"".0/24] is MISSING";
else      
      echo "[$now1]: Subnet [$SUBNET1"".0/24] is RUNNING";
fi

#truncate file, keep 7days log:
FILE_NAME="/opt/log/routing-monitor-stats/routing-monitor-stats.log"
tail -10080 $FILE_NAME > $FILE_NAME.temp && cat $FILE_NAME.temp > $FILE_NAME
#THE_END