Postgres Get Replication Slots

Posted on

Replication slots and OOM errors. When Fivetran tried to read data from the slot during the next sync, Postgres has to provide all 100GB of data because we changed it rapidly.

What are PostgreSQL Replication Slots?

A replication slot is a feature in PostgreSQL that ensures that the master server will retain the WAL logs that are needed by the replicas even when they are disconnected from the master.

This article will focus on PostgreSQL replication slots. Here is an outline of this blog:

Postgres get replication slots games
  • Types of Replication Slots

Hevo Data, An Easier Way To Work With PostgreSQL

Hevo Data is a fully managed No-code Data Pipeline, which supports integrations with over a hundred different sources. You can move your data from PostgreSQL to any destination with ease. Checkout how Hevo might be perfect for your needs. Here are just a few of Hevo’s many awesome features:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call.

Logical replication can be used to replicate data between databases in a single Postgres cluster. It’s a perfectly valid setup, but it requires special treatment: you have to create logical replication slot first, and with the slot already in place, create a subscription pointing to that slot. Streaming replication was introduced in Postgres 9.0. Physical Replication Slots. In Postgres 9.4, replication slots were introduced. A process receiving changes via streaming replication can create a replication slot on the primary server. The primary will then use this slot to store the location up to where the changes have been sent to the. Alternative approaches of not using replication slots is to configure PostgreSQL with continuous archiving and provide a restorecommand to give the replica access to the archive. To avoid WAL build-up on the primary, you may use a separate volume or storage device for the WAL files, e.g., SAN or NFS. Logical replication. From Postgres version 9.4, you can set up logical replication slots at RDS PostgreSQL instance, and stream database changes. AWS Database Migration Service (AWS DMS) provides the most common use case of logical replication. Logical replication uses logical slots that remain ignorant of the recipient.

You can, in fact, try it out first by signing up for the free 14-day trial. You can experience how easy it is working with Hevo!

What are WAL Files?

Postgres Get Replication Slots Software

Write-Ahead Log (WAL) is a record of the changes made to the data. It ensures that when there is a crash in the system or loss of connection, the database can be recovered. When you make changes to the database, WAL files keep building up. WAL logs are stored in the pg_wal directory as a set of segment files.

The purpose of keeping the WAL files is you can recreate the database from scratch by replaying all the changes recorded in the WAL files.

Now let us see why PostgreSQL replication slots are needed.

Why PostgreSQL Replication Slots are Necessary?

If you have a master and some hot or archiving standbys, and you are running streaming replication between them, a replication slot is used to retain the WAL files even when the replica is offline or disconnected.

This feature was introduced in PostgreSQL 9.4. Prior to that, if a standby went offline, until it came back online, a set of WAL files had to be kept by the master. This is done by setting the wal_keep_segment correctly or high enough to keep the replica from falling behind too much.

If this is not done, then the standby can’t keep up with the master after the master deletes the WAL files that the standby is yet to replay and you will get an error.

The problem with setting the correct wal_keep_segment is, it is difficult to know exactly how many WAL files need to be kept. This is guesswork and if it is set too high, the master will exhaust the space in the pg_wal directory (WAL files are stored in pg_wal directory. Prior to version 10, they were stored in the pg_xlog directory).

Alternatives to the above method are either WAL archiving which means you have to write a script to move the WAL files to another long term location or create the standby that has fallen behind again from scratch.

After replication slots came in with PostgreSQL 9.4, you don’t have to worry about wal_keep_segments because replication slots make sure that the WAL files are kept forever. If it so happens that the standby goes offline, the master can keep track of how much the standby lags and retain the WAL it needs files until the standby reconnects again. Then the WAL files can be decoded and replayed to the replica.

Limitations of PostgreSQL Replication Slots

The WAL files are retained by the master when the replica disconnects. This also means that the pg_wal directory may run out of space. Imagine a scenario where the replica fails forever and cannot be recovered (an orphaned replication slot), or when a replica cannot replay the WAL segments fast enough. The WAL files will just pile up. So you need to monitor the slots and manually drop them. Only when you do that will the master delete anything form the pg_wal directory. We will see how you can monitor and drop replication slots later.

Postgres Get Replication Slots Key

So while WAL retention is taken care of without manual settings, pg_wal directory space needs to be manually monitored.

Types of Replication Slots

PostgreSQL Replication slots are of two types:

  • Physical replication slots
  • Logical replication slots

Physical Replication Slots

The changes that take place on the main server via streaming replication are recorded in the WAL segments. These WAL files are sent to the standby server and then replayed.

So a physical replication slot can be created on the primary server and the location up to where the transactions have been sent to the standby is stored. Now when the standby loses connection, the primary server will keep those WAL files.

Logical Replication Slots

Logical replication was introduced in PostgreSQL 10. Logical replication brings over only the SQL-like changes. It does not work without replication slots. Logical replication data has to be decoded using a plugin.

How to Create a Replication Slot?

The function pg_create_physical_replication_slot that is used to create a physical replication slot. This command has to be run in the master node.

Let us create a replication slot by the name ‘ocean’.

How to Monitor a Replication Slot?

The below command displays all the replication slots that exist on the database cluster.

These are the columns that you will see in the pg_replication_slots view:

  • slot_name: This is a unique identifier of the replication slot which can contain lower-case letters, underscore characters, and numbers.
  • plugin: For physical slots, it will be null.
  • slot_type: Slot type is a text indicating whether the slot is physical or logical.
  • datoid: Physical slots have no associated databases and hence is null. For logical slots, it will be the OID of the database this slot is associated with.
  • active: this is a Boolean value. It is True if the slot is currently active and is false if it is inactive.
  • xmin: This represents the oldest transaction this slot requires the database to keep.
  • catalog_xmin: This is the oldest transaction this slot needs the database to retain that affects the system catalogs.
  • restart_lsn: Log Sequence Number (LSN) is a unique identifier in the transaction log. Restart_lsn is the oldest WAL which might be needed by this slot.
Postgres get replication slots key

How to Delete a Replication Slot?

You already know that replication slots have to be created and deleted manually. Do not let the inactive slots stay because the master will retain the WAL files needed by the inactive slot indefinitely and will fill up space in the disk.

This is the command used to delete a replication slot:

Conclusion

Replication

The article introduced you to PostgreSQL replication slots, their purpose, and their limitations. But when it comes to working with replication, you need to be an expert at PostgreSQL to set up servers from scratch and manually configure several details.

Most of the time, the data is not available in the right format and you will need data engineering and PostgreSQL administration skills to transform the data.

You don’t really have to deal with such problems if you use a fully managed ETL tool like Hevo. You don’t have to worry about the dreaded configuration problems with Hevo. It is a No-code platform and has a simple user interface. It supports complex transformations and it is fast!

Why don’t you try it out for free here? You can continue if you like it!

Postgres Get Replication Slots Free

Share your thoughts on PostgreSQL replications slots in the comments below!