pg_dump tool is used to take single database backup in PostgreSQL
pg_dumpall tool is used to take backup of all databases in single script file of PostgreSQL Instance
- Plain Backup (SQL Format) Readable
- Custom Backup (Encrypted : .dump,.backup)
Syntax
pg_dump dbname > outfile pg_dump [options] dbname > outfile
[options]
-U : Specify user name to connect database
-p : port
-h : hostname (localhost,IP address)
-F : Format (Fp | Fc | Ft )
-Fp : Plain (SQL)
-Fc : Custom (.backup,.dump etc Encrypted )
-Ft : tar
However, the backup created from this method has limited feature.
pg_dump -U user_name -p port_number database_name > filename.sql
WHERE user_name : Name of the user which will connect database database_name : Name of the database whom you want to take database filename : Name of the file where backup will be created with location
EXAMPLE
pg_dump testdb > /opt/testdb.sql pg_dump -U postgres -p 5432 testdb > /opt/testdb.sql pg_dump -h 192.168.12.42 -U postgres -p 5432 testdb > /opt/testdb.sql pg_dump -U postgres -p 5432 testdb > /opt/testdb.sql -- Plain Backup (-Fp) pg_dump -U postgres -Fp testdb > /opt/testdb.sql pg_dump -U postgres -p 5432 -Fp testdb > /opt/testdb.sql -- Custom Backup (-Fc) pg_dump -U postgres -Fc testdb > /opt/testdb.backup pg_dump -U postgres -p 5432 -Fc testdb > /opt/testdb.backup pg_dump -U postgres -Fc testdb > /opt/testdb.dump
User postgres is taking testdb backup in testdb.sql file in /opt/ location on port 5432