{"id":10609,"date":"2017-11-03T09:03:07","date_gmt":"2017-11-03T08:03:07","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/"},"modified":"2017-11-03T09:03:07","modified_gmt":"2017-11-03T08:03:07","slug":"can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/","title":{"rendered":"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?"},"content":{"rendered":"<p>When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, &#8230;) but will show what happens when you try to do that. Lets go &#8230;<\/p>\n<p><!--more--><\/p>\n<p>My two instances run on the same host, one on port 6000 the other one on 6001. To start I&#8217;ll create the same table in both instances:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t1 ( a int primary key, b varchar(50) );\nCREATE TABLE\npostgres=# alter table t1 replica identity using INDEX t1_pkey;\nALTER TABLE\npostgres=# d+ t1\n                                            Table \"public.t1\"\n Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description \n--------+-----------------------+-----------+----------+---------+----------+--------------+-------------\n a      | integer               |           | not null |         | plain    |              | \n b      | character varying(50) |           |          |         | extended |              | \nIndexes:\n    \"t1_pkey\" PRIMARY KEY, btree (a) REPLICA IDENTITY\n<\/pre>\n<p>Create the same publication on both sides:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create publication my_pub for table t1;\nCREATE PUBLICATION\npostgres=# select * from pg_publication;\n pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete \n---------+----------+--------------+-----------+-----------+-----------\n my_pub  |       10 | f            | t         | t         | t\n(1 row)\npostgres=# select * from pg_publication_tables;\n pubname | schemaname | tablename \n---------+------------+-----------\n my_pub  | public     | t1\n(1 row)\n<\/pre>\n<p>Create the same subscription on both sides (except for the port, of course):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# show port;\n port \n------\n 6000\n(1 row)\nppostgres=# create subscription my_sub connection 'host=localhost port=6001 dbname=postgres user=postgres' publication my_pub;\nCREATE SUBSCRIPTION\npostgres=# select * from pg_subscription;\n subdbid | subname | subowner | subenabled |                      subconninfo                       | subslotname | \n---------+---------+----------+------------+--------------------------------------------------------+-------------+-\n   13212 | my_sub  |       10 | t          | host=localhost port=6001 dbname=postgres user=postgres | my_sub      | \n(1 row)\n\n\n### second instance\n\npostgres=# show port;\n port \n------\n 6001\n(1 row)\n\npostgres=# create subscription my_sub connection 'host=localhost port=6000 dbname=postgres user=postgres' publication my_pub;\nCREATE SUBSCRIPTION\npostgres=# select * from pg_subscription;\n subdbid | subname | subowner | subenabled |                      subconninfo                       | subslotname | \n---------+---------+----------+------------+--------------------------------------------------------+-------------+-\n   13212 | my_sub  |       10 | t          | host=localhost port=6000 dbname=postgres user=postgres | my_sub      | \n(1 row)\n<\/pre>\n<p>So far, so good, everything worked until now. Now lets insert a row in the first instance:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into t1 (a,b) values (1,'a');\nINSERT 0 1\npostgres=# select * from t1;\n a | b \n---+---\n 1 | a\n(1 row)\n<\/pre>\n<p>That seemed to worked as well as the row is there on the second instance as well:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# show port;\n port \n------\n 6001\n(1 row)\n\npostgres=# select * from t1;\n a | b \n---+---\n 1 | a\n(1 row)\n<\/pre>\n<p>But: When you take a look at the log file of the first instance you&#8217;ll see something like this (which is repeated over and over again):<\/p>\n<pre class=\"brush: text; gutter: true; first-line: 1\">\n2017-11-03 09:56:29.176 CET - 2 - 10687 -  - @ ERROR:  duplicate key value violates unique constraint \"t1_pkey\"\n2017-11-03 09:56:29.176 CET - 3 - 10687 -  - @ DETAIL:  Key (a)=(1) already exists.\n2017-11-03 09:56:29.178 CET - 29 - 10027 -  - @ LOG:  worker process: logical replication worker for subscription 16437 (PID 10687) exited with exit code 1\n2017-11-03 09:56:34.198 CET - 1 - 10693 -  - @ LOG:  logical replication apply worker for subscription \"my_sub\" has started\n<\/pre>\n<p>Now the second instance is constantly trying to insert the same row back to the first instance and that obviously can not work as the row is already there. So the answer to the original question: Do not try to do that, it will not work anyway.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, &#8230;) but will show what happens when you [&hellip;]<\/p>\n","protected":false},"author":29,"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-10609","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 v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 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\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?\" \/>\n<meta property=\"og:description\" content=\"When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, &#8230;) but will show what happens when you [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-11-03T08:03:07+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 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\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?\",\"datePublished\":\"2017-11-03T08:03:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\"},\"wordCount\":227,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\",\"name\":\"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-11-03T08:03:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 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\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 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\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/","og_locale":"en_US","og_type":"article","og_title":"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?","og_description":"When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, &#8230;) but will show what happens when you [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/","og_site_name":"dbi Blog","article_published_time":"2017-11-03T08:03:07+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?","datePublished":"2017-11-03T08:03:07+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/"},"wordCount":227,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/","url":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/","name":"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-11-03T08:03:07+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-have-the-same-table-published-and-subscribed-bi-directional-in-postgresql-10-logical-replication\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10609","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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10609"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10609\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10609"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}