postgresql16/CREATE_PUBLICATION(7)

1CREATE PUBLICATION(7)    PostgreSQL 16.1 Documentation   CREATE PUBLICATION(7)
2
3
4

NAME

6       CREATE_PUBLICATION - define a new publication
7

SYNOPSIS

9       CREATE PUBLICATION name
10           [ FOR ALL TABLES
11             | FOR publication_object [, ... ] ]
12           [ WITH ( publication_parameter [= value] [, ... ] ) ]
13
14       where publication_object is one of:
15
16           TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
17           TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
18

DESCRIPTION

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
24       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

PARAMETERS

30       name
31           The name of the new publication.
32
33       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
44           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
49           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
55           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
60           Specifying a column list when the publication also publishes FOR
61           TABLES IN SCHEMA is not supported.
62
63           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
69       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
73       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
78           Specifying a schema when the publication also publishes a table
79           with a column list is not supported.
80
81           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
86           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
93       WITH ( publication_parameter [= value] [, ... ] )
94           This clause specifies optional parameters for a publication. The
95           following parameters are supported:
96
97           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
105               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
110           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
120               There can be a case where a subscription combines multiple
121               publications. If a partitioned table is published by any
122               subscribed publications which set publish_via_partition_root =
123               true, changes on this partitioned table (or on its partitions)
124               will be published using the identity and schema of this
125               partitioned table rather than that of the individual
126               partitions.
127
128               This parameter also affects how row filters and column lists
129               are chosen for partitions; see below for details.
130
131               If this is enabled, TRUNCATE operations performed directly on
132               partitions are not replicated.
133
134       When specifying a parameter of type boolean, the = value part can be
135       omitted, which is equivalent to specifying TRUE.
136

NOTES

138       If FOR TABLE, FOR ALL TABLES or FOR TABLES IN SCHEMA are not specified,
139       then the publication starts out with an empty set of tables. That is
140       useful if tables or schemas are to be added later.
141
142       The creation of a publication does not start replication. It only
143       defines a grouping and filtering logic for future subscribers.
144
145       To create a publication, the invoking user must have the CREATE
146       privilege for the current database. (Of course, superusers bypass this
147       check.)
148
149       To add a table to a publication, the invoking user must have ownership
150       rights on the table. The FOR ALL TABLES and FOR TABLES IN SCHEMA
151       clauses require the invoking user to be a superuser.
152
153       The tables added to a publication that publishes UPDATE and/or DELETE
154       operations must have REPLICA IDENTITY defined. Otherwise those
155       operations will be disallowed on those tables.
156
157       Any column list must include the REPLICA IDENTITY columns in order for
158       UPDATE or DELETE operations to be published. There are no column list
159       restrictions if the publication publishes only INSERT operations.
160
161       A row filter expression (i.e., the WHERE clause) must contain only
162       columns that are covered by the REPLICA IDENTITY, in order for UPDATE
163       and DELETE operations to be published. For publication of INSERT
164       operations, any column may be used in the WHERE expression. The row
165       filter allows simple expressions that don't have user-defined
166       functions, user-defined operators, user-defined types, user-defined
167       collations, non-immutable built-in functions, or references to system
168       columns.
169
170       The row filter on a table becomes redundant if FOR TABLES IN SCHEMA is
171       specified and the table belongs to the referred schema.
172
173       For published partitioned tables, the row filter for each partition is
174       taken from the published partitioned table if the publication parameter
175       publish_via_partition_root is true, or from the partition itself if it
176       is false (the default). See Section 31.3 for details about row filters.
177       Similarly, for published partitioned tables, the column list for each
178       partition is taken from the published partitioned table if the
179       publication parameter publish_via_partition_root is true, or from the
180       partition itself if it is false.
181
182       For an INSERT ... ON CONFLICT command, the publication will publish the
183       operation that results from the command. Depending on the outcome, it
184       may be published as either INSERT or UPDATE, or it may not be published
185       at all.
186
187       For a MERGE command, the publication will publish an INSERT, UPDATE, or
188       DELETE for each row inserted, updated, or deleted.
189
190       ATTACHing a table into a partition tree whose root is published using a
191       publication with publish_via_partition_root set to true does not result
192       in the table's existing contents being replicated.
193
194       COPY ... FROM commands are published as INSERT operations.
195
196       DDL operations are not published.
197
198       The WHERE clause expression is executed with the role used for the
199       replication connection.
200

EXAMPLES

202       Create a publication that publishes all changes in two tables:
203
204           CREATE PUBLICATION mypublication FOR TABLE users, departments;
205
206       Create a publication that publishes all changes from active
207       departments:
208
209           CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);
210
211       Create a publication that publishes all changes in all tables:
212
213           CREATE PUBLICATION alltables FOR ALL TABLES;
214
215       Create a publication that only publishes INSERT operations in one
216       table:
217
218           CREATE PUBLICATION insert_only FOR TABLE mydata
219               WITH (publish = 'insert');
220
221       Create a publication that publishes all changes for tables users,
222       departments and all changes for all the tables present in the schema
223       production:
224
225           CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;
226
227       Create a publication that publishes all changes for all the tables
228       present in the schemas marketing and sales:
229
230           CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
231
232       Create a publication that publishes all changes for table users, but
233       replicates only columns user_id and firstname:
234
235           CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
236

COMPATIBILITY

238       CREATE PUBLICATION is a PostgreSQL extension.
239