Menu
There are two different tools in PostgreSQL server: pg_dump, pg_basebackup
What is the difference between this tools?
Which one to use to create database backup?
448
There are three fundamentally different approaches to backing up PostgreSQL data: SQL dump. File system level backup. Continuous archiving.
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). pg_dump only dumps a single database.
The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump. Normally that is no big problem unless you have very high write activity in the database.
PostgreSQL offers pgAdmin Backup Database services that allow companies to maintain a backup of their data and avoid any data loss in the future. pgAdmin Backup Database makes it easier for users to easily maintain the backup and restore functionalities in PostgreSQL.
pg_dump creates a logical backup, that is a series of SQL statements that, when executed, create a new database that is logically like the original one.
pg_basebackup creates a physical backup, that is a copy of the files that constitute the database cluster. You have to use recovery to make such a backup consistent.
The main differences are:
pg_dump typically takes longer and creates a smaller backup.
With pg_dump you can back up one database or parts of a database, while pg_basebackup always backs up the whole cluster.
A backup created by pg_dump is complete, while you need WAL archives to restore a backup created with pg_basebackup (unless you used the default option -X stream, in which case the backup contains the WAL segments required to make the backup consistent).
With a logical backup you can only restore the state of the database at backup time, while with a physical backup you can restore any point in time after the end of the backup, provided you archived the required WAL segments.
You need pg_basebackup to create a standby server, pg_dump won't do.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
