Backup and Restore Database in PostgreSQL

There are different ways to take backup and restore database in PostrgeSQL. With PostgreSQL, backups can be full, incremental or continuous, and they can be at logical or filesystem level. Point-in-time recovery is also possible from incremental backups.

Tools Used for Backup and Restore Database in PostgreSQL:

pg_dump:
1. This utility is used backing up a database. pg_dump does not block other users accessing database.
2.pg_dump can create dumps as plain SQL files, as a tar archive, as a directory with a set of files or as a single file called a custom format file.
3.pg_dump does not does not dump global objects such as roles(users and groups) and tablespaces. These are only dumped by pg_dumpall utility.

pg_dumpall:
1.pg_dumpall utility dumps all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to psql to restore the databases. It does this by calling pg_dump for each database in a cluster.
2. pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.)

pg_restore:
pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats.
psql:
1.psql is interactive terminal.
2.Using psql terminal, we can restore database.

Steps for Backup and Restore Database in PostgreSQL

Step1: Backup Database Using pg_dump utility

Syntax:

pg_dump [connection-option...] [option...] [dbname]

Prerequisites to run pg_dump:

1. A user with superuser permission only can perform backup using pg_dump.
2. Should have permission on the file system where we want to store the backup file.

pg_dump -U postgres bank -f /home/vijay/dumps/bank_20170531.sql

Note: Dump is success. Because user is superuser and having access to file system /home/vijay/dumps/.
If you receive following error then refer to pg_dump: [archiver] could not open output file: Permission denied

pg_dump: [archiver] could not open output file "bank_20170531.sql": Permission denied.

Verification: Check dump file created or not. If created, check contents of the file.

$ ls -ltrh *bank_2017* -rw-rw-r-- 1 postgres postgres 5.3K May 30 20:42 bank_20170531.sql $ pwd /home/vijay/dumps $ ls -ltrh *bank_2017* -rw-rw-r-- 1 postgres postgres 5.3K May 30 20:42 bank_20170531.sql

Restore database in PostgreSQL using psql:
Login to target server. In my case, we restoring the dump in development server.

1. drop database bank_dev;
2. create database bank_dev;
3. Then, run following psql script.

psql -d bank_dev

Restore database in PostgreSQL using pg_restore:

1. Take backup with pg_dump by mentioning –format=c option. Otherwise pg_restore will through error.

pg_dump -d bank –format=c >/home/vijay/dumps/bankldump_31052017.dmp

2. Drop the database and recreate it.

drop database db_name;
create database db_name;

3. Restore database with pg_restore.

pg_restore -d bank_dev

Related Posts

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: