diff options
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/src/sgml/func.sgml | 171 | ||||
| -rw-r--r-- | doc/src/sgml/logical-replication.sgml | 449 | ||||
| -rw-r--r-- | doc/src/sgml/logicaldecoding.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/ref/pg_dump.sgml | 20 | ||||
| -rw-r--r-- | doc/src/sgml/ref/pg_dumpall.sgml | 20 | ||||
| -rw-r--r-- | doc/src/sgml/release-18.sgml | 47 |
6 files changed, 225 insertions, 487 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b405525a465..c67688cbf5f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -28666,143 +28666,6 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> - <primary>pg_get_process_memory_contexts</primary> - </indexterm> - <function>pg_get_process_memory_contexts</function> ( <parameter>pid</parameter> <type>integer</type>, <parameter>summary</parameter> <type>boolean</type>, <parameter>timeout</parameter> <type>float</type> ) - <returnvalue>setof record</returnvalue> - ( <parameter>name</parameter> <type>text</type>, - <parameter>ident</parameter> <type>text</type>, - <parameter>type</parameter> <type>text</type>, - <parameter>path</parameter> <type>integer[]</type>, - <parameter>level</parameter> <type>integer</type>, - <parameter>total_bytes</parameter> <type>bigint</type>, - <parameter>total_nblocks</parameter> <type>bigint</type>, - <parameter>free_bytes</parameter> <type>bigint</type>, - <parameter>free_chunks</parameter> <type>bigint</type>, - <parameter>used_bytes</parameter> <type>bigint</type>, - <parameter>num_agg_contexts</parameter> <type>integer</type>, - <parameter>stats_timestamp</parameter> <type>timestamptz</type> ) - </para> - <para> - This function handles requests to display the memory contexts of a - <productname>PostgreSQL</productname> process with the specified - process ID. The function can be used to send requests to backends as - well as <glossterm linkend="glossary-auxiliary-proc">auxiliary processes</glossterm>. - </para> - <para> - The returned record contains extended statistics per each memory - context: - <itemizedlist spacing="compact"> - <listitem> - <para> - <parameter>name</parameter> - The name of the memory context. - </para> - </listitem> - <listitem> - <para> - <parameter>ident</parameter> - Memory context ID (if any). - </para> - </listitem> - <listitem> - <para> - <parameter>type</parameter> - The type of memory context, possible - values are: AllocSet, Generation, Slab and Bump. - </para> - </listitem> - <listitem> - <para> - <parameter>path</parameter> - Memory contexts are organized in a - tree model with TopMemoryContext as the root, and all other memory - contexts as nodes in the tree. The <parameter>path</parameter> - displays the path from the root to the current memory context. The - path is limited to 100 children per node, which each node limited - to a max depth of 100, to preserve memory during reporting. The - printed path will also be limited to 100 nodes counting from the - TopMemoryContext. - </para> - </listitem> - <listitem> - <para> - <parameter>level</parameter> - The level in the tree of the current - memory context. - </para> - </listitem> - <listitem> - <para> - <parameter>total_bytes</parameter> - The total number of bytes - allocated to this memory context. - </para> - </listitem> - <listitem> - <para> - <parameter>total_nblocks</parameter> - The total number of blocks - used for the allocated memory. - </para> - </listitem> - <listitem> - <para> - <parameter>free_bytes</parameter> - The amount of free memory in - this memory context. - </para> - </listitem> - <listitem> - <para> - <parameter>free_chunks</parameter> - The number of chunks that - <parameter>free_bytes</parameter> corresponds to. - </para> - </listitem> - <listitem> - <para> - <parameter>used_bytes</parameter> - The total number of bytes - currently occupied. - </para> - </listitem> - <listitem> - <para> - <parameter>num_agg_contexts</parameter> - The number of memory - contexts aggregated in the displayed statistics. - </para> - </listitem> - <listitem> - <para> - <parameter>stats_timestamp</parameter> - When the statistics were - extracted from the process. - </para> - </listitem> - </itemizedlist> - </para> - <para> - When <parameter>summary</parameter> is <literal>true</literal>, statistics - for memory contexts at levels 1 and 2 are displayed, with level 1 - representing the root node (i.e., <literal>TopMemoryContext</literal>). - Statistics for contexts on level 2 and below are aggregates of all - child contexts' statistics, where <literal>num_agg_contexts</literal> - indicate the number aggregated child contexts. When - <parameter>summary</parameter> is <literal>false</literal>, - <literal>the num_agg_contexts</literal> value is <literal>1</literal>, - indicating that individual statistics are being displayed. - </para> - <para> - Busy processes can delay reporting memory context statistics, - <parameter>timeout</parameter> specifies the number of seconds - to wait for updated statistics. <parameter>timeout</parameter> can be - specified in fractions of a second. - </para> - <para> - After receiving memory context statistics from the target process, it - returns the results as one row per context. If all the contexts don't - fit within the pre-determined size limit, the remaining context - statistics are aggregated and a cumulative total is displayed. The - <literal>num_agg_contexts</literal> column indicates the number of - contexts aggregated in the displayed statistics. When - <literal>num_agg_contexts</literal> is <literal>1</literal> it means - that the context statistics are displayed separately. - </para></entry> - </row> - - <row> - <entry role="func_table_entry"><para role="func_signature"> - <indexterm> <primary>pg_log_backend_memory_contexts</primary> </indexterm> <function>pg_log_backend_memory_contexts</function> ( <parameter>pid</parameter> <type>integer</type> ) @@ -28939,40 +28802,6 @@ LOG: Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560 because it may generate a large number of log messages. </para> - <para> - <function>pg_get_process_memory_contexts</function> can be used to request - memory contexts statistics of any <productname>PostgreSQL</productname> - process. For example: -<programlisting> -postgres=# SELECT * FROM pg_get_process_memory_contexts( - (SELECT pid FROM pg_stat_activity - WHERE backend_type = 'checkpointer'), - false, 0.5) LIMIT 1; --[ RECORD 1 ]----+------------------------------ -name | TopMemoryContext -ident | -type | AllocSet -path | {1} -level | 1 -total_bytes | 90304 -total_nblocks | 3 -free_bytes | 2880 -free_chunks | 1 -used_bytes | 87424 -num_agg_contexts | 1 -stats_timestamp | 2025-03-24 13:55:47.796698+01 -</programlisting> - <note> - <para> - While <function>pg_get_process_memory_contexts</function> can be used to - query memory contexts of the local backend, - <structname>pg_backend_memory_contexts</structname> - (see <xref linkend="view-pg-backend-memory-contexts"/> for more details) - will be less resource intensive when only the local backend is of interest. - </para> - </note> - </para> - </sect2> <sect2 id="functions-admin-backup"> diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index f288c049a5c..686dd441d02 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -363,34 +363,25 @@ <para> Create some test tables on the publisher. <programlisting> -test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); -CREATE TABLE -test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c)); -CREATE TABLE -test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e)); -CREATE TABLE +/* pub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); +/* pub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c)); +/* pub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e)); </programlisting></para> <para> Create the same tables on the subscriber. <programlisting> -test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); -CREATE TABLE -test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c)); -CREATE TABLE -test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e)); -CREATE TABLE +/* sub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a)); +/* sub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c)); +/* sub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e)); </programlisting></para> <para> Insert data to the tables at the publisher side. <programlisting> -test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); -INSERT 0 3 -test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C'); -INSERT 0 3 -test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii'); -INSERT 0 3 +/* pub # */ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); +/* pub # */ INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C'); +/* pub # */ INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii'); </programlisting></para> <para> @@ -399,41 +390,34 @@ INSERT 0 3 <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link> operations. The publication <literal>pub3b</literal> has a row filter (see <xref linkend="logical-replication-row-filter"/>). -<programlisting> -test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1; -CREATE PUBLICATION -test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate'); -CREATE PUBLICATION -test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate'); -CREATE PUBLICATION -test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5); -CREATE PUBLICATION -</programlisting></para> +<programlisting><![CDATA[ +/* pub # */ CREATE PUBLICATION pub1 FOR TABLE t1; +/* pub # */ CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate'); +/* pub # */ CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate'); +/* pub # */ CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5); +]]></programlisting></para> <para> Create subscriptions for the publications. The subscription <literal>sub3</literal> subscribes to both <literal>pub3a</literal> and <literal>pub3b</literal>. All subscriptions will copy initial data by default. <programlisting> -test_sub=# CREATE SUBSCRIPTION sub1 -test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1' -test_sub-# PUBLICATION pub1; -CREATE SUBSCRIPTION -test_sub=# CREATE SUBSCRIPTION sub2 -test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2' -test_sub-# PUBLICATION pub2; -CREATE SUBSCRIPTION -test_sub=# CREATE SUBSCRIPTION sub3 -test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3' -test_sub-# PUBLICATION pub3a, pub3b; -CREATE SUBSCRIPTION +/* sub # */ CREATE SUBSCRIPTION sub1 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1' +/* sub - */ PUBLICATION pub1; +/* sub # */ CREATE SUBSCRIPTION sub2 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub2' +/* sub - */ PUBLICATION pub2; +/* sub # */ CREATE SUBSCRIPTION sub3 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub3' +/* sub - */ PUBLICATION pub3a, pub3b; </programlisting></para> <para> Observe that initial table data is copied, regardless of the <literal>publish</literal> operation of the publication. <programlisting> -test_sub=# SELECT * FROM t1; +/* sub # */ SELECT * FROM t1; a | b ---+------- 1 | one @@ -441,7 +425,7 @@ test_sub=# SELECT * FROM t1; 3 | three (3 rows) -test_sub=# SELECT * FROM t2; +/* sub # */ SELECT * FROM t2; c | d ---+--- 1 | A @@ -456,7 +440,7 @@ test_sub=# SELECT * FROM t2; it means the copied table <literal>t3</literal> contains all rows even when they do not match the row filter of publication <literal>pub3b</literal>. <programlisting> -test_sub=# SELECT * FROM t3; +/* sub # */ SELECT * FROM t3; e | f ---+----- 1 | i @@ -468,18 +452,15 @@ test_sub=# SELECT * FROM t3; <para> Insert more data to the tables at the publisher side. <programlisting> -test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six'); -INSERT 0 3 -test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F'); -INSERT 0 3 -test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi'); -INSERT 0 3 +/* pub # */ INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six'); +/* pub # */ INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F'); +/* pub # */ INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi'); </programlisting></para> <para> Now the publisher side data looks like: <programlisting> -test_pub=# SELECT * FROM t1; +/* pub # */ SELECT * FROM t1; a | b ---+------- 1 | one @@ -490,7 +471,7 @@ test_pub=# SELECT * FROM t1; 6 | six (6 rows) -test_pub=# SELECT * FROM t2; +/* pub # */ SELECT * FROM t2; c | d ---+--- 1 | A @@ -501,7 +482,7 @@ test_pub=# SELECT * FROM t2; 6 | F (6 rows) -test_pub=# SELECT * FROM t3; +/* pub # */ SELECT * FROM t3; e | f ---+----- 1 | i @@ -521,7 +502,7 @@ test_pub=# SELECT * FROM t3; only replicate data that matches the row filter of <literal>pub3b</literal>. Now the subscriber side data looks like: <programlisting> -test_sub=# SELECT * FROM t1; +/* sub # */ SELECT * FROM t1; a | b ---+------- 1 | one @@ -532,7 +513,7 @@ test_sub=# SELECT * FROM t1; 6 | six (6 rows) -test_sub=# SELECT * FROM t2; +/* sub # */ SELECT * FROM t2; c | d ---+--- 1 | A @@ -540,7 +521,7 @@ test_sub=# SELECT * FROM t2; 3 | C (3 rows) -test_sub=# SELECT * FROM t3; +/* sub # */ SELECT * FROM t3; e | f ---+----- 1 | i @@ -567,8 +548,7 @@ test_sub=# SELECT * FROM t3; <para> First, create a publication for the examples to use. <programlisting> -test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES; -CREATE PUBLICATION +/* pub # */ CREATE PUBLICATION pub1 FOR ALL TABLES; </programlisting></para> <para> Example 1: Where the subscription says <literal>connect = false</literal> @@ -579,13 +559,12 @@ CREATE PUBLICATION <para> Create the subscription. <programlisting> -test_sub=# CREATE SUBSCRIPTION sub1 -test_sub-# CONNECTION 'host=localhost dbname=test_pub' -test_sub-# PUBLICATION pub1 -test_sub-# WITH (connect=false); +/* sub # */ CREATE SUBSCRIPTION sub1 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub' +/* sub - */ PUBLICATION pub1 +/* sub - */ WITH (connect=false); WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. -CREATE SUBSCRIPTION </programlisting></para> </listitem> <listitem> @@ -594,7 +573,7 @@ CREATE SUBSCRIPTION specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the slot to create is same as the subscription name, e.g. "sub1". <programlisting> -test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput'); +/* pub # */ SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput'); slot_name | lsn -----------+----------- sub1 | 0/19404D0 @@ -606,10 +585,8 @@ test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput'); On the subscriber, complete the activation of the subscription. After this the tables of <literal>pub1</literal> will start replicating. <programlisting> -test_sub=# ALTER SUBSCRIPTION sub1 ENABLE; -ALTER SUBSCRIPTION -test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE; +/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; </programlisting></para> </listitem> </itemizedlist> @@ -625,13 +602,12 @@ ALTER SUBSCRIPTION <para> Create the subscription. <programlisting> -test_sub=# CREATE SUBSCRIPTION sub1 -test_sub-# CONNECTION 'host=localhost dbname=test_pub' -test_sub-# PUBLICATION pub1 -test_sub-# WITH (connect=false, slot_name='myslot'); +/* sub # */ CREATE SUBSCRIPTION sub1 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub' +/* sub - */ PUBLICATION pub1 +/* sub - */ WITH (connect=false, slot_name='myslot'); WARNING: subscription was created, but is not connected HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription. -CREATE SUBSCRIPTION </programlisting></para> </listitem> <listitem> @@ -639,7 +615,7 @@ CREATE SUBSCRIPTION On the publisher, manually create a slot using the same name that was specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot". <programlisting> -test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); +/* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); slot_name | lsn -----------+----------- myslot | 0/19059A0 @@ -651,10 +627,8 @@ test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput' On the subscriber, the remaining subscription activation steps are the same as before. <programlisting> -test_sub=# ALTER SUBSCRIPTION sub1 ENABLE; -ALTER SUBSCRIPTION -test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE; +/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; </programlisting></para> </listitem> </itemizedlist> @@ -669,18 +643,17 @@ ALTER SUBSCRIPTION <literal>enabled = false</literal>, and <literal>create_slot = false</literal> are also needed. <programlisting> -test_sub=# CREATE SUBSCRIPTION sub1 -test_sub-# CONNECTION 'host=localhost dbname=test_pub' -test_sub-# PUBLICATION pub1 -test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false); -CREATE SUBSCRIPTION +/* sub # */ CREATE SUBSCRIPTION sub1 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub' +/* sub - */ PUBLICATION pub1 +/* sub - */ WITH (slot_name=NONE, enabled=false, create_slot=false); </programlisting></para> </listitem> <listitem> <para> On the publisher, manually create a slot using any name, e.g. "myslot". <programlisting> -test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); +/* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); slot_name | lsn -----------+----------- myslot | 0/1905930 @@ -692,18 +665,15 @@ test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput' On the subscriber, associate the subscription with the slot name just created. <programlisting> -test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot'); -ALTER SUBSCRIPTION +/* sub # */ ALTER SUBSCRIPTION sub1 SET (slot_name='myslot'); </programlisting></para> </listitem> <listitem> <para> The remaining subscription activation steps are same as before. <programlisting> -test_sub=# ALTER SUBSCRIPTION sub1 ENABLE; -ALTER SUBSCRIPTION -test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* sub # */ ALTER SUBSCRIPTION sub1 ENABLE; +/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION; </programlisting></para> </listitem> </itemizedlist> @@ -752,7 +722,7 @@ ALTER SUBSCRIPTION will return the relevant replication slots associated with the failover-enabled subscriptions. <programlisting> -test_sub=# SELECT +/* sub # */ SELECT array_agg(quote_literal(s.subslotname)) AS slots FROM pg_subscription s WHERE s.subfailover AND @@ -775,7 +745,7 @@ test_sub=# SELECT as they will either be dropped or re-created on the new primary server in those cases. <programlisting> -test_sub=# SELECT +/* sub # */ SELECT array_agg(quote_literal(slot_name)) AS slots FROM ( @@ -794,7 +764,7 @@ test_sub=# SELECT Check that the logical replication slots identified above exist on the standby server and are ready for failover. <programlisting> -test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready +/* standby # */ SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready FROM pg_replication_slots WHERE slot_name IN ('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164'); @@ -1024,12 +994,9 @@ test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) <para> Create some tables to be used in the following examples. <programlisting> -test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); -CREATE TABLE -test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d)); -CREATE TABLE -test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g)); -CREATE TABLE +/* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); +/* pub # */ CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d)); +/* pub # */ CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g)); </programlisting></para> <para> @@ -1038,43 +1005,40 @@ CREATE TABLE <literal>p2</literal> has two tables. Table <literal>t1</literal> has no row filter, and table <literal>t2</literal> has a row filter. Publication <literal>p3</literal> has two tables, and both of them have a row filter. -<programlisting> -test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW'); -CREATE PUBLICATION -test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99); -CREATE PUBLICATION -test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10); -CREATE PUBLICATION -</programlisting></para> +<programlisting><![CDATA[ +/* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW'); +/* pub # */ CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99); +/* pub # */ CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10); +]]></programlisting></para> <para> <command>psql</command> can be used to show the row filter expressions (if defined) for each publication. -<programlisting> -test_pub=# \dRp+ - Publication p1 +<programlisting><![CDATA[ +/* pub # */ \dRp+ + Publication p1 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: - "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text)) + "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text)) - Publication p2 + Publication p2 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: - "public.t1" - "public.t2" WHERE (e = 99) + "public.t1" + "public.t2" WHERE (e = 99) - Publication p3 + Publication p3 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: - "public.t2" WHERE (d = 10) - "public.t3" WHERE (g = 10) -</programlisting></para> + "public.t2" WHERE (d = 10) + "public.t3" WHERE (g = 10) +]]></programlisting></para> <para> <command>psql</command> can be used to show the row filter expressions (if @@ -1082,8 +1046,8 @@ Tables: of two publications, but has a row filter only in <literal>p1</literal>. See that table <literal>t2</literal> is a member of two publications, and has a different row filter in each of them. -<programlisting> -test_pub=# \d t1 +<programlisting><![CDATA[ +/* pub # */ \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- @@ -1096,7 +1060,7 @@ Publications: "p1" WHERE ((a > 5) AND (c = 'NSW'::text)) "p2" -test_pub=# \d t2 +/* pub # */ \d t2 Table "public.t2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- @@ -1109,7 +1073,7 @@ Publications: "p2" WHERE (e = 99) "p3" WHERE (d = 10) -test_pub=# \d t3 +/* pub # */ \d t3 Table "public.t3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- @@ -1120,43 +1084,33 @@ Indexes: "t3_pkey" PRIMARY KEY, btree (g) Publications: "p3" WHERE (g = 10) -</programlisting></para> +]]></programlisting></para> <para> On the subscriber node, create a table <literal>t1</literal> with the same definition as the one on the publisher, and also create the subscription <literal>s1</literal> that subscribes to the publication <literal>p1</literal>. <programlisting> -test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); -CREATE TABLE -test_sub=# CREATE SUBSCRIPTION s1 -test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1' -test_sub-# PUBLICATION p1; -CREATE SUBSCRIPTION +/* sub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); +/* sub # */ CREATE SUBSCRIPTION s1 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1' +/* sub - */ PUBLICATION p1; </programlisting></para> <para> Insert some rows. Only the rows satisfying the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal> are replicated. <programlisting> -test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW'); -INSERT 0 1 - -test_pub=# SELECT * FROM t1; +/* pub # */ INSERT INTO t1 VALUES (2, 102, 'NSW'); +/* pub # */ INSERT INTO t1 VALUES (3, 103, 'QLD'); +/* pub # */ INSERT INTO t1 VALUES (4, 104, 'VIC'); +/* pub # */ INSERT INTO t1 VALUES (5, 105, 'ACT'); +/* pub # */ INSERT INTO t1 VALUES (6, 106, 'NSW'); +/* pub # */ INSERT INTO t1 VALUES (7, 107, 'NT'); +/* pub # */ INSERT INTO t1 VALUES (8, 108, 'QLD'); +/* pub # */ INSERT INTO t1 VALUES (9, 109, 'NSW'); + +/* pub # */ SELECT * FROM t1; a | b | c ---+-----+----- 2 | 102 | NSW @@ -1170,7 +1124,7 @@ test_pub=# SELECT * FROM t1; (8 rows) </programlisting> <programlisting> -test_sub=# SELECT * FROM t1; +/* sub # */ SELECT * FROM t1; a | b | c ---+-----+----- 6 | 106 | NSW @@ -1184,10 +1138,9 @@ test_sub=# SELECT * FROM t1; <literal>p1</literal>. The <command>UPDATE</command> replicates the change as normal. <programlisting> -test_pub=# UPDATE t1 SET b = 999 WHERE a = 6; -UPDATE 1 +/* pub # */ UPDATE t1 SET b = 999 WHERE a = 6; -test_pub=# SELECT * FROM t1; +/* pub # */ SELECT * FROM t1; a | b | c ---+-----+----- 2 | 102 | NSW @@ -1201,7 +1154,7 @@ test_pub=# SELECT * FROM t1; (8 rows) </programlisting> <programlisting> -test_sub=# SELECT * FROM t1; +/* sub # */ SELECT * FROM t1; a | b | c ---+-----+----- 9 | 109 | NSW @@ -1216,10 +1169,9 @@ test_sub=# SELECT * FROM t1; transformed into an <command>INSERT</command> and the change is replicated. See the new row on the subscriber. <programlisting> -test_pub=# UPDATE t1 SET a = 555 WHERE a = 2; -UPDATE 1 +/* pub # */ UPDATE t1 SET a = 555 WHERE a = 2; -test_pub=# SELECT * FROM t1; +/* pub # */ SELECT * FROM t1; a | b | c -----+-----+----- 3 | 103 | QLD @@ -1233,7 +1185,7 @@ test_pub=# SELECT * FROM t1; (8 rows) </programlisting> <programlisting> -test_sub=# SELECT * FROM t1; +/* sub # */ SELECT * FROM t1; a | b | c -----+-----+----- 9 | 109 | NSW @@ -1249,10 +1201,9 @@ test_sub=# SELECT * FROM t1; transformed into a <command>DELETE</command> and the change is replicated. See that the row is removed from the subscriber. <programlisting> -test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9; -UPDATE 1 +/* pub # */ UPDATE t1 SET c = 'VIC' WHERE a = 9; -test_pub=# SELECT * FROM t1; +/* pub # */ SELECT * FROM t1; a | b | c -----+-----+----- 3 | 103 | QLD @@ -1266,7 +1217,7 @@ test_pub=# SELECT * FROM t1; (8 rows) </programlisting> <programlisting> -test_sub=# SELECT * FROM t1; +/* sub # */ SELECT * FROM t1; a | b | c -----+-----+----- 6 | 999 | NSW @@ -1284,17 +1235,13 @@ test_sub=# SELECT * FROM t1; <para> Create a partitioned table on the publisher. <programlisting> -test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); -CREATE TABLE -test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT; -CREATE TABLE +/* pub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); +/* pub # */ CREATE TABLE child PARTITION OF parent DEFAULT; </programlisting> Create the same tables on the subscriber. <programlisting> -test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); -CREATE TABLE -test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT; -CREATE TABLE +/* sub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); +/* sub # */ CREATE TABLE child PARTITION OF parent DEFAULT; </programlisting></para> <para> @@ -1302,16 +1249,14 @@ CREATE TABLE publication parameter <literal>publish_via_partition_root</literal> is set as true. There are row filters defined on both the partitioned table (<literal>parent</literal>), and on the partition (<literal>child</literal>). -<programlisting> -test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5) -test_pub-# WITH (publish_via_partition_root=true); -CREATE PUBLICATION -</programlisting> -<programlisting> -test_sub=# CREATE SUBSCRIPTION s4 -test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4' -test_sub-# PUBLICATION p4; -CREATE SUBSCRIPTION +<programlisting><![CDATA[ +/* pub # */ CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5) +/* pub - */ WITH (publish_via_partition_root=true); +]]></programlisting> +<programlisting> +/* sub # */ CREATE SUBSCRIPTION s4 +/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s4' +/* sub - */ PUBLICATION p4; </programlisting></para> <para> @@ -1320,12 +1265,10 @@ CREATE SUBSCRIPTION <literal>parent</literal> (because <literal>publish_via_partition_root</literal> is true). <programlisting> -test_pub=# INSERT INTO parent VALUES (2), (4), (6); -INSERT 0 3 -test_pub=# INSERT INTO child VALUES (3), (5), (7); -INSERT 0 3 +/* pub # */ INSERT INTO parent VALUES (2), (4), (6); +/* pub # */ INSERT INTO child VALUES (3), (5), (7); -test_pub=# SELECT * FROM parent ORDER BY a; +/* pub # */ SELECT * FROM parent ORDER BY a; a --- 2 @@ -1337,7 +1280,7 @@ test_pub=# SELECT * FROM parent ORDER BY a; (6 rows) </programlisting> <programlisting> -test_sub=# SELECT * FROM parent ORDER BY a; +/* sub # */ SELECT * FROM parent ORDER BY a; a --- 2 @@ -1350,16 +1293,13 @@ test_sub=# SELECT * FROM parent ORDER BY a; Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>. The publication parameter <literal>publish_via_partition_root</literal> is set as false. A row filter is defined on the partition (<literal>child</literal>). +<programlisting><![CDATA[ +/* pub # */ DROP PUBLICATION p4; +/* pub # */ CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5) +/* pub - */ WITH (publish_via_partition_root=false); +]]></programlisting> <programlisting> -test_pub=# DROP PUBLICATION p4; -DROP PUBLICATION -test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5) -test_pub-# WITH (publish_via_partition_root=false); -CREATE PUBLICATION -</programlisting> -<programlisting> -test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* sub # */ ALTER SUBSCRIPTION s4 REFRESH PUBLICATION; </programlisting></para> <para> @@ -1367,14 +1307,11 @@ ALTER SUBSCRIPTION row filter of <literal>child</literal> (because <literal>publish_via_partition_root</literal> is false). <programlisting> -test_pub=# TRUNCATE parent; -TRUNCATE TABLE -test_pub=# INSERT INTO parent VALUES (2), (4), (6); -INSERT 0 3 -test_pub=# INSERT INTO child VALUES (3), (5), (7); -INSERT 0 3 +/* pub # */ TRUNCATE parent; +/* pub # */ INSERT INTO parent VALUES (2), (4), (6); +/* pub # */ INSERT INTO child VALUES (3), (5), (7); -test_pub=# SELECT * FROM parent ORDER BY a; +/* pub # */ SELECT * FROM parent ORDER BY a; a --- 2 @@ -1386,7 +1323,7 @@ test_pub=# SELECT * FROM parent ORDER BY a; (6 rows) </programlisting> <programlisting> -test_sub=# SELECT * FROM child ORDER BY a; +/* sub # */ SELECT * FROM child ORDER BY a; a --- 5 @@ -1505,8 +1442,7 @@ test_sub=# SELECT * FROM child ORDER BY a; <para> Create a table <literal>t1</literal> to be used in the following example. <programlisting> -test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id)); -CREATE TABLE +/* pub # */ CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id)); </programlisting></para> <para> @@ -1515,15 +1451,14 @@ CREATE TABLE replicated. Notice that the order of column names in the column list does not matter. <programlisting> -test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d); -CREATE PUBLICATION +/* 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> -test_pub=# \dRp+ +/* pub # */ \dRp+ Publication p1 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- @@ -1536,7 +1471,7 @@ Tables: <literal>psql</literal> can be used to show the column lists (if defined) for each table. <programlisting> -test_pub=# \d t1 +/* pub # */ \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- @@ -1559,24 +1494,19 @@ Publications: <literal>s1</literal> that subscribes to the publication <literal>p1</literal>. <programlisting> -test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id)); -CREATE TABLE -test_sub=# CREATE SUBSCRIPTION s1 -test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1' -test_sub-# PUBLICATION p1; -CREATE SUBSCRIPTION +/* 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 some rows to table <literal>t1</literal>. <programlisting> -test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2'); -INSERT 0 1 -test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3'); -INSERT 0 1 -test_pub=# SELECT * FROM t1 ORDER BY id; +/* pub # */ INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1'); +/* pub # */ INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2'); +/* pub # */ INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3'); +/* pub # */ SELECT * FROM t1 ORDER BY id; id | a | b | c | d | e ----+-----+-----+-----+-----+----- 1 | a-1 | b-1 | c-1 | d-1 | e-1 @@ -1589,7 +1519,7 @@ test_pub=# SELECT * FROM t1 ORDER BY id; Only data from the column list of publication <literal>p1</literal> is replicated. <programlisting> -test_sub=# SELECT * FROM t1 ORDER BY id; +/* sub # */ SELECT * FROM t1 ORDER BY id; id | b | a | d ----+-----+-----+----- 1 | b-1 | a-1 | d-1 @@ -1617,13 +1547,10 @@ test_sub=# SELECT * FROM t1 ORDER BY id; For example, note below that subscriber table generated column value comes from the subscriber column's calculation. <programlisting> -test_pub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 1) STORED); -CREATE TABLE -test_pub=# INSERT INTO tab_gen_to_gen VALUES (1),(2),(3); -INSERT 0 3 -test_pub=# CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen; -CREATE PUBLICATION -test_pub=# SELECT * FROM tab_gen_to_gen; +/* pub # */ CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 1) STORED); +/* pub # */ INSERT INTO tab_gen_to_gen VALUES (1),(2),(3); +/* pub # */ CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen; +/* pub # */ SELECT * FROM tab_gen_to_gen; a | b ---+--- 1 | 2 @@ -1631,11 +1558,9 @@ test_pub=# SELECT * FROM tab_gen_to_gen; 3 | 4 (3 rows) -test_sub=# CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 100) STORED); -CREATE TABLE -test_sub=# CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1; -CREATE SUBSCRIPTION -test_sub=# SELECT * from tab_gen_to_gen; +/* sub # */ CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 100) STORED); +/* sub # */ CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1; +/* sub # */ SELECT * from tab_gen_to_gen; a | b ---+---- 1 | 100 @@ -2690,8 +2615,7 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; </programlisting> </para> </step> @@ -2780,8 +2704,7 @@ pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile <xref linkend="two-node-cluster-disable-subscriptions-node2"/> and now, e.g.: <programlisting> -node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); -CREATE TABLE +/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); </programlisting> </para> </step> @@ -2793,8 +2716,7 @@ CREATE TABLE <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; </programlisting> </para> </step> @@ -2805,8 +2727,7 @@ ALTER SUBSCRIPTION <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; </programlisting> </para> </step> @@ -2844,8 +2765,7 @@ ALTER SUBSCRIPTION <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; </programlisting> </para> </step> @@ -2896,8 +2816,7 @@ pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>, e.g.: <programlisting> -node3=# ALTER SUBSCRIPTION sub1_node2_node3 DISABLE; -ALTER SUBSCRIPTION +/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 DISABLE; </programlisting> </para> </step> @@ -2948,8 +2867,7 @@ pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile <xref linkend="cascaded-cluster-disable-sub-node1-node2"/> and now, e.g.: <programlisting> -node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); -CREATE TABLE +/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); </programlisting> </para> </step> @@ -2961,8 +2879,7 @@ CREATE TABLE <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; </programlisting> </para> </step> @@ -2973,8 +2890,7 @@ ALTER SUBSCRIPTION <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; </programlisting> </para> </step> @@ -3025,8 +2941,7 @@ pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile <xref linkend="cascaded-cluster-disable-sub-node2-node3"/> and now, e.g.: <programlisting> -node3=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); -CREATE TABLE +/* node3 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); </programlisting> </para> </step> @@ -3038,8 +2953,7 @@ CREATE TABLE <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>, e.g.: <programlisting> -node3=# ALTER SUBSCRIPTION sub1_node2_node3 ENABLE; -ALTER SUBSCRIPTION +/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 ENABLE; </programlisting> </para> </step> @@ -3050,8 +2964,7 @@ ALTER SUBSCRIPTION <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>, e.g.: <programlisting> -node3=# ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION; </programlisting> </para> </step> @@ -3082,8 +2995,7 @@ ALTER SUBSCRIPTION <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; </programlisting> </para> </step> @@ -3134,8 +3046,7 @@ pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; </programlisting> </para> </step> @@ -3146,8 +3057,7 @@ ALTER SUBSCRIPTION <literal>node2</literal> between <xref linkend="circular-cluster-disable-sub-node2"/> and now, e.g.: <programlisting> -node1=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); -CREATE TABLE +/* node1 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); </programlisting> </para> </step> @@ -3160,8 +3070,7 @@ CREATE TABLE <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>, e.g.: <programlisting> -node1=# ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION; </programlisting> </para> </step> @@ -3173,8 +3082,7 @@ ALTER SUBSCRIPTION <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>, e.g.: <programlisting> -node1=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE; -ALTER SUBSCRIPTION +/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 DISABLE; </programlisting> </para> </step> @@ -3225,8 +3133,7 @@ pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>, e.g.: <programlisting> -node1=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE; -ALTER SUBSCRIPTION +/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 ENABLE; </programlisting> </para> </step> @@ -3237,8 +3144,7 @@ ALTER SUBSCRIPTION the upgraded <literal>node1</literal> between <xref linkend="circular-cluster-disable-sub-node1"/> and now, e.g.: <programlisting> -node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); -CREATE TABLE +/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); </programlisting> </para> </step> @@ -3250,8 +3156,7 @@ CREATE TABLE <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>, e.g.: <programlisting> -node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; -ALTER SUBSCRIPTION +/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; </programlisting> </para> </step> diff --git a/doc/src/sgml/logicaldecoding.sgml b/doc/src/sgml/logicaldecoding.sgml index 3f2bcd45a1e..dd9e83b08ea 100644 --- a/doc/src/sgml/logicaldecoding.sgml +++ b/doc/src/sgml/logicaldecoding.sgml @@ -455,9 +455,8 @@ postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NU using the slot's contents without losing any changes. </para> <para> - Creation of a snapshot is not always possible. In particular, it will - fail when connected to a hot standby. Applications that do not require - snapshot export may suppress it with the <literal>NOEXPORT_SNAPSHOT</literal> + Applications that do not require + snapshot export may suppress it with the <literal>SNAPSHOT 'nothing'</literal> option. </para> </sect2> diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index c10bca63e55..d7595a7e546 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -1134,7 +1134,7 @@ PostgreSQL documentation <term><option>--no-statistics</option></term> <listitem> <para> - Do not dump statistics. + Do not dump statistics. This is the default. </para> </listitem> </varlistentry> @@ -1461,7 +1461,7 @@ PostgreSQL documentation <term><option>--with-statistics</option></term> <listitem> <para> - Dump statistics. This is the default. + Dump statistics. </para> </listitem> </varlistentry> @@ -1681,14 +1681,14 @@ CREATE DATABASE foo WITH TEMPLATE template0; </para> <para> - By default, <command>pg_dump</command> will include most optimizer - statistics in the resulting dump file. However, some statistics may not be - included, such as those created explicitly with <xref - linkend="sql-createstatistics"/> or custom statistics added by an - extension. Therefore, it may be useful to run <command>ANALYZE</command> - after restoring from a dump file to ensure optimal performance; see <xref - linkend="vacuum-for-statistics"/> and <xref linkend="autovacuum"/> for more - information. + If <option>--with-statistics</option> is specified, + <command>pg_dump</command> will include most optimizer statistics in the + resulting dump file. However, some statistics may not be included, such as + those created explicitly with <xref linkend="sql-createstatistics"/> or + custom statistics added by an extension. Therefore, it may be useful to + run <command>ANALYZE</command> after restoring from a dump file to ensure + optimal performance; see <xref linkend="vacuum-for-statistics"/> and <xref + linkend="autovacuum"/> for more information. </para> <para> diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index 8c5141d036c..723a466cfaa 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -567,7 +567,7 @@ exclude database <replaceable class="parameter">PATTERN</replaceable> <term><option>--no-statistics</option></term> <listitem> <para> - Do not dump statistics. + Do not dump statistics. This is the default. </para> </listitem> </varlistentry> @@ -741,7 +741,7 @@ exclude database <replaceable class="parameter">PATTERN</replaceable> <term><option>--with-statistics</option></term> <listitem> <para> - Dump statistics. This is the default. + Dump statistics. </para> </listitem> </varlistentry> @@ -957,14 +957,14 @@ exclude database <replaceable class="parameter">PATTERN</replaceable> </para> <para> - By default, <command>pg_dumpall</command> will include most optimizer - statistics in the resulting dump file. However, some statistics may not be - included, such as those created explicitly with <xref - linkend="sql-createstatistics"/> or custom statistics added by an - extension. Therefore, it may be useful to run <command>ANALYZE</command> - on each database after restoring from a dump file to ensure optimal - performance. You can also run <command>vacuumdb -a -z</command> to analyze - all databases. + If <option>--with-statistics</option> is specified, + <command>pg_dumpall</command> will include most optimizer statistics in the + resulting dump file. However, some statistics may not be included, such as + those created explicitly with <xref linkend="sql-createstatistics"/> or + custom statistics added by an extension. Therefore, it may be useful to + run <command>ANALYZE</command> on each database after restoring from a dump + file to ensure optimal performance. You can also run <command>vacuumdb -a + -z</command> to analyze all databases. </para> <para> diff --git a/doc/src/sgml/release-18.sgml b/doc/src/sgml/release-18.sgml index 2a52cef1c7c..619592bd882 100644 --- a/doc/src/sgml/release-18.sgml +++ b/doc/src/sgml/release-18.sgml @@ -6,7 +6,7 @@ <formalpara> <title>Release date:</title> - <para>2025-??-??, CURRENT AS OF 2025-05-01</para> + <para>2025-??-??, CURRENT AS OF 2025-05-23</para> </formalpara> <sect2 id="release-18-highlights"> @@ -82,7 +82,8 @@ Deprecate MD5 password authentication (Nathan Bossart) </para> <para> -Warnings generated by their use can be disabled by the server variable md5_password_warnings. +Support for MD5 passwords will be removed in a future major version release. CREATE ROLE and ALTER ROLE now emit deprecation warnings when setting MD5 passwords. +These warnings can be disabled by setting the md5_password_warnings parameter to "off". </para> </listitem> @@ -469,7 +470,8 @@ Allow skip scans of btree indexes (Peter Geoghegan) </para> <para> -This is effective if the earlier non-referenced columns contain few unique values. +This allows multi-column btree indexes to be used by queries that only +equality-reference the second or later indexed columns. </para> </listitem> @@ -961,21 +963,6 @@ This is true even if the tables in different schemas have different column names </listitem> <!-- -Author: Daniel Gustafsson <dgustafsson@postgresql.org> -2025-04-08 [042a66291] Add function to get memory context stats for processes -Author: Daniel Gustafsson <dgustafsson@postgresql.org> -2025-04-08 [c57971034] Rename argument in pg_get_process_memory_contexts(). ---> - -<listitem> -<para> -Add function pg_get_process_memory_contexts() to report process memory context statistics (Rahila Syed) -<ulink url="&commit_baseurl;042a66291">§</ulink> -<ulink url="&commit_baseurl;c57971034">§</ulink> -</para> -</listitem> - -<!-- Author: David Rowley <drowley@postgresql.org> 2024-07-01 [12227a1d5] Add context type field to pg_backend_memory_contexts --> @@ -1226,12 +1213,15 @@ This is useful for operating system configuration. <!-- Author: Peter Eisentraut <peter@eisentraut.org> 2025-03-19 [4f7f7b037] extension_control_path +Author: Peter Eisentraut <peter@eisentraut.org> +2025-05-02 [81eaaa2c4] Make "directory" setting work with extension_control_pat --> <listitem> <para> Add server variable extension_control_path to specify the location of extension control files (Peter Eisentraut, Matheus Alcantara) <ulink url="&commit_baseurl;4f7f7b037">§</ulink> +<ulink url="&commit_baseurl;81eaaa2c4">§</ulink> </para> </listitem> @@ -1315,7 +1305,7 @@ Author: Amit Kapila <akapila@postgresql.org> <listitem> <para> -Change the default CREATE SUBSCRIPTION streaming option from "off" to "parallel" (Hayato Kuroda, Masahiko Sawada, Peter Smith, Amit Kapila) +Change the default CREATE SUBSCRIPTION streaming option from "off" to "parallel" (Vignesh C) <ulink url="&commit_baseurl;1bf1140be">§</ulink> </para> </listitem> @@ -2596,12 +2586,15 @@ Add pg_dump options --with-schema, --with-data, and --with-statistics (Jeff Davi <!-- Author: Nathan Bossart <nathan@postgresql.org> 2025-03-25 [9c49f0e8c] pg_dump: Add - -sequence-data. +Author: Nathan Bossart <nathan@postgresql.org> +2025-05-07 [acea3fc49] pg_dumpall: Add - -sequence-data. --> <listitem> <para> -Add pg_dump option --sequence-data to dump sequence data that would normally be excluded (Nathan Bossart) +Add pg_dump and pg_dumpall option --sequence-data to dump sequence data that would normally be excluded (Nathan Bossart) <ulink url="&commit_baseurl;9c49f0e8c">§</ulink> +<ulink url="&commit_baseurl;acea3fc49">§</ulink> </para> </listitem> @@ -2753,7 +2746,7 @@ This is to handle cases where a pre-Postgres 18 cluster's default CPU signedness </sect4> <sect4 id="release-18-logicalrep-app"> - <title>Logical Replication Applications></title> + <title>Logical Replication Applications</title> <itemizedlist> @@ -2848,6 +2841,18 @@ Injection points can now be created, but not run, via INJECTION_POINT_LOAD(), an </listitem> <!-- +Author: Michael Paquier <michael@paquier.xyz> +2025-05-10 [371f2db8b] Add support for runtime arguments in injection points +--> + +<listitem> +<para> +Support runtime arguments in injection points (Michael Paquier) +<ulink url="&commit_baseurl;371f2db8b">§</ulink> +</para> +</listitem> + +<!-- Author: Heikki Linnakangas <heikki.linnakangas@iki.fi> 2024-07-26 [20e0e7da9] Add test for early backend startup errors --> |