DarkMatter in Cyberspace
  • Home
  • Categories
  • Tags
  • Archives

Backup and Restore Postgresql Database


Backup

(On CentOS 6.4)

Enable backup as user "sonar": modify authentication file pg_hba.conf following SonarQube and Postgresql on Linux ;

Then you can backup a database: $ pg_dump -U sonar -F c -v -f sonar.bak sonar

Synopsis: pg_dump [options] . "-F c" means using "custom" file format. -f specifies the output file name.

Or backup all: $ pg_dumpall -U postgres -f totalbackup.sql -v -c

The "-c" will print drop commands to remove existing databases or roles. So you can restore them without cleaning existing databases in your backup server first.

Restore

Install and start postgresql server. Then you can restore a full backup:

$ psql -U postgres -f totalbackup.sql

Or restore a specific database:

  1. $ psql -U postgres (modify pg_hba.conf for authenticaiton);

  2. create role "sonar" and database "sonar":

create role sonar login password 'sonar';

create database sonar owner sonar;

  1. $ pg_restore -U sonar -d sonar -j 4 -v sonar.bak

Verify

  • Print database size of "sonar":

$ psql -U sonar -c "\l+" sonar|grep sonar

  • Print all tables and sizes in database "sonar":

$ psql -U sonar -c "\d+" sonar

Or run a psql console ($ psql -U sonar), and use the following commands to verify:

  • \d // list all tables

  • select * from projects; // print contents of table "projects"

  • select pg_size_pretty(pg_database_size('sonar')); // print the human_readable size of database "sonar"

  • select pg_database_size('sonar'); // give the exact size of database "sonar"



Published

Nov 29, 2013

Last Updated

Nov 29, 2013

Category

Tech

Tags

  • backup 8
  • postgresql 7

Contact

  • Powered by Pelican. Theme: Elegant by Talha Mansoor