Postgresql Backup & Recovery

POSTGRESQL BACKUP AND RECOVERY

Hot Backup:-
Note:-
1. Database is must online(No Shutdown).
2.Archive mode must enabled
     archive_command = 'cp %p /archive/%f'
     archive_mode =on
     wal_level=archive
Step1:-
Go to database Level to find databse id ,its helpfull for easily find os level folders
 Select datname,oid from pg_database;

Step2:-
 select pg_start_backup('sdbt-postgresdb-bkp');

Step3:-
 Then Go to Os level and copy the the database.

[root@www.sakthidbtech.com ~]# cp -rf $PGDATA/base/24733  /backup/jan-backup/

Step4:-
Once complete the os level Copy command,the come to databse level isse the below querie.

Select pg_stop_backup();



Cold Backup:-
Note:-
1.Database Must shutdown,
2.It is consistency Backup.

Step 1:
Go to os level stop the postgresql sever or Data cluster.
[root@www.sakthidbtech.com ~]# /etc/init.d/postgresql-9.5 stop
[OR]
[root@www.sakthidbtech.com ~]# su - postgres
-bash-4.1$
-bash-4.1$ pg_ctl stop -D /opt/PostgreSQL/9.5/data/

Step2:-
Go to os level and take a file system level backup.
[root@www.sakthidbtech.com ~]#cp -rf $PGDATA/24734  /backup/jan-backup/