From 665d1fad99e7b11678b0d5fa24d2898424243cd6 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 19 Jan 2017 12:00:00 -0500 Subject: Logical replication - Add PUBLICATION catalogs and DDL - Add SUBSCRIPTION catalog and DDL - Define logical replication protocol and output plugin - Add logical replication workers From: Petr Jelinek Reviewed-by: Steve Singer Reviewed-by: Andres Freund Reviewed-by: Erik Rijkers Reviewed-by: Peter Eisentraut --- doc/src/sgml/catalogs.sgml | 309 +++++++++++++ doc/src/sgml/config.sgml | 41 ++ doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/func.sgml | 2 +- doc/src/sgml/logical-replication.sgml | 396 ++++++++++++++++ doc/src/sgml/monitoring.sgml | 74 +++ doc/src/sgml/postgres.sgml | 1 + doc/src/sgml/protocol.sgml | 721 ++++++++++++++++++++++++++++++ doc/src/sgml/ref/allfiles.sgml | 6 + doc/src/sgml/ref/alter_publication.sgml | 139 ++++++ doc/src/sgml/ref/alter_subscription.sgml | 139 ++++++ doc/src/sgml/ref/create_publication.sgml | 206 +++++++++ doc/src/sgml/ref/create_subscription.sgml | 176 ++++++++ doc/src/sgml/ref/drop_publication.sgml | 107 +++++ doc/src/sgml/ref/drop_subscription.sgml | 110 +++++ doc/src/sgml/ref/pg_dump.sgml | 21 + doc/src/sgml/ref/psql-ref.sgml | 28 ++ doc/src/sgml/reference.sgml | 6 + 18 files changed, 2482 insertions(+), 1 deletion(-) create mode 100644 doc/src/sgml/logical-replication.sgml create mode 100644 doc/src/sgml/ref/alter_publication.sgml create mode 100644 doc/src/sgml/ref/alter_subscription.sgml create mode 100644 doc/src/sgml/ref/create_publication.sgml create mode 100644 doc/src/sgml/ref/create_subscription.sgml create mode 100644 doc/src/sgml/ref/drop_publication.sgml create mode 100644 doc/src/sgml/ref/drop_subscription.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 493050618df..7c758a5081a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -240,6 +240,16 @@ functions and procedures + + pg_publication + publications for logical replication + + + + pg_publication_rel + relation to publication mapping + + pg_range information about range types @@ -285,6 +295,11 @@ planner statistics + + pg_subscription + logical replication subscriptions + + pg_tablespace tablespaces within this database cluster @@ -5271,6 +5286,137 @@ + + <structname>pg_publication</structname> + + + pg_publication + + + + The catalog pg_publication contains all + publications created in the database. For more on publications see + . + + + + <structname>pg_publication</structname> Columns + + + + + Name + Type + References + Description + + + + + + oid + oid + + Row identifier (hidden attribute; must be explicitly selected) + + + + pubname + Name + + Name of the publication + + + + pubowner + oid + pg_authid.oid + Owner of the publication + + + + puballtables + bool + + If true, this publication automatically includes all tables + in the database, including any that will be created in the future. + + + + + pubinsert + bool + + If true, INSERT operations are replicated for + tables in the publication. + + + + pubupdate + bool + + If true, UPDATE operations are replicated for + tables in the publication. + + + + pubdelete + bool + + If true, DELETE operations are replicated for + tables in the publication. + + + +
+ + + + <structname>pg_publication_rel</structname> + + + pg_publication_rel + + + + The catalog pg_publication_rel contains the + mapping between relations and publications in the database. This is a + many-to-many mapping. See also + for a more user-friendly view of this information. + + + + <structname>pg_publication_rel</structname> Columns + + + + + Name + Type + References + Description + + + + + + prpubid + oid + pg_publication.oid + Reference to publication + + + + prrelid + oid + pg_class.oid + Reference to relation + + + +
+ + <structname>pg_range</structname> @@ -6150,6 +6296,109 @@ + + <structname>pg_subscription</structname> + + + pg_subscription + + + + The catalog pg_subscription contains all existing + logical replication subscriptions. For more information about logical + replication see . + + + + Unlike most system catalogs, pg_subscription is + shared across all databases of a cluster: There is only one copy + of pg_subscription per cluster, not one per + database. + + + + Access to this catalog is restricted from normal users. Normal users can + use the view to get some information + about subscriptions. + + + + <structname>pg_subscription</structname> Columns + + + + + Name + Type + References + Description + + + + + + oid + oid + + Row identifier (hidden attribute; must be explicitly selected) + + + + subdbid + oid + pg_database.oid + OID of the database which the subscription resides in + + + + subname + name + + Name of the subscription + + + + subowner + oid + pg_authid.oid + Owner of the subscription + + + + subenabled + bool + + If true, the subscription is enabled and should be replicating. + + + + subconninfo + text + + Connection string to the upstream database + + + + subslotname + name + + Name of the replication slot in the upstream database. Also used + for local replication origin name. + + + + subpublications + text[] + + Array of subscribed publication names. These reference the + publications on the publisher server. For more on publications + see . + + + + +
+ <structname>pg_tablespace</structname> @@ -7589,6 +7838,11 @@ prepared transactions + + pg_publication_tables + publications and their associated tables + + pg_replication_origin_status information about replication origins, including replication progress @@ -8871,6 +9125,61 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + <structname>pg_publication_tables</structname> + + + pg_publication_tables + + + + The view pg_publication_tables provides + information about the mapping between publications and the tables they + contain. Unlike the underlying + catalog pg_publication_rel, this view expands + publications defined as FOR ALL TABLES, so for such + publications there will be a row for each eligible table. + + + + <structname>pg_publication_tables</structname> Columns + + + + + Name + Type + References + Description + + + + + + pubname + name + pg_publication.pubname + Name of publication + + + + schemaname + name + pg_namespace.nspname + Name of schema containing table + + + + tablename + name + pg_class.relname + Name of table + + + +
+ + <structname>pg_replication_origin_status</structname> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 07afa3c77a7..fb5d6473efe 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -3411,6 +3411,47 @@ ANY num_sync ( + Subscribers + + + These settings control the behavior of a logical replication subscriber. + Their values on the publisher are irrelevant. + + + + Note that wal_receiver_timeout and + wal_retrieve_retry_interval configuration parameters + affect the logical replication workers as well. + + + + + + max_logical_replication_workers (int) + + max_logical_replication_workers configuration parameter + + + + + Specifies maximum number of logical replication workers. This includes + both apply workers and table synchronization workers. + + + Logical replication workers are taken from the pool defined by + max_worker_processes. + + + The default value is 4. + + + + + + + diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 69649a7da4b..2624c627dcb 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -50,6 +50,7 @@ + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2504a466e6d..b214218791e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18762,7 +18762,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); - + pg_replication_origin_advance diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml new file mode 100644 index 00000000000..9312c0c9a0d --- /dev/null +++ b/doc/src/sgml/logical-replication.sgml @@ -0,0 +1,396 @@ + + + + Logical Replication + + + Logical replication is a method of replicating data objects and their + changes, based upon their replication identity (usually a primary key). We + use the term logical in contrast to physical replication, which uses exact + block addresses and byte-by-byte replication. PostgreSQL supports both + mechanisms concurrently, see . Logical + replication allows fine-grained control over both data replication and + security. + + + + Logical replication uses a publish + and subscribe model with one or + more subscribers subscribing to one or more + publications on a publisher + node. Subscribers pull data from the publications they subscribe to and may + subsequently re-publish data to allow cascading replication or more complex + configurations. + + + + Logical replication sends the changes on the publisher to the subscriber as + they occur in real-time. The subscriber applies the data in the same order + as the publisher so that transactional consistency is guaranteed for + publications within a single subscription. This method of data replication + is sometimes referred to as transactional replication. + + + + The typical use-cases for logical replication are: + + + + + Sending incremental changes in a single database or a subset of a + database to subscribers as they occur. + + + + + + Firing triggers for individual changes as they are incoming to + subscriber. + + + + + + Consolidating multiple databases into a single one (for example for + analytical purposes). + + + + + + Replicating between different major versions of PostgreSQL. + + + + + + Giving access to replicated data to different groups of users. + + + + + + Sharing a subset of the database between multiple databases. + + + + + + + The subscriber database behaves in the same way as any other PostgreSQL + instance and can be used as a publisher for other databases by defining its + own publications. When the subscriber is treated as read-only by + application, there will be no conflicts from a single subscription. On the + other hand, if there are other writes done either by application or other + subscribers to the same set of tables conflicts can arise. + + + + Publication + + + A publication object can be defined on any physical + replication master. The node where a publication is defined is referred to + as publisher. A publication is a set of changes + generated from a group of tables, and might also be described as a change + set or replication set. Each publication exists in only one database. + + + + Publications are different from schemas and do not affect how the table is + accessed. Each table can be added to multiple publications if needed. + Publications may currently only contain tables. Objects must be added + explicitly, except when a publication is created for ALL + TABLES. + + + + Publications can choose to limit the changes they produce to show + any combination of INSERT, UPDATE, and + DELETE in a similar way to the way triggers are fired by + particular event types. If a table without a REPLICA + IDENTITY is added to a publication that + replicates UPDATE or DELETE + operations then subsequent UPDATE + or DELETE operations will fail on the publisher. + + + + Every publication can have multiple subscribers. + + + + A publication is created using the + command and may be later altered or dropped using corresponding commands. + + + + The individual tables can be added and removed dynamically using + . Both the ADD + TABLE and DROP TABLE operations are + transactional; so the table will start or stop replicating at the correct + snapshot once the transaction has committed. + + + + + Subscription + + + A subscription is the downstream side of logical + replication. The node where a subscription is defined is referred to as + the subscriber. Subscription defines the connection + to another database and set of publications (one or more) to which it wants + to be subscribed. + + + + The subscriber database behaves in the same way as any other PostgreSQL + instance and can be used as a publisher for other databases by defining its + own publications. + + + + A subscriber node may have multiple subscriptions if desired. It is + possible to define multiple subscriptions between a single + publisher-subscriber pair, in which case extra care must be taken to ensure + that the subscribed publication objects don't overlap. + + + + Each subscription will receive changes via one replication slot (see + ). + + + + Subscriptions are not dumped by pg_dump by default but + can be requested using the command-line + option . + + + + The subscription is added using and + can be stopped/resumed at any time using the + command and removed using + . + + + + When a subscription is dropped and recreated, the synchronization + information is lost. This means that the data has to be resynchronized + afterwards. + + + + The schema definitions are not replicated and the published tables must + exist on the subsriber for replication to work. Only regular tables may be + the target of replication. For example, you can't replicate to a view. + + + + The tables are matched between the publisher and the subscriber using the + fully qualified table name. Replication to differently-named tables on the + subscriber is not supported. + + + + Columns of a table are also matched by name. A different order of columns + in the target table is allowed, but the column types have to match. + + + + + Conflicts + + + The logical replication behaves similarly to normal DML operations in that + the data will be updated even if it was changed locally on the subscriber + node. If the incoming data violates any constraints the replication will + stop. This is referred to as a conflict. When + replicating UPDATE or DELETE + operations, missing data will not produce a conflict and such operations + will simply be skipped. + + + + A conflict will produce an error and will stop the replication; it must be + resolved manually by the user. Details about the conflict can be found in + the subscriber's server log. + + + + The resolution can be done either by changing data on the subscriber so + that it does not conflict with the incoming change or by skipping the + transaction that conflicts with the existing data. The transaction can be + skipped by calling the + pg_replication_origin_advance() function with + a node_name corresponding to the subscription name. + The current position of origins can be seen in the + + pg_replication_origin_status system view. + + + + + Architecture + + + Logical replication starts by copying a snapshot of the data on the + publisher database. Once that is done, changes on the publisher are sent + to the subscriber as they occur in real time. The subscriber applies data + in the order in which commits were made on the publisher so that + transactional consistency is guaranteed for the publications within any + single subscription. + + + + Logical replication is built with an architecture similar to physical + streaming replication (see ). It is + implemented by walsender and the apply + processes. The walsender starts logical decoding (described + in ) of the WAL and loads the standard + logical decoding plugin (pgoutput). The plugin transforms the changes read + from WAL to the logical replication protocol + (see ) and filters the data + according to the publication specification. The data is then continuously + transferred using the streaming replication protocol to the apply worker, + which maps the data to local tables and applies the individual changes as + they are received in exact transactional order. + + + + The apply process on the subscriber database always runs with + session_replication_role set + to replica, which produces the usual effects on triggers + and constraints. + + + + + Monitoring + + + Because logical replication is based on similar architecture as + physical streaming replication + the monitoring on a publication node is very similar to monitoring of + physical replication master + (see ). + + + + The monitoring information about subscription is visible in + pg_stat_subscription. + This view contains one row for every subscription worker. A subscription + can have zero or more active subscription workers depending on its state. + + + + Normally, there is a single apply process running for an enabled + subscription. A disabled subscription or a crashed subscription will have + zero rows in this view. + + + + + Security + + + Logical replication connections occur in the same way as physical streaming + replication. It requires access to be specifically given using + pg_hba.conf. The role used for the replication + connection must have the REPLICATION attribute. This + gives a role access to both logical and physical replication. + + + + To create a publication, the user must have the CREATE + privilege in the database. + + + + To create a subscription, the user must be a superuser. + + + + The subscription apply process will run in the local database with the + privileges of a superuser. + + + + Privileges are only checked once at the start of a replication connection. + They are not re-checked as each change record is read from the publisher, + nor are they re-checked for each change when applied. + + + + + Configuration Settings + + + Logical replication requires several configuration options to be set. + + + + On the publisher side, wal_level must be set to + logical, and max_replication_slots + has to be set to at least the number of subscriptions expected to connect. + And max_wal_senders should be set to at least the same + as max_replication_slots plus the number of physical replicas + that are connected at the same time. + + + + The subscriber also requires the max_replication_slots + to be set. In this case it should be set to at least the number of + subscriptions that will be added to the subscriber. + max_logical_replication_workers has to be set to at + least the number of subscriptions. Additionally the + max_worker_processes may need to be adjusted to + accommodate for replication workers, at least + (max_logical_replication_workers + + 1). Note that some extensions and parallel queries + also take worker slots from max_worker_processes. + + + + + Quick Setup + + + First set the configuration options in postgresql.conf: + +wal_level = logical + + The other required settings have default values that are sufficient for a + basic setup. + + + + pg_hba.conf needs to be adjusted to allow replication + (the values here depend on your actual network configuration and user you + want to use for connecting): + +host replication repuser 0.0.0.0/0 md5 + + + + + Then on the publisher database: + +CREATE PUBLICATION mypub FOR TABLE users, departments; + + + + + And on the subscriber database: + +CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub; + + + + + The above will start the replication process of changes to + users and departments tables. + + + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 1545f03656c..01fad3870f6 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -308,6 +308,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser + + pg_stat_subscriptionpg_stat_subscription + At least one row per subscription, showing information about + the subscription workers. + See for details. + + + pg_stat_sslpg_stat_ssl One row per connection (regular and replication), showing information about @@ -1545,6 +1553,72 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i connected server. + + <structname>pg_stat_subscription</structname> View + + + + Column + Type + Description + + + + + + subid + oid + OID of the subscription + + + subname + text + Name of the subscription + + + pid + integer + Process ID of the subscription worker process + + + received_lsn + pg_lsn + Last transaction log position received, the initial value of + this field being 0 + + + last_msg_send_time + timestamp with time zone + Send time of last message received from origin WAL sender + + + last_msg_receipt_time + timestamp with time zone + Receipt time of last message received from origin WAL sender + + + + latest_end_lsn + pg_lsn + Last transaction log position reported to origin WAL sender + + + + latest_end_time + timestamp with time zone + Time of last transaction log position reported to origin WAL + sender + + + +
+ + + The pg_stat_subscription view will contain one + row per subscription for main worker (with null PID if the worker is + not running). + + <structname>pg_stat_ssl</structname> View diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml index 9143917c490..4e169d1b189 100644 --- a/doc/src/sgml/postgres.sgml +++ b/doc/src/sgml/postgres.sgml @@ -160,6 +160,7 @@ &monitoring; &diskusage; &wal; + &logical-replication; ®ress; diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 9ba147cae5e..5f89db58570 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -2122,6 +2122,119 @@ The commands accepted in walsender mode are: + + Logical Streaming Replication Protocol + + + This section describes the logical replication protocol, which is the message + flow started by the START_REPLICATION + SLOT slot_name + LOGICAL replication command. + + + + The logical streaming replication protocol builds on the primitives of + the physical streaming replication protocol. + + + + Logical Streaming Replication Parameters + + + The logical replication START_REPLICATION command + accepts following parameters: + + + + + proto_version + + + + Protocol version. Currently only version 1 is + supported. + + + + + + + publication_names + + + + Comma separated list of publication names for which to subscribe + (receive changes). The individual publication names are treated + as standard objects names and can be quoted the same as needed. + + + + + + + + + + Logical Replication Protocol Messages + + + The individual protocol messages are discussed in the following + subsections. Individual messages are describer in + section. + + + + All top-level protocol messages begin with a message type byte. + While represented in code as a character, this is a signed byte with no + associated encoding. + + + + Since the streaming replication protocol supplies a message length there + is no need for top-level protocol messages to embed a length in their + header. + + + + + + Logical Replication Protocol Message Flow + + + With the exception of the START_REPLICATION command and + the replay progress messages, all information flows only from the backend + to the frontend. + + + + The logical replication protocol sends individual transactions one by one. + This means that all messages between a pair of Begin and Commit messages + belong to the same transaction. + + + + Every sent transaction contains zero or more DML messages (Insert, + Update, Delete). In case of a cascaded setup it can also contain Origin + messages. The origin message indicated that the transaction originated on + different replication node. Since a replication node in the scope of logical + replication protocol can be pretty much anything, the only identifier + is the origin name. It's downstream's responsibility to handle this as + needed (if needed). The Origin message is always sent before any DML + messages in the transaction. + + + + Every DML message contains an arbitrary relation ID, which can be mapped to + an ID in the Relation messages. The Relation messages describe the schema of the + given relation. The Relation message is sent for a given relation either + because it is the first time we send a DML message for given relation in the + current session or because the relation definition has changed since the + last Relation message was sent for it. The protocol assumes that the client + is capable of caching the metadata for as many relations as needed. + + + + Message Data Types @@ -5149,6 +5262,614 @@ not line breaks. + +Logical Replication Message Formats + + +This section describes the detailed format of each logical replication message. +These messages are returned either by the replication slot SQL interface or are +sent by a walsender. In case of a walsender they are encapsulated inside the replication +protocol WAL messages as described in +and generally obey same message flow as physical replication. + + + + + + +Begin + + + + + + + + Byte1('B') + + + + Identifies the message as a begin message. + + + + + + Int64 + + + + The final LSN of the transaction. + + + + + + Int64 + + + + Commit timestamp of the transaction. The value is in number + of microseconds since PostgreSQL epoch (2000-01-01). + + + + + + Int32 + + + + Xid of the transaction. + + + + + + + + + + + +Commit + + + + + + + + Byte1('C') + + + + Identifies the message as a commit message. + + + + + + Int64 + + + + The LSN of the commit. + + + + + + Int64 + + + + The end LSN of the transaction. + + + + + + Int64 + + + + Commit timestamp of the transaction. The value is in number + of microseconds since PostgreSQL epoch (2000-01-01). + + + + + + + + + + + +Origin + + + + + + + + Byte1('O') + + + + Identifies the message as an origin message. + + + + + + Int64 + + + + The LSN of the commit on the origin server. + + + + + + String + + + + Name of the origin. + + + + + + + + + Note that there can be multiple Origin messages inside a single transaction. + + + + + + + +Relation + + + + + + + + Byte1('R') + + + + Identifies the message as a relation message. + + + + + + Int32 + + + + ID of the relation. + + + + + + String + + + + Namespace (empty string for pg_catalog). + + + + + + String + + + + Relation name. + + + + + + + Int8 + + + + Replica identity setting for the relation (same as + relreplident in pg_class). + + + + + + + Int16 + + + + Number of columns. + + + + + Next, the following message part appears for each column: + + + + Int8 + + + + Flags for the column. Currently can be either 0 for no flags + or 1 which marks the column as part of the key. + + + + + + String + + + + Name of the column. + + + + + + + + + + + +Insert + + + + + + + + Byte1('I') + + + + Identifies the message as an insert message. + + + + + + Int32 + + + + ID of the relation corresponding to the ID in the relation + message. + + + + + + Byte1('N') + + + + Identifies the following TupleData message as a new tuple. + + + + + + + TupleData + + + + TupleData message part representing the contents of new tuple. + + + + + + + + + + + +Update + + + + + + + + Byte1('U') + + + + Identifies the message as an update message. + + + + + + Int32 + + + + ID of the relation corresponding to the ID in the relation + message. + + + + + + + Byte1('K') + + + + Identifies the following TupleData submessage as a key. + This field is optional and is only present if + the update changed data in any of the column(s) that are + part of the REPLICA IDENTITY index. + + + + + + + Byte1('O') + + + + Identifies the following TupleData submessage as an old tuple. + This field is optional and is only present if table in which + the update happened has REPLICA IDENTITY set to FULL. + + + + + + + TupleData + + + + TupleData message part representing the contents of the old tuple + or primary key. Only present if the previous 'O' or 'K' part + is present. + + + + + + + Byte1('N') + + + + Identifies the following TupleData message as a new tuple. + + + + + + + TupleData + + + + TupleData message part representing the contents of a new tuple. + + + + + + + + + The Update message may contain either a 'K' message part or an 'O' message part + or neither of them, but never both of them. + + + + + + + +Delete + + + + + + + + Byte1('D') + + + + Identifies the message as a delete message. + + + + + + Int32 + + + + ID of the relation corresponding to the ID in the relation + message. + + + + + + + Byte1('K') + + + + Identifies the following TupleData submessage as a key. + This field is present if the table in which the delete has + happened uses an index as REPLICA IDENTITY. + + + + + + + Byte1('O') + + + + Identifies the following TupleData message as a old tuple. + This field is is present if the table in which the delete has + happened has REPLICA IDENTITY set to FULL. + + + + + + + TupleData + + + + TupleData message part representing the contents of the old tuple + or primary key, depending on the previous field. + + + + + + + + The Delete message may contain either a 'K' message part or an 'O' message part, + but never both of them. + + + + + + + + + +Following message parts that are shared by above messages. + + + + + + + +TupleData + + + + + + + + Int16 + + + + Number of columns. + + + + + Next, one of the following submessages appears for each column: + + + + Byte1('n') + + + + Idenfifies the data as NULL value. + + + + + Or + + + + Byte1('u') + + + + Idenfifies unchanged TOASTed value (the actual value is not + sent). + + + + + Or + + + + Byte1('t') + + + + Idenfifies the data as text formatted value. + + + + + + Int32 + + + + Length of the column value. + + + + + + String + + + + The text value. + + + + + + + + + + + + + Summary of Changes since Protocol 2.0 diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 77667bdebd1..0d09f81ccc7 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -26,11 +26,13 @@ Complete list of usable sgml source files in this directory. + + @@ -72,11 +74,13 @@ Complete list of usable sgml source files in this directory. + + @@ -116,11 +120,13 @@ Complete list of usable sgml source files in this directory. + + diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml new file mode 100644 index 00000000000..47d83b80be5 --- /dev/null +++ b/doc/src/sgml/ref/alter_publication.sgml @@ -0,0 +1,139 @@ + + + + + ALTER PUBLICATION + + + + ALTER PUBLICATION + 7 + SQL - Language Statements + + + + ALTER PUBLICATION + change the definition of a publication + + + + +ALTER PUBLICATION name WITH ( option [, ... ] ) + +where option can be: + + PUBLISH INSERT | NOPUBLISH INSERT + | PUBLISH UPDATE | NOPUBLISH UPDATE + | PUBLISH DELETE | NOPUBLISH DELETE + +ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } +ALTER PUBLICATION name ADD TABLE table_name [, ...] +ALTER PUBLICATION name SET TABLE table_name [, ...] +ALTER PUBLICATION name DROP TABLE table_name [, ...] + + + + + Description + + + The first variant of this command listed in the synopsis can change + all of the publication properties specified in + . Properties not mentioned in the + command retain their previous settings. Database superusers can change any + of these settings for any role. + + + + To alter the owner, you must also be a direct or indirect member of the + new owning role. The new owner has to be a superuser + + + + The other variants of this command deal with the table membership of the + publication. The SET TABLE clause will replace the + list of tables in the publication with the specified one. + The ADD TABLE and + DROP TABLE will add and remove one or more tables from + the publication. + + + + + Parameters + + + + name + + + The name of an existing publication whose definition is to be altered. + + + + + + PUBLISH INSERT + NOPUBLISH INSERT + PUBLISH UPDATE + NOPUBLISH UPDATE + PUBLISH DELETE + NOPUBLISH DELETE + + + These clauses alter properties originally set by + . See there for more information. + + + + + + table_name + + + Name of an existing table. + + + + + + + + Examples + + + Change the publication to not publish inserts: + +ALTER PUBLICATION noinsert WITH (NOPUBLISH INSERT); + + + + + Add some tables to the publication: + +ALTER PUBLICATION mypublication ADD TABLE users, departments; + + + + + + Compatibility + + + ALTER PUBLICATION is a PostgreSQL + extension. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml new file mode 100644 index 00000000000..032ecbb885e --- /dev/null +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -0,0 +1,139 @@ + + + + + ALTER SUBSCRIPTION + + + + ALTER SUBSCRIPTION + 7 + SQL - Language Statements + + + + ALTER SUBSCRIPTION + change the definition of a subscription + + + + +ALTER SUBSCRIPTION name WITH ( option [, ... ] ) ] + +where option can be: + + SLOT NAME = slot_name + +ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } +ALTER SUBSCRIPTION name CONNECTION 'conninfo' +ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] +ALTER SUBSCRIPTION name ENABLE +ALTER SUBSCRIPTION name DISABLE + + + + + Description + + + ALTER SUBSCRIPTION can change most of the subscription + properties that can be specified + in . + + + + To alter the owner, you must also be a direct or indirect member of the + new owning role. The new owner has to be a superuser + + + + + Parameters + + + + name + + + The name of a subscription whose properties are to be altered. + + + + + + CONNECTION 'conninfo' + SET PUBLICATION publication_name + SLOT NAME = slot_name + + + These clauses alter properties originally set by + . See there for more + information. + + + + + + ENABLE + + + Enables the previously disabled subscription, starting the logical + replication worker at the end of transaction. + + + + + + DISABLE + + + Disables the running subscription, stopping the logical replication + worker at the end of transaction. + + + + + + + + Examples + + + Change the publication subscribed by a subscription to + insert_only: + +ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only; + + + + + Disable (stop) the subscription: + +ALTER SUBSCRIPTION mysub DISABLE; + + + + + + Compatibility + + + ALTER SUBSCRIPTION is a PostgreSQL + extension. + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml new file mode 100644 index 00000000000..995f2bcf3c1 --- /dev/null +++ b/doc/src/sgml/ref/create_publication.sgml @@ -0,0 +1,206 @@ + + + + + CREATE PUBLICATION + + + + CREATE PUBLICATION + 7 + SQL - Language Statements + + + + CREATE PUBLICATION + define a new publication + + + + +CREATE PUBLICATION name + [ FOR TABLE table_name [, ...] + | FOR ALL TABLES ] + [ WITH ( option [, ... ] ) ] + +where option can be: + + PUBLISH INSERT | NOPUBLISH INSERT + | PUBLISH UPDATE | NOPUBLISH UPDATE + | PUBLISH DELETE | NOPUBLISH DELETE + + + + + Description + + + CREATE PUBLICATION adds a new publication + into the current database. The publication name must be distinct from + the name of any existing publication in the current database. + + + + A publication is essentially a group of tables whose data changes are + intended to be replicated through logical replication. See + for details about how + publications fit into the logical replication setup. + + + + + Parameters + + + + name + + + The name of the new publication. + + + + + + FOR TABLE + + + Specifies a list of tables to add to the publication. + + + + + + FOR ALL TABLES + + + Marks the publication as one that replicates changes for all tables in + the database, including tables created in the future. + + + + + + PUBLISH INSERT + NOPUBLISH INSERT + + + These clauses determine whether the new publication will send + the INSERT operations to the subscribers. + PUBLISH INSERT is the default. + + + + + + PUBLISH UPDATE + NOPUBLISH UPDATE + + + These clauses determine whether the new publication will send + the UPDATE operations to the subscribers. + PUBLISH UPDATE is the default. + + + + + + PUBLISH DELETE + NOPUBLISH DELETE + + + These clauses determine whether the new publication will send + the DELETE operations to the subscribers. + PUBLISH DELETE is the default. + + + + + + + + + Notes + + + If neither FOR TABLE nor FOR ALL + TABLES is specified, then the publication starts out with an + empty set of tables. That is useful if tables are to be added later. + + + + The creation of a publication does not start replication. It only defines + a grouping and filtering logic for future subscribers. + + + + To create a publication, the invoking user must have the + CREATE privilege for the current database. + (Of course, superusers bypass this check.) + + + + To add a table to a publication, the invoking user must have + SELECT privilege on given table. The + FOR ALL TABLES clause requires superuser. + + + + The tables added to a publication that publishes UPDATE + and/or DELETE operations must have + REPLICA IDENTITY defined. Otherwise those operations will be + disallowed on those tables. + + + + For an INSERT ... ON CONFLICT command, the publication will + publish the operation that actually results from the command. So depending + of the outcome, it may be published as either INSERT or + UPDATE, or it may not be published at all. + + + + TRUNCATE and other DDL operations + are not published. + + + + + Examples + + + Create a simple publication that just publishes all DML for tables in it: + +CREATE PUBLICATION mypublication; + + + + + Create an insert-only publication: + +CREATE PUBLICATION insert_only WITH (NOPUBLISH UPDATE, NOPUBLISH DELETE); + + + + + + Compatibility + + + CREATE PUBLICATION is a PostgreSQL + extension. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml new file mode 100644 index 00000000000..40d08b34409 --- /dev/null +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -0,0 +1,176 @@ + + + + + CREATE SUBSCRIPTION + + + + CREATE SUBSCRIPTION + 7 + SQL - Language Statements + + + + CREATE SUBSCRIPTION + define a new subscription + + + + +CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION { publication_name [, ...] } [ WITH ( option [, ... ] ) ] + +where option can be: + + | ENABLED | DISABLED + | CREATE SLOT | NOCREATE SLOT + | SLOT NAME = slot_name + + + + + Description + + + CREATE SUBSCRIPTION adds a new subscription for a + current database. The subscription name must be distinct from the name of + any existing subscription in the database. + + + + The subscription represents a replication connection to the publisher. As + such this command does not only add definitions in the local catalogs but + also creates a replication slot on the publisher. + + + + A logical replication worker will be started to replicate data for the new + subscription at the commit of the transaction where this command is run. + + + + Additional info about subscriptions and logical replication as a whole + can is available at and + . + + + + + + Parameters + + + + subscription_name + + + The name of the new subscription. + + + + + + CONNECTION 'conninfo' + + + The connection string to the publisher. + + + + + + PUBLICATION publication_name + + + Name(s) of the publications on the publisher to subscribe to. + + + + + + ENABLED + DISABLED + + + Specifies whether the subscription should be actively replicating or + if it should be just setup but not started yet. Note that the + replication slot as described above is created in either case. + ENABLED is the default. + + + + + + CREATE SLOT + NOCREATE SLOT + + + Specifies whether the command should create the replication slot on the + publisher. CREATE SLOT is the default. + + + + + + SLOT NAME = slot_name + + + Name of the replication slot to use. The default behavior is to use + subscription_name for slot name. + + + + + + + + Examples + + + Create a subscription to a remote server that replicates tables in + the publications mypubclication and + insert_only and starts replicating immediately on + commit: + +CREATE SUBSCRIPTION mysub + CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb password=foopass' + PUBLICATION mypublication, insert_only; + + + + + Create a subscription to a remote server that replicates tables in + the insert_only publication and does not start replicating + until enabled at a later time. + +CREATE SUBSCRIPTION mysub + CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb password=foopass' + PUBLICATION insert_only + WITH (DISABLED); + + + + + + Compatibility + + + CREATE SUBSCRIPTION is a PostgreSQL + extension. + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/ref/drop_publication.sgml b/doc/src/sgml/ref/drop_publication.sgml new file mode 100644 index 00000000000..1a1be579ad8 --- /dev/null +++ b/doc/src/sgml/ref/drop_publication.sgml @@ -0,0 +1,107 @@ + + + + + DROP PUBLICATION + + + + DROP PUBLICATION + 7 + SQL - Language Statements + + + + DROP PUBLICATION + remove a publication + + + + +DROP PUBLICATION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] + + + + + Description + + + DROP PUBLICATION removes an existing publication from + the database. + + + + A publication can only be dropped by its owner or a superuser. + + + + + Parameters + + + + IF EXISTS + + + Do not throw an error if the extension does not exist. A notice is issued + in this case. + + + + + + name + + + The name of an existing publication. + + + + + + CASCADE + RESTRICT + + + + These key words do not have any effect, since there are no dependencies + on publications. + + + + + + + + Examples + + + Drop a publication: + +DROP PUBLICATION mypublication; + + + + + + + Compatibility + + + DROP PUBLICATION is a PostgreSQL + extension. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/drop_subscription.sgml b/doc/src/sgml/ref/drop_subscription.sgml new file mode 100644 index 00000000000..9f2fb93275c --- /dev/null +++ b/doc/src/sgml/ref/drop_subscription.sgml @@ -0,0 +1,110 @@ + + + + + DROP SUBSCRIPTION + + + + DROP SUBSCRIPTION + 7 + SQL - Language Statements + + + + DROP SUBSCRIPTION + remove a subscription + + + + +DROP SUBSCRIPTION [ IF EXISTS ] name [ DROP SLOT | NODROP SLOT ] + + + + + Description + + + DROP SUBSCRIPTION removes a subscription from the + database cluster. + + + + A subscription can only be dropped by a superuser. + + + + The replication worker associated with the subscription will not stop until + after the transaction that issued this command has committed. + + + + + Parameters + + + + name + + + The name of a subscription to be dropped. + + + + + + DROP SLOT + NODROP SLOT + + + Specifies whether to drop the replication slot on the publisher. The + default is + DROP SLOT. + + + + If the publisher is not reachable when the subscription is to be + dropped, then it is useful to specify NODROP SLOT. + But the replication slot on the publisher will then have to be removed + manually. + + + + + + + + + Examples + + + Drop a subscription: + +DROP SUBSCRIPTION mysub; + + + + + + + Compatibility + + + DROP SUBSCRIPTION is a PostgreSQL + extension. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index b70e7d57e95..a1e03c481d1 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -755,6 +755,15 @@ PostgreSQL documentation + + + + + Include logical replication subscriptions in the dump. + + + + @@ -789,6 +798,18 @@ PostgreSQL documentation + + + + + When dumping logical replication subscriptions, + generate CREATE SUBSCRIPTION commands that do not + create the remote replication slot. That way, the dump can be + restored without requiring network access to the remote servers. + + + + diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 991573121be..640fe12bbf6 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1600,6 +1600,34 @@ testdb=> + + \dRp[+] [ pattern ] + + + Lists replication publications. + If pattern is + specified, only those publications whose names match the pattern are + listed. + If + is appended to the command name, the tables + associated with each publication are shown as well. + + + + + + \dRs[+] [ pattern ] + + + Lists replication subscriptions. + If pattern is + specified, only those subscriptions whose names match the pattern are + listed. + If + is appended to the command name, additional + properties of the subscriptions are shown. + + + + \dT[S+] [ pattern ] diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 8acdff1393f..34007d3508d 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -54,11 +54,13 @@ &alterOperatorClass; &alterOperatorFamily; &alterPolicy; + &alterPublication; &alterRole; &alterRule; &alterSchema; &alterSequence; &alterServer; + &alterSubscription; &alterSystem; &alterTable; &alterTableSpace; @@ -100,11 +102,13 @@ &createOperatorClass; &createOperatorFamily; &createPolicy; + &createPublication; &createRole; &createRule; &createSchema; &createSequence; &createServer; + &createSubscription; &createTable; &createTableAs; &createTableSpace; @@ -144,11 +148,13 @@ &dropOperatorFamily; &dropOwned; &dropPolicy; + &dropPublication; &dropRole; &dropRule; &dropSchema; &dropSequence; &dropServer; + &dropSubscription; &dropTable; &dropTableSpace; &dropTSConfig; -- cgit v1.2.3