.::: Upgrade PostgreSQL from 14 to 15 on CentOS 7 :::.

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

2. backup all
su - postgres -c "pg_dumpall -p 5432 > /data/edb/backupupgrade/all_14posg.sql"

3. install postgres15 as
detail http://teguhth.blogspot.com/2021/07/install-postgresql-linux-using.html

wget http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libzstd-1.4.4-1.el8.x86_64.rpm
rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm

yum -y install postgresql15-server

[root@teguhth data]# wget http://mirror.centos.org/centos/8-stream/BaseOS/x86_64/os/Packages/libzstd-1.4.4-1.el8.x86_64.rpm
[root@teguhth data]# rpm -ivh libzstd-1.4.4-1.el8.x86_64.rpm
[root@teguhth data]# yum -y install postgresql15-server
[root@teguhth data]#

.::: Check Database Size and Table Size in PostgreSQL , EDB, YugeByte :::.

 1. Check size db Postgresql
SELECT pg_database.datname,
pg_database_size(pg_database.datname) AS DBSizeB,
pg_database_size(pg_database.datname)/1024 AS DBSizeKB,
pg_database_size(pg_database.datname)/1024/1024 AS DBSizeMB,
pg_database_size(pg_database.datname)/1024/1024/1024 AS DBSizeGB
FROM pg_database;
 

.::: How to Check MySQL MariaDB Database Size, Total All Size Database And Table Size, Uptime Time, Create Time Table :::.

 1. type table
SELECT @@hostname as ServerName,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'teguhth'     
ORDER BY TABLE_NAME,table_rows desc;
 

::: Backup & Restore MariaDB MySQL using bat Script in windows :::.

1. run script fullbackup Full_backup_mariadb.bat

2. run script transaksi log back_log_mariadb_windows.bat

3. result

 

 

.:::How to Check Disk Type SSD in Linux & Windows :::.

A. Check Disk Type in Windows
you can also use the Command Prompt to check whether you have an SSD or an HDD in Windows 11. Here's how you can do it:

Open Command Prompt:
To open the Command Prompt, you can press Win + R to open the Run dialog, type in "cmd," and press Enter. Alternatively, you can search for "Command Prompt" in the Start menu and open it from there.

Run Command:
In the Command Prompt window, type the following command and press Enter:

.::: Query Script Backup Full & Transaction Log WAL Shipping PostgreSQL, EDB :::.

 
1. run script full backup
sh full_backup_edb.sh

-bash-4.2$ cd /var/lib/pgsql/14/edb/
-bash-4.2$ ls
full  full_backup_edb.sh  wal_backup_edb.sh  wal_edb
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:56:33.
-bash-4.2$
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:56:37.
-bash-4.2$
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:57:12.
-bash-4.2$
-bash-4.2$ sh full_backup_edb.sh
pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "admin"
Full backup successfully created and compressed at 2023-08-11_16:58:39.
-bash-4.2$
 

.::: Shell Script to Get CPU Memory Usage (%), Swap (%) & Local Check, all_check GTID Mirroring,IP & Hostname,uptime MariaDB in Unix, Linux & Solaris 10 from Local & Remote :::.

A. From Local

 

 1. create script to create cpu memory

[root@teguhth ~]# cat check_cpu_mem_usage.sh
#!/bin/bash

# Get CPU and Memory usage using top command
TOP_OUTPUT=$(top -b -n 1)

# Extract CPU usage percentage
CPU_USAGE=$(echo "$TOP_OUTPUT" | awk '/Cpu/ {print $2}')

.::: Microsoft Script Bat : Create Store Procedure Simple Calculator :::.

 
1. create Simple_Calculator.sh

@echo off
echo Simple Calculator
set /p "num1=Enter first number: "
set /p "num2=Enter second number: "
set /p "operator=Enter operator (+, -, *, /): "

if "%operator%"=="+" (
    set /a "result=num1 + num2"
) else if "%operator%"=="-" (
    set /a "result=num1 - num2"
) else if "%operator%"=="*" (
    set /a "result=num1 * num2"
) else if "%operator%"=="/" (
    set /a "result=num1 / num2"
) else (
    echo Invalid operator
    exit /b 1
)

echo Result: %result%
 

.::: Linux Unix Shell Script : Create Store Procedure Simple Calculator :::.

 1. create Simple_Calculator.sh
[root@teguhth data]# cat Simple_Calculator.sh
#!/bin/bash

echo "Simple Calculator"
echo "Enter first number:"
read num1

echo "Enter second number:"
read num2

.::: PostgreSQL EDB : Create Store Procedure Simple Calculator :::.

 
1. Create simple calculator
CREATE OR REPLACE FUNCTION Sp_SimpleCalculator(
    num1 NUMERIC,
    num2 NUMERIC,
    operator CHAR(1)
) RETURNS NUMERIC AS $$
BEGIN
    CASE
        WHEN operator = '+' THEN RETURN num1 + num2;
        WHEN operator = '-' THEN RETURN num1 - num2;
        WHEN operator = '*' THEN RETURN num1 * num2;
        WHEN operator = '/' THEN RETURN num1 / num2;
        ELSE RETURN NULL;
    END CASE;
END;
$$ LANGUAGE plpgsql;

.::: MariaDB MySQL : Create Store Procedure Simple Calculator :::.

 
1. Create store procedure

DELIMITER //

CREATE PROCEDURE Sp_SimpleCalculator(
    IN num1 DECIMAL(10, 2),
    IN num2 DECIMAL(10, 2),
    IN operator CHAR(1),
    OUT result DECIMAL(10, 2)
)
BEGIN
    CASE
        WHEN operator = '+' THEN SET result = num1 + num2;
        WHEN operator = '-' THEN SET result = num1 - num2;
        WHEN operator = '*' THEN SET result = num1 * num2;
        WHEN operator = '/' THEN SET result = num1 / num2;
        ELSE SET result = NULL;
    END CASE;
END //

DELIMITER ;
 

.::: Create Script Backup Database Full, Differential & Log Using T-SQL & Run using CMD SQL Server :::.

1. Create Script Backup Full > sp_backup_full


CREATE procedure [dbo].[sp_backup_full]
as
begin
DECLARE @BackupPath NVARCHAR(255);
DECLARE @DateSuffix NVARCHAR(20);
DECLARE @BackupFileName NVARCHAR(255);
declare @DBName NVARCHAR(255);
-- Replace 'YourDatabaseName' with the name of your database
SET @DBName ='teguhth'
SET @BackupPath = 'C:\backupdb_full\' -- Replace 'C:\YourBackupFolder\' with the desired backup folder path
 

.::: MSSQL: Create Store Procedure Simple Calculator :::.

1. Create store procedure Sp_Simple_Calculator

CREATE PROCEDURE Sp_Simple_Calculator
    @Num1 decimal,
    @Num2 decimal
AS
BEGIN
    SELECT @@ServerName as ServerName,db_name() as [DBName],getdate() [Date],'Result @Num1 & @Num2' as Desctiption,@Num1 as [Num1],@Num2 as [Num2], @Num1 + @Num2 AS 'Result Additional',
    @Num1 - @Num2 AS 'Result Substraction',@Num1 * @Num2 AS 'Result Multiplication',@Num1 / @Num2 AS 'Result Division';
END
 

.::: Backup and Restore PostgreSQL EDB Databases using Pg_dump Utility :::.

1. Backup Single Database

 pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]

 pg_dump -h 10.10.10.8 -p 5432 -U admin teguhth > teguhth_remote.sql 

restore

psql -h 10.10.10.8 -p 5432 -U admin teguhth  < teguhth_remote.sql

Backup

pg_dump teguhth  > teguhth_singledb.sql

-bash-4.2$ pg_dump teguhth  > teguhth_singledb.sql

[root@teguhth edb]# su - postgres
Last login: Tue Jul 25 13:09:57 WIB 2023 on pts/1
-bash-4.2$ cd 14/backups/
-bash-4.2$ pwd
/var/lib/pgsql/14/backups
-bash-4.2$
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$ pg_dump teguhth  > teguhth_singledb.sql
-bash-4.2$
-bash-4.2$ ls
teguhth_singledb.sql
-bash-4.2$ ls -l
total 8
-rw-r--r-- 1 postgres postgres 7023 Jul 25 13:12 teguhth_singledb.sql
-bash-4.2$