2021-11-17

PostgreSQL check all DB SIZE



#CHECK DB SIZE:

postgres=# select t1.datname AS db_name,pg_size_pretty(pg_database_size(t1.datname)) as db_size from pg_database t1 order by pg_database_size(t1.datname) desc;

        db_name         | db_size 
------------------------+---------
 xxx                    | 8109 MB
 xxx_20210622_14h05     | 7520 MB
 xxx_500001_1000000     | 1052 MB
 xxx_000001_500000      | 566 MB
 postgres               | 11 MB
 beta_xxxchat           | 8461 kB
 template1              | 7605 kB
 template0              | 7433 kB
(8 rows)

2021-11-08

[Solved] Percona XtraBackup for MariaDB/MySQL. Binary [HotBackup / HotRestore]

Steps:

A/ FULL BACKUP a DATABASE
A.1/ Backup Full Single Database
A.2/ Backup Schema of Single Database


B/ FULL RESTORE a DATABASE
B.1/ Create new empty Database <New DBName>
B.2/ Import Schema (A.2) to new Empty Database
B.3/ Discard Tablespace
B.4/ Copy all file of Backuped Database (A.1) to new Database
B.5/ 
chown -R mysql.mysql /opt/mariadb/data/<New DBName>
B.6/ Import Tablespace for all new Table
B.7/ DONE

P.S:
1/ Backup full a MariaDB/MySQL Database in Binary mode
2/ Backup full all 
MariaDB/MySQL Database in Binary mode
3/ Backup Incremental a or all database in Binary mode

4/ Restore full a or all 
database in Binary mode

For detail script, contact to me



2021-10-22

Upgrade "Ubuntu 16.04.LTS.x64" to "Ubuntu 18.04.LTS.x64"

#A/ Upgrade "Ubuntu 16.04.LTS.x64" to "Ubuntu 18.04.LTS.x64"
sudo apt-get update -y && sudo apt-get upgrade -y && sudo apt-get dist-upgrade -y && sudo do-release-upgrade


#B/Upgrade Ubuntu 16.04 -> 18.04, Fix lỗi ERROR Curl_Open_SSL cho PHP:
sudo apt-get -y remove --auto-remove libcurl4-openssl-dev && sudo apt-get -y install libcurl3


#C/ Fixed "LetsEncrypt Error":
sudo apt-get -y install libgnutls-openssl27 libgnutls30 ubuntu-advantage-tools


#D/REBOOT OS


#E/ Result: DONE




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-11

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-23

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

2021-07-18

Windows Subsystem for Linux Installation Guide for Windows 10/11

#Windows Subsystem for Linux Installation Guide for Windows 10/11:
#REF: https://docs.microsoft.com/en-us/windows/wsl/install-win10

# Manual Installation Steps
# Step 1 - Enable the Windows Subsystem for Linux
# You must first enable the "Windows Subsystem for Linux" optional feature before installing any Linux distributions on Windows.
# Open PowerShell as Administrator and run:
# PowerShell
dism.exe /online /enable-feature /featurename:Microsoft-Windows-Subsystem-Linux /all /norestart

# Step 2 - Check requirements for running WSL 2
# To update to WSL 2, you must be running Windows 10.

# Step 3 - Enable Virtual Machine feature
# Before installing WSL 2, you must enable the Virtual Machine Platform optional feature. Your machine will require virtualization capabilities to use this feature.
# Open PowerShell as Administrator and run:
# PowerShell
dism.exe /online /enable-feature /featurename:VirtualMachinePlatform /all /norestart
#Restart your machine to complete the WSL install and update to WSL 2.

# Step 4 - Download the Linux kernel update package
# Download the latest package:

# Step 5 - Set WSL 2 as your default version
# Open PowerShell and run this command to set WSL 2 as the default version when installing a new Linux distribution:
# PowerShell
wsl --set-default-version 2

# Step 6 - Install your Linux distribution of choice
# Open the Microsoft Store and select your favorite Linux distribution.
wsl --list --verbose

#9:36 2021.07.18
#REF: https://github.com/microsoft/WSL/issues/5393
If someone have trouble in Windows 11
you should:
wsl --update
wsl --shutdown

#https : //wslstorestorage.blob.core.windows.net/wslblob/wsl_update_x64.msi

Install WSL on Windows 10 | Microsoft Docs


CONGRATULATIONS! You've successfully installed and set up a Linux distribution that is completely integrated with your Windows operating system!

2021-06-18

Get file content without comment

#Select all content, paste it into linux shell
#__________[CONFIG]:BEGIN
FILE_NAME="/opt/script/file-uncommented-line.sh"
now1="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
chattr -i -f $FILE_NAME
cp -v $FILE_NAME $FILE_NAME-[$now1].bk

cat <<EOF > $FILE_NAME
#!/bin/bash
#!/bin/sh -e
###################################
#FILE_NAME: $FILE_NAME
#Author: qwerty
#LastUpdate: #$now1
###################################
#Usage: file-uncommented-line.sh <filename>
###################################CONTENT:BEGIN
now1="\$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
TEMP_LOC="/opt/temp"; mkdir -p \$TEMP_LOC

FileWithFullPath="\$1"
FileNameOnly="\${FileWithFullPath##*/}"
FileName_WithoutExtension="\${FileNameOnly%.*}"
FileExtensionOnly="\${FileNameOnly##*.}"

echo "FileWithFullPath         : \$FileWithFullPath"
echo "FileNameOnly             : \$FileNameOnly"
echo "FileName_WithoutExtension: \$FileName_WithoutExtension"
echo "FileExtensionOnly        : \$FileExtensionOnly"

echo "=========================="
echo "REMOVE HASH COMMENT: DONE"
grep -v '^ *#' \$FileWithFullPath > \$TEMP_LOC/\$FileName_WithoutExtension"_RemovedHash".\$FileExtensionOnly
sleep 2


echo "=========================="
echo "REMOVE EMPTY LINES: DONE"
sed '/^\$/d' \$TEMP_LOC/\$FileName_WithoutExtension"_RemovedHash".\$FileExtensionOnly > \$TEMP_LOC/\$FileName_WithoutExtension"_RemovedEmpyLines".\$FileExtensionOnly
sleep 2


echo "=========================="
echo "RESULT: "
ls -l \$TEMP_LOC/\$FileName_WithoutExtension*
###################################CONTENT:END
#THE-END
EOF

cat $FILE_NAME
chmod +x $FILE_NAME
chattr +i -f $FILE_NAME
echo "FILE_NAME: $FILE_NAME"; echo ""
#__________[CONFIG]:END



#RESULT:
root@host /opt/temp# file-uncommented-line.sh /etc/MailScanner/MailScanner.conf 
FileWithFullPath         : /etc/MailScanner/MailScanner.conf
FileNameOnly             : MailScanner.conf
FileName_WithoutExtension: MailScanner
FileExtensionOnly        : conf
==========================
REMOVE HASH COMMENT: DONE
==========================
REMOVE EMPTY LINES: DONE
==========================
RESULT: 
-rw-r--r--. 1 root root 14609 Jun 18 14:22 /opt/temp/MailScanner_RemovedEmpyLines.conf
-rw-r--r--. 1 root root 14983 Jun 18 14:22 /opt/temp/MailScanner_RemovedHash.conf
root@host /opt/temp#