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)