{"id":11158,"date":"2024-09-18T20:21:58","date_gmt":"2024-09-19T01:21:58","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=11158"},"modified":"2024-09-19T10:33:52","modified_gmt":"2024-09-19T15:33:52","slug":"postgresql-12-cascading-replication","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=11158","title":{"rendered":"PostgreSQL 12 &#8212; Cascading Replication"},"content":{"rendered":"<p>I&#8217;ve got replicated PostgreSQL database pairs that each have some 50TB of data. The server operating systems need to be upgraded, but there is a constraint: no in-place upgrades. I don&#8217;t get to veto that constraint (i.e. the fact that we could just cross our fingers and upgrade a replica &#8230; and, if it fails, built new and pull the data again doesn&#8217;t matter). Unfortunately, trying to add a <em>second<\/em> replica delays the existing replication. Since all write operations to to the RW server and reads to to the read-only replica &#8230; having the read-only copy a day or two out of sync whilst this secondary replica comes online is a non-starter.<\/p>\n<p>Fortunately, you can <em>cascade<\/em> replication &#8212; seed the new replica <em>from the current read-only replica<\/em>. Create a new replication slot &#8212; here new-pg-ro-replica-pgdata. You need to verify the new server is in the pg_hba.conf file to authenticate with the replication account.<\/p>\n<p><tt>pg_basebackup -h pg-ro-replica.example.net -D \/pgdata -U replicatorID -v -P --wal-method=stream --slot=new-pg-ro-replica-pgdata<br \/>\n<\/tt><\/p>\n<p>Wait &#8230; wait &#8230; wait. It&#8217;ll finish eventually. Then tweak your recovery.conf<\/p>\n<pre class=\"hljs\"><code id=\"code-c94d1f891d255915b7aa554cb19bf794\">standby_mode = 'on'\r\nprimary_conninfo = 'host=pg-rw-replica.example.net port=5432 user=replicatorID password=your_password' sslmode=require\r\nprimary_slot_name = 'new-pg-ro-replica-pgdata'<\/code><\/pre>\n<p>And<br \/>\n<tt>touch \/pgdata\/standby.signal<\/tt><\/p>\n<p>Finally, start the server<\/p>\n<p><tt>pg_ctl start -D \/pgdata<\/tt><\/p>\n<p>Voila &#8212; a second read-only replica. Now they can decom the old server.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve got replicated PostgreSQL database pairs that each have some 50TB of data. The server operating systems need to be upgraded, but there is a constraint: no in-place upgrades. I don&#8217;t get to veto that constraint (i.e. the fact that we could just cross our fingers and upgrade a replica &#8230; and, if it fails, &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1522],"tags":[1348,1349,1580],"class_list":["post-11158","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgres","tag-postgresql","tag-postgresql-replication"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11158"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11158\/revisions"}],"predecessor-version":[{"id":11159,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/11158\/revisions\/11159"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}