.::: Install & Configure PgBouncer in Centos 9 Almalinux 9 for PostgreSQL 18 :::.

 

1. Install PgBouncer

dnf install -y pgbouncer

rpm -qa | grep pgbouncer

[root@teguhth-all ~]# rpm -qa | grep pgbouncer
pgbouncer-1.25.2-43PGDG.rhel9.8.x86_64
[root@teguhth-all ~]# pgbouncer --version
PgBouncer 1.25.2
libevent 2.1.12-stable
adns: c-ares 1.19.1
tls: OpenSSL 3.5.5 27 Jan 2026
systemd: yes
[root@teguhth-all ~]#

2. Backup konfigurasi

cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/back_pgbouncer.ini
 

.::: Install Pgpool-II for load balancer PostgreSQL EDB like Maxscale for MariaDB :::.

 

1. lab

ip master 10.10.10.31
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Master

ip slave 10.10.10.33
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Slave 1

ip slave 10.10.10.33
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Slave 2
 

.::: Store Procedure backup database & database dmart or other in MSSQL SQL Server 2025 :::.

  

1. check store procedure

SELECT 
    db_name() as dbname,name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM 
    sys.procedures
ORDER BY 
    name;
    
-- or 

SELECT 
    db_name() as dbname,name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM 
    sys.procedures where name='sp_backupdev' 
ORDER BY 
    name;
 

.::: Convert pgaudit & pgauditlogtofile log insert into table pgauditfile in dbatools PostgreSQL 18 EDB :::.

 
correlation https://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html

1. config postgresql.conf & restart systemctl restart postgresql-18 

listen_addresses='*'
shared_preload_libraries = 'pgaudit,pgauditlogtofile'

pgaudit.log_exclude = 'pg_catalog,information_schema'
pgaudit.log_directory = '/var/lib/pgsql/18/data/log'
pgaudit.log_filename = 'pgaudit-teguhth.log'

pgaudit.log = 'write,ddl,role'
#pgaudit.log = 'all'
pgaudit.log_catalog = off
pgaudit.log_relation = on
pgaudit.log_statement = on
pgaudit.log_parameter = off
pgaudit.log_rows = off
pgaudit.log_execution_time = off
pgaudit.log_execution_memory = off
# pgaudit.log_format = csv
 

.::: Testing & Check Primary key & autoincremental status in MariaDB :::.

 

https://teguhth.blogspot.com/2024/10/sample-database-erp-enterprise-resource.html

1. sample table customers 

show create table customers\G
 

2. check table customers 

select * from customers;
 

.::: Check xml_deadlock_report offline & online :::.

 

correlation deadlock 

https://teguhth.blogspot.com/2026/03/simalation-locking-blocking-deadlock-in.html
https://teguhth.blogspot.com/2021/09/sql-server-how-to-simulate-and-catch.html

1. check targent data  event_file

SELECT create_time,target_name,name,session_source,
    CAST(xt.target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets xt
JOIN sys.dm_xe_sessions xs
ON xs.address = xt.event_session_address
WHERE xs.name='system_health'
AND xt.target_name='event_file';

output 

<EventFileTarget truncated="0">
  <Buffers logged="4" dropped="0" />
  <File name="C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\system_health_0_134303098936130000.xel" />
</EventFileTarget>
 

 

.::: Test MariaDB MaxScale Routing Hint Routing to Master or Slave Using Lucee & PHP :::.

 

 
correlation https://teguhth.blogspot.com/2025/01/test-insert-select-data-using-maxscale.html

1. info server testing 
 

2. diagram
 

 

.::: Upgrading from MariaDB 11.8 to MariaDB 12.3 :::.

 

https://teguhth.blogspot.com/2023/06/upgrading-from-mariadb-104-to-mariadb.html

1. check Repo for prepare maria 12x
[root@teguhth-all /]# cat /etc/yum.repos.d/mariadb.repo

[mariadb-main]
name = MariaDB Server
#baseurl = https://dlm.mariadb.com/repo/mariadb-server/11/yum/rhel/9/x86_64
baseurl = https://dlm.mariadb.com/repo/mariadb-server/12.rolling/yum/rhel/9/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1
module_hotfixes = 1

[mariadb-maxscale]
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
name = MariaDB MaxScale
baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/9/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
gpgcheck = 1
enabled = 1

[root@teguhth-all /]#
 

.::: Script Backup User MariaDB Include Privileges :::.

 

1. Script backup All user mariadb
 

.::: Upgrade PostgreSQL from 16 to 18 on Almalinux 9 :::.

 

 

correlation https://teguhth.blogspot.com/2023/10/upgrade-postgresql-from-15-to-16-on.html

 1. check version before upgrade
SELECT version();
select pg_read_file('/etc/hostname') as hostname, version();
 

2. backup all

su - postgres -c "pg_dumpall -p 5432 > /var/lib/pgsql/backupupgrade/all_16postgresbeforeupgrade.sql"

3. remove old repository & install repository, install postgres16
detail http://teguhth.blogspot.com/2021/07/install-postgresql-linux-using.html

rpm -qa | grep -i pgdg
yum remove pgdg-redhat-repo-42.0-66.rhel9.8PGDG.noarch -y
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum clean all
yum -y install postgresql18-server
 

 

.::: How to Simulation Locking with 'Waiting for table metadata lock' MariaDB MySQL :::.

 

correlation https://teguhth.blogspot.com/2026/07/how-to-simulation-blockinglock-locking.html

1. terminal 1 (user admin)

USE teguhth;

START TRANSACTION;
select connection_id() as session_id, user() as user;
SELECT * FROM pembelian WHERE KODE_PEMBELIAN='BEL-E001';

atau 
 

.::: Check ETA (Estimated Time of Arrival) restore status MariaDB MarinaDB on Persent (%) :::.

 

correlation https://teguhth.blogspot.com/2026/06/check-progress-restore-status-mariadb.html


1. check sourcedata full database 

du -s /var/lib/mysql/teguhth
du -sh /var/lib/mysql/teguhth

[root@teguhth-all eta]# du -s /var/lib/mysql/teguhth
570752  /var/lib/mysql/teguhth
[root@teguhth-all eta]#

[root@teguhth-all eta]# du -sh /var/lib/mysql/teguhth
558M    /var/lib/mysql/teguhth
[root@teguhth-all eta]#
 

.::: Sample Access Database MariaDB via Frontend & Backend using Lucee with access login Application.cfc, users save in table users, insert, update, delete with confirm :::.

 

 
hanya berubah pembelian_all.cfm n lanjutan dari https://teguhth.blogspot.com/2026/07/sample-access-database-mariadb-via_01669209479.html

1. create user for login

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    nama VARCHAR(100),
    aktif CHAR(1) DEFAULT 'Y'
);

INSERT INTO users(username,password,nama,aktif)
VALUES
('admin','123456','Administrator','Y');

INSERT INTO users(username,password,nama,aktif)
VALUES
('aisyah','123456','Administrator','Y');

INSERT INTO users(username,password,nama,aktif)
VALUES
('teguh','123456','readonly','Y');
 

.::: Sample Access Database MariaDB via Frontend & Backend using Lucee with accecc login Application.cfc, users save in table users :::

 

 

correlation https://teguhth.blogspot.com/2026/07/sample-access-database-mariadb-via.html

1. create user for login

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    nama VARCHAR(100),
    aktif CHAR(1) DEFAULT 'Y'
);

INSERT INTO users(username,password,nama,aktif)
VALUES
('admin','123456','Administrator','Y');

INSERT INTO users(username,password,nama,aktif)
VALUES
('aisyah','123456','Administrator','Y');

INSERT INTO users(username,password,nama,aktif)
VALUES
('teguh','123456','readonly','Y');
 

.::: How to Simulation Blocking,Lock, Locking Identifying and Avoiding Deadlocks MariaDB MySQL & Advanced PROCESSLIST Update Juli 2026 :::.

 

correlation 

https://teguhth.blogspot.com/2023/09/how-to-sample-simulation-blocking.html

1. SIMULASI LOCKING

Session 1 - root - session_id 3

select connection_id() as session_id, user() as user;
START TRANSACTION;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 10 
WHERE KODE_PEMBELIAN = 'BEL-E001';

select connection_id() as session_id, user() as user; START TRANSACTION; UPDATE pembelian SET JUMLAH_PEMBELIAN = 10 WHERE KODE_PEMBELIAN = 'BEL-E001';
 

 

.::: Sample Access Database MariaDB via Frontend & Backend using Lucee with accecc login Application.cfc :::.

  

https://teguhth.blogspot.com/2026/02/sample-access-database-mariadb-via.html

Kalau tujuan Anda adalah setiap kali membuka:

http://10.10.10.90:8888/pembelian_front.cfm

maka pengguna harus login terlebih dahulu, saya sarankan menggunakan Session Login. Cara ini lebih sederhana dibanding CFLOGIN dan umum dipakai di Lu

1. Struktur file

root@teguhth-all ROOT]# pwd
/opt/lucee/tomcat/webapps/ROOT
[root@teguhth-all ROOT]#
[root@teguhth-all ROOT]# ls -lh Application.cfc login.cfm logout.cfm pembelian_front.cfm pembelian_api.cfm
-rw-r--r-- 1 root root  744 Jul 10 15:06 Application.cfc
-rw-r--r-- 1 root root 1.1K Jul 10 14:57 login.cfm
-rw-r--r-- 1 root root   76 Jul 10 14:57 logout.cfm
-rw-r--r-- 1 root root  296 Jul 10 15:07 pembelian_api.cfm
-rw-r--r-- 1 root root  912 Jul 10 15:09 pembelian_front.cfm
[root@teguhth-all ROOT]#
 

.::: SOP Restore table production without lost data in MariaDB :::.

 

1. server

source 
ip         : 10.10.10.90 
db         : teguhthdb
table    : pembelian 

destination
ip         : 10.10.10.90 
db         : teguhth
table    : pembelian 
 
 

.::: Check progress restore and backup status MariaDB MarinaDB :::.

  

1. check folder size

watch du -s /var/lib/mysql/teguhth
 

 

.::: Check dbsize MariaDB using ls -h OS, with & exclude table :::.

 

correlation https://teguhth.blogspot.com/2025/12/script-check-size-database-base-on-size.html

1. check dbsize full 

sh cek_teguhth.sh

[root@teguhth-all ceksizemaria]# cat cek_teguhth.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql/teguhth"

echo "Database Directory Size Report"
echo "=============================="
printf "%-25s %10s\n" "FOLDER" "SIZE"
echo "------------------------------"

du -sh ${BASE_DIR}/* 2>/dev/null | sort -h | while read size folder; do
    printf "%-25s %10s\n" "$(basename "$folder")" "$size"
done

echo "------------------------------"
echo "TOTAL:"
du -sh "$BASE_DIR" | awk '{print $1}'
[root@teguhth-all ceksizemaria]#
 

.::: Script check IP NAS Mounting in fstab for trouble shooting :::.

 

1. check df -h 
 

2. cek simple 

[root@teguhth8 cekfstab]# more cek_mounting_simple.sh
for ip in $(grep -v '^#' /etc/fstab | awk '$3=="nfs"{print $1}' | cut -d: -f1 | sort -u)
do
    ping -c 1 -W 1 $ip >/dev/null
    if [ $? -ne 0 ]; then
        echo "DOWN: $ip"
    fi
done
[root@teguhth8 cekfstab]#
 

3. check all ip 

[root@teguhth8 cekfstab]# cat cek_mounting_allip.sh
#!/bin/bash

for ip in $(grep -v '^#' /etc/fstab | awk '$3=="nfs"{print $1}' | cut -d: -f1 | sort -u)
do
    if ping -c 1 -W 1 $ip >/dev/null 2>&1
    then
        echo "UP   : $ip"
    else
        echo "DOWN : $ip"
    fi
done
[root@teguhth8 cekfstab]#

.::: bat script to simulate pararel query thread or Injector CPU in MSSQL SQL Server :::.

 

1. create script cpu_injector_mssql.bat  

@echo off
setlocal enabledelayedexpansion

REM ===== CONFIG DATABASE =====
set DB_HOST=10.10.10.7
set DB_PORT=1433
set DB_USER=sa
set DB_PASS=P@ssw0rd
set DB_NAME=teguhth

REM ===== QUERY =====
set QUERY=SELECT 'CPU Injector by Teguh Triharto',WAITFOR DELAY '00:00:00.001';
 

.::: Install Maxscale 25 Trial with license license_key (maxscale: The mandatory parameter 'license_key' is not provided) :::.

 

1. Downlaod Maxscale & license 

https://mariadb.com/downloads/community/maxscale-trial/ 
https://customers.mariadb.com/license/maxscale-trial/

2. Install 
tar -xvf maxscale-25.10.2-trial-release.rocky.9.x86_64.tar

yum install maxscale-common-25.10.2-1.rhel.9.x86_64.rpm -y
yum install maxscale-maxctrl-25.10.2-1.rhel.9.x86_64.rpm -y
yum install maxscale-trial-25.10.2-1.rhel.9.x86_64.rpm -y

3. Try start & enable automatic

[root@teguhth-ha03 data]# systemctl status maxscale.service
× maxscale.service - MariaDB MaxScale Database Proxy
     Loaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; preset: disabled)
     Active: failed (Result: exit-code) since Wed 2026-04-22 15:28:47 WIB; 2min 23s ago
    Process: 13163 ExecStart=/usr/bin/maxscale (code=exited, status=1/FAILURE)
        CPU: 72ms
 

.::: Command remote access mongodb using mongosh, authenticationMechanism SCRAM-SHA-256, authenticationMechanism SCRAM-SHA-1 :::

 .

https://teguhth.blogspot.com/2024/04/backup-restore-database-mongodb-using.html

1. console admin 

mongosh -u admin -p admin --authenticationDatabase admin

[root@teguhth-all ~]# mongosh -u admin -p admin --authenticationDatabase admin
Current Mongosh Log ID: 69f05c29e551710562d805da
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&authSource=admin&appName=mongosh+2.8.1
Using MongoDB:          8.0.20
Using Mongosh:          2.8.1
mongosh 2.8.2 is available for download: https://www.mongodb.com/try/download/shell

For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/

------
   The server generated these startup warnings when booting
   2026-04-28T14:01:22.306+07:00: For customers running the current memory allocator, we suggest changing the contents of the following sysfsFile
   2026-04-28T14:01:22.306+07:00: We suggest setting the contents of sysfsFile to 0.
   2026-04-28T14:01:22.306+07:00: We suggest setting swappiness to 0 or 1, as swapping can cause performance problems.
------

test>
 

.::: Read Json file using OPENJSON, OPENROWSET in SQL Server :::.

 

1. copy json sample to disk C:\bcp\teguhth.pembelian.json
 

 

2. only read json 
 

.::: Export MSSQL SQL Server to csv using OPENROWSET :::.

 

https://teguhth.blogspot.com/2025/12/backup-restore-table-sql-server-using.html

https://teguhth.blogspot.com/2026/04/sample-lab-using-openrowset-in.html

1. create excel dummy with header save as "openrowsori.xlsx"
 

2. replace openrows.xlsx (

copy openrowsori.xlsx openrows.xlsx

Karena Excel (via Microsoft ACE OLEDB):

tidak support DELETE
tidak support TRUNCATE
tidak support OVERWRITE

.::: Command Check top service, Top Memory in Linux (Centos 9, Rhel 9, Almalinux 9 include grouping & size memory & count proccess ) Top Process

 

1. total Service group 

ps -eo comm | sort | uniq -c | sort -nr | head

[root@teguhth ~]# ps -eo comm | sort | uniq -c | sort -nr | head
     13 sshd
     10 postgres
      8 nfsd
      5 metricbeat
      4 kworker/R-xfs-c
      4 kworker/R-xfs-b
      4 bash
      3 systemd
      3 filebeat
      2 sort
[root@teguhth ~]#
 

.::: Shell Script Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB, MySQL, MarinaDB :::.

 

corelation https://teguhth.blogspot.com/2026/04/create-store-procedure-sync-manual_10.html
https://teguhth.blogspot.com/2026/04/sync-manual-table-add-column-alter_10.html

1. script 

run_sp_compare_table.sh
run_sp_compare_add_column_table.sh
run_sp_compare_alter_column_table.sh

run_sp_compare_table_csv.sh
run_sp_compare_add_column_table_csv.sh
run_sp_compare_alter_column_table_csv.sh

2. run

sh run_sp_compare_table.sh teguhth aisyah
sh run_sp_compare_add_column_table.sh teguhth aisyah
sh run_sp_compare_alter_column_table.sh teguhth aisyah

sh run_sp_compare_table_csv.sh teguhth aisyah
sh run_sp_compare_add_column_table_csv.sh teguhth aisyah
sh run_sp_compare_alter_column_table_csv.sh teguhth aisyah
 

.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB MarinaDB MySQL :::.

 

correlation https://teguhth.blogspot.com/2026/04/sync-manual-table-add-column-alter_10.html

1. CREATE TABLE (yang belum ada di aisyah)

DELIMITER $$

CREATE PROCEDURE sp_compare_table(
    IN p_source_db VARCHAR(100),
    IN p_target_db VARCHAR(100)
)
BEGIN
    SET SESSION group_concat_max_len = 1000000;

    SET @sql = CONCAT(
    'SELECT 
        CONCAT(
            ''CREATE TABLE ', p_target_db, '.'' , t.table_name, '' ( \n'',
            GROUP_CONCAT(
                CONCAT(
                    ''  '', c.column_name, '' '',
                    c.column_type,
                    IF(c.is_nullable = ''YES'', '' NULL'', '' NOT NULL'')
                )
                ORDER BY c.ordinal_position
                SEPARATOR '',\n''
            ),
            ''\n);''
        ) AS create_table_script
    FROM information_schema.tables t
    JOIN information_schema.columns c 
        ON t.table_schema = c.table_schema
        AND t.table_name = c.table_name
    WHERE t.table_schema = ''', p_source_db, '''
    AND t.table_name NOT IN (
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = ''', p_target_db, '''
    )
    GROUP BY t.table_name'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;
 

.::: Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in MariaDB, MySQL, MarinaDB :::.

 

correlation https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. CREATE TABLE (yang belum ada di aisyah)

SELECT 
CONCAT(
    'CREATE TABLE aisyah.', t.table_name, ' ( \n',
    GROUP_CONCAT(
        CONCAT(
            '  ', c.column_name, ' ',
            c.column_type,
            IF(c.is_nullable = 'YES', ' NULL', ' NOT NULL')
        )
        ORDER BY c.ordinal_position
        SEPARATOR ',\n'
    ),
    '\n);'
) AS create_table_script
FROM information_schema.tables t
JOIN information_schema.columns c 
    ON t.table_schema = c.table_schema
    AND t.table_name = c.table_name
WHERE t.table_schema = 'teguhth'
AND t.table_name NOT IN (
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'aisyah'
)
GROUP BY t.table_name;
 

.::: Create Store Procedure Sync Manual Table, Add Column, Alter Column Compare Database (teguhth,aisyah) in SQL Server :::.

 

correlation https://teguhth.blogspot.com/2026/04/sync-manual-table-add-column-alter.html


1. CREATE TABLE (yang belum ada di aisyah)

CREATE PROCEDURE sp_compare_table
    @source_db SYSNAME,
    @target_db SYSNAME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX);
 

.::: Sample lab Using OPENROWSET in Microsoft SQL Server 2025 (read excel csv from sql server) :::.

 

A. prerequirement

1. prerequirement 
Access Database Engine 2016 (x64)

https://www.microsoft.com/en-us/download/details.aspx?id=54920

2. enable access service 
 

3. AKTIFKAN OPENROWSET

Di Microsoft SQL Server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;