Postgresql Backup and Restore Database or Table

Backup and Restore

• When login as the postgresql admin user, you don't need a user name when do the backup or restore (Don't need -U username)
• When you are login as another user name (not postgresql admin or user), you will need a user name in order to do the backup and restore.

Command line

Backup

// backup databasename and store in the filename
$pg_dump databasename -f filename

// backup all the databases to the filename
$pg_dumpall filename

// backup tablename in the databasename and store in filename
$pg_dump --table tablename databsename -f filename

// backup databasename and store in filename login as username
$pg_dump -U username databasename -f filename

// backup only single table
$pg_dump -U username -a -t tablename databasename -f filename

Restore

// restore databasename from filename and login as username
$psql -U username databasename -f filename

// restore databasename from filename
$psql -d databasename -f filename

// restore to the databasename from tablefile (tablefile contains only a single table)
// this use to restore for a single table
$psql -U username -d databasename -f tablefile

Login to the postgresql admin user

[email protected]# su postgres

List all the databases

[email protected]:$ psql -l

Backup a specific database

[email protected]:$ pg_dump totaradb -f /tmp/bk_28_9_12.sql

Backup all databases

[email protected]:$ pg_dumpall > /tmp/all.sql

Backup a specific table

Backup table tblUser in article database.

[email protected]:$ pg_dump --table tblUser article -f /tmp/tblUser.sql

Restore Database

[email protected]:$ psql -d totaradb -f bk_28_9_12.sql