I need to backup my database and the 2 options I'm considering are:
pg_dump
to do daily dumps.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?
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.