Creating a PostgreSQL backup - pg_dump

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.

Article content:

1. Create a directory for backups.
2. 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.