.::: Test Pgpool-II Monitor PostgreSQL Routing to Master or Slave replication streaming Using Lucee & PHP :::.

 

1. PostgreSQL Connection

chmod 755 /opt/lucee/bin/pgpool_connection.sh
chmod 755 /opt/lucee/bin/pgpool_nodes.sh
chmod 755 /opt/lucee/bin/pgpool_replication.sh

[root@teguhth-all ROOT]# cat /opt/lucee/bin/pgpool_connection.sh
#!/bin/bash

export PGPASSWORD='admin'

/usr/bin/psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port(),'PostgreSQL ' || current_setting('server_version') as edbversion;"
[root@teguhth-all ROOT]#
 

.::: 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]#