# Physical Backup-------------------------------
# first stop the server
systemctl stop postgresql
# Create a backup directory to store the backup files
mkdir /var/backups/postgresql
# Copy the data directory to the backup directory
cp -r /var/lib/postgresql/13/main /var/backups/postgresql
# Start the server
systemctl start postgresql
# Logical Backup--------------------------------------
# create a backup of the "mydatabase" database
pg_dump -U postgres -d mydatabase -f mydatabase_backup.sql
# Point in time Backup--------------------------------
# create a backup of the "mydatabase" database up to January 1st, 2022
pg_dump -h localhost -U postgres -d mydatabase --clean --format=plain --file=mydatabase_backup.sql --verbose --no-owner --no-acl --jobs=4 --lock-wait-timeout=300 --snapshot --before-datetime='2022-01-01 12:00:00'
# Differential Backup----------------------------------
# take a full backup
pg_dump --username=username --dbname=dbname --format=custom --file=full_backup.dump --verbose
# take a differential backup
pg_dump --username=username --dbname=dbname --format=custom --file=diff_backup.dump --verbose --incremental
# Incremental Backup-----------------------------------
# take a full backup
pg_dump dbname > full_backup.sql
# take an incremental backup
pg_basebackup -D /path/to/incremental/backup --incremental -X stream -P -F p -z -v -h localhost -U username