{"id":104,"date":"2018-10-17T03:55:54","date_gmt":"2018-10-17T03:55:54","guid":{"rendered":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2018\/10\/17\/postgresql-replication-on-ubuntu-tutorial\/"},"modified":"2018-10-17T03:55:54","modified_gmt":"2018-10-17T03:55:54","slug":"postgresql-replication-on-ubuntu-tutorial","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2018\/10\/17\/postgresql-replication-on-ubuntu-tutorial\/","title":{"rendered":"PostgreSQL Replication on Ubuntu Tutorial"},"content":{"rendered":"<p>&#xD;<br \/>\n&#xD;<br \/>\n <a href=\"https:\/\/s24255.pcdn.co\/wp-content\/uploads\/2017\/07\/postgresql-logo.png\"><img loading=\"lazy\" decoding=\"async\" alt=\"PostgreSQL Database System Logo\" height=\"774\" src=\"https:\/\/s24255.pcdn.co\/wp-content\/uploads\/2017\/07\/postgresql-logo-696x774.png\" width=\"696\" \/><\/a>&#xD;<\/p>\n<h3>PostgreSQL Database System<\/h3>\n<p><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL<\/a> is an advanced open source Object-Relational Database Management System (or ORDBMS). It is an extensible and highly-scalable database system, meaning that it can handle loads ranging from single machine applications to enterprise web services with many concurrent users. PostgreSQL is transactional and ACID-compliant (Atomicity, Consistency, Isolation, Durability).<\/p>\n<p>It supports a large part of the SQL standard, and offers many features including:<\/p>\n<ul>\n<li>Complex queries<\/li>\n<li>Foreign keys<\/li>\n<li>Triggers<\/li>\n<li>Updatable views<\/li>\n<li>Transactional integrity<\/li>\n<li>Multiversion concurrency control<\/li>\n<\/ul>\n<p>As previously said, the PostgreSQL database system can be extended by its users. There are different ways to do this, like adding new functions, operators, data types, index methods, procedural languages, etc.<\/p>\n<p>It is developed by the PostgreSQL Global Development Group and released under the terms of the PostgreSQL License.<\/p>\n<p>PostgreSQL provides many ways to replicate a database. in this tutorial we will configure the Master\/Slave replication, which is the process of syncing data between two database by copying from a database on a server (the master) to one on another server (the slave).<\/p>\n<p>This configuration will be done on a server running Ubuntu 16.04.<\/p>\n<h3>Prerequisites<\/h3>\n<p>PostgreSQL 9.6 installed on the Ubuntu 16.04 Servers<\/p>\n<h3>Configure UFW<\/h3>\n<p>UFW (or Uncomplicated Firewall) is a tool to manage iptables based firewall on Ubuntu systems. Install it (on both servers) through apt by executing:<\/p>\n<p># apt-get install -y ufw<\/p>\n<p>Next, add PostgreSQL and SSH service to the firewall. To do this, execute:<\/p>\n<p># ufw allow ssh&#xD;<br \/>\n# ufw allow postgresql<\/p>\n<p>Enable the firewall:<\/p>\n<p># ufw enable<\/p>\n<h3>Configure PostgreSQL Master Server<\/h3>\n<p>The master server will have reading and writing permissions to the database, and will be the one capable of performing data streaming to the slave server.<\/p>\n<p>With a text editor, edit the PostgreSQL main configuration file, which is \/etc\/postgresql\/9.6\/main\/postgresql.conf:<\/p>\n<p># $EDITOR \/etc\/postgresql\/9.6\/main\/postgresql.conf<\/p>\n<p>Uncomment the listen_addresses line and edit adding the master server IP address:<\/p>\n<p>listen_addresses = &#8216;master_server_IP_address&#8217;<\/p>\n<p>Next, uncomment the wal_level line changing its value:<\/p>\n<p>wal_level = hot_standby<\/p>\n<p>To use local syncing for the synchronization level, uncomment and edit the following line:<\/p>\n<p>synchronous_commit = local<\/p>\n<p>We are using two servers, so uncomment and edit the two lines as follows:<\/p>\n<p>max_wal_senders = 2&#xD;<br \/>\nwal_keep_segments = 10<\/p>\n<p>Save and close the file.<\/p>\n<p>Edit the pg_hba.conf file for the authentication configuration.<\/p>\n<p># $EDITOR \/etc\/postgresql\/9.6\/main\/pg_hba.conf<br \/>\nPaste the following configuration:&#xD;<br \/>\n&#xD;<br \/>\n# Localhost&#xD;<br \/>\nhost replication replica 127.0.0.1\/32 md5&#xD;<br \/>\n &#xD;<br \/>\n# PostgreSQL Master IP address&#xD;<br \/>\nhost replication replica master_IP_address\/32 md5&#xD;<br \/>\n &#xD;<br \/>\n# PostgreSQL SLave IP address&#xD;<br \/>\nhost replication replica slave_IP_address\/32 md5<\/p>\n<p>Save, exit and restart PostgreSQL:<\/p>\n<p># systemctl restart postgresql<\/p>\n<h4>Create a User for Replication<\/h4>\n<p>Create a new PostgreSQL user for the replication process. Log in to the postgres user and start PostgreSQL shell:<\/p>\n<p># su &#8211; postgres&#xD;<br \/>\n$ psql<\/p>\n<p>Create a new user:<\/p>\n<p>postgres=# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD &#8216;usr_strong_pwd&#8217;;<\/p>\n<p>Close the shell.<\/p>\n<p>This concludes the master server configuration.<\/p>\n<h3>Configuring the Slave Server<\/h3>\n<p>The slave server won\u2019t have writing permissions to the database, being that its only function is to accept streaming from the master. So it will have only READ permissions.<\/p>\n<p>First, stop the PostgreSQL service:<\/p>\n<p># systemctl stop postgresql<\/p>\n<p>Edit the PostgreSQL main configuration file:<\/p>\n<p># $EDITOR \/etc\/postgresql\/9.6\/main\/postgresql.conf<\/p>\n<p>In this file, uncomment the listen_addresses line and change its value:.<\/p>\n<p>listen_addresses = &#8216;slave_IP_address&#8217;<\/p>\n<p>Next, uncomment the wal_level line and change as follow:<\/p>\n<p>wal_level = hot_standby<\/p>\n<p>As in the master settings, uncomment the synchronous_commit line to use local syncing.<\/p>\n<p>synchronous_commit = local<\/p>\n<p>Also as in the master, uncomment and edit the following two lines:<\/p>\n<p>max_wal_senders = 2&#xD;<br \/>\nwal_keep_segments = 10<\/p>\n<p>Enable hot_standby for the slave server by uncommenting the following line and changing its value:<\/p>\n<p>hot_standby = on<\/p>\n<p>Save and exit.<\/p>\n<h3>Copy Data From Master to Slave<\/h3>\n<p>To sync from master to slave server, the PostgreSQL main directory on the slave must be replaced with the main directory from the master. In the slave server, log in to the postgres user:<\/p>\n<p># su &#8211; postgres<\/p>\n<p>Make a backup of the actual data directory:<\/p>\n<p>$ cd\/var\/lib\/postgresql\/9.6\/&#xD;<br \/>\n$ mv main main_bak<\/p>\n<p>Create a new main directory:<\/p>\n<p>$ mkdir main\/<\/p>\n<p>Change permissions:<\/p>\n<p>$ chmod 700 main<\/p>\n<p>At this point, copy the main directory from the master to the slave server by using pg_basebackup:<\/p>\n<p># pg_basebackup -h master_IP_address -U replica -D \/var\/lib\/postgresql\/9.6\/main -P &#8211;xlog<\/p>\n<p>Once the transfer is complete, in the main directory create a new recovery.conf file, and paste the following content:<\/p>\n<p>standby_mode = &#8216;on&#8217;&#xD;<br \/>\nprimary_conninfo = &#8216;host=10.0.15.10 port=5432 user=replica password=usr_strong_pwd&#8217;&#xD;<br \/>\ntrigger_file = &#8216;\/tmp\/postgresql.trigger.5432&#8217;<\/p>\n<p>Save, exit and change permissions to this file:<\/p>\n<p># chmod 600 recovery.conf<\/p>\n<p>Start PostgreSQL:<\/p>\n<p># systemctl start postgresql<\/p>\n<p>This concludes the slave server configuration.<\/p>\n<h3>Conclusion<\/h3>\n<p>We have seen how to configure the PostgreSQL master\/slave replication, by using two servers running Ubuntu 16.04. This is just one of the many replication capabilities provided by this advanced and fully open source database system.<\/p>\n<p> <a href=\"https:\/\/www.unixmen.com\/postgresql-replication-tutorial\/\" target=\"_blank\">Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#xD; &#xD; &#xD; PostgreSQL Database System PostgreSQL is an advanced open source Object-Relational Database Management System (or ORDBMS). It is an extensible and highly-scalable database system, meaning that it can handle loads ranging from single machine applications to enterprise web services with many concurrent users. PostgreSQL is transactional and ACID-compliant (Atomicity, Consistency, Isolation, Durability). It &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.appservgrid.com\/paw92\/index.php\/2018\/10\/17\/postgresql-replication-on-ubuntu-tutorial\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;PostgreSQL Replication on Ubuntu Tutorial&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=104"}],"version-history":[{"count":0,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/104\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}