opendbteam workspace

opendbteam workspace
opendbteam workspace

Tuesday, May 3, 2016

Streaming replication slots in #PostgreSQL 9.4 / 9.5

Streaming replication slots are a pending feature in PostgreSQL 9.4, as part of the logical changeset extraction feature.
What are they for, what do you need to know, what changes?

What are replication slots?

Streaming replication slots are a new facility introduced in PostgreSQL 9.4. They are a persistent record of the state of a replica that is kept on the master server even when the replica is offline and disconnected.
They aren’t used for physical replication by default, so you’ll only be dealing with them if you enable their use. This post explains what they do, why they’re useful for physical replication, and why they’re necessary for logical replication.

Why slots?

As part of the ongoing work Andres has been doing on log-streaming logical replication, changeset extraction, and bidirectional replication, there’s been the need to better control WAL retention.
As streaming replication currently works, the master doesn’t make any effort to retain extra WAL for a standby that’s fallen behind. If the standby gets too far behind, the master will delete WAL segments the standby still needs to replay, and the standby cannot recover. You get an error like:
ERROR:  requested WAL segment 00000001000000010000002D has already been removed
To prevent this, you are currently expected to configure continuous archiving and provide a restore_command to give the replica access to the archive, or be willing to re-create a standby that falls behind.
A less reliable alternative is to set wal_keep_segments to a “high enough” value to ensure that the replica never falls too far behind – but there’s no real way to guess what is high enough, and the higher the value is set the greater the risk the master will be able to run out of space in pg_xlog. So generally archiving is preferred.
The master could retain WAL for a replica while it’s connected, but as soon as the replica disconnects for any reason the master forgets all about it. As transient disconnects are common, this means there’s not much point in trying to retain WAL just for connected replicas.
We’ve gotten away with this so far because the option of falling back to continuous archiving has been available for when the master discards WAL the replicas still need. When combined with or scripts likeWAL-E, a shared network file system, or a shared host to scp archives to/from, it’s done the job pretty well. It’s a bit confusing for new users, but it works well.
With log streaming logical replication we can’t fall back on continuous archiving anymore, because the master must read WAL and decode it to send the results to the replicas; it can’t just send raw WAL records. Replicas cannot just read raw WAL from the master and extract what they require themselves because only the master has a record of transaction ID statuses, the local oids of objects, etc.
We could just teach the master to run restore_command (something people seem to assume happens anyway) – but that could get very inefficient when many replicas want the same WAL archive. We’d need a caching system and other complexity. It also doesn’t get rid of the need for the admin to manage retention for archived WAL.
Unlike with physical replication, the option of just re-seeding a replica that has fallen too far behind is not acceptable for logical replication. There may be other databases on the replica host that contain their own original data. In the bidirectional replication case, the replica database may also contain newdata not yet replicated to other nodes. So we can’t just write the replica off and make a new pg_basebackup from the master or re-rsync it like we do with physcial replication.
The introduction of slots is intended to deal with this issue by giving the master persistent knowledge of the state of replicas, even when they are disconnected. While it was added for logical replication, the concept is useful in physical replication as well, as it lets the master retain as much WAL as is required by all its standbys and no more. There’s no guesswork like there is with wal_keep_segments. There’s no need to set up a separate WAL archiving system and manage retention in it. So long as you have enough space in pg_xlog for the extra WAL, it just works. If a replica disconnects the master can keep WAL for it until it reconnects and replays the WAL.
The downside of this approach is that it adds another way for unbounded WAL growth to fill pg_xlog – when a replica cannot receive or replay WAL fast enough or remains offline for a long time, the master will keep on retaining WAL until pg_xlog fills up and it starts reporting errors to clients. This can already happen on a master with long checkpoint intervals under heavy load, but it’s much easer to trigger with slots as a replica that goes ofline indefinitely will cause pg_xlog to fill up.
While monitoring and adjustment of pg_xlog capacity is already necessary for a busy PostgreSQL server, it is much more so for a server that is using slots to manage WAL retention for replication.

Operational impact

By default, physical replication will not use slots. Nothing changes. You must use WAL archiving on top of streaming to ensure reliable replication.
You can enable the use of replication slots for physical replication with the primary-slotnameparameter in recovery.conf. The physical replication slot must already have been created on the master with the pg_create_physical_replication_slot(...) function; see the docs.
When using slots to manage physical replication, WAL archiving is no longer required for replication (though you may wish to retain it for PITR anyway). You don’t need to deal with the difficulties of managing WAL retention amongst multiple replicas that may or may not be online, with replay positions that are not known to the master when offline. You don’t need to manage the space in the archive store.
Instead, you need to keep an eye on space in pg_xlog. WAL retention is managed automatically for you, but you must ensure there’s enough space available.
If a replica is retired from service or unrecoverably lost due to failure of the host, you must manually remove the slot from the master because the master won’t delete any xlog until you do. If you have a system where the master produces xlog rapidly, this may require a lot of pg_xlog space or fairly quick action. A monitoring system like Icinga or Zabbix is strongly recommended.
A replica that is unable to keep up the master must be fixed or retired to prevent the master from running out of xlog space.
With slots you no longer need to monitor the replicas to determine their state, as accurate and up to date information is always available in the pg_replication_slots view. So all you need to do is monitor the master’s pg_xlog space and the status of all replication slots.
Any wal_keep_segments parameter will be respected as a minimum for WAL retention.
So: using slots for physical replication is a trade-off. You don’t need archiving anymore, but you have to monitor the state of the system more closely to avoid disrupting the master.

Future work

To reduce the need for a large pg_xlog space allocation on what could be expensive high-performance storage, in future slots may be extended to support moving WAL to a separate location once it’s no longer required for the master’s own checkpointing.


  1. Really nice post about the overall development for job applicants especially people expecting govt jobs will like this site very much. Thanks a lot.

  2. snapdeal is now offering Various snapdeal coupons and also you can get latest deals andsnapdeal coupon codes. checkout once


  3. Recruitment Notification is one of the Top most Job Portal Govt jobs karnataka notificationsthere

  4. Thank u for Sharing this Information. For more cashback Offers And cashback coupons just ping me

  5. At first those web slots were the clones of Las Vegas slots. The creators of the first web slots were so resolved to maintain the original format and feel that they practically copy-pasted the boxy look to our computer screen.

  6. Actually, with a basic count, if 50 clients watch your video stream or your web video communicate consistently for a month; it can use as much as 50 GB of data transfer capacity from your server and from your specialist iptv service 2019

  7. In the past few years, the streaming media services has evolved as a vital segment of the internet experience for both enterprise users and consumers. movie box

  8. This comment has been removed by the author.

  9. Movie streaming is delivered to users with terrarium tv. Users can now interact with the application to install terrarium tv for all their free devices: