.::: Script restore database MariaDB using record to table include duration Restore :::.

 

A. simple record

1. create table restore_log

CREATE TABLE IF NOT EXISTS restore_log (
    db_name VARCHAR(100),
    restore_time DATETIME,
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

.::: Script Backup Full & Incremental All Databases Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

  

https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.html

1. create database & table

CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 

.::: Create New Container or add Container CDB in Oracle 19C :::.

 

1. Cek dulu container yang ada

sqlplus sys/oracle as sysdba;

SHOW CON_NAME;
SHOW PDBS;
 

2. Buat PDB baru dari PDB$SEED (template bawaan)

Misalnya mau buat container baru bernama TGHDBHR:

CREATE PLUGGABLE DATABASE TGHDBHR
  ADMIN USER hradmin IDENTIFIED BY hrpass
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TGH/pdbseed/',
                     '/u01/app/oracle/oradata/TGH/TGHDBHR/');

.::: First time login Oracle using CDB (Container Database / Multitenant) :::.

 

1. login as sysdba 
sqlplus sys/oracle as sysdba;

oracle@teguhth ~]$ sqlplus sys/oracle as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 24 14:12:31 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
 

.::: Script Backup Full & Incremental All Database Using Record to Table in PostgreSQL EDB :::.

correlation with https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
correlation with https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create table 

-- Table: public.backuplogs

CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Script to Restore Backup Full & all Transaction (Restore Incremental) in one Command for EnterpriseDB PostgreSQL :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
correlation https://teguhth.blogspot.com/2025/09/script-daily-backup-full-incremental.html

1. check before restore
 
2. check directory backup & create dblist,txt
[postgres@teguhth dwh]$ pwd
/var/lib/pgsql/16/manual/dwh
[postgres@teguhth dwh]$
[postgres@teguhth dwh]$ cat dblist.txt
FULL_dwh_20250913_000001.sql.gz
INC_dwh_20250913_010002.sql.gz
INC_dwh_20250913_020002.sql.gz
INC_dwh_20250913_030002.sql.gz
INC_dwh_20250913_040002.sql.gz
INC_dwh_20250913_050002.sql.gz
INC_dwh_20250913_060002.sql.gz
INC_dwh_20250913_070001.sql.gz
INC_dwh_20250913_080002.sql.gz
INC_dwh_20250913_090002.sql.gz
INC_dwh_20250913_100001.sql.gz
INC_dwh_20250913_110002.sql.gz
INC_dwh_20250913_120002.sql.gz
INC_dwh_20250913_130001.sql.gz
INC_dwh_20250913_140002.sql.gz
INC_dwh_20250913_150002.sql.gz
INC_dwh_20250913_160001.sql.gz
INC_dwh_20250913_170002.sql.gz
INC_dwh_20250913_180002.sql.gz
INC_dwh_20250913_190001.sql.gz
INC_dwh_20250913_200002.sql.gz
INC_dwh_20250913_210002.sql.gz
INC_dwh_20250913_220002.sql.gz
INC_dwh_20250913_230001.sql.gz
[postgres@teguhth dwh]$
 

.::: Script Daily Backup FULL & Incremental PostgreSQL EDB Using pgdump & pgaudit :::.

 

correlation with https://teguhth.blogspot.com/2025/09/how-to-create-backup-incremental-in.html

1. Backup All FULL
 

 

.::: How to Create Backup Incremental in PostgreSQL EDB as Logical Backup (like SQL Server or MariaDB with mysqlbinlog) using pgaudit pgauditlogtofile :::.

 

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

B. convert to table 

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

C. Testing sample CRUD Activity (sample database 'dwh')

1. create table pembelian;

create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));