{"id":17143,"date":"2022-02-24T09:57:08","date_gmt":"2022-02-24T08:57:08","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/"},"modified":"2022-02-24T09:57:08","modified_gmt":"2022-02-24T08:57:08","slug":"postgresql-15-add-row-filters-for-logical-replication","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/","title":{"rendered":"PostgreSQL 15: Add row filters for logical replication"},"content":{"rendered":"<p>My last post about <a href=\"https:\/\/www.dbi-services.com\/blog\/in-core-logical-replication-will-hit-postgresql-10\/\" target=\"_blank\" rel=\"noopener\">logical replication in PostgreSQL<\/a> was in 2017 before PostgreSQL 10 was released later that year. There have been many enhancements to logical replication since then and today we&#8217;ll look at a new features that will probably come with PostgreSQL 15 later this year: <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commit;h=52e4f0cd472d39d07732b99559989ea3b615be78\" target=\"_blank\" rel=\"noopener\">Allow specifying row filters for logical replication of tables<\/a>. What is it about? Up until today you can only replicate whole tables to a subscriber node. It is not possible to filter for specific rows or, in other words: You cannot replicate a subset of the rows in the table. With this commit this becomes possible, which is a great feature. <\/p>\n<p><!--more--><\/p>\n<p>Lets start by creating two brand new instances, the first one will be used as the publisher, and the second one as the subscriber:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] mkdir \/var\/tmp\/pub &amp;&amp; initdb -D \/var\/tmp\/pub &amp;&amp; echo \"port=8888\" &gt;&gt; \/var\/tmp\/pub\/postgresql.auto.conf\npostgres@debian11pg:\/home\/postgres\/ [pgdev] mkdir \/var\/tmp\/sub &amp;&amp; initdb -D \/var\/tmp\/sub &amp;&amp; echo \"port=9999\" &gt;&gt; \/var\/tmp\/sub\/postgresql.auto.conf\n<\/pre>\n<p>For being able to use logical replication, <a href=\"https:\/\/www.postgresql.org\/docs\/current\/runtime-config-wal.html#GUC-WAL-LEVEL\" target=\"_blank\" rel=\"noopener\">wal_level<\/a> needs to be increased to &#8220;logical&#8221;, so lets do that and start up both instances:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"wal_level='logical'\" &gt;&gt; \/var\/tmp\/pub\/postgresql.auto.conf\npostgres@debian11pg:\/home\/postgres\/ [pgdev] echo \"wal_level='logical'\" &gt;&gt; \/var\/tmp\/sub\/postgresql.auto.conf\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl -D \/var\/tmp\/pub\/ start -l \/dev\/null\npostgres@debian11pg:\/home\/postgres\/ [pgdev] pg_ctl -D \/var\/tmp\/sub\/ start -l \/dev\/null\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -p 8888 -l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n(3 rows)\n\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -p 9999 -l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n(3 rows)\n<\/pre>\n<p>Although it is not really required (as long as table and column names match), the exactly same schema and table are now getting created in both clusters:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] cat c.sql \ncreate schema a;\ncreate table a.t ( a int primary key, b text, c date );\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -f \/home\/postgres\/c.sql -p 8888\nCREATE SCHEMA\nCREATE TABLE\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -f \/home\/postgres\/c.sql -p 9999\nCREATE SCHEMA\nCREATE TABLE\n<\/pre>\n<p>Lets assume we have a kind of tenancy in that table on the publisher, which is defined by the second (b) column:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"insert into a.t select i,'aaa',now() from generate_series(1,1000) i\" -p 8888\nINSERT 0 1000\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"insert into a.t select i,'bbb',now() from generate_series(1001,2000) i\" -p 8888\nINSERT 0 1000\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"insert into a.t select i,'ccc',now() from generate_series(2001,3000) i\" -p 8888\nINSERT 0 1000\n<\/pre>\n<p>Up to PostgreSQL 14, the only option you have for logically replicating that table, is to replicate the whole table. Since the commit mentioned above we can filter on a subset of the rows:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [12]\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"h create publication\" -p 8888\nCommand:     CREATE PUBLICATION\nDescription: define a new publication\nSyntax:\nCREATE PUBLICATION name\n    [ FOR ALL TABLES\n      | FOR publication_object [, ... ] ]\n    [ WITH ( publication_parameter [= value] [, ... ] ) ]\n\nwhere publication_object is one of:\n\n    TABLE [ ONLY ] table_name [ * ] [ WHERE ( expression ) ] [, ... ]\n    ALL TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]\n\nURL: https:\/\/www.postgresql.org\/docs\/devel\/sql-createpublication.html\n<\/pre>\n<p>Lets assume we only want to replicate tenant &#8216;aaa&#8217;, so we can do it like this:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"create publication pub1 for table a.t where (b = 'aaa')\" -p 8888\nCREATE PUBLICATION\n<\/pre>\n<p>Once we consume that on the subscriber side we&#8217;ll only have the data for that tenant:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"create subscription sub1 connection 'port=8888' publication pub1\" -p 9999\nNOTICE:  created replication slot \"sub1\" on publisher\nCREATE SUBSCRIPTION\npostgres@debian11pg:\/home\/postgres\/ [pgdev] psql -c \"select distinct(b), count(*) from a.t group by b\" -p 9999\n  b  | count \n-----+-------\n aaa |  1000\n(1 row)\n<\/pre>\n<p>Really cool. Thanks to all involved for making this happen.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>My last post about logical replication in PostgreSQL was in 2017 before PostgreSQL 10 was released later that year. There have been many enhancements to logical replication since then and today we&#8217;ll look at a new features that will probably come with PostgreSQL 15 later this year: Allow specifying row filters for logical replication of [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-17143","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 15: Add row filters for logical replication - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 15: Add row filters for logical replication\" \/>\n<meta property=\"og:description\" content=\"My last post about logical replication in PostgreSQL was in 2017 before PostgreSQL 10 was released later that year. There have been many enhancements to logical replication since then and today we&#8217;ll look at a new features that will probably come with PostgreSQL 15 later this year: Allow specifying row filters for logical replication of [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-24T08:57:08+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"PostgreSQL 15: Add row filters for logical replication\",\"datePublished\":\"2022-02-24T08:57:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/\"},\"wordCount\":286,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/\",\"name\":\"PostgreSQL 15: Add row filters for logical replication - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-02-24T08:57:08+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-add-row-filters-for-logical-replication\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 15: Add row filters for logical replication\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 15: Add row filters for logical replication - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 15: Add row filters for logical replication","og_description":"My last post about logical replication in PostgreSQL was in 2017 before PostgreSQL 10 was released later that year. There have been many enhancements to logical replication since then and today we&#8217;ll look at a new features that will probably come with PostgreSQL 15 later this year: Allow specifying row filters for logical replication of [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/","og_site_name":"dbi Blog","article_published_time":"2022-02-24T08:57:08+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"PostgreSQL 15: Add row filters for logical replication","datePublished":"2022-02-24T08:57:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/"},"wordCount":286,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/","name":"PostgreSQL 15: Add row filters for logical replication - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-02-24T08:57:08+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-add-row-filters-for-logical-replication\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 15: Add row filters for logical replication"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17143"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17143\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17143"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}