.::: Bat Script sftp windows to Server Linux with Scheduler & password :::.

  

 
1. Just sftp
 

 @echo off
setlocal

set SFTP_SERVER=10.10.10.90
set SFTP_USER=ftpaccess
set SFTP_PASSWORD=xxx

set REMOTE_DIR=/media/backup
set LOCAL_DIR=D:\temp

set SFTP_SCRIPT=D:\other\windows\sftpbat\sftp_download.txt

echo ========================================
echo SFTP DOWNLOAD ALL FILES
echo ========================================
echo Remote : %REMOTE_DIR%
echo Server : %SFTP_SERVER%
echo Local  : %LOCAL_DIR%
echo ========================================

if not exist "%LOCAL_DIR%" (
    mkdir "%LOCAL_DIR%"
)
 

.::: Simulation Blocking,lock, Locking in PostgreSQL EDB with Advanced process list Pg-Activity ,pg Lock like 'Waiting for table metadata lock' MariaDB MySQL :::.

 

correlation metadata mariadb https://teguhth.blogspot.com/2026/07/how-to-simulation-locking-with-waiting.html
correlation https://teguhth.blogspot.com/2023/10/how-to-sample-simulation-blocking-in.html

1. terminal 1

BEGIN;

SELECT
    pg_backend_pid() AS session_id,
    current_user AS user;

UPDATE pembelian
SET jumlah_pembelian = 10
WHERE kode_pembelian = 'BEL-E001';

-- JANGAN COMMIT
-- biarkan transaksi terbuka

BEGIN;
SELECT pg_backend_pid() AS session_id,current_user AS user;
UPDATE pembelian SET jumlah_pembelian = 10 WHERE kode_pembelian = 'BEL-E001';
 

.::: PostgreSQL 18 Access via Pgpool-II – SCRAM Authentication with AES-Encrypted fix psql: error: connection to server at "10.10.10.0", port 9999 failed: ERROR: failed to authenticate with backend using SCRAM :::.

  

1. login as root 

echo 'rahasia' > /root/.pgpoolkey
chmod 600 /root/.pgpoolkey

 

cp /root/.pgpoolkey /var/lib/pgsql/.pgpoolkey
chown postgres:postgres /var/lib/pgsql/.pgpoolkey
chmod 600 /var/lib/pgsql/.pgpoolkey 
 

2. create sample user n password

create user adminaes ;
ALTER ROLE adminaes SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE adminaes PASSWORD 'adminaes';
ALTER USER adminaes WITH PASSWORD 'adminaes';
 

.::: Check ETA (Estimated Time of Arrival) restore status All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera :::.

 

 
correlation with https://teguhth.blogspot.com/2026/07/check-eta-estimated-time-of-arrival.html

1. check dbsize all maria (only folder maria)

du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'

[root@teguhth-all ~]# du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
3.7G
[root@teguhth-all ~]# du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
3878576
[root@teguhth-all ~]#
 

2. recommendation drop all database no system

https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html 
 

.::: Testing Routing Balancing PostgreSQL EDB using PGPool-II Write & Read :::.

 

A. queri normal select normal

SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,
    inet_server_addr() AS ip,
    inet_server_port() AS port,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,
    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,
    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username,
    CURRENT_TIMESTAMP AS current_time;
 

.::: Change http to https to Activate Clipboard Apache Guacamole in Almalinux 10 / Redhat 10 for remote ssh/Rdp via Web Browser :::.

  

continue for https://teguhth.blogspot.com/2026/08/install-apache-guacamole-in-almalinux.html

1. install nginx & Buat sertifikat SSL

dnf install nginx openssl -y

mkdir -p /etc/nginx/ssl

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 \
-keyout /etc/nginx/ssl/guacamole.key \
-out /etc/nginx/ssl/guacamole.crt \
-subj "/C=ID/ST=Jawa Barat/L=Bogor/O=IT/OU=IT/CN=10.10.10.22"

ls -l /etc/nginx/ssl/
 

.::: Install Apache Guacamole in Almalinux 10 / Redhat 10 for remote ssh/Rdp via Web Browser :::.

 

1. Pastikan AlmaLinux 10

Saya sarankan minimal AlmaLinux 10.2 karena 10.2 sudah dirilis 26 Mei 2026.

cat /etc/redhat-release 

grep '^PRETTY_NAME=' /etc/os-release | cut -d= -f2- | tr -d '"'

2. Aktifkan EPEL

dnf install -y epel-release
dnf makecache
 

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

 

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;