Creating a PostgreSQL backup - pg_dump
Last updated: Jul 09, 2022
Colleagues hello to all.
In today's article, we will talk about how you can use the pg_dump utility to create database backups in PostgreSQL. pg_dump can create a copy of the database even when the database is being used in parallel and it will not interfere with other users when executing some queries. The pg_dump utility can only dump a single database, it does not dump global objects such as accounts and tablespaces. In order to unload global objects and accounts, you need to use another utility called pg_dumpall. I will talk about pg_dumpall utility in other articles.
- Create a directory for backups.
- Examples of creating a database backup.
1. Create a directory for backups.
My recommendation to you is to always back up the PostgreSQL database not far from the database itself. My database is in the /app/postgresql path, so I will create a directory for backups nearby and name the directory postgresql_backup.
$. mkdir -p /app/postgresql_backup
And don't forget to make the owner of the postgres directory. THIS IS IMPORTANT!
$. chown -R postgres:postgres /app/postgresql_backup
2. Examples of creating a database backup.
2.1. The easiest way to back up the demo database.
$. pg_dump --dbname=demo --file=/app/postgresql_backup/backup_demo.sql
2.2. Create a backup copy under another user and ask for a password for authorization.
$. pg_dump -U postgres -W --dbname=demo --file=/app/postgresql_backup/backup_demo.sql
2.3. Perform a database backup on a remote server.
$. pg_dump -h 192.168.4.10 --dbname=demo --file=/app/postgresql_backup/backup_demo.sql
2.4. Create a backup as an archive.
$. pg_dump -Ft --dbname=demo --file=/app/postgresql_backup/backup_demo.tar
2.5. Back up only the database structure itself without the data.
$. pg_dump --schema-only --dbname=demo --file=/app/postgresql_backup/backup_demo.sql
2.6. Back up only the data without the structure itself.
$. pg_dump --data-only --dbname=demo --file=/app/postgresql_backup/backup_demo.sql
2.7. Enable verbose mode when performing a backup.
$. pg_dump --verbose --data-only --dbname=demo --file=/app/postgresql_backup/backup_demo.sql
Thank you all, I hope that my article helped you in some way.