Replication Slots In Postgres… !!!

2-elephants

Hi All,

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’);

1_OK
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’

recovery

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.

3_Ok
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::

5_okIn 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>>>
6_ok
Note:: Make sure that you can destroy a slot you no longer need to stop it consuming server resources.

Advertisements

3 thoughts on “Replication Slots In Postgres… !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s