8

I am trying to understand best uses of PostgreSQL replication and how it works so I can troubleshoot in a production environment.

I am having a hard time understanding the differences between these 2 types of replication in terms of (1) Configuration (2) How the 2 servers Master/Slave perform in each scenario

Replication on PostgreSQL (9.2+) is essentially XLOG files of 16MB in size (depending on frequency settings for creating each file) are being created on Master and sent by some method to the Slave.

My Setup (for purposes of this question)

Configuration of Postgresql.conf on Master
archive_command= 'rsync -av %p postgres@[SlaveIP]:[wal_archive_folder]/%f'

Configuration of Recovery.conf on Slave to read log files
restore_command = 'cp [wal_archive_folder]/%f \"%p\"'
primary_conninfo = 'host=[MasterIP] port=5432 user=postgres'

My question is what part of this configuration makes this "streaming" replication versus "log shipping"? My master is configured to use rsync to send logs to the slave (is this log shipping?) My slave is configured to be able to connect to the master in recovery.conf (is this streaming?)

Second part of the question: What is happening? I understand there is another protocol on PostgreSQL via WAL_sender & WAL_receiver. But I am not clear if this is used for streaming only and if so, how is the rsync being used in the Master?

:) Thank you!! And sorry if this is an obvious question. I've been doing a bunch of reading blogs/books but having a hard time understanding. Postgres wiki is so in depth that it takes a long time to get through it all (and I have deadlines)

Improve this question
1
  • The wiki tends to be quite outdated, as well as in depth. It's often full of documents oriented toward development and feature design. The main user manual is usually a better resource for things like this.
    – Craig Ringer
    Aug 13, 2014 at 1:10

1 Answer 1

Reset to default
19

"Streaming replication" refers to continuous sending of WAL records over a TCP/IP connection between the master and the replica, using the walsender protocol over replication connections. The master reads its own WAL from pg_xlog and sends it to the replica on demand. It's configured with a primary_conninfo directive in recovery.conf and pg_hba.conf entries on the master to permit replication connections. You also need wal_keep_segments and some other options covered in the docs.

"Log shipping" refers to periodic sending of WAL records as whole WAL archives via a file transfer protocol to an archive location from which the replica can then fetch them. It's configured with a restore_command directive in recovery.conf and an archive_command in the master. PostgreSQL doesn't care where the files are or how they're transferred, only that the archive_command puts them there and the restore_command fetches the required archive; this permits the building of systems like PgBarman and WAL-E.

Streaming replication doesn't have as much lag, as records are sent as they are generated. However, it requires both master and replica to be online and able to communicate directly. It also requires the replica to keep up well enough that the master still has on-disk copies of the WAL the replica needs, and generally requires you to spend extra pg_xlog space on retaining extra WAL for the replica.

Log shipping replication has more lag because the replica only sees WAL once a whole archive is sent. However, it can work even when the master and replica can't communicate directly over TCP/IP by using a shared storage location. It continues to work even if the replica is down for a while, because the master will have discarded the WAL from pg_xlog only after archiving it, so the WAL is still in the archive and usable by the replica even though the master can't send it by streaming anymore. Note that archive_command never gives up, so pg_xlog can fill up if archiving is failing; for that reason it's better to archive to a reliable location and then have the replica server fetch from that location.

In general you actually combine the two, i.e. use both. In that case, streaming replication gets used when everything's going fine. If the replica gets too far behind and the master has discarded xlogs it requires, a connectivity problem arises, etc, then the replica will switch to reading archived WAL until it's caught up. It'll periodically re-try switching back to streaming until it succeeds.

If you're only going to use one, use log shipping, because streaming replication without log shipping fallback is (until PostgreSQL 9.4) potentially prone to replication lag causing failures that force a replica to be re-built .

PostgreSQL 9.4 changes this a bit, because streaming replication can now use "replication slots". That lets the master keep track of how much WAL a replica needs, and avoid throwing it away until the replica has replayed it. So there's no more need for wal_keep_segments if you use a replication slot (not the default).

See my article streaming replication slots in PostgreSQL 9.4.

9.4 also introduces the foundations for streaming logical replication, which is yet another mechanism, designed for use by logical replication systems like Londiste, Slony-I, and the new bi-directional async multi-master replication feature.

Improve this answer
4
  • Very helpful, I am wondering if you think this article: blogs.amd.co.at/robe/2009/05/… is on topic with my question. I have been told "log shipping is more stable" and this article seems to share that opinion.
    – Dina
    Aug 14, 2014 at 14:26
  • 1
    @Dina It's outdated at least, e.g. Log shipping has the drawback that the slave servers can't be used for queries as long as they are replicating data is wrong now. They can perform read-only queries if in hot_standby mode. Also, streaming and log shipping both use WAL, they're just different ways to transfer it. You can and should use log shipping to supplement streaming replication. Overall, the article is OK but not especially enlightening and a bit outdated; the official docs are a better resource.
    – Craig Ringer
    Aug 14, 2014 at 15:04
  • the answer is very helpful Chris, so your article (blog.2ndquadrant.com/postgresql-9-4-slots)
    – Max L.
    Jul 30, 2015 at 0:29
  • @Dina if once you are set up with streaming replication (which is asynchronous by default) you would like to configure your setup to synchronous replication, you can do this by setting the synchronous_standby_names parameter to a non-empty value, e.g.: standby_1. You do this on the primary server. Then, on the standby server, you modify the primary_conninfo setting by adding application_name=standby_1 e.g.: primary_conninfo = 'host=x port=y user=z application_name=standby_1'. This is from postgresql.org/docs/9.6/static/warm-standby.html, section 26.2.8.
    – dw8547
    Jun 6, 2017 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.