Description
Databases are used to store huge amount of data and it becomes very crucial to Backup the data of PostgreSQL Backup. In the event of some software or hardware failures, PostgreSQL Backup data can be used to recover the data. The PostgreSQL Backup and restore fundamentals provides the necessary security for protecting data stored in the PostgreSQL databases server.
Conceptual
figure
Description
PostgreSQL Backup can be classified into 2 types:
- Replicate the PostgreSQL database files.
- Convey tables to text files.
Description
PostgreSQL uses similar table arrangement on distant platforms, so copying PostgreSQL table and index files from one platform to another without any complications. Backing up PostgreSQL databases server, working test recovery procedures backups, and keeping copies of backups is secure, off-site location protects database from disastrous data loss.
Description
The PostgreSQL DUMP is accessible command that can be used to instantly backup the PostgreSQL Database to the text files. To use the PostgreSQL Dump command it is mandatory to login to the System running the PostgreSQL Database.
Syntax
pg_dump -U USERNAME DBNAME>Mybackup.pgsql;
Username =>Database user name.
DBNAME=>Name of the database.
Mybackup.pgsql => Contains all the data for the DBNAME database.
Examples
By viewing the below example, the concept of single database, multiple database and all database backup and be understand easily.
[sql]
C:\Program Files\PostgreSQL\9.1\bin>Pg_dump -h 127.0.0.1 -u root -p employee21;
d:\backup\singleDBBackup.sql
Enter password:**********
C:\Program Files\PostgreSQL\9.1\bin>pg_dump -h 127.0.0.1 -u root -p --database employee sakila database;
c:\backup\multipleDBBackup.sql
Warning: Using unique option prefix database instead of databases is deprecated
and will be removed in future release.Please use the full name instead.
Enter password:**********
C:\Program Files\PostgreSQL\9.1\bin>pg_dump -h 127.0.0.1 -u root -p --all-database>
c:\backup\allDBBackup.sql
Warning: Using unique option prefix all-database instead of all-databases is deprecated
and will be removed in future release.Please use the full name instead.
Enter password:**********
Warning:Skipping the data of table postgresql event.specify the --events option explicit[/sql]
In the above example,first employee21 table had been chosen for backup and all the data contained in employee21 database has been backup into another location in the server.In the same way multiple and all databases can be backup.
Description
Content documents made by pg_dump are expected to be perused in by the program OF psql. Sort the accompanying charge, and after that press ENTER. Supplant USERNAME with the username and DBNAME with the name of the database that need to reestablish the information into
Syntax
The syntax for restore a postgreSQL database is as follows:
PSQL -U USERNAME DBNAME < Mybackup.pgsql;
Username =>Database user name.
DBNAME=>Name of the database.
Mybackup.pgsql => Contains all the data for the DBNAME database.
Description
Pg_restore is an adequacy for recovering the database in PostgreSQL from a chronicle made by pg_dump in a single non-plain-message groups.
Pg_restore can work in two process. On the off chance that a database name is determined, Pg_restore associates with that database and recover the archive substance specifically into the database. Something else, a script consisting of the commands related to SQL and to reconstruct the database is made and written to a file of accepted output standards. This script yield is proportional to the plain content yield organisation of pg_dump. A portion of the alternatives controlling the yield are in this manner that are undifferentiated from pg_dump privileges.
Key Points
- PostgreSQL Backup and restoring - Backup and restore is used to recover the data from database server,if any technical problems arises.
- Replication - Replication is nothing but coping data from the server.
- Convection of tables into text files - Converting tables into files.
- Pg_restore - Is a adequacy for recovering the database.