Postgres Get Replication Slots

Hi All,

The pgreplicationslots view provides a listing of all replication slots that currently exist on the database cluster, along with their current state. For more on replication slots, see Section 26.2.6 and Chapter 48. Pgreplicationslots Columns. 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. Gary's presentation on logical decoding of replication slots. Brisbane PostgreSQL Users' Group (BPUG): http://www.meetup.com/Brisbane-PostgreSQL-User-Group/.

Am back again with another topic … “Replication Slots” …

What is Replication Slots and Why to use it ?

To understand replication slots, we need to understand Logical Decoding.
Logical decoding is decoding contents of write ahead log, by which you can identify database changes in storage level in the form of stream of tuples or SQL statements. And a replication slot will represent this stream of changes which can be replayed on the client in the same order as made on primary server. Each slot is corresponds to single database.Multiple independent slots may exist for a single database. Output plugin is required to transform the data from the write-ahead log’s internal representation into the format the consumer of a replication slot desires.

When a new replication slot is created using the streaming replication interface, a snapshot is exported by using pg_export_snapshot(), which will show exactly the state of the database after which all changes will be included in the change stream.

Streaming Replication Slots(Physical Slots)::
—————————————————————–

It ensures that the master doesn’t remove WALs or rows from database until they have been received by all the standby. We can also manage this WAL removal by tuning parameters wal_keep_segments or by archiving but in some cases it may keep more WAL segments, but in case of replication_slot we can keep only those WAL files which are required.

Now we can start with actual configuration of replication slots::

1>> Replication Slot creation::
Please make sure for the following configuration in your primary server
max_replication_slot > 0
wal_level >= archive

Then execute following statement for creating replication slots::
Select * from pg_create_physical_replication_slot(‘test_slot’);

2>> Once you configured your primary server for physical replication slots, now you need to configure your receiver server as logical replication slot knows nothing about the state of the receiver(s).
(Here, am considering that your streaming replication is already setup and running. If not please setup your streaming replication)

Please add following line about replication slot in your recovery.conf file of standby server.
primary_slot_name = ‘test_slot’

3>> Once you are done with adding slot information into your recovery.conf and by starting your server. You will get changed values for columns of query “select * from pg_replication_slots ;” for column active from “f” to “t” and restart_lsn from blank to value.

Note :: As documented on link,
Replication slots persist across crashes and know nothing about the state of their consumer(s). They will prevent removal of required resources even when there is no connection using them. This consumes storage because neither required WAL nor required rows from the system catalogs can be removed by VACUUM as long as they are required by a replication slot. So if a slot is no longer required it should be dropped.

logical slots ::
——————–
Logical decoding uses the replication slots and decoding plugins to send the changes in the db and make them understandable to external agents. To see the changes, one can use the pg_logical_slot_get_changes and pg_logical_slot_peek_changes functions. The difference between the two functions is that the first consumes changes in the queue and the second reads them only, without removing them. The output of the function depends on the output plugin used to create the slot. “test_decoding” plugins is default plugin developed.

Before you can use logical decoding, you must set wal_level to logical and max_replication_slots to at least 1.

1>>> Create Replication slot and check pg_replication_slots as per below snapshot::

In the above slot information you can see one slot is active and the newly created slot is not active yet. And the slot which we created previously is physical and the one which we created recently is logical.

2>>>
Note:: Make sure that you can destroy a slot you no longer need to stop it consuming server resources.

PostgreSQL 9.4 and later come with a feature called “replication slots”.They can be used to make WAL archiving and streaming replication morerobust and efficient. Read on to learn more, with hands-on examples.

WAL Files

Postgres Get Replication Slots Games

PostgreSQL provides ACID semantics for your data by sequencing transactionsinto a long series of changes, like an ever-growing changelog. Descriptionsof these changes are encoded into “WAL files” – a series of ordered 16MB binaryfiles stored alongside other files inside the server’s data directory.

This means that if you keep around all the WAL files since the creation of thedatabase, you can recreate that database by reading and replaying the changesfrom the WAL files. This is the basis for streaming replication, WAL archivingand Point-In-Time-Recovery (PITR) in PostgreSQL.

(Note: some objects, like unlogged tables and hash indexes are not captured inWAL files – but that does not compromise the integrity of the data.)

WAL stands for Write Ahead Log, by the way.

Archiving WAL Files

Copying out WAL files as they are generated, into a “safe” location is calledWAL archiving. A “safe” location istypically an NFS-mounted disk, file servers, S3 bucket etc.

Traditionally, a user-supplied script is invoked by PostgreSQL each time aWAL file is generated. The script then copies (using rsync, scp or the like)the file to one or more safe locations.

The WAL files stored in the safe location becomes a form of incrementalbackup from which a database can be recovered up to any specified point intime.

Log Shipping

Instead of copying the WAL files to a safe location, it can instead be copiedto another machine where another PostgreSQL server is running. This servercan be configured to be in “recovery mode”, meaning all it does is to applyany new WAL files that it gets. This second server then, becomes a “standby” –it will be an identical, warm backup of the “primary” PostgreSQL server.

(The standby can also be made a “hot standby”, where it can also serve read-onlyqueries, thus being a “read replica”.)

The process of “shipping” WAL files (rather than “archiving” them) to one oremore standby servers is called “log shipping”. Typically, log shipping is alsodone using the same archive script as before – it copies newly-created WALfiles at the primary over the network to the standby servers.

Streaming Replication

Streaming replication was added to PostgreSQL inorder to improve the logshipping process. With streaming replication, the standby server will connect tothe primary server over the network (using a different protocol than thestandard client protocol). The primary server can then send WAL file contentsdirectly over this connection without having to rely on user-supplied scripts.

This also allows for better tracking. You can know for example, how far thestandby has caught up with the primary in terms of receiving, applying andreplaying the changes from the primary.

Streaming replication can be, and is popularly, used to maintain a failover-readystandby that can be promoted to the primary in case the primary fails.

WAL File Retention

Which brings us to the topic of how long the primary should retain WAL files.

Typically, you’ll have a WAL archival script that will copy WAL files to oneor more long-term archive locations. If you don’t have any standbys or otherstreaming replication clients, then basically the server can discard the WALfile once the archive script reports success. You’ll still need some recentWAL files for crash recovery (data from recent WAL files get replayed duringcrash recovery).

(BTW: PostgreSQL does not actually discard the file, it renames and recyclesthem.)

If you have standbys though, there is a problem – you need to keep aroundWAL files long enough for as long as your slowest standby needs them. If thestandby that was taken down for a while comes back online and asks the primaryfor a WAL file that the primary no longer has, then the replication fails.

The logical solution is to make the primary to keep track of how far behind thestandby is, and to not delete WAL files that any standbys still need.And this feature is called replication slots.

Replication Slots

(Note: there are “physical” and “logical” replication slots. We cover only“physical” replication slots in this article.)

Replication slots are uniquely identified by names. Each slot is associatedwith:

• the oldest WAL required by the consumer of the slot - WAL files later thanthis are not deleted during checkpoints
• the oldest transaction ID required to be retained by the consumer of the slot -rows needed by any transactions later than this are not deleted by vacuum

Let’s try it out. First, we need a new database to experiment with:

Make sure that data/pg_hba.conf allows normal and replication connections:

(This is about as insecure as you can get, but works great for experimenting!)Let’s start the server now:

Creating a Replication Slot

Now that we have a running PostgreSQL server, let’s create a replication slot.We can do this using the pg_create_physical_replication_slot function.

Let’s also run pgbench for some traffic. Make a database first:

Then initialize and run pgbench:

Now that we have a pgbench busy in the background, we can see that the serveris generating WAL files:

Receiving WALs Using a Slot

We can receive these WAL files using the pg_receivewal command (on versionsbefore 10, this is pg_receivexlog). Let’s run it now, and ask it to use the“slot1” that we created earlier:

Postgres Get Replication Slots Key

We created a directory called “wals” and asked pg_receivewal to fetch and storethe WALs into this directory. Since this is the first time we’re using the slot,pg_receivewal starts fetching from the WAL location at the time it was started – in this case from 0/17000000. When it has fetched 17, 18 completely and ispartway through 19, here is how the “wals” directory looks:

There is a catalog view called pg_replication_slots which gives youinformation about these slots:

PostgreSQL will not remove WAL files prior to “restart_lsn”, even at the riskof filling up the disk.

Let’s stop pg_receivewal for a while now, and let pgbench do more transactions:

More pgbench:

After the pgbench, the current WAL has crept up from 19 to 2B:

while our slot remains at 19. Let’s see what happens when we restartpg_receivewal:

The server had been saving up WAL files from 19 onwards for slot1’s sake, whichit serves up immediately on the consumer’s startup, and the consumer thencontinues to wait for more. The server’s WAL directory (data/pg_wal in v10,data/pg_xlog in earlier versions) can be checked to verify whether the oldfiles were retained or deleted as intended.

WAL Archiving with Slots

What we just did was in fact WAL archiving – saving a copy of every WALgenerated by the server. We did not use the traditional “archive_command”method, but the set of WAL files that we have are just as good for restorationand PITR.

Streaming Replication with Slots

Let’s try doing streaming replication using slots. First, let’s create anotherslot:

We added a “true” parameter to the function this time – this sets the“restart_lsn” of the slot to the current REDO LSN. If we takea “pg_basebackup” at this point, the data files will be guaranteed to containchanges up to REDO LSN. Having the slot’s “restart_lsn” set to this value willguarantee that our new standby, bootstrapped from a pg_basebackup will beable to fetch all changes until it is ready.

Let’s get the backup next. We’ll use the “-S / –slot” parameter to supply theslot name to the pg_basebackup command:

Because we created the slot earlier, changes made between the time the slot wascreated, and the completion of the pg_basebackup command, will be includedin the newly created “data2” directory. pg_basebackup informs us that it waswritten WAL 32 partially, and pg_replication_slots confirms that the restart_lsnhas advanced in sync:

The recovery.conf for the standby should contain the slot name (we made thepg_basebackup write this using the -R flag):

The “primary_slot_name” make the standby use that slot for it’s streamingreplication. Let’s start the standby now:

We hit a checkpoint in between the backup and the start, so the WAL file hascrept up to 33.

Examining Replication Status

We now have two active replication slot consumers (pg_receivewal and thestandby). We can check the replication status using thepgmetrics tool:

Where to go from here

Replication in PosgreSQL represents a whole lot of features, knobs and leversaccumulated into a stable codebase over more than a decade. Understandably, ittakes a lot of reading and experimenting to get to any level of comfort with it.

Read more about replication and standby servers in the docs here.

We didn’t cover temporary physical replication slots,a feature added in v10. pg_basebackup can also make use of such slots bydefault.

We also didn’t cover logical replication slots or logical decoding.

Postgres Get Replication Slots Free

Monitoring PostgreSQL With OpsDash

With our own product,OpsDash, you can quickly start monitoring yourPostgreSQL servers, and get instant insight into key performance and healthmetrics including replication stats.

Here’s a default, pre-configured dashboard for PostgreSQL.

Postgres Get Replication Slots Software

OpsDash strives to save you the tedious work of setting up a useful dashboard.The metrics you see here were carefully chosen to represent the most relevanthealth and performance indicators for a typical PostgreSQL instance.

OpsDash understands the streaming replicationfeature of PostgreSQL and displays per-slave replication status on the master:

The replication lag (as a length of time) is also visible on a slave’s dashboard.In the graph below (part of the dashboard of a slave), we can see that the slavecould not catch up to the master’s changes for a while.

Sign up for a free 14-day trial of OpsDash SaaStoday!