Streaming replication means the changes are synchronously applied from the master to the slave(s).<\/p>\n
First, create the replication user on the master:<\/p>\n
\r\n$ sudo -u postgres psql -c \"CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD '';\"\r\n<\/replicator_password><\/pre>\n The user created is called replicator. Make sure to create strong password for production use.<\/p>\n
Next, configure the master for streaming replication. Edit `\/etc\/postgresql\/9.1\/main\/postgresql.conf` file:<\/p>\n
\r\n[...]\r\nlisten_address = '*'\r\nlog_line_prefix='%t [%p] %u@%d '\r\nwal_level = hot_standby\r\nmax_wal_senders = 3\r\ncheckpoint_segments = 8 \r\nwal_keep_segments = 16\r\n[...]\r\n<\/pre>\nWe’re configuring 8 x WAL segments here, each is 16MB. Consider increasing those values if we expect our database to have more than 128MB of changes in the time it will take to make a copy of it across the network to our slave, or in the time we expect our slave to be down for maintenance.<\/p>\n
Then edit the access control on the master to allow the connection from the slave in `\/etc\/postgresql\/9.1\/main\/pg_hba.conf` file:<\/p>\n
\r\n[...]\r\nhostssl replication replicatormd5\r\n[...]\r\n<\/slave_ip><\/pre>\n Restart the server for the changes to take effect:<\/p>\n
\r\n$ sudo service postgresql restart\r\n<\/pre>\nNow on to the slave. In the slave’s `postgresql.conf` we add the following:<\/p>\n
\r\n[...]\r\nlog_line_prefix='%t [%p] %u@%d '\r\nwal_level = hot_standby\r\nmax_wal_senders = 3\r\ncheckpoint_segments = 8 \r\nwal_keep_segments = 16\r\nhot_standby = on\r\n[...]\r\n<\/pre>\nThen restart the slave. No changes are required in the slave’s `pg_hba.conf` specifically to support replication. We’ll still need to make whatever entries we need in order to connect to it from our application and run read-only queries, if we wish to have multiple db hosts read access. All writes should still go to the master only.<\/p>\n
Then on the slave:<\/p>\n
Stop PostgreSQL<\/p>\n
\r\n$ sudo service postgresql start\r\n<\/pre>\nRemove the database<\/p>\n
\r\n$ sudo -u postgres rm -rf \/var\/lib\/postgresql\/9.1\/main\r\n<\/pre>\nStart the base backup on the master to the slave as replicator user<\/p>\n
\r\n$ sudo -u postgres pg_basebackup -h-D \/var\/lib\/postgresql\/9.1\/main -U replicator -v -P\r\n<\/master_ip><\/pre>\n Create recovery.conf file `\/var\/lib\/postgresql\/9.1\/main\/recovery.conf`<\/p>\n
\r\nstandby_mode = 'on'\r\nprimary_conninfo = 'host=port=5432 user=replicator password= sslmode=require'\r\ntrigger_file = '\/tmp\/postgresql.trigger'\r\n<\/replicator_password><\/master_ip><\/pre>\n Start PostgreSQL<\/p>\n
\r\n$ sudo service postgresql start\r\n<\/pre>\nAfter that we should see something like this in the slave log file:<\/p>\n
\r\nuser@slave:~$ sudo tail -f \/var\/log\/postgresql\/postgresql-9.1-main.log\r\nLOG: received smart shutdown request\r\nLOG: autovacuum launcher shutting down\r\nLOG: shutting down\r\nLOG: database system is shut down\r\nLOG: database system was interrupted; last known up at 2015-10-01 08:43:28 BST\r\nLOG: entering standby mode\r\nLOG: streaming replication successfully connected to primary\r\nLOG: redo starts at 0\/66000020\r\nLOG: consistent recovery state reached at 0\/66000688\r\nLOG: database system is ready to accept read only connections\r\n<\/pre>\nand find PostgreSQL WAL processes running on both servers. On the master:<\/p>\n
\r\nuser@master:~$ ps ax -o pid,command | grep postgres | grep wal\r\n27075 postgres: wal writer process \r\n27934 postgres: wal sender process replicator(19846) streaming 0\/6719D650 [slave_ip]\r\n<\/slave_ip><\/pre>\n and on the slave:<\/p>\n
\r\nuser@slave:~$ ps ax -o pid,command | grep postgres | grep wal\r\n26357 postgres: wal receiver process streaming 0\/6719D650\r\n<\/pre>\nWe can notice the streaming numbers match. We can also check the xlog segments position to confirm they are same. On the master:<\/p>\n
\r\ndatabase=# SELECT pg_current_xlog_location();\r\n pg_current_xlog_location\r\n--------------------------\r\n 0\/6713BEF8\r\n(1 row)\r\n<\/pre>\nand on the slave:<\/p>\n
\r\ndatabase=# select pg_last_xlog_replay_location();\r\n pg_last_xlog_replay_location\r\n------------------------------\r\n 0\/6713BEF8\r\n(1 row)\r\n \r\ndatabase=# select pg_last_xlog_receive_location();\r\n pg_last_xlog_receive_location\r\n-------------------------------\r\n 0\/6713BEF8\r\n(1 row)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Streaming replication means the changes are synchronously applied from the master to the slave(s). First, create the replication user on the master: $ sudo -u postgres psql -c “CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD ”;” The user created is…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-52","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/52","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=52"}],"version-history":[{"count":4,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/52\/revisions"}],"predecessor-version":[{"id":56,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/52\/revisions\/56"}],"wp:attachment":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=52"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=52"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=52"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}