${toc}

type of backup

1. logical backup

A logical backup refers to the process of converting the data in a database into a straightforward text format. This typically involves creating scripts of the database, table, or database cluster, which are then saved as SQL files.

The scripts for tables and databases are composed of SQL statements. By executing these SQL statements, the tables and databases can be recreated

2. physical backup

A physical backup entails duplicating the actual files used for the storage and retrieval of the database. These backups are typically in binary form, which is not human-readable.
Physical backups can be categorized into two types:

• Offline Backup: This type of backup is performed when the system is shut down. During this time, a backup of the data directory is taken.
• Online Backup: This is conducted while the system is operational and running, with users actively connected to it.
  pg_dump -U postgres -d dd >

logical backup

taking logical backup

To take a logical backup in PostgreSQL, you can use the built-in utility pg_dump. The syntax for using this utility is as follows:

pg_dump -U [username] -d [database_name] > [path_to_backup_file]/backup_file_name.sql

Replace [username] with your PostgreSQL username, [database_name] with the name of the database you want to back up, and [path_to_backup_file]/backup_file_name.sql with the path and name of the file where you want to store the backup.
For example:

pg_dump -U postgres -d dvdrental > /share/dvdrental_backup.sql

In this example, postgres is the username, dvdrental is the database name, and the backup will be stored in the specified path /share/dvdrental_backup.sql. Make sure that you have the necessary permissions for the folder where you intend to store the backup. If you don’t specify an extension for the file, it will be automatically saved with the .sql extension, which is the standard for SQL files.

if you check the file content by using any prefers text editor such as less you will find SQL statement only on the file .

taking backup of the entire cluster

To perform a logical backup of a PostgreSQL database cluster, you can use the built-in utility pg_dumpall. This utility is designed to back up all the databases in the cluster, and the user executing pg_dumpall needs to have full superuser privileges.

The syntax for using pg_dumpall is as follows:

pg_dumpall -U [superuser_username] > [path_to_backup_file]/backup_file_name.sql

Replace [superuser_username] with the username of the superuser and [path_to_backup_file]/backup_file_name.sql with the path and name of the file where you want to store the backup.
For example:

pg_dumpall -U postgres > /share/dbcluster_backup.sql

In this example, postgres is the superuser, and the backup of the entire database cluster will be stored at the specified path /share/dbcluster_backup.sql. Remember to ensure that you have the necessary permissions for the folder where the backup file will be stored. By default, if you don’t specify an extension for the file, it will be saved with the .sql extension.

While running pg_dumpall for a PostgreSQL database cluster backup, it’s normal for the utility to prompt for the password multiple times. This happens because it requests the password for each database in the cluster.

This approach is suitable for smaller databases due to its straightforward nature. However, it is not recommended for large databases as it can be time-consuming to generate the backup. For larger databases, more efficient methods or tools that can handle large volumes of data more effectively might be preferable.

How to Compress and Split Dump Files

In the case of logical backups, where the dump file can become quite large, there are strategies to manage the file size and storage requirements:

1.Compression: You can compress the dump file to reduce its size. This is particularly useful when dealing with large databases, as it can significantly decrease the space needed for storage. Most compression tools can reduce the file size substantially, making it easier to handle and store.
syntax : 

pg_dumpall | gzip > [filepath]/[backupfilename].gz

example:

pg_dumpall | gzip > /share/clusterall_backup.gz

by doing compression you can see the big deterrent in size between the original dump file and compresses dump file .

2- Splitting the File: If you have limited space in your operating system or wish to distribute the storage of the dump across different partitions, you can split the dump file into smaller parts. This approach allows you to manage storage more effectively, especially when dealing with constraints in disk space or organizational policies on data storage.

To split the output of pg_dumpall into smaller files, you can indeed use the split command in Unix/Linux systems. The -b option allows you to specify the size of each split file. You can specify the size in kilobytes (k), megabytes (m), or gigabytes (g).

For example, if you want to split the dump into 1KB chunks, you would use the 1k option. Similarly, you can use m for megabytes and g for gigabytes.

The syntax for this operation would be as follows

pg_dumpall | split -b 1k – [filepath]/cluster_backup

pg_dumpall | split -b 1k – /share/cluster_backup

restore logical backup using psql

to test the process i have go ahead and drop database dvdrental and then i will attempt to restore the database

we can use psql for restoring the database from backup
before we restore database we have to create empty database
syntax: 

psql -U postgres -d dvdrental < /shar/dvdrental_bacup

restore logical backup pg_restore

pg_restore is used to restore PostgreSQL database from archive created by pg_dump in one of the non plain text format
meaning we need to create custom dump with certain format so that it can be supported by pg_restore.

to create custom dump use the below syntax 

pg_dump -U postgres -d[database_name]dvdrental -Fc > /share/dvdrental.dump

the file is in binary format not understand by the human .

restore only single table.

in this case i will drop a table and only restore this table , this one of common scenarios you will encounter during production .

command to restore table 

syntax:

pg_restore -t [table-name ] department -d [database-name] empolyee -U postgres /share/dvdrental.dump

physical backup

offline backup

in here the database server must be offline in order to take backup
this type of backup is useful when we want to make changes to database directory and we want to revert back if there is any flaw in our implementation .

its important to note that when we restore the database the PostgreSQL server must be shutdown during the restore .

partial restore or singe table restore is not possible because we are backup the entire data directory and when we restore we are going to restore the entire data directory 

to start i have two dbcluster
using the below command we can stop ne of them 

pg_lscluster pg_ctlcluster 13 ahmed stop

syntax for taking offline back is as follows 

tar -cvzf [filename]data_backup ‘[data directory path]’

the backup will happen in folder you are currently in so better to change you directory to folder you want to store the backup

online physical backup

In this type of backup the system available and online for the user to do there
In background continuous backup is taken
In postgresql we use a continuous archiving method to enforce online backup.

Wall files : similar to MS SQL log file , here the transaction gets written on wall files upon commit before they are written to the data file .

This is done to ensure that in case of a crash we can recover the system using the wal files .

Archiver : archiver role is to copy the wall files to another safe location
To achieve online backup we will use continuous archiving of wall files or in better context continuous copying of wall files to safe locations .

why i need to copy the wall files to another location

In case of a disaster assuming i have full backup on sunday and system crash monday at 10:00AM .
And you need to recover the system till 10 Am because you don’t want to lose data .
In this case you will need the full backup taken on Sunday plus all archived wal files.
This method of restore is called point in time recovery.

how to setup wall archiving

1- Enable wal archiving in the postgresql.conf files
2- We have to make base backup which is our full backup

Login to postgres and check if archive mode
Using the below command
Show archive_mode; 
You can see the wall archiver is off still not configured 

To configure wall archiving sop the cluster
Pg_ctlcluster 13 ahmed stop
No we need to make changes in the postgresql.conf file 
Vi /etc/postgresql/13/ahmed/postgresql.conf
Look for parameter called ‘wal_level and uncomment the line 

Also look for parameter‘archive_mode’uncomment and change it from off to on

The look for archive command parameter
Before you do you need to make a directory where the wall files will be restore and ensure the correct permission are given
Chmod 700 [directory]
Chown postgres:postgres [directory ]
Now back to archive_command , uncomment the line in the ‘’ add the below command
Cp -i %p [the directory you want to store the wal files ]/%f
Cp -i $p /share/archive/%f
Once done start the cluster and check if archiving is enabled
By going to psql console and check the status using the below command 

Show archive_mode;

test the wal level arching

We can use the which just inform postgresql that i am going to start a backup
note : this is not real backup
Select pg_start_backup(‘database_name’);

from the restult you can find one row copyied
you can use \! [bash command] to run os bach command directorly from psql console .

from the reslut there is one line is copyed
lt’s stop the command by usin the below

select pg_stop_backup();

for now we have archive wall for certain database from psql

taking full online full backup using low level api

we have setup archive backup , but you must keep in mind that archiver require full backup to be avilable
without that archiver is totaly useless.
base_backup is very 

full online backup means full backup is taken while system is online 

there two to take full online backup
1-low level api
2- pg_basebackup

to take low level api backup use the below syntax
in pql console
select pg_start_backup('give lable',false , false)
the first false means you are asking postgresql to take its time for doing I/O andnot return the contol to user imiddiatly .
the second false inform PostgreSQL to take non exeslusive backup.

now we will take tar o the entire data directory.
similar to offline backup but this time we will stop the cluster

tar -cvzf ahmed_clustr_backup.tar /postgres2/data/

now go back to psql console and stop the backup that we run

select pg_stop_backup(‘f’);

we mentioned f because this non exclusive backup

online backup using base_backup

base backup is can be used for replication and point in time recovery
the base_backup don’t put the database in backup mode but make the database accessible while the backup is running .
base_backup cannot take single object or single table or singe database , insist it will only take full backup of the entire DBCLUSTER .
syntax:

pg_basebackup -D [backup diretcory location]

pg_basebackup -h localhost -p 5433 -U postgred -D /share/backup/ -Ft -z -P -X

-h this host in which where the backup will be store in my case is localhost but you can store it in remote server
-Ft is for format you want
-z this will store the backup in gzip format
-P this will allow you to view the progress of the backup.
-Xs means you want backup of the entire database along with all transaction which are happening during the time of the backup