Physical Backup - PostgreSQL

 

Physical Backup:-

You can take complete server file system backup of data directory, 

it is online process, no locks will be applied.

If you want, you can perform point in time recovery with using this 

phyical backup, if you have enough WAL files.

If you have the file system backup, if you want you can bring up the server anywhere if you have proper software (PostgreSQL version) 

To archive this, we have an utility called pg_basebackup.

You can take this backup in Plain format , at the same time compressed format as well.

You can bring up in any linux flavour, there will be no change data directory until unless page size and blocks size of your OS is equal, or else there will be problem data distribution.

VIMP:- If you want to take the base backup, that target directory should be empty.

We have to enable below parameters in postgresql.conf file to gather all the requried wal files

for consistency.

Enable the parameters in postgresql.conf file

=============================================

wal_level = replica (upto 9.6 this hot_standby) -- It will keep all the required WAL files for repilcation 

      Minimal:- it will keep WAL file in pg_xlog only for recovery purpose, so never use this minimal,

it will be not suitable for replication/baseback

   logical: as part of logical replication we will enable

Eventhough whether it is replication or backup activity to send all the WAL files to target location we have below parameter

max_wal_senders = 5 ( If you have enough resource(CPU+RAM) in your system then you can increase the value

wal_keep_segments = 32 ( how many number of WAL files you want to store in pg_xlog location)

                    (If you have enough space you can use as much as you can, some people will keep 1024 files, 16GB space they are blocking for pg_wal directory.

 If you are taking archive files you no need to bother about the size.

To reflect 

show wal_level;

show max_wal_senders;

show wal_keep_segements;

How to take base backup:-

pg_backbackup -D /var/lib/pgsql/backup -- this will take backup in plain format

pg_backbackup -D /var/lib/pgsql/backup/ -Ft -X fetch -v -P

where 

-F - format

-t :- tar

-D :- target backup directory

-X fetch :- it will gather all required the WAL files - checkpoint will be happened.

-v :- versose

-P :- Progress 

16429.tar

base.tar 

these two files will be created 

Here 2 folders are there, by default only one folder , but if you have tablespaces, each tablespace one tar file will be created.

How to Restore Full backup:-

================================

$] tar -xvf base.tar  ----> extract this base.tar file 

$] rm base_tar ----> in real time keep this base.tar file keep in safe locaiton 

$] tar -xvf 16429.tar -------> this is tablespace tar file so extract this also

$] rm 16429.tar ---------> remove this file also

Once you extract both tar files, you will get exact copy of PostgreSQL datafiles./ datadirectory

Accordingly make the changes in the postgresql.conf file.

like port = 5432 to any

Change the permissions of backup directory to promote it as a data directory

chmod 0700 /var/lib/pgsql/backup