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
just save this table
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).