Backup
Database Backup (With default options)
$ pg_dump mydb > mydb.bak.sql
// Create a backup for a database “mydb” in plain-text SQL Script file (mydb.bak.sql) (pg_dump)
Database Backup (With Customised options)
$ pg_dump -c -C -F p -f mydb.bak.sql mydb
// Creates a backup for a database “mydb” in plain text format with drop & create database commands included in output file mydb.bak.sql (pg_dump)
Backup options:
– -c: Output commands to clean(drop) database objects prior to writing commands to create them
– -C: Begin output with “CREATE DATABASE” command itself and reconnect to created database
– -F: Format of the output (value p means plain SQL output and value c means custom archive format suitable for pg_restore)
– -f: Backup output file name
Remote Backup
$ pg_dump -h <remote_host> -p <port> -U <user> -f mydb.bak mydb
// Running pg_dump on client computer to back up data on a remote postgres server (pg_dump)
Use the -h flag to specify the IP address of your remote Host and -p to identify the port on which PostgreSQL is listening:
All databases backup
$ pg_dumpall > alldb.bak.sql
// Backup of all databases along with database roles and cluster wide information. (pg_dumpall)