Home Explore Blog CI



postgresql

18th chunk of `doc/src/sgml/logical-replication.sgml`
eb82724d1d03e7266b358e68dcf3ca2cd2e5773a52874f530000000100000fa4
 However, if the subscriber is from a release prior to 15, then
   all the columns in the table are copied during initial data synchronization,
   ignoring any column lists. If the subscriber is from a release prior to 18,
   then initial table synchronization won't copy generated columns even if they
   are defined in the publisher.
  </para>

   <warning id="logical-replication-col-list-combining">
    <title>Warning: Combining Column Lists from Multiple Publications</title>
    <para>
     There's currently no support for subscriptions comprising several
     publications where the same table has been published with different
     column lists.  <xref linkend="sql-createsubscription"/> disallows
     creating such subscriptions, but it is still possible to get into
     that situation by adding or altering column lists on the publication
     side after a subscription has been created.
    </para>
    <para>
     This means changing the column lists of tables on publications that are
     already subscribed could lead to errors being thrown on the subscriber
     side.
    </para>
    <para>
     If a subscription is affected by this problem, the only way to resume
     replication is to adjust one of the column lists on the publication
     side so that they all match; and then either recreate the subscription,
     or use <link linkend="sql-altersubscription-params-setadddrop-publication">
     <literal>ALTER SUBSCRIPTION ... DROP PUBLICATION</literal></link> to
     remove one of the offending publications and add it again.
    </para>
   </warning>

  <sect2 id="logical-replication-col-list-examples">
   <title>Examples</title>

   <para>
    Create a table <literal>t1</literal> to be used in the following example.
<programlisting>
/* pub # */ CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
</programlisting></para>

   <para>
    Create a publication <literal>p1</literal>. A column list is defined for
    table <literal>t1</literal> to reduce the number of columns that will be
    replicated. Notice that the order of column names in the column list does
    not matter.
<programlisting>
/* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
</programlisting></para>

    <para>
     <literal>psql</literal> can be used to show the column lists (if defined)
     for each publication.
<programlisting>
/* pub # */ \dRp+
                               Publication p1
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1" (id, a, b, d)
</programlisting></para>

    <para>
     <literal>psql</literal> can be used to show the column lists (if defined)
     for each table.
<programlisting>
/* pub # */ \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 a      | text    |           |          |
 b      | text    |           |          |
 c      | text    |           |          |
 d      | text    |           |          |
 e      | text    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Publications:
    "p1" (id, a, b, d)
</programlisting></para>

    <para>
     On the subscriber node, create a table <literal>t1</literal> which now
     only needs a subset of the columns that were on the publisher table
     <literal>t1</literal>, and also create the subscription
     <literal>s1</literal> that subscribes to the publication
     <literal>p1</literal>.
<programlisting>
/* sub # */ CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
/* sub # */ CREATE SUBSCRIPTION s1
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
/* sub - */ PUBLICATION p1;
</programlisting></para>

    <para>
     On the publisher node, insert

Title: Column Lists in Logical Replication: Warnings and Examples
Summary
This section covers a warning regarding combining column lists from multiple publications and provides an example of creating a publication with a column list. It also demonstrates how to view publication and table column lists using psql and how to create a corresponding table and subscription on the subscriber side.