PostgreSQL 9.4 – Configuring Replication Slots

PostgreSQL 9.4 – Configuring Replication Slots

PostgreSQL 9.4 introduce a very useful feature called Replication Slots. Previously, we had to configure a high value for WAL_KEEP_SEGMENTS parameter in postgresql.conf. This is done via estimation based on delta change on the master database. Alternative, we could also configure the ARCHIVE_COMMAND instead to move it to another location for the slave to pick it up. Both methods require a larger than required capacity to store the WAL archives. This new feature is just plain cool 🙂

You can read more about it over here.

Step 1: Modify MAX_REPLICATION_SLOTS parameter in postgresql.conf on master

1.1) max_replication_slots = 0 =>max_replication_slots = 3
1.2) pg_ctl restart

Step 2: Create the replication slot on master

postgres=# SELECT * FROM pg_create_physical_replication_slot('postgres_slave');
 slot_name | xlog_position
 ----------------+---------------
 postgres_slave |

Step 3: Configure PRIMARY_SLOT_NAME in recovery.conf on slave

# echo "primary_slot_name = 'postgres_slave' >> recovery.conf
# pg_ctl restart

Step 4: Verify configuration on master

postgres=# SELECT * FROM pg_replication_slots;
 slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
 ----------------+--------+-----------+--------+----------+--------+------+--------------+------
 postgres_slave | | physical | | | t | | | 1/50002B0

Cheers,
Wei Shan

Advertisements
  1. Leave a comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: