There are two types of database backup in PostgreSQL.
1. Logical Backup
· In the logical backup technique, the IMPORT/EXPORT utilities are used to create the backup of the database. A logical backup backs up the contents of the database. A logical backup can be used to restore the database to the last backup. However, unlike physical back, it should not be used to create an OS backup copy because restoring using this approach would make it possible to correct the damaged data files. Therefore, in these situations, physical backups should be preferred.
· Logical backup is using SQL statements. Export using exp tool is logical.
· Logical Data Elements such as tables, records, and their associated metadata are stored across many different locations.
· In the logical backup, you don’t take copies of any physical things; you only extract the data from the data files into dump files. (Example: using export)
2. Physical Backup
· The operating system saves the database files onto tape or some other media. This is useful to restore the system to an earlier point whenever needed.
· Physical backup is copying the data files either when the database is up and running (HOT BACKUP) or when the database is shut down(COLD BACKUP).
· In other words, Physical backup is to copy for backing up all the physical files that belong to a database like data files, control files, log files, executables, etc.).
Logical database backups are critical for the granular recovery of specific components. And Physical backups are useful for full disaster recovery scenarios. The choice between Logical and Physical database backups should be covered as part of your Recovery Point Objectives. (RPOs)
How to take Physical Backup in PostgreSQL?
pg_basebackup:
Pg_basebackup is a tool that performs a copy of the data from the database node to a separate location. It is used to take the entire cluster full backup. The PITR is also supported for this backup method. It supports all types of backup like PLAIN, TAR, CUSTOM, and DIRECTORY.
· pg_basebackup is used to take base backups of a running PostgreSQL database cluster.
· pg_basebackup makes a binary copy of the database cluster files while ensuring the system is automatically put in and out of backup mode.
· Backups are always taken of the entire database cluster; it is not possible to back up individual databases or database objects.
· For individual database backups, a tool such as pg_dump must be used
· pg_basebackup is faster than pg_dumpall. and take backup of both physical and WAL files (redo log).
· Realtime we use pg_basebackup.
Step-1: To create Backup directory
[root@postgres11_custom_cre ~]# mkdir -p /u01/basebkp
[root@postgres11_custom_cre ~]# chown -R postgres:postgres /u01/basebkp
[root@postgres11_custom_cre ~]# chmod -R 775 /u01/basebkp
[postgres@oel6 ~]$ cd /u01/backup
[postgres@oel6 backup]$ mkdir -p phybackup
[postgres@oel6 backup]$ chown -R postgres:postgres /u01/backup/phybackup
[postgres@oel6 backup]$ chmod 0700 /u01/backup/phybackup #u=rwx (0700)
Step-2: To take the backup in Tar format
[postgres@postgres11_custom_cre ~]$ cd /u01/postgres/bin
[postgres@postgres11_custom_cre bin]$
[postgres@postgres11_custom_cre bin]$ ./pg_basebackup -U postgres -p 5432 -D /u01/basebkp/clu_bkp -Ft -Xs -P
39319/39319 kB (100%), 2/2 tablespaces
/usr/local/pgsql/bin/pg_basebackup -U postgres -p 5432 -D /u01/backup/phybackup -Ft -Xs -P
Output:
[postgres@oel6 backup]$ /usr/local/pgsql/bin/pg_basebackup -U postgres -p 5432 -D /u01/backup/phybackup -Ft -Xs -P
1608052/1608052 kB (100%), 2/2 tablespaces
[postgres@oel6 backup]$ cd /u01/backup/phybackup
[postgres@oel6 phybackup]$ ls -ltr
total 1640832
-rw — — — -. 1 postgres postgres 134144 Oct 10 10:36 16494.tar
-rw — — — -. 1 postgres postgres 1646513152 Oct 10 10:38 base.tar
-rw — — — -. 1 postgres postgres 33556992 Oct 10 10:38 pg_wal.tar
Step-3: To check the backup is came or not
[postgres@postgres11_custom_cre bin]$ cd /u01/basebkp
[postgres@postgres11_custom_cre basebkp]$ ls -lrt
total 4
drwx — — — . 2 postgres postgres 4096 Jul 14 07:29 clu_bkp
[postgres@postgres11_custom_cre basebkp]$ cd clu_bkp
[postgres@postgres11_custom_cre clu_bkp]$ ls -lrt
total 55712
-rw — — — -. 1 postgres postgres 1536 Jul 14 07:29 17062.tar
-rw — — — -. 1 postgres postgres 40263168 Jul 14 07:29 base.tar
-rw — — — -. 1 postgres postgres 16779264 Jul 14 07:29 pg_wal.tar
How to take Logical Backup in PostgreSQL?
pg_dumpall:
Pg_dumpall is a tool dedicated to performing logical backups in PostgreSQL. It can be used in ClusterControl to take an ad-hoc backup and create the backup schedule.
1. To take the backup from entire cluster backup.
==> Create the cluster backup directory
[root@postgres11_custom_cre ~]# mkdir -p /u01/clubkp
[root@postgres11_custom_cre ~]#
[root@postgres11_custom_cre ~]# chown -R postgres:postgres /u01/clubkp
[postgres@postgres11_custom_cre bin]$ ./pg_dumpall -U postgres -p 5432 -f /u01/clubkp/cluster_bkp.sql
[root@postgres11_custom_cre ~]# cd /u01/clubkp
[root@oel6 clubkp]# ls -ltr
total 689928
-rw-rw-r — . 1 postgres postgres 706479811 Sep 26 16:39 cluster_bkp.sql
2. To take the backup from global objects.
[postgres@postgres11_custom_cre bin]$ ./pg_dumpall -U postgres -p 5432 — globals-only — file=/u01/clubkp/global_bkp.sql
[root@postgres11_custom_cre ~]# cd /u01/clubkp
[root@postgres11_custom_cre clubkp]# ll
total 2708
-rw-rw-r — . 1 postgres postgres 2766377 Jul 13 08:23 cluster_bkp.sql
-rw-rw-r — . 1 postgres postgres 752 Jul 13 08:23 global_bkp.sql
3. To restore the cluster.
[postgres@postgres11_custom_cre bin]$ psql -U postgres -p 5432 < /u01/clubkp/cluster_bkp.sql
Output:
[postgres@oel6 bin]$ psql -U postgres -p 5432 < /u01/clubkp/cluster_bkp.sql
SET
SET
SET
ERROR: role “approle” already exists
ALTER ROLE
ERROR: role “odxa” already exists
4. To restore the globals.
[postgres@postgres11_custom_cre bin]$ psql -U postgres -p 5432 < /u01/clubkp/global_bkp.sql
pg_dump:
PostgreSQL provides tool called pg_dump. This utility is designed to perform logical backups on the PostgreSQL database. It supports several output formats: Plain, Custom, TAR, Directory formats. By default, it stores data in plain text files, but it can also produce a tar file or a custom binary format, compressed, which can be restored using pg_restore. Refer below the usage of pg_dump to create a backup of our PostgreSQL server and restore.
Backup steps:
1) PLAIN FORMAT: (p) Simple SQL based file in readable format.
[postgres@oel6 bin]$ /usr/local/pgsql/bin/pg_dump -d empdb -U postgres -p 5432 -v -f /u01/plain_format/db_empdb.sql
pg_dump: last built-in OID is 16383
2) CUSTOM FORMAT: © add -Fc option in plain format cmd which is in encrypted format.
/usr/local/pgsql/bin/pg_dump -d empdb -U postgres -p 5432 -Fc -v -f /u01/custom_format/db_custom_empdb.bkp
pg_dump: last built-in OID is 16383
3) TAR FORMAT: (t) create compressed tar backup file.
/usr/local/pgsql/bin/pg_dump -d empdb -U postgres -p 5432 -Ft -v -f /u01/tar_format/db_empdb.tar
4) DIRECTORY FORMAT: (d) Backups will be stored in the directory defined in the “Storage Directory.” Such a directory can be an NFS mount from an external backup server.
[root@postgres_source dir_format]# mkdir -p db_dir
[root@postgres_source dir_format]# chown -R postgres:postgres db_dir
Restore steps:
PSQL: psql utility is used to restore the backup which is in plain format:
pg_restore: backup is in other plain format.
1) PLAIN FORMAT:
Create database empdb ;
/usr/local/pgsql/bin/psql -d empdb -U postgres -p 5432 -f /u01/plain_format/db_empdb.sql
2) CUSTOM FORMAT:
Create database empdb ;
/usr/local/pgsql/bin/pg_restore -d empdb -U postgres -p 5432 -Fc /u01/custom_format/db_custom_empdb.bkp
3) TAR FORMAT:
Create database empdb ;
/usr/local/pgsql/bin/pg_restore -d lab8 -U postgres -p 5432 -Ft /u01/tar_format/db_empdb.tar
4) DIRECTORY FORMAT:
Create database empdb ;
/usr/local/pgsql/bin/pg_restore -d empdb -U postgres -p 5432 -Fd /u01/dir_format/db_dir/
Happy reading…
No comments:
Post a Comment