.::: Store Procedure: Enlarge Table & Database PostgreSQL to 1,000,000 Rows :::.

 

1. table barang 

ALTER TABLE barang
ALTER COLUMN kode_barang TYPE VARCHAR(11);

-- 2. Pastikan KODE_BARANG NOT NULL
ALTER TABLE barang
ALTER COLUMN kode_barang SET NOT NULL;


DROP PROCEDURE IF EXISTS generate_barang(INT);

CREATE OR REPLACE PROCEDURE generate_barang(IN jumlah INT)
LANGUAGE plpgsql
AS $$
DECLARE
    mulai BIGINT;
BEGIN

    SELECT COALESCE(
        MAX(
            NULLIF(
                regexp_replace(kode_barang, '^ELK-', ''),
                ''
            )::BIGINT
        ),
        0
    )
    INTO mulai
    FROM barang
    WHERE kode_barang ~ '^ELK-[0-9]+$';

    RAISE NOTICE 'Nomor ELK terakhir = %', mulai;
    RAISE NOTICE 'Generate % data mulai dari ELK-%',
                 jumlah,
                 mulai + 1;
 

.::: Backup MariaDB mysqldump mariadb-dump using --ignore-table-data, --ignore-table Some Table :::.

  

1. sample create table dummy sample typ*

CREATE TABLE typa (text VARCHAR(100));
CREATE TABLE typb (text VARCHAR(100));
CREATE TABLE typc (text VARCHAR(100));
CREATE TABLE typd (text VARCHAR(100));
CREATE TABLE type (text VARCHAR(100));

INSERT INTO typa (text) VALUES ('typa sample typa');
INSERT INTO typb (text) VALUES ('typb sample typb');
INSERT INTO typc (text) VALUES ('typc sample typc');
INSERT INTO typd (text) VALUES ('typd sample typd');
INSERT INTO type (text) VALUES ('type sample type');
 

.::: Server Resource & MariaDB Health Check local_check, Local_check_bar, Local_check_loop using mpstat, sysstat :::.

 

correlation with 

https://teguhth.blogspot.com/2024/01/shell-script-to-get-cpu-memory-usage.html

1. install mpstat & run 

dnf install -y sysstat

mpstat 1 1 | awk '/Average:/ {printf "%.2f", 100-$NF}'
 

 

.::: Shell Script to Compare MariaDB Restore_log, backup_log with dump .sql include resume log :::.

  

A. Activity test compare 

1. prepare file 

[root@teguhth10x mirortest]# pwd
/data/mirror/mirortest
[root@teguhth10x mirortest]# ls -lh
total 296M
-rw-r--r-- 1 root root 1023 Sep 22 14:53 cek_compare_backup_error_basic.sh
-rw-r--r-- 1 root root 1.4K Sep 22 14:50 cek_compare_backup_error_detail.sh
-rw-r--r-- 1 root root 2.0K Sep 22 14:50 cek_compare_backup_error_detail_compres.sh
-rw-r--r-- 1 root root 2.5K Sep 22 14:50 cek_compare_backup_error_detail_compres_enc.sh
-rw-r--r-- 1 root root  973 Sep 22 15:58 restore_error.log
-rw-r--r-- 1 root root 139M Sep 22 14:53 teguhth_ori.sql
-rw-r--r-- 1 root root  19M Sep 22 14:51 teguhth_ori.sql.gz
-rw-r--r-- 1 root root 139M Sep 22 14:52 teguhthenc.sql.enc
[root@teguhth10x mirortest]#
 

.::: Script Check ETA (Estimated Time of Arrival) Backup Database MariaDB on % Percent in .sql or .sql.gz :::.

  

1. check size database (kb)

du -s /var/lib/mysql/teguhth

du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth

[root@teguhth10x testbackup]# du -sh /var/lib/mysql/teguhth
505M    /var/lib/mysql/teguhth
[root@teguhth10x testbackup]# du -s /var/lib/mysql/teguhth
516132  /var/lib/mysql/teguhth

2. backup in sql or comprese 

mariadb-dump -uroot -pxxx teguhth > teguhth_all.sql
mariadb-dump -uroot -pxxx teguhth | gzip -c > teguhth_all.sql.gz

mariadb-dump -uroot -pxxx marinadb > marinadb_all.sql
mariadb-dump -uroot -pxxx marinadb | gzip -c > marinadb_all.sql.gz
 

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

 

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

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

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

 

1. Total database/folder (result 11 folder or db)
 

ls -d /var/lib/mysql/*/ | wc -l

2. sample size database mariadb (sample result 516132 kib or 528,519,168 bytes)
 

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

[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]#

.::: Store Procedure: Enlarge Table & Database MariaDB to 1,000,000 Rows :::.

 

 

correlation with https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html

1. table barang 

ALTER TABLE barang MODIFY KODE_BARANG CHAR(10) NOT NULL;

DELIMITER //

CREATE PROCEDURE generate_barang(IN jumlah INT)
BEGIN
    DECLARE i INT;
    DECLARE mulai INT;

    SELECT COALESCE(
        MAX(CAST(SUBSTRING(KODE_BARANG, 5) AS UNSIGNED)),
        0
    )
    INTO mulai
    FROM barang
    WHERE KODE_BARANG LIKE 'ELK-%';
 

.::: Shell Script to Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB some database, table :::.

correlation https://teguhth.blogspot.com/2025/11/grouping-year-month-day-time-date-for.html

1. run check all hardcode
 

2. run check manual 
 

3. script check hardcode

[root@teguhth-all countx]# cat table_count.sh
hostx=$(hostname -s)
ipx='10.10.10.90'
portx='3306'
userdb='admin'
pwddb='xxx'
dbname='teguhth'
tblname='datefilter'
cldatex='datex'
 

.::: Script Scheduling crontab SCP Linux to other server like Backup Daily (run from source server ) :::.

 

1. enable ssh-key 

hostname -I | awk '{print $1}'
ssh-keygen -t rsa
ssh-copy-id -p 22 root@10.10.10.9
 

2. manual command

scp /data/sample/* root@10.10.10.9:/dummy/
scp -P22 /data/sample/*  root@10.10.10.9:/dummy/
 

.::: Bat Script sftp windows to Server Linux with Scheduler & password using hiddep password or private key .ppk (run from destination server) using putty psftp.exe :::.

 

correlation https://teguhth.blogspot.com/2026/09/bat-script-sftp-windows-to-server-linux.html

1. create sshkey without password

C:\Windows\System32>type %USERPROFILE%\.ssh\id_rsa.pub | ssh ftpaccess@10.10.10.90 "mkdir -p ~/.ssh && chmod 700 ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 600 ~/.ssh/authorized_keys"

C:\Windows\System32>
C:\Windows\System32>sftp ftpaccess@10.10.10.90
Connected to 10.10.10.90.
sftp> exit
 

.::: 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';