PostgreSQL Administration: Streaming Replication Slots in PostgreSQL 9.4

The bellow Nine steps will give the configuration steps for Streaming Replication Slot with cascading in PPAS-9.5

Let’s go with hands on. Here I am going to choose one master and two slaves.

Master IP : 192.168.205.182 Port:5446

Slave IP : 192.168.205.181 Port:5447

Slave 1 IP : 192.168.205.181 Port:5448

Step 1

To configure streaming replication slot , edit postgresql.conf and pg_hba.conf as follows on the master server.

pg_hba.conf

Step 2

[enterprisedb@localhost ~]$ psql -p 5446

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

(0 rows)

edb=# select pg_create_physical_replication_slot('test_1');

pg_create_physical_replication_slot

-------------------------------------

(test_1,)

(1 row)

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

test_1 | | physical | | | f | 90372 | | | 1/37000140

(1 row)

Step 3

[enterprisedb@localhost ~]$ pg_basebackup -P -R -X stream -c fast -h 192.168.205.182 -U enterprisedb -p 5446 -D /opt/slave/

43626/43626 kB (100%), 1/1 tablespace

# These settings are ignored on a master server.

hot_standby = on # "on" allows queries during recovery

[enterprisedb@localhost ~]$ vi /opt/slave/recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb host=192.168.205.182 port=5446'

primary_slot_name= 'test_1'

trigger_file = '/tmp/trigger_test'

Step 4

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave/ start

server starting

[enterprisedb@localhost ~]$ 2016-04-30 03:46:31 PDT LOG: redirecting log output to logging collector process

2016-04-30 03:46:31 PDT HINT: Future log output will appear in directory "pg_log".

Type "help" for help.

edb=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

(1 row)

[enterprisedb@localhost ~]$ psql -p 5446

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

test_1 | | physical | | | t | 90372 | | | 1/37000140

(1 row)

edb=# select * from pg_stat_replication;

-[ RECORD 1 ]----+---------------------------------

pid | 90372

usesysid | 10

usename | enterprisedb

application_name | walreceiver

client_addr | 192.168.205.181

client_hostname |

client_port | 51708

backend_start | 30-APR-16 03:46:31.452655 -07:00

backend_xmin |

state | streaming

sent_location | 1/37000220

write_location | 1/37000220

flush_location | 1/37000220

replay_location | 1/37000220

sync_priority | 0

sync_state | async

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

Step 5

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave/ stop -mf

waiting for server to shut down.... done

server stopped

[enterprisedb@localhost ~]$ psql -p 5446

psql.bin (9.5.0.5)

Type "help" for help.

edb=# insert into stest values(generate_series(1,10000000),('sample'));

INSERT 0 10000000

edb=# insert into stest values(generate_series(10000001,20000000),('sample'));

INSERT 0 10000000

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

100

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

102

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

105

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

107

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

108

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

Step 6

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave/ start

server starting

[enterprisedb@localhost ~]$ 2016-04-30 04:00:49 PDT LOG: redirecting log output to logging collector process

2016-04-30 04:00:49 PDT HINT: Future log output will appear in directory "pg_log".

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

102

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

106

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

107

[enterprisedb@localhost ~]$ ls -lrth /opt/slave/pg_xlog/|wc -l

After slave start on master files as follows :

[enterprisedb@localhost ~]$ ls -lrth /opt/master/pg_xlog/|wc -l

Cascading Replication Configuration Using Slot

Step-1

[enterprisedb@localhost ~]$ pg_basebackup -P -R -X stream -c fast -h 192.168.205.182 -U enterprisedb -p 5446 -D /opt/slave1

1347443/1347443 kB (100%), 1/1 tablespace

[enterprisedb@localhost ~]$ psql -p 5447

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select pg_create_physical_replication_slot('test_2');

pg_create_physical_replication_slot

-------------------------------------

(test_2,)

(1 row)

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

(1 row)

[enterprisedb@localhost ~]$ vi /opt/slave1/recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb port=5447 password=adminedb'

primary_slot_name = 'test_2'

trigger_file = '/tmp/trigger_file'

Step-2

[enterprisedb@localhost ~]$ pg_ctl -D /opt/slave1/ start

server starting

[enterprisedb@localhost ~]$ 2016-04-30 04:25:43 PDT LOG: redirecting log output to logging collector process

2016-04-30 04:25:43 PDT HINT: Future log output will appear in directory "pg_log".

[enterprisedb@localhost ~]$ psql -p 5447

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select * from pg_replication_slots;

-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------

test_2 | | physical | | | t | 28785 | | | 1/A0000220

(1 row)

edb=# select * from pg_stat_replication;

-------+----------+--------------+------------------+-----------------+-----------------+-------------+----------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------

28785 | 10 | enterprisedb | walreceiver | 192.168.205.181 | | 56177 | 30-APR-16 04:25:43.722475 -07:00 | | streaming | 1/A0000220 | 1/A0000220 | 1/A0000220 | 1/A0000220 | 0 | async

(1 row)

edb=# \x

Expanded display is on.

edb=# select * from pg_stat_replication;

-[ RECORD 1 ]----+---------------------------------

pid | 28785

usesysid | 10

usename | enterprisedb

application_name | walreceiver

client_addr | 192.168.205.181

client_hostname |

client_port | 56177

backend_start | 30-APR-16 04:25:43.722475 -07:00

backend_xmin |

state | streaming

sent_location | 1/A0000220

write_location | 1/A0000220

flush_location | 1/A0000220

replay_location | 1/A0000220

sync_priority | 0

sync_state | async

edb=# select * from pg_replication_slots;

-[ RECORD 1 ]+-----------

slot_name | test_2

plugin |

slot_type | physical

datoid |

database |

active | t

active_pid | 28785

xmin |

catalog_xmin |

restart_lsn | 1/A0000220

Step-3

edb=# show port;

port

------

5446

(1 row

edb=# create database cascade;

CREATE DATABASE

edb=# show port ;

port

------

5447

(1 row)

edb=# \l+

List of databases

-----------+--------------+----------+-------------+-------------+-----+-------------------------------+---------+------------+--------------------------------------------

edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 1284 MB | pg_default |

(5 rows)|?

edb=# show port ;

port

------

5448

(1 row)

edb=# \l+

List of databases

-----------+--------------+----------+-------------+-------------+-----+-------------------------------+---------+------------+--------------------------------------------

edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 1284 MB | pg_default |

(5 rows)

PostgreSQL Administration

Friday, May 13, 2016

Streaming Replication Slots in PostgreSQL 9.4

No comments:

Post a Comment