CREATE_PUBLICATION(7)

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

NAME

6       CREATE_PUBLICATION - define a new publication
7

SYNOPSIS

9       CREATE PUBLICATION name
10           [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
11             | FOR ALL TABLES ]
12           [ WITH ( publication_parameter [= value] [, ... ] ) ]
13

DESCRIPTION

15       CREATE PUBLICATION adds a new publication into the current database.
16       The publication name must be distinct from the name of any existing
17       publication in the current database.
18
19       A publication is essentially a group of tables whose data changes are
20       intended to be replicated through logical replication. See Section 30.1
21       for details about how publications fit into the logical replication
22       setup.
23

PARAMETERS

25       name
26           The name of the new publication.
27
28       FOR TABLE
29           Specifies a list of tables to add to the publication. If ONLY is
30           specified before the table name, only that table is added to the
31           publication. If ONLY is not specified, the table and all its
32           descendant tables (if any) are added. Optionally, * can be
33           specified after the table name to explicitly indicate that
34           descendant tables are included. This does not apply to a
35           partitioned table, however. The partitions of a partitioned table
36           are always implicitly considered part of the publication, so they
37           are never explicitly added to the publication.
38
39           Only persistent base tables and partitioned tables can be part of a
40           publication. Temporary tables, unlogged tables, foreign tables,
41           materialized views, and regular views cannot be part of a
42           publication.
43
44           When a partitioned table is added to a publication, all of its
45           existing and future partitions are implicitly considered to be part
46           of the publication. So, even operations that are performed directly
47           on a partition are also published via publications that its
48           ancestors are part of.
49
50       FOR ALL TABLES
51           Marks the publication as one that replicates changes for all tables
52           in the database, including tables created in the future.
53
54       WITH ( publication_parameter [= value] [, ... ] )
55           This clause specifies optional parameters for a publication. The
56           following parameters are supported:
57
58           publish (string)
59               This parameter determines which DML operations will be
60               published by the new publication to the subscribers. The value
61               is comma-separated list of operations. The allowed operations
62               are insert, update, delete, and truncate. The default is to
63               publish all actions, and so the default value for this option
64               is 'insert, update, delete, truncate'.
65
66           publish_via_partition_root (boolean)
67               This parameter determines whether changes in a partitioned
68               table (or on its partitions) contained in the publication will
69               be published using the identity and schema of the partitioned
70               table rather than that of the individual partitions that are
71               actually changed; the latter is the default. Enabling this
72               allows the changes to be replicated into a non-partitioned
73               table or a partitioned table consisting of a different set of
74               partitions.
75
76               If this is enabled, TRUNCATE operations performed directly on
77               partitions are not replicated.
78

NOTES

80       If neither FOR TABLE nor FOR ALL TABLES is specified, then the
81       publication starts out with an empty set of tables. That is useful if
82       tables are to be added later.
83
84       The creation of a publication does not start replication. It only
85       defines a grouping and filtering logic for future subscribers.
86
87       To create a publication, the invoking user must have the CREATE
88       privilege for the current database. (Of course, superusers bypass this
89       check.)
90
91       To add a table to a publication, the invoking user must have ownership
92       rights on the table. The FOR ALL TABLES clause requires the invoking
93       user to be a superuser.
94
95       The tables added to a publication that publishes UPDATE and/or DELETE
96       operations must have REPLICA IDENTITY defined. Otherwise those
97       operations will be disallowed on those tables.
98
99       For an INSERT ... ON CONFLICT command, the publication will publish the
100       operation that actually results from the command. So depending of the
101       outcome, it may be published as either INSERT or UPDATE, or it may not
102       be published at all.
103
104       COPY ... FROM commands are published as INSERT operations.
105
106       DDL operations are not published.
107

EXAMPLES

109       Create a publication that publishes all changes in two tables:
110
111           CREATE PUBLICATION mypublication FOR TABLE users, departments;
112
113       Create a publication that publishes all changes in all tables:
114
115           CREATE PUBLICATION alltables FOR ALL TABLES;
116
117       Create a publication that only publishes INSERT operations in one
118       table:
119
120           CREATE PUBLICATION insert_only FOR TABLE mydata
121               WITH (publish = 'insert');
122

COMPATIBILITY

124       CREATE PUBLICATION is a PostgreSQL extension.
125