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)
Backups in Timescale are fully automated. Learn how Timescale handles database backups and disaster recovery, and create a free Timescale account today.
Database backup (with customized 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 the 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)