(window.webpackJsonp=window.webpackJsonp||[]).push([[2559],{2967:function(e,t,a){"use strict";a.r(t);var r=a(31),s=Object(r.a)({},(function(){var e=this,t=e.$createElement,a=e._self._c||t;return a("ContentSlotsDistributor",{attrs:{"slot-key":e.$parent.slotKey}},[a("h1",{attrs:{id:"postgresql-high-availability"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#postgresql-high-availability"}},[e._v("#")]),e._v(" PostgreSQL High Availability")]),e._v(" "),a("h2",{attrs:{id:"replication-in-postgresql"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#replication-in-postgresql"}},[e._v("#")]),e._v(" Replication in PostgreSQL")]),e._v(" "),a("li",[e._v("\n**Configuring the Primary Server**\n"),a("ul",[a("li",[e._v("\n**Requirements:**\n"),a("ul",[e._v("\n- Replication User for replication activities\n- Directory to store the WAL archives\n"),a("p",[a("strong",[e._v("Create Replication user")])]),e._v(" "),a("p",[a("code",[e._v("createuser -U postgres replication -P -c 5 --replication")])]),e._v(" "),a("div",{staticClass:"language- extra-class"},[a("pre",{pre:!0,attrs:{class:"language-text"}},[a("code",[e._v("\n + option -P will prompt you for new password\n  + option -c is for maximum connections. 5 connections are enough for replication\n  + -replication will grant replication privileges to the user\n\n")])])]),a("li",[e._v("\n**Create a archive directory in data directory**\n`mkdir $PGDATA/archive`\n")]),e._v(" "),a("li",[e._v("\n**Edit the pg_hba.conf file**\nThis is host base authentication file, contains the setting for client autherntication. Add below entry:\n"),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[e._v("      "),a("span",{pre:!0,attrs:{class:"token comment"}},[e._v("#hosttype    database_name     user_name       hostname/IP      method")]),e._v("\n       host        "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("replication")]),e._v("       "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("replication")]),e._v("     "),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v("<")]),e._v("slave"),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v("-")]),e._v("IP"),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v(">")]),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v("/")]),a("span",{pre:!0,attrs:{class:"token number"}},[e._v("32")]),e._v("    md5\n\n")])])])]),e._v(" "),a("li",[e._v("\n**Edit the postgresql.conf file**\nThis is the configuration file of PostgreSQL.\n`wal_level = hot_standby`\nThis parameter decides the behavior of slave server.\n"),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[e._v("   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v("`")]),e._v("hot_standby"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v("`")]),e._v(" logs what "),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v("is")]),e._v(" required "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("to")]),e._v(" accept "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("read")]),e._v(" only queries "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("on")]),e._v(" slave server"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(".")]),e._v("\n\n   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v("`")]),e._v("streaming"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v("`")]),e._v(" logs what "),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v("is")]),e._v(" required "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("to")]),e._v(" just "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("apply")]),e._v(" the WAL's "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("on")]),e._v(" slave"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(".")]),e._v("\n\n   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v("`")]),e._v("archive"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v("`")]),e._v(" which logs what "),a("span",{pre:!0,attrs:{class:"token operator"}},[e._v("is")]),e._v(" required "),a("span",{pre:!0,attrs:{class:"token keyword"}},[e._v("for")]),e._v(" archiving"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[e._v(".")]),e._v("\n\n")])])]),a("p",[a("code",[e._v("archive_mode=on")]),e._v("\nThis parameters allows to send WAL segments to archive location using "),a("code",[e._v("archive_command")]),e._v(" parameter.\n"),a("code",[e._v("archive_command = 'test ! -f /path/to/archivedir/%f && cp %p /path/to/archivedir/%f'")]),e._v("\nBasically what above "),a("code",[e._v("archive_command")]),e._v(" does is it copies the WAL segments to archive directory.\n")]),a("p",[a("code",[e._v("wal_senders = 5")]),e._v("\nThis is maximum number of WAL sender processes.")]),e._v("\nNow restart the primary server.\n")]),a("p"),e._v(" "),a("li",[e._v("\n**Backing up the primay server to the slave server**\nBefore making changes on the server stop the primary server.\n")]),e._v(" "),a("blockquote"),e._v(" "),a("p",[e._v("Important: Don't start the service again until all configuration and\nbackup steps are complete. You must bring up the standby server in a\nstate where it is ready to be a backup server. This means that all\nconfiguration settings must be in place and the databases must be\nalready synchronized. Otherwise, streaming replication will fail to\nstart`")]),e._v(" "),a("li",[e._v("\n**Now run the pg_basebackup utility**\n`pg_basebackup` utility copies the data from primary server data directory to slave data directory.\n`$ pg_basebackup -h "),a("primary",{attrs:{IP:""}},[e._v(" -D /var/lib/postgresql/"),a("version",[e._v("/main -U replication -v -P --xlog-method=stream`\n")])],1)],1),e._v(" "),a("div",{staticClass:"language- extra-class"},[a("pre",{pre:!0,attrs:{class:"language-text"}},[a("code",[e._v("\n   -D: This is tells pg_basebackup where to the initial backup\n\n    -h: Specifies the system where to look for the primary server\n\n    -xlog-method=stream: This will force the pg_basebackup to open another connection and stream enough xlog while backup is running.\n                         It also ensures that fresh backup can be started without failing back to using an archive.\n\n")])])]),a("li",[e._v("\n**Configuring the standby server**\nTo configure the standby server, you'll edit postgresql.conf and create a new configuration file named recovery.conf.\n`hot_standby = on`\nThis specifies whether you are allowed to run queries while recovering\n"),a("ul",[a("li",[e._v("\n**Creating recovery.conf file**\n`standby_mode = on`\nSet the connection string to the primary server. Replace  with the external IP address of the primary server. Replace  with the password for the user named replication\n`primary_conninfo = 'host= port=5432 user=replication password='\n(Optional) Set the trigger file location:\n`trigger_file = '/tmp/postgresql.trigger.5432'`\nThe `trigger_file` path that you specify is the location where you can add a file when you want the system to fail over to the standby server. The presence of the file \"triggers\" the failover. Alternatively, you can use the pg_ctl promote command to trigger failover.\n")]),e._v(" "),a("p",[a("strong",[e._v("Start the standby server")])]),e._v(" "),a("p",[e._v("You now have everything in place and are ready to bring up the standby server")]),e._v(" "),a("p",[a("strong",[e._v("Attribution")])]),e._v(" "),a("p",[e._v("This article is substantially derived from and attributed to "),a("a",{attrs:{href:"https://cloud.google.com/solutions/setup-postgres-hot-standby#create_a_user_for_replication",target:"_blank",rel:"noopener noreferrer"}},[e._v("How to Set Up PostgreSQL for High Availability and Replication with Hot Standby"),a("OutboundLink")],1),e._v(", with minor changes in formatting and examples and some text deleted. The source was published under the "),a("a",{attrs:{href:"https://creativecommons.org/licenses/by/3.0/",target:"_blank",rel:"noopener noreferrer"}},[e._v("Creative Commons Public License 3.0"),a("OutboundLink")],1),e._v(", which is maintained here.")])])])])])])])])}),[],!1,null,null,null);t.default=s.exports}}]);