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)

[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

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

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/

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

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.

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

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!

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#