.::: 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$
 
2. check result full backup
-bash-4.2$ pwd
/var/lib/pgsql/14/edb/full
-bash-4.2$ ls
-bash-4.2$ ls
teguhth_backup_full_20230811_165633.sql.gz  teguhth_backup_full_20230811_165712.sql.gz
teguhth_backup_full_20230811_165637.sql.gz  teguhth_backup_full_20230811_165839.sql.gz
-bash-4.2$
 

3. run script Transaction log / wall Backup
sh full_backup_edb.sh
-bash-4.2$ pwd
/var/lib/pgsql/14/edb
-bash-4.2$ sh wal_backup_edb.sh
WAL backup successfully created and compressed at 2023-08-11_17:13:20.
-bash-4.2$
-bash-4.2$ sh wal_backup_edb.sh
WAL backup successfully created and compressed at 2023-08-11_17:13:27.
-bash-4.2$
-bash-4.2$ sh wal_backup_edb.sh
WAL backup successfully created and compressed at 2023-08-11_17:14:06.
-bash-4.2$
-bash-4.2$

4. check result wall backup
result log
-bash-4.2$ pwd
/var/lib/pgsql/14/edb/wal_edb
-bash-4.2$ ls
-bash-4.2$
-bash-4.2$ ls
20230811_171319  20230811_171326
-bash-4.2$ ls
20230811_171319  20230811_171326  20230811_171405
-bash-4.2$
-bash-4.2$ cd 20230811_171405
-bash-4.2$ ls -l
total 4664
-rw------- 1 postgres postgres  182789 Aug 11 17:14 backup_manifest
-rw------- 1 postgres postgres 4570511 Aug 11 17:14 base.tar.gz
-rw------- 1 postgres postgres   17074 Aug 11 17:14 pg_wal.tar.gz
-bash-4.2$ ls
backup_manifest  base.tar.gz  pg_wal.tar.gz
-bash-4.2$
 

5. Query backup full
-bash-4.2$ cat full_backup_edb.sh
#!/bin/bash

# Configuration
DB_USER="admin"
DB_NAME="teguhth"
BACKUP_DIR="/var/lib/pgsql/14/edb/full"

# Create a folder with today's date
#backup_folder="$BACKUP_DIR/$(date +%Y%m%d)"
backup_folder="$BACKUP_DIR/"

mkdir -p "$backup_folder"

# Create the backup file with the current timestamp
backup_file="$backup_folder/"$DB_NAME"_backup_full_$(date +%Y%m%d_%H%M%S).sql"

# Run pg_dump to create the full backup
pg_dump -U $DB_USER -w $DB_NAME > "$backup_file"

# Compress the backup file using gzip
gzip "$backup_file"

# Done
echo "Full backup successfully created and compressed at $(date +%Y-%m-%d_%H:%M:%S)."

-bash-4.2$

6. query backup wall

-bash-4.2$ more wal_backup_edb.sh
#!/bin/bash

# Configuration
BACKUP_DIR="/var/lib/pgsql/14/edb/wal_edb"
PGDATA="/var/lib/pgsql/14/data"
# Create a folder with today's date
backup_folder="$BACKUP_DIR/$(date +%Y%m%d_%H%M%S)"
mkdir -p "$backup_folder"

# Run pg_basebackup to create the WAL backup
pg_basebackup -D "$backup_folder" -Ft -z

# Done
echo "WAL backup successfully created and compressed at $(date +%Y-%m-%d_%H:%M:%S)."


-bash-4.2$