Search code examples
postgresqldatabase-replicationpg-dump

Performance of PG_DUMP vs Replication with master/slave?


I need to backup my database and the 2 options I'm considering are:

  1. Use pg_dump to do daily dumps.
  2. Set up a slave database that will be a replication of the master.

I'm not asking which one is the best option, but rather:

  • How does the performance of these 2 compare?

    Intuitively, both are doing similar things, which is streaming all the data in 1 single process from 1 server to another.

  • Which one of the 2 has less of an impact CPU/load-wise on the master database server?

  • Which one will have less of an impact on the current queries being processed?


Solution

  • That's an apples to oranges type of question. pg_dump gives you a logical backup of your database, which you can manipulate or move to other systems to reload as an internally-consistent database. The replicated slave is an exact copy of the live master, which changes as the master changes. Now, if you wanted to go for a hybrid approach, you could set up replication between master and slave, and configure the hot standby so that you could take a pg_dump from the slave. That's the method I usually employ in production environments because of the flexibility and reduced impact on the master.