22

There are two different tools in PostgreSQL server:

  • pg_dump
  • pg_basebackup

What is the difference between these tools?
Which one to use to create database backup?

toraritte
  • 6,300
  • 3
  • 46
  • 67
Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

1 Answers1

32

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 3
    Why do I need WAL archives for a backup created by pg_basebackup? when as stated: "while pg_basebackup always backs up the whole cluster" ? – beatrice Feb 09 '21 at 19:54
  • 8
    @beatrice Because the backup is not consistent - the files changed while they were copied. – Laurenz Albe Feb 10 '21 at 06:51
  • 2
    can `pg_dump` and `pg_basebackup` running concurrently ? say I have crontab running pg_dump on the master at 1 am. I also have `pg_basebackup`, that pulls data from the master, run from the slave from 10 pm to 3 am. so the processes intersect at 1 am. is it "dangerous" ? thanks @LaurenzAlbe – padjee Oct 25 '22 at 14:27
  • 4
    @padjee No, that's no dangerous. But both `pg_dump` and `pg_basebackup` will stress the server, so the performance of your normal workload may suffer. – Laurenz Albe Oct 25 '22 at 14:49