Continue Logical Replication After Master Became Slave and then Became Master Again

Hi All, Postgres 15.2 We have Patroni cluster with one master and two replicas. The master is publisher (logical replication) to some subscriptions running on other clusters. When we have a failover, the master becomes replica and one of the replicas assume the role of master. In such a cas

logical replication - who is managing replication slots created automatically during initial sync

Hi Experts I have seen that logical replication slots created automatically by Postgres during initial sync (a slot per table), are marked as "wal_status = lost" and "active = false". 1. Who is responsible for removing those faulty replication slots? 2. Can a slot with "wal_status = lo

Tables get stuck at srsubstate = f

Hi all, I'm using logical replication with Postgres 15.2. When creating logical replication on multiple tables, sometimes the initial sync get stuck for few tables at state srsubstate = f. If I recreate the logical replication again, it may get stuck at srsubstate = f for other tables, so it

Scenarios that Replication Slot will be Marked as Active = false

Hi Experts, I would like to write a monitoring script that will check logical replication running on our setup. For that I would like to know when to alert when I see that replication slot is marked as active= false. I will probably need to know when it is a temporary state that I need to wai

replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist

at is wrong? From: Avi Weinberg Sent: Wednesday, April 17, 2024 6:01 PM To: pgsql-generallists.postgresql.org Subject: [External] replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist Hi Experts, For a second time in the past few months I'm getting the

replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist

Hi Experts, For a second time in the past few months I'm getting the following errors in Postgres log. Last time it was solved when I reset all Postgres pods. Now reset no longer helps. Logical replication is now working even after I performed the reset. Any ideas what is wrong? ERROR: re

RE: [External] Simple way to simulate a bug in logical replication

Please note: I tried it also with Postgres 15.6 and the behavior is the same I'm running inside docker. I do not know if it matters... Hi All, I think I hit a bug in logical replication in version 15.2. I sync two tables of size 305MB but pg_stat_progress_copy shows that 5GB as bytes_processed

Simple way to simulate a bug in logical replication

Hi All, I think I hit a bug in logical replication in version 15.2. I sync two tables of size 305MB but pg_stat_progress_copy shows that 5GB as bytes_processed and the sync takes forever. Is this a bug? If so, what can I do with this scenario? Thanks Create a dummy table CREATE TABLE exampl

RE: [External] Re: walsender RAM increases by 500 MB while data is 80 MB

did not help. Any other suggestions that might help? Thanks! -Original Message- From: Masahiko Sawada Sent: Monday, March 11, 2024 10:38 AM To: Avi Weinberg Cc: pgsql-generallists.postgresql.org Subject: [External] Re: walsender RAM increases by 500 MB while data is 80 MB On Mon, Mar

walsender RAM increases by 500 MB while data is 80 MB

Hi Experts, Your input is most welcome! We are using Postgres 13 (and plan to upgrade to 15 soon). We have logical replication with about 40 servers subscribing to one publisher. 40 Walsender processes are running on the publisher server. When we insert a row into a table holding binary dat

Trigger to Count Number of Logical Replication Table Changes.

I'm using Postgres (13 and 15) logical replication to sync data from two servers. I would like to have an update counter whenever data is changed. The counter can be incremented by 1 even if multiple rows are updated, but it is also ok to be incremented the counter by the number of rows updated

Postgres Out Of Memory Crash

Hi experts, I'm using Patroni Postgres installation and noticed that twice already postgres crashed due to out of memory. I'm using logical replication with around 30-40 active subscribers on this machine. The machine has 128GB but only 32GB is allocated to Postgres. How can I know what is a

Logical Replication - Adding Not-Null Column Without Default Value

Hi Experts, I'm using logical replication with Postgres 15. I added not-null column without default value to one of my published tables. The way I so it on publisher side is to add the column as nullable, then update the value based on some logic, and finally make the column not-null. On the

Problematic enforcement of "ERROR: functions in index predicate must be marked IMMUTABLE"

Hi, If you attempt to create an index based on function that is not IMMUTABLE you will get an exception "ERROR: functions in index predicate must be marked IMMUTABLE". However, if you created the index when the function was IMMUTABLE, but later on you updated the function and mistakenly remov

Postgres Incompatibility

Hi Experts I noticed this problem when creating a backup on Postgres 13 and restoring it on Postgres 15. CREATE FUNCTION sync.show_pg_subscription1() RETURNS SETOF pg_subscription LANGUAGE sql AS $$ SELECT * from pg_subscription; $$;

Logical Replication - Give One Subscription Priority Over Other Subscriptions

Hi all, I use logical replication to synchronize one big table + some small tables. I know that if all tables are part of the same subscription it will be executed in transactional order. In other words, as long as the transaction that updated the big table is not synchronized and executed on t

Is data passing between publisher and subscriber in logical replication compressed?

Hi Expert, I'm using logical replication to synchronize data between publisher and various subscribers. I would like to know if the data flowing between publisher and subscriber is compressed? In addition, is there anything I an do to make the bandwidth usage more efficient? Thanks! IMPORTAN

RE: Multiple SELECT statements Using One WITH statement

if I have two queries that wants to use it? Thanks From: David G. Johnston [mailto:david.g.johns...@gmail.com] Sent: Thursday, January 20, 2022 2:58 PM To: Avi Weinberg Cc: pgsql-general@lists.postgresql.org Subject: Re: Multiple SELECT statements Using One WITH statement On Thursday, January

Multiple SELECT statements Using One WITH statement

Hi, Can I have multiple select statements using one WITH statement? WITH t AS ( Select A, B from ... ) SELECT A into tableA FROM t where ; SELECT B into tableB FROM t where ; IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain in

Using FOREIGN TABLE to get the Size of the Actual Remote Table Behind it

Hi Experts, Is it possible to get the size of the table a foreign table is pointing to (without creating another connecting to the remote server)? Obviously this "select pg_size_pretty(pg_total_relation_size('f_table'))" returns 0. Thanks! IMPORTANT - This email and any attachments is intende

Refresh ONE publication out of the Two Publications the Subscription has

Is it possible to specify the publication name to be refreshed in case I do not want to refresh all publications of the subscription? Something like: alter subscription subscription_name refresh publication publication_name Thanks IMPORTANT - This email and any attachments is intended for the abo

postgres_fdw FAST "where id in (140,144,148)" SLOW: "where id in (select 140 as id union select 144 union select 148)"

Hi I have postgres_fdw table called tbl_link. The source table is 2.5 GB in size with 122 lines (some lines has 70MB bytea column, but not the ones I select in the example) I noticed that when I put the specific ids in the list "where id in (140,144,148)" it works fast (few ms), but when I put

Initial Sync - One Subscriber After The Other vs. Parallel

Hi All I'm testing logical replication and noticed that when I sync large tables to multiple subscribers, it is synchronizing two large tables out of 4 for subscriber A and then then two tables to subscriber B and continue to toggle between the two subscriptions. It is possible to tell it to c

Logical Replication - One table 8 minutes, Identical Two Tables 28 Minutes (Expected 16 Minutes)

Hi All, When I have only one table (2.5GB) in logical replication publication it takes 8 minutes to sync to the subscriber when I created a copy of the table and added it to the publication it took 28 minutes. When I sync three IDENTICAL tables of the same size it take 46 minutes! I expected t

RE: Identity/Serial Column In Subscriber's Tables

ginal Message- From: Laurenz Albe [mailto:laurenz.a...@cybertec.at] Sent: Thursday, December 9, 2021 1:51 PM To: Avi Weinberg ; pgsql-general@lists.postgresql.org Subject: Re: Identity/Serial Column In Subscriber's Tables On Thu, 2021-12-09 at 09:13 +, Avi Weinberg wrote: > What is the

Identity/Serial Column In Subscriber's Tables

Hi Experts, What is the best practice when dealing with Identity/Serial column in logical replication. Should the subscriber table have this column also defined as Identity/Serial or have it defined as regular integer? Is leaving it as Identity/Serial also on subscriber tables, can potentially

RE: Are Foreign Key Disabled During Logical Replication Initial Sync?

pg_subscription_rel.srsubstate is 'i' or 'd' for all tables or there is a better way? Thanks! -Original Message- From: Peter Eisentraut [mailto:peter.eisentr...@enterprisedb.com] Sent: Tuesday, December 7, 2021 7:19 AM To: Avi Weinberg ; pgsql-general@lists.postgresql.org Subj

Are Foreign Key Disabled During Logical Replication Initial Sync?

Hi all, I understood that during logical replication initial sync tables are copied in full using some type of "copy command". Does it mean that populating each table is done in a single transaction? If so, when I have tables with foreign keys between them, is it guaranteed that logical replic

Logical Replication - When to Enable Disabled Subscription and When to Create a New One

Sorry, my previous mail had in the title Publication instead of Subscription Hi All, If I find a subscription in subenabled = false state. Is it safe to run ALTER subscription and put it in ENABLE state or it is better to drop and create it from scratch? I'm trying to figure our what can cause

Logical Replication - When to Enable Disabled Publication and When to Create a New One

Hi All, If I find a subscription in subenabled = false state. Is it safe to run ALTER subscription and put it in ENABLE state or it is better to drop and create it from scratch? I'm trying to figure our what can cause subscription to be in subenabled = false state. If we know all the scenari

Logical Replication - Source and Destination Table Name Not The Same

Hi, Is it possible to define logical replication where the source and destination table names are not the same. I need to sync a table from some source, but already have a table with the same name. Thanks Avi IMPORTANT - This email and any attachments is intended for the above named addressee

RE: Logical Replication - Should Destination Table Columns Be Defined With Default Value

etc. does not help and the column on subscriber side remains with nulls Your input is most welcome From: David G. Johnston [mailto:david.g.johns...@gmail.com] Sent: Tuesday, November 9, 2021 5:55 PM To: Avi Weinberg Cc: pgsql-general@lists.postgresql.org Subject: Re: Logical Replication

Logical Replication - Should Destination Table Columns Be Defined With Default Value

Hi Experts, I'm using logical replication and have the following open issue: If the table on the publisher side has column with default value, should the table on the destination (subscriber) be defined with default value as well? My intuition was not to define it with default value since it get

Subscriber to Get Only Some of The Tables From Publisher

Hi, I have a publisher with around 30 tables. I have two types of subscribers. Both types needs 25 "common" tables from the publisher plus 2-3 specific tables for each type of subscriber. For maintenance and monitoring reasons it is better for me to have both subscribers point to the same pu

get current worker processes count

Hi I set max_worker_processes to 20. How can I check how many are currently running? IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform th

max_worker_processes - Check How Many Worker Processes are Currently Active

Hi, Is it possible to check how many worker processes and logical replication workers are currently running I would like to find out how close I'm to the max limit. max_logical_replication_workers max_worker_processes Thanks IMPORTANT - This email and any attachments is intended for the abov

Logical Replication - Different Primary Key on Source Table and Destination Table

Hi I use logical replication to copy data from many source tables to one destination table. On destination table I would like to have primary key composed of the source site ID and row ID. On the source table I only need the primary key to be the row ID since the site ID is always the same pe

Lazy View's Column Computing

Hi, Is there a way to compute a column in a view only if it is referenced in the query? I have a view's column that its value is computed by a function. If in the query that column is not used at all, can Postgres "skip" computing it? Thanks! IMPORTANT - This email and any attachments is inte

Recursive Parent-Child Function Bottom Up

Hi, I would like to populate the children_ids column with all the ids of the children recursively (+ grandchildren etc.) If I do it top-bottom I will end up doing extra work since there is no need to go all levels down if I can just compute my IMMEDIATE children "children_ids" and just concaten

View of Union Multiple Tables - Only If table EXISTS

Hi, I would like to create a view which is UNION of multiple tables with the same name from different schemas. Select col1, col2 from schemaA.table UNION Select col1, col2 from schemaB.table However, it is possible that in some of the schemas the table was not created yet. I know I can check

Logical Replication Configuration for 11 sites Bi-directional logical replication

I have a setup with the following: One (1) "main" site Ten (10) "regional" sites "main" site is a publisher to each of the regional sites (for 20 tables). "main" site is also a subscriber from each of the 10 regional sites (5 tables). In short: The main site has 1 publication and 10 subscription

RE: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

have both old and new names? From: Vijaykumar Jain [mailto:vijaykumarjain.git...@gmail.com] Sent: Sunday, July 4, 2021 6:53 PM To: Avi Weinberg Cc: pgsql-general@lists.postgresql.org Subject: Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure

Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

I'm using logical replication to copy data from multiple tables to a single destination table. At times the structure of the source table needs to change. However, not all source table will have their structure updated at the same time. Let's assume, for example, a column type needs to be cha

Must be superuser to create subscriptions - Any way I can avoid using superuser

I would like to avoid using superuser to create subscriptions (logical replication). If I do not use superuser, I get the following error message "must be superuser to create subscriptions". Is there a way to void using superuser to create a subscription? What is pg_subscription_users that I

Is it Possible to Rename Replication Slot + Can Publisher be Paused

Hi Experts, 1. Is it possible to rename replication slot? 2. Is it possible to pause publisher and resume if from the point it stopped after few minutes? It know it is possible to do it with subscriber, but since I have many subscribers, I would like to do the pause to the publisher. IM

Notify When Streaming Replication Failover Occurred (slave promoted to master).

I need to take actions when Postgres streaming replication failover occurred. Is there a way to be notified when Postgres slave becomes master? If no such notification possible, what is the best way to actively monitor which server is master. My Postgres is running inside Kubernetes and the HA i

Notify When Streaming Replication Failover Occurred (slave promoted to master).

I need to take actions when Postgres streaming replication failover occurred. Is there a way to be notified when Postgres slave becomes master? If no such notification possible, what is the best way to actively monitor which server is master. My Postgres is running inside Kubernetes and the

Implicit table removal from logical replication publication

Hi Experts I had a case where a team member deleted and recreated an empty table which participated in logical replication. After that action the table no longer was part of the replication and new inserts were not copied to the subscribers. How can I check existing publication for the list o