v6-0004-Tests-and-documentation-for-skip_table-support-fo.patch

From 962484dfea77017ec4ede92b5b9490d405b13390 Mon Sep 17 00:00:00 2001
From: vignesh <vignesh21@gmail.com>
Date: Wed, 16 Jun 2021 18:33:08 +0530
Subject: [PATCH v6 4/4] Tests and documentation for skip_table support for
 publication.

Tests and documentation for skip_table support for publication.
---
 doc/src/sgml/catalogs.sgml                   |  66 ++++++
 doc/src/sgml/ref/alter_publication.sgml      |  34 ++-
 doc/src/sgml/ref/create_publication.sgml     |  28 ++-
 src/test/regress/expected/object_address.out |  10 +-
 src/test/regress/expected/publication.out    | 216 +++++++++++++++++++
 src/test/regress/sql/object_address.sql      |   5 +
 src/test/regress/sql/publication.sql         |  90 ++++++++
 src/test/subscription/t/001_rep_changes.pl   |  47 +++-
 8 files changed, 491 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b87d84a2e1..35ab340e34 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -250,6 +250,11 @@
       <entry>schema to publication mapping</entry>
      </row>
 
+     <row>
+      <entry><link linkend="catalog-pg-publication-skiprel"><structname>pg_publication_skiprel</structname></link></entry>
+      <entry>skip relation to publication mapping</entry>
+     </row>
+
      <row>
       <entry><link linkend="catalog-pg-range"><structname>pg_range</structname></link></entry>
       <entry>information about range types</entry>
@@ -6326,6 +6331,67 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
   </table>
  </sect1>
 
+ <sect1 id="catalog-pg-publication-skiprel">
+  <title><structname>pg_publication_skiprel</structname></title>
+
+  <indexterm zone="catalog-pg-publication-skiprel">
+   <primary>pg_publication_skiprel</primary>
+  </indexterm>
+
+  <para>
+   The catalog <structname>pg_publication_skiprel</structname> contains the
+   mapping between skip relations and publications in the database.  This is a
+   many-to-many mapping.
+  </para>
+
+  <table>
+   <title><structname>pg_publication_skiprel</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>oid</structfield> <type>oid</type>
+      </para>
+      <para>
+       Row identifier
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pspubid</structfield> <type>oid</type>
+       (references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>oid</structfield>)
+      </para>
+      <para>
+       Reference to publication
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>psrelid</structfield> <type>oid</type>
+       (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+      </para>
+      <para>
+       Reference to relation
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
  <sect1 id="catalog-pg-range">
   <title><structname>pg_range</structname></title>
 
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 532ca2ff62..88eb8bd914 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -27,6 +27,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP TABLE [
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...] 
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD SKIP_TABLE <replaceable class="parameter">table_name</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET SKIP_TABLE <replaceable class="parameter">table_name</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP SKIP_TABLE <replaceable class="parameter">table_name</replaceable> [, ...]
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -64,7 +67,18 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   </para>
 
   <para>
-   The seventh variant of this command listed in the synopsis can change
+   The seventh, eighth and ninth variants change which tables should be skipped
+   from the publication.  The <literal>SET TABLE</literal> clause will replace
+   the list of skip tables in the publication with the specified one.  The
+   <literal>ADD TABLE</literal> and <literal>DROP TABLE</literal> clauses will
+   add and remove one or more skip tables from the publication.  Note that
+   adding skip tables to a publication that is already subscribed to will
+   require a <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
+   action on the subscribing side in order to become effective.
+  </para>
+
+  <para>
+   The tenth variant of this command listed in the synopsis can change
    all of the publication properties specified in
    <xref linkend="sql-createpublication"/>.  Properties not mentioned in the
    command retain their previous settings.
@@ -184,6 +198,24 @@ ALTER PUBLICATION production_quarterly_publication DROP SCHEMA production_july;
    Set schema to the publication:
 <programlisting>
 ALTER PUBLICATION production_publication SET SCHEMA production_july;
+</programlisting></para>
+
+  <para>
+   Add some skip tables to the publication:
+<programlisting>
+ALTER PUBLICATION mypublication ADD SKIP_TABLE users, departments;
+</programlisting></para>
+
+  <para>
+   Drop some skip tables from the publication:
+<programlisting>
+ALTER PUBLICATION mypublication DROP SKIP_TABLE users, departments;
+</programlisting></para>
+
+  <para>
+   Set some skip tables to the publication:
+<programlisting>
+ALTER PUBLICATION mypublication SET SKIP_TABLE users, departments;
 </programlisting></para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 60fea8debe..5896a85b1f 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -23,8 +23,8 @@ PostgreSQL documentation
 <synopsis>
 CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
     [ FOR TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
-      | FOR SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
-      | FOR ALL TABLES 
+      | FOR SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ] [ SKIP_TABLE <replaceable class="parameter">table_name</replaceable> [, ... ] ] ] 
+      | FOR ALL TABLES [ SKIP_TABLE <replaceable class="parameter">table_name</replaceable> [, ... ] ]
     [ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
 </synopsis>
  </refsynopsisdiv>
@@ -111,6 +111,15 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>SKIP_TABLE</literal></term>
+    <listitem>
+     <para>
+      Specifies a list of tables to be skipped from the publication.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
     <listitem>
@@ -252,6 +261,21 @@ marketing and sales schemas:
 <programlisting>
 CREATE PUBLICATION sales_publication FOR SCHEMA marketing, sales;
 </programlisting></para>
+
+  <para>
+   Create a publication that publishes all changes for all the tables present in
+marketing except marketing_temp table:
+<programlisting>
+CREATE PUBLICATION sales_publication FOR SCHEMA marketing SKIP_TABLE marketing_temp;
+</programlisting></para>
+
+  <para>
+   Create a publication that publishes all changes in all tables, except tbl1
+and tbl2 tables:
+<programlisting>
+CREATE PUBLICATION alltables FOR ALL TABLES SKIP_TABLE tbl1, tbl2;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 49ea22f427..9495c86ecf 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -46,6 +46,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
 SET client_min_messages = 'ERROR';
 CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
 CREATE PUBLICATION addr_pub_schema FOR SCHEMA addr_nsp;
+CREATE PUBLICATION addr_pub_schema_skip FOR SCHEMA addr_nsp SKIP_TABLE addr_nsp.gentable;
 RESET client_min_messages;
 CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
 WARNING:  tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
@@ -430,6 +431,7 @@ WITH objects (type, name, args) AS (VALUES
 				('publication', '{addr_pub}', '{}'),
 				('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
 				('publication schema', '{addr_nsp}', '{addr_pub_schema}'),
+				('publication skiprelation', '{addr_nsp, gentable}', '{addr_pub_schema_skip}'),
 				('subscription', '{regress_addr_sub}', '{}'),
 				('statistics object', '{addr_nsp, gentable_stat}', '{}')
         )
@@ -492,8 +494,9 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
  subscription              |            | regress_addr_sub  | regress_addr_sub                                                     | t
  publication               |            | addr_pub          | addr_pub                                                             | t
  publication relation      |            |                   | addr_nsp.gentable in publication addr_pub                            | t
+ publication skiprelation  |            |                   | addr_nsp.gentable in publication addr_pub_schema_skip                | t
  publication schema        |            |                   | addr_nsp in publication addr_pub_schema                              | t
-(50 rows)
+(51 rows)
 
 ---
 --- Cleanup resources
@@ -506,6 +509,7 @@ drop cascades to server integer
 drop cascades to user mapping for regress_addr_user on server integer
 DROP PUBLICATION addr_pub;
 DROP PUBLICATION addr_pub_schema;
+DROP PUBLICATION addr_pub_schema_skip;
 DROP SUBSCRIPTION regress_addr_sub;
 DROP SCHEMA addr_nsp CASCADE;
 NOTICE:  drop cascades to 14 other objects
@@ -573,6 +577,8 @@ WITH objects (classid, objid, objsubid) AS (VALUES
     ('pg_policy'::regclass, 0, 0), -- no policy
     ('pg_publication'::regclass, 0, 0), -- no publication
     ('pg_publication_rel'::regclass, 0, 0), -- no publication relation
+    ('pg_publication_schema'::regclass, 0, 0), -- no publication schema
+    ('pg_publication_skiprel'::regclass, 0, 0), -- no publication skip rel
     ('pg_subscription'::regclass, 0, 0), -- no subscription
     ('pg_transform'::regclass, 0, 0) -- no transformation
   )
@@ -623,5 +629,7 @@ ORDER BY objects.classid, objects.objid, objects.objsubid;
 ("(subscription,,,)")|("(subscription,,)")|NULL
 ("(publication,,,)")|("(publication,,)")|NULL
 ("(""publication relation"",,,)")|("(""publication relation"",,)")|NULL
+("(""publication skiprelation"",,,)")|("(""publication skiprelation"",,)")|NULL
+("(""publication schema"",,,)")|("(""publication schema"",,)")|NULL
 -- restore normal output mode
 \a\t
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index bed28ef8a7..023ed3625b 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -554,6 +554,218 @@ ALTER PUBLICATION testpub1_forschema SET SCHEMA CURRENT_SCHEMA;
 Schemas:
     "public"
 
+-- CREATE publication SKIP_TABLE
+CREATE TABLE testpub_tbl1 (id serial primary key, data text);
+CREATE TABLE testpub_tbl2 (id serial primary key, data text);
+CREATE TABLE testpub_tbl3 (id serial primary key, data text);
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub1_forskip FOR ALL TABLES SKIP_TABLE testpub_tbl1;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+
+CREATE PUBLICATION testpub2_forskip FOR SCHEMA pub_test1 SKIP_TABLE pub_test1.tbl1;
+\dRp+ testpub2_forskip
+                                     Publication testpub2_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f          | t       | t       | t       | t         | f        | s
+Schemas:
+    "pub_test1"
+Skip Tables:
+    "pub_test1.tbl1"
+
+--- Check create publication on CURRENT_SCHEMA
+CREATE PUBLICATION testpub3_forskip FOR SCHEMA CURRENT_SCHEMA SKIP_TABLE testpub_tbl2;
+\dRp+ testpub3_forskip
+                                     Publication testpub3_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f          | t       | t       | t       | t         | f        | s
+Schemas:
+    "public"
+Skip Tables:
+    "public.testpub_tbl2"
+
+CREATE PUBLICATION testpub4_forskip FOR SCHEMA CURRENT_SCHEMA SKIP_TABLE testpub_tbl3;
+RESET client_min_messages;
+\dRp+ testpub4_forskip
+                                     Publication testpub4_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f          | t       | t       | t       | t         | f        | s
+Schemas:
+    "public"
+Skip Tables:
+    "public.testpub_tbl3"
+
+--- Check create publication on a table that does not exist
+CREATE PUBLICATION testpub_forskip FOR ALL TABLES SKIP_TABLE non_existent_table;
+ERROR:  relation "non_existent_table" does not exist
+--- Check create publication on a object which is not a table
+CREATE PUBLICATION testpub_forkip FOR ALL TABLES SKIP_TABLE testpub_view;
+ERROR:  "testpub_view" is not a table
+DETAIL:  Only tables can be added to publications.
+-- Dropping the table should reflect the change in publication
+DROP TABLE testpub_tbl3;
+\dRp+ testpub4_forskip
+                                     Publication testpub4_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f          | t       | t       | t       | t         | f        | s
+Schemas:
+    "public"
+
+-- Renaming the table should reflect the change in publication
+ALTER TABLE testpub_tbl2 RENAME to testpub_tbl2_renamed;
+\dRp+ testpub3_forskip
+                                     Publication testpub3_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f          | t       | t       | t       | t         | f        | s
+Schemas:
+    "public"
+Skip Tables:
+    "public.testpub_tbl2_renamed"
+
+ALTER TABLE testpub_tbl2_renamed RENAME to testpub_tbl2;
+\dRp+ testpub3_forskip
+                                     Publication testpub3_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f          | t       | t       | t       | t         | f        | s
+Schemas:
+    "public"
+Skip Tables:
+    "public.testpub_tbl2"
+
+-- Alter publication add table
+ALTER PUBLICATION testpub1_forskip ADD SKIP_TABLE testpub_tbl2;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+    "public.testpub_tbl2"
+
+-- Add non existent table
+ALTER PUBLICATION testpub1_forskip ADD SKIP_TABLE non_existent_table;
+ERROR:  relation "non_existent_table" does not exist
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+    "public.testpub_tbl2"
+
+-- Add a table which is already added to the publication
+ALTER PUBLICATION testpub1_forskip ADD SKIP_TABLE testpub_tbl2;
+ERROR:  skip relation "testpub_tbl2" is already member of publication "testpub1_forskip"
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+    "public.testpub_tbl2"
+
+-- Alter publication drop table
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE testpub_tbl2;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+
+-- Drop table that is not preset in the publication
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE testpub_tbl2;
+ERROR:  skip relation "testpub_tbl2" is not part of the publication
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+
+-- Drop a table that does not exist in the system
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE non_existent_table;
+ERROR:  relation "non_existent_table" does not exist
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+
+-- Drop all tables
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE testpub_tbl1;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+(1 row)
+
+-- Alter publication set SKIP_TABLE
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE testpub_tbl1;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+
+-- Alter publication set multiple SKIP_TABLE
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE testpub_tbl1, testpub_tbl2;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+    "public.testpub_tbl2"
+
+-- Alter publication set non-existent table
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE non_existent_table;
+ERROR:  relation "non_existent_table" does not exist
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+    "public.testpub_tbl2"
+
+-- Alter publication set it with the same skip tables
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE testpub_tbl1, testpub_tbl2;
+\dRp+ testpub1_forskip
+                                     Publication testpub1_forskip
+          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype 
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | t          | t       | t       | t       | t         | f        | a
+Skip Tables:
+    "public.testpub_tbl1"
+    "public.testpub_tbl2"
+
+DROP TABLE testpub_tbl1;
+DROP TABLE testpub_tbl2;
 DROP VIEW testpub_view;
 DROP PUBLICATION testpub_default;
 DROP PUBLICATION testpib_ins_trunct;
@@ -561,6 +773,10 @@ DROP PUBLICATION testpub_fortbl;
 DROP PUBLICATION testpub1_forschema;
 DROP PUBLICATION testpub2_forschema;
 DROP PUBLICATION testpub3_forschema;
+DROP PUBLICATION testpub1_forskip;
+DROP PUBLICATION testpub2_forskip;
+DROP PUBLICATION testpub3_forskip;
+DROP PUBLICATION testpub4_forskip;
 DROP SCHEMA pub_test CASCADE;
 NOTICE:  drop cascades to table pub_test.testpub_nopk
 DROP SCHEMA pub_test1 CASCADE;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 56d9b852fd..db3d113b14 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -49,6 +49,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
 SET client_min_messages = 'ERROR';
 CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
 CREATE PUBLICATION addr_pub_schema FOR SCHEMA addr_nsp;
+CREATE PUBLICATION addr_pub_schema_skip FOR SCHEMA addr_nsp SKIP_TABLE addr_nsp.gentable;
 RESET client_min_messages;
 CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
 CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
@@ -200,6 +201,7 @@ WITH objects (type, name, args) AS (VALUES
 				('publication', '{addr_pub}', '{}'),
 				('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
 				('publication schema', '{addr_nsp}', '{addr_pub_schema}'),
+				('publication skiprelation', '{addr_nsp, gentable}', '{addr_pub_schema_skip}'),
 				('subscription', '{regress_addr_sub}', '{}'),
 				('statistics object', '{addr_nsp, gentable_stat}', '{}')
         )
@@ -218,6 +220,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
 DROP FOREIGN DATA WRAPPER addr_fdw CASCADE;
 DROP PUBLICATION addr_pub;
 DROP PUBLICATION addr_pub_schema;
+DROP PUBLICATION addr_pub_schema_skip;
 DROP SUBSCRIPTION regress_addr_sub;
 
 DROP SCHEMA addr_nsp CASCADE;
@@ -274,6 +277,8 @@ WITH objects (classid, objid, objsubid) AS (VALUES
     ('pg_policy'::regclass, 0, 0), -- no policy
     ('pg_publication'::regclass, 0, 0), -- no publication
     ('pg_publication_rel'::regclass, 0, 0), -- no publication relation
+    ('pg_publication_schema'::regclass, 0, 0), -- no publication schema
+    ('pg_publication_skiprel'::regclass, 0, 0), -- no publication skip rel
     ('pg_subscription'::regclass, 0, 0), -- no subscription
     ('pg_transform'::regclass, 0, 0) -- no transformation
   )
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index d55a6f42b3..d96699e92f 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -272,6 +272,91 @@ ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2;
 ALTER PUBLICATION testpub1_forschema SET SCHEMA CURRENT_SCHEMA;
 \dRp+ testpub1_forschema
 
+-- CREATE publication SKIP_TABLE
+CREATE TABLE testpub_tbl1 (id serial primary key, data text);
+CREATE TABLE testpub_tbl2 (id serial primary key, data text);
+CREATE TABLE testpub_tbl3 (id serial primary key, data text);
+
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub1_forskip FOR ALL TABLES SKIP_TABLE testpub_tbl1;
+\dRp+ testpub1_forskip
+
+CREATE PUBLICATION testpub2_forskip FOR SCHEMA pub_test1 SKIP_TABLE pub_test1.tbl1;
+\dRp+ testpub2_forskip
+
+--- Check create publication on CURRENT_SCHEMA
+CREATE PUBLICATION testpub3_forskip FOR SCHEMA CURRENT_SCHEMA SKIP_TABLE testpub_tbl2;
+\dRp+ testpub3_forskip
+
+CREATE PUBLICATION testpub4_forskip FOR SCHEMA CURRENT_SCHEMA SKIP_TABLE testpub_tbl3;
+RESET client_min_messages;
+\dRp+ testpub4_forskip
+
+--- Check create publication on a table that does not exist
+CREATE PUBLICATION testpub_forskip FOR ALL TABLES SKIP_TABLE non_existent_table;
+
+--- Check create publication on a object which is not a table
+CREATE PUBLICATION testpub_forkip FOR ALL TABLES SKIP_TABLE testpub_view;
+
+-- Dropping the table should reflect the change in publication
+DROP TABLE testpub_tbl3;
+\dRp+ testpub4_forskip
+
+-- Renaming the table should reflect the change in publication
+ALTER TABLE testpub_tbl2 RENAME to testpub_tbl2_renamed;
+\dRp+ testpub3_forskip
+
+ALTER TABLE testpub_tbl2_renamed RENAME to testpub_tbl2;
+\dRp+ testpub3_forskip
+
+-- Alter publication add table
+ALTER PUBLICATION testpub1_forskip ADD SKIP_TABLE testpub_tbl2;
+\dRp+ testpub1_forskip
+
+-- Add non existent table
+ALTER PUBLICATION testpub1_forskip ADD SKIP_TABLE non_existent_table;
+\dRp+ testpub1_forskip
+
+-- Add a table which is already added to the publication
+ALTER PUBLICATION testpub1_forskip ADD SKIP_TABLE testpub_tbl2;
+\dRp+ testpub1_forskip
+
+-- Alter publication drop table
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE testpub_tbl2;
+\dRp+ testpub1_forskip
+
+-- Drop table that is not preset in the publication
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE testpub_tbl2;
+\dRp+ testpub1_forskip
+
+-- Drop a table that does not exist in the system
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE non_existent_table;
+\dRp+ testpub1_forskip
+
+-- Drop all tables
+ALTER PUBLICATION testpub1_forskip DROP SKIP_TABLE testpub_tbl1;
+\dRp+ testpub1_forskip
+
+-- Alter publication set SKIP_TABLE
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE testpub_tbl1;
+\dRp+ testpub1_forskip
+
+-- Alter publication set multiple SKIP_TABLE
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE testpub_tbl1, testpub_tbl2;
+\dRp+ testpub1_forskip
+
+-- Alter publication set non-existent table
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE non_existent_table;
+\dRp+ testpub1_forskip
+
+-- Alter publication set it with the same skip tables
+ALTER PUBLICATION testpub1_forskip SET SKIP_TABLE testpub_tbl1, testpub_tbl2;
+\dRp+ testpub1_forskip
+
+DROP TABLE testpub_tbl1;
+DROP TABLE testpub_tbl2;
+
 DROP VIEW testpub_view;
 
 DROP PUBLICATION testpub_default;
@@ -280,6 +365,11 @@ DROP PUBLICATION testpub_fortbl;
 DROP PUBLICATION testpub1_forschema;
 DROP PUBLICATION testpub2_forschema;
 DROP PUBLICATION testpub3_forschema;
+DROP PUBLICATION testpub1_forskip;
+DROP PUBLICATION testpub2_forskip;
+DROP PUBLICATION testpub3_forskip;
+DROP PUBLICATION testpub4_forskip;
+
 
 DROP SCHEMA pub_test CASCADE;
 DROP SCHEMA pub_test1 CASCADE;
diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index 36aa4393b7..316f9ec242 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -6,7 +6,7 @@ use strict;
 use warnings;
 use PostgresNode;
 use TestLib;
-use Test::More tests => 44;
+use Test::More tests => 46;
 
 # Initialize publisher node
 my $node_publisher = get_new_node('publisher');
@@ -392,6 +392,51 @@ $node_subscriber->safe_psql('postgres', "DROP SCHEMA sch1 cascade");
 $node_subscriber->safe_psql('postgres', "DROP SCHEMA sch2 cascade");
 $node_subscriber->safe_psql('postgres', "DROP SCHEMA sch3 cascade");
 
+# Test replication with publications created using FOR SCHEMA option.
+# Create schemas and tables on publisher
+$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch1");
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab1 AS SELECT generate_series(1,10) AS a");
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab2 AS SELECT generate_series(1,10) AS a");
+
+# Create schemas and tables on subscriber
+$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch1");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab1 (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab2 (a int)");
+
+# Setup logical replication for schema sch1 and sch2 that will only be used for
+# this test
+$node_publisher->safe_psql('postgres',
+	"CREATE PUBLICATION tap_pub_skip FOR SCHEMA sch1 SKIP_TABLE sch1.tab2");
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub_skip CONNECTION '$publisher_connstr' PUBLICATION tap_pub_skip"
+	);
+
+$node_publisher->wait_for_catchup('tap_sub_skip');
+
+# Also wait for initial table sync to finish
+$synced_query =
+  "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');";
+$node_subscriber->poll_query_until('postgres', $synced_query)
+  or die "Timed out while waiting for subscriber to synchronize data";
+
+# Check the schema table data is synced up.
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(10|1|10), 'check rows on subscriber catchup');
+$result = $node_subscriber->safe_psql('postgres',
+	"SELECT count(*), min(a), max(a) FROM sch1.tab2");
+is($result, qq(0||), 'check rows on subscriber catchup');
+
+# Drop subscription as we don't need it anymore
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_skip");
+
+# Drop publications as we don't need them anymore
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_skip");
+
+# Clean up the tables on both publisher and subscriber as we don't need them
+$node_publisher->safe_psql('postgres', "DROP SCHEMA sch1 cascade");
+$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch1 cascade");
+
 # add REPLICA IDENTITY FULL so we can update
 $node_publisher->safe_psql('postgres',
 	"ALTER TABLE tab_full REPLICA IDENTITY FULL");
-- 
2.25.1