# Physical Restore---------------------------
# stop the server
systemctl stop postgresql
# Delete the current data directory
rm -rf /var/lib/postgresql/13/main
# Copy the backup directory to the PostgreSQL data directory
cp -r /var/backups/postgresql/main /var/lib/postgresql/13/
# Set the correct ownership and permissions on the files in the new data directory
chown -R postgres:postgres /var/lib/postgresql/13/main
chmod 700 /var/lib/postgresql/13/main
# Start the server
systemctl start postgresql
# Logical Restore----------------------------
# Create a new database to restore the backup:
createdb -U postgres mydatabase_new
# Restore the backup file to the new database:
psql -U postgres -d mydatabase_new -f mydatabase_backup.sql
# Verify that the data has been restored
psql -U postgres -d mydatabase_new
\dt
SELECT * FROM mytable;
# Point in time Restore-----------------------
# Create a new database to restore the backup
createdb -h localhost -U postgres -e mydatabase_new
# Restore the backup file to the new database
psql -h localhost -U postgres -d mydatabase_new -f mydatabase_backup.sql
# Differential Restore--------------------------
# restore the full backup
pg_restore --username=username --dbname=dbname --verbose full_backup.dump
# restore the differential backup
pg_restore --username=username --dbname=dbname --verbose diff_backup.dump
# Incremental Restore--------------------------------
# restore the full backup
pg_restore -U username -d dbname full_backup.sql
# restore the incremental backup
pg_basebackup -D /path/to/restored/database --incremental -X stream -P -F p -z -v -h localhost -U username --restore-command="psql -U username -d dbname -f %f"