PostgreSQL: backup with pg_dump and pg_dumpall

We had seen how save a database PostgreSQL with the graphical tool pgAdmin this is the command line version. An efficient and more suitable alternative to regular processing, for example used by an application developer, a system administrator and of course a DBA (database administrator). The DBA of production and DBA development PostgreSQL must already know all these parameters.

This tutorial explains how to backup (export) and restore (import) a database on a server PostgreSQL with the commands pg_dump and pg_dumpall (dump all). A simple method advised by the SGBD to create dumps in a few actions. We can also script this operation to automate it in order to create automatic backups of databases PG SQL .

The main difference between pg_dump and pg_dumpall is the following: pg_dump saves a database, pg_dumpall does a full backup of the instance with the BDD , objects, etc.

Backup options pg_dump and pg_dumpall for PostgreSQL

pg_dump -F p | t | t | c | d choose the format between:

  • p: plain, SQL
  • t : tar
  • c: custom (specific format PG SQL )
  • d: directory

pg_dump -f specify the file where the backup will be created (without -f, pgdump uses the standard output folder)

pg_dump -s (for -schema-only): exports only the structure of the database

pg_dump -a (for -data-only) : only backs up the data of the BDD

pg_dump -n Selects only the schema

pg_dump -N takes all schemas except the one indicated

pg_dump -C Integrates the command to create the database during restoration

pg_dump -t

just save this table

pg_dump -T

Save all tables except the one indicated

pg_dump -j Parallelization of the job, indicate a number of threads

pg_dump -O ignore the owner

pg_dump -x ignore rights: ignore rights

pg_dump -no-role-passwords does not save passwords

pg_dump -no-tablespaces ignore tablespaces: ignore tablespaces

pg_dump -inserts replace: replace COPY through INSERT

pg_dump -v see the progress of the action

Examples of commands pg_dump and pg_dumpall

1. Open a Command Prompt ( cmd ).

2. Go to the folder of PG SQL : cd D:\pgsql\bin or cd /opt/pgsql/bin

Or, in Linux, indicate the variable in the PATH export: export PATH =/usr/local/pgsql/bin:$PATH

3. Examples of Windows commands. For Linux, remove the “.exe”:

pg_dumpall.exe -U username > D:\Backup\dumpall

-> Will save all databases and objects (users, tablespaces…) in the dumpall file.

pg_dump.exe -U username -C -F c -f D:\Backup\NomBase.dump NameBase

-> Will save the database DatabaseName in the D:\Backup folder in the proprietary format PG SQL (well compressed and fast to run).