{"id":2841,"date":"2022-12-25T18:58:26","date_gmt":"2022-12-25T15:58:26","guid":{"rendered":"https:\/\/www.fatihacar.com\/blog\/?p=2841"},"modified":"2022-12-25T19:10:56","modified_gmt":"2022-12-25T16:10:56","slug":"postgresql-table-and-column-based-logical-replication","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/postgresql-table-and-column-based-logical-replication\/","title":{"rendered":"PostgreSQL Table and Column Based Logical Replication"},"content":{"rendered":"<aside class=\"single-left-section\">\n<div class=\"section-inner\">\n<aside class=\"single-left-share is-light-share\">\n<div class=\"share-content\">With\u00a0PostgreSQL 15 version, logical replication became applicable by filtering on the tables by column and row.\u00a0In this way, replication can be made in the desired columns and rows in the tables.<\/div>\n<\/aside>\n<\/div>\n<\/aside>\n<h2>Logical Replication<\/h2>\n<p><strong>wal_level=logical<\/strong><\/p>\n<p>For logical\u00a0replication, the\u00a0<strong>wal_level<\/strong> parameter must be logical in the <strong>postgresql.conf<\/strong>\u00a0file\u00a0.\u00a0By default, the\u00a0value of\u00a0<strong>wal_level <\/strong>is <strong>replica<\/strong>\u00a0.\u00a0The replica\u00a0value\u00a0provides the necessary wal files for\u00a0<strong>streaming replication .\u00a0<\/strong><strong>wal_level<\/strong>\u00a0parameter is\u00a0<strong>logical <\/strong>when set to , it allows adding the necessary wal information for logical replication to the wal information provided with the replica value. In other words, when you set the wal_level parameter as logical, it also includes the replica value and streaming replication can continue to work. When wal_level runs logically, the size of wal files grows. Parameter editing is done in both databases.<\/p>\n<p><!--more--><\/p>\n<p><strong>Example Database and Tables<\/strong><\/p>\n<p>postgres=# CREATE DATABASE testdb;<br \/>\nCREATE DATABASE<br \/>\npostgres=# \\c testdb<br \/>\nYou are now connected to database \u201ctestdb\u201d as user \u201cpostgres\u201d.<br \/>\ntestdb=# CREATE TABLE person(person_id int, name text, surname text, gender text, PRIMARY KEY(person_id));<br \/>\nCREATE TABLE<br \/>\ntestdb=# INSERT INTO person values \u200b\u200b(1,&#8217;Fatih&#8217;,&#8217;Acar&#8217;,&#8217;Male&#8217;);<br \/>\nINSERT 0 1<br \/>\ntestdb=# INSERT INTO person values \u200b\u200b(2,&#8217;Ay\u015fe&#8217;,&#8217;Fatma&#8217;,&#8217;Woman&#8217;);<br \/>\nINSERT 0 1<br \/>\ntestdb=# INSERT INTO person values \u200b\u200b(3,&#8217;John&#8217;,&#8217;Jack&#8217;,&#8217;Male&#8217;);<br \/>\nINSERT 0 1<\/p>\n<p>testdb=# select * from person;<br \/>\nperson_id |\u00a0name |\u00a0surname |\u00a0gender<br \/>\n\u2014\u2014\u2014\u2013+\u2014\u2014-+\u2014\u2014\u2014+\u2014\u2014\u2013<br \/>\n1 |\u00a0Fatih |\u00a0Acar |\u00a0Male<br \/>\n2 |\u00a0Ayse |\u00a0Fatima |\u00a0Woman<br \/>\n3 |\u00a0Ahmet |\u00a0Mehmet |\u00a0Male<br \/>\n(3 rows)<\/p>\n<p><strong>Publication Creation<\/strong><\/p>\n<p>A publication is created by determining the desired fields of the table or tables to be used for logical replication.<\/p>\n<p>postgres=# CREATE PUBLICATION pub_person FOR TABLE person (person_id, name, surname);<br \/>\nCREATE PUBLICATION<\/p>\n<p>&nbsp;<\/p>\n<p>You can add a where condition to the query as follows to add a row-based filter in the publicized table.<\/p>\n<p>CREATE PUBLICATION pub_person FOR TABLE person (person_id, name, surname) where (gender=&#8217;Male&#8217;);<\/p>\n<p>If you want to include all tables in the publication scope, you can use the following query.<\/p>\n<p>CREATE PUBLICATION pub_tables FOR ALL TABLES;<\/p>\n<p>If you want to include certain tables in the scope of publication with all their fields, you can use the following query.<\/p>\n<p>CREATE PUBLICATION pub_tablo123 FOR TABLE table1,table2,table3;<\/p>\n<p><strong>Replication User<\/strong><\/p>\n<p>There is a need for a user that has been created specifically for replication and has authorization to the tables to be replicated.\u00a0For example, the repuser user can be created as follows.<\/p>\n<p>[postgres@btcozumleri ~]$ createuser -p 5432 repuser -P \u2013replication<\/p>\n<p>postgres=# GRANT ALL ON person TO repuser;<br \/>\nGRANT<\/p>\n<p><strong>Subscription Creation<\/strong><\/p>\n<p>A table is created in the standby database to be the same as the columns specified in the publication in the master database.<\/p>\n<p>postgres=# CREATE DATABASE testdb;<br \/>\nCREATE DATABASE<br \/>\npostgres=# \\c testdb;<br \/>\nYou are now connected to database \u201ctestdb\u201d as user \u201cpostgres\u201d.<br \/>\ntestdb=# CREATE TABLE person(person_id int, name text, surname text, PRIMARY KEY(person_id));<br \/>\nCREATE TABLE<\/p>\n<p>testdb=# CREATE SUBSCRIPTION sub_person CONNECTION &#8216;host=localhost dbname=testdb user=repuser password=123456 application_name=sub_person&#8217; PUBLICATION pub_person;<br \/>\nNOTICE: created replication slot \u201csub_person\u201d on publisher<br \/>\nCREATE SUBSCRIPTION<\/p>\n<p>postgres=# \\c testdb<br \/>\nYou are now connected to database \u201ctestdb\u201d as user \u201cpostgres\u201d.<br \/>\ntestdb=# select * from person;<br \/>\nperson_id |\u00a0name |\u00a0surname<br \/>\n\u2014\u2014\u2014\u2013+\u2014\u2014+\u2014\u2014\u2014<br \/>\n1 |\u00a0Fatih |\u00a0Acar<br \/>\n2 |\u00a0Ayse |\u00a0Fatma<br \/>\n3 |\u00a0Ahmet |\u00a0Mehmet<br \/>\n(3 rows)<\/p>\n<p>Data came into the standby database.\u00a0You can perform test operations by adding and removing data from the master database.<\/p>\n<p>select * from pg_replication_slots; You can list replication slots that are open on the master side.<\/p>\n<p>Source : <a href=\"https:\/\/btcozumleri.com\/postgresql-tablo-ve-kolon-bazli-logical-replication\/\">https:\/\/btcozumleri.com\/postgresql-tablo-ve-kolon-bazli-logical-replication\/<\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>With\u00a0PostgreSQL 15 version, logical replication became applicable by filtering on the tables by column and row.\u00a0In this way, replication can be made in the desired columns and rows in the tables. Logical Replication wal_level=logical For logical\u00a0replication, the\u00a0wal_level parameter must be logical in the postgresql.conf\u00a0file\u00a0.\u00a0By default, the\u00a0value of\u00a0wal_level is replica\u00a0.\u00a0The replica\u00a0value\u00a0provides the necessary wal files for\u00a0streaming&#8230;<\/p>\n","protected":false},"author":37,"featured_media":2842,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[10],"tags":[14,93,88],"class_list":["post-2841","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administration-postgresql","tag-database-administration","tag-postgresql","tag-postgresql-administration"],"jetpack_featured_media_url":"http:\/\/www.fatihacar.com\/blog\/resimler\/postgresql-logical-replication.png","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-JP","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2841","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=2841"}],"version-history":[{"count":3,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2841\/revisions"}],"predecessor-version":[{"id":2845,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2841\/revisions\/2845"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media\/2842"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=2841"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=2841"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=2841"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}