1CREATE PUBLICATION(7) PostgreSQL 15.4 Documentation CREATE PUBLICATION(7)
2
6 CREATE_PUBLICATION - define a new publication
7
9 CREATE PUBLICATION name
10 [ FOR ALL TABLES
11 | FOR publication_object [, ... ] ]
12 [ WITH ( publication_parameter [= value] [, ... ] ) ]
13 where publication_object is one of:
15 TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
17 TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
18
20 CREATE PUBLICATION adds a new publication into the current database.
21 The publication name must be distinct from the name of any existing
22 publication in the current database.
23 A publication is essentially a group of tables whose data changes are
25 intended to be replicated through logical replication. See Section 31.1
26 for details about how publications fit into the logical replication
27 setup.
28
30 name
31 The name of the new publication.
32 FOR TABLE
34 Specifies a list of tables to add to the publication. If ONLY is
35 specified before the table name, only that table is added to the
36 publication. If ONLY is not specified, the table and all its
37 descendant tables (if any) are added. Optionally, * can be
38 specified after the table name to explicitly indicate that
39 descendant tables are included. This does not apply to a
40 partitioned table, however. The partitions of a partitioned table
41 are always implicitly considered part of the publication, so they
42 are never explicitly added to the publication.
43 If the optional WHERE clause is specified, it defines a row filter
45 expression. Rows for which the expression evaluates to false or
46 null will not be published. Note that parentheses are required
47 around the expression. It has no effect on TRUNCATE commands.
48 When a column list is specified, only the named columns are
50 replicated. If no column list is specified, all columns of the
51 table are replicated through this publication, including any
52 columns added later. It has no effect on TRUNCATE commands. See
53 Section 31.4 for details about column lists.
54 Only persistent base tables and partitioned tables can be part of a
56 publication. Temporary tables, unlogged tables, foreign tables,
57 materialized views, and regular views cannot be part of a
58 publication.
59 Specifying a column list when the publication also publishes FOR
61 TABLES IN SCHEMA is not supported.
62 When a partitioned table is added to a publication, all of its
64 existing and future partitions are implicitly considered to be part
65 of the publication. So, even operations that are performed directly
66 on a partition are also published via publications that its
67 ancestors are part of.
68 FOR ALL TABLES
70 Marks the publication as one that replicates changes for all tables
71 in the database, including tables created in the future.
72 FOR TABLES IN SCHEMA
74 Marks the publication as one that replicates changes for all tables
75 in the specified list of schemas, including tables created in the
76 future.
77 Specifying a schema when the publication also publishes a table
79 with a column list is not supported.
80 Only persistent base tables and partitioned tables present in the
82 schema will be included as part of the publication. Temporary
83 tables, unlogged tables, foreign tables, materialized views, and
84 regular views from the schema will not be part of the publication.
85 When a partitioned table is published via schema level publication,
87 all of its existing and future partitions are implicitly considered
88 to be part of the publication, regardless of whether they are from
89 the publication schema or not. So, even operations that are
90 performed directly on a partition are also published via
91 publications that its ancestors are part of.
92 WITH ( publication_parameter [= value] [, ... ] )
94 This clause specifies optional parameters for a publication. The
95 following parameters are supported:
96 publish (string)
98 This parameter determines which DML operations will be
99 published by the new publication to the subscribers. The value
100 is comma-separated list of operations. The allowed operations
101 are insert, update, delete, and truncate. The default is to
102 publish all actions, and so the default value for this option
103 is 'insert, update, delete, truncate'.
104 This parameter only affects DML operations. In particular, the
106 initial data synchronization (see Section 31.7.1) for logical
107 replication does not take this parameter into account when
108 copying existing table data.
109 publish_via_partition_root (boolean)
111 This parameter determines whether changes in a partitioned
112 table (or on its partitions) contained in the publication will
113 be published using the identity and schema of the partitioned
114 table rather than that of the individual partitions that are
115 actually changed; the latter is the default. Enabling this
116 allows the changes to be replicated into a non-partitioned
117 table or a partitioned table consisting of a different set of
118 partitions.
119 This parameter also affects how row filters and column lists
121 are chosen for partitions; see below for details.
122 If this is enabled, TRUNCATE operations performed directly on
124 partitions are not replicated.
125
127 If FOR TABLE, FOR ALL TABLES or FOR TABLES IN SCHEMA are not specified,
128 then the publication starts out with an empty set of tables. That is
129 useful if tables or schemas are to be added later.
130 The creation of a publication does not start replication. It only
132 defines a grouping and filtering logic for future subscribers.
133 To create a publication, the invoking user must have the CREATE
135 privilege for the current database. (Of course, superusers bypass this
136 check.)
137 To add a table to a publication, the invoking user must have ownership
139 rights on the table. The FOR ALL TABLES and FOR TABLES IN SCHEMA
140 clauses require the invoking user to be a superuser.
141 The tables added to a publication that publishes UPDATE and/or DELETE
143 operations must have REPLICA IDENTITY defined. Otherwise those
144 operations will be disallowed on those tables.
145 Any column list must include the REPLICA IDENTITY columns in order for
147 UPDATE or DELETE operations to be published. There are no column list
148 restrictions if the publication publishes only INSERT operations.
149 A row filter expression (i.e., the WHERE clause) must contain only
151 columns that are covered by the REPLICA IDENTITY, in order for UPDATE
152 and DELETE operations to be published. For publication of INSERT
153 operations, any column may be used in the WHERE expression. The row
154 filter allows simple expressions that don't have user-defined
155 functions, user-defined operators, user-defined types, user-defined
156 collations, non-immutable built-in functions, or references to system
157 columns.
158 The row filter on a table becomes redundant if FOR TABLES IN SCHEMA is
160 specified and the table belongs to the referred schema.
161 For published partitioned tables, the row filter for each partition is
163 taken from the published partitioned table if the publication parameter
164 publish_via_partition_root is true, or from the partition itself if it
165 is false (the default). See Section 31.3 for details about row filters.
166 Similarly, for published partitioned tables, the column list for each
167 partition is taken from the published partitioned table if the
168 publication parameter publish_via_partition_root is true, or from the
169 partition itself if it is false.
170 For an INSERT ... ON CONFLICT command, the publication will publish the
172 operation that results from the command. Depending on the outcome, it
173 may be published as either INSERT or UPDATE, or it may not be published
174 at all.
175 For a MERGE command, the publication will publish an INSERT, UPDATE, or
177 DELETE for each row inserted, updated, or deleted.
178 ATTACHing a table into a partition tree whose root is published using a
180 publication with publish_via_partition_root set to true does not result
181 in the table's existing contents being replicated.
182 COPY ... FROM commands are published as INSERT operations.
184 DDL operations are not published.
186 The WHERE clause expression is executed with the role used for the
188 replication connection.
189
191 Create a publication that publishes all changes in two tables:
192 CREATE PUBLICATION mypublication FOR TABLE users, departments;
194 Create a publication that publishes all changes from active
196 departments:
197 CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);
199 Create a publication that publishes all changes in all tables:
201 CREATE PUBLICATION alltables FOR ALL TABLES;
203 Create a publication that only publishes INSERT operations in one
205 table:
206 CREATE PUBLICATION insert_only FOR TABLE mydata
208 WITH (publish = 'insert');
209 Create a publication that publishes all changes for tables users,
211 departments and all changes for all the tables present in the schema
212 production:
213 CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;
215 Create a publication that publishes all changes for all the tables
217 present in the schemas marketing and sales:
218 CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
220 Create a publication that publishes all changes for table users, but
222 replicates only columns user_id and firstname:
223 CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
225
227 CREATE PUBLICATION is a PostgreSQL extension.
228
230 ALTER PUBLICATION (ALTER_PUBLICATION(7)), DROP PUBLICATION
231 (DROP_PUBLICATION(7)), CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)),
232 ALTER SUBSCRIPTION (ALTER_SUBSCRIPTION(7))
233PostgreSQL 15.4 2023 CREATE PUBLICATION(7)