Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

Hello Michał, Thanks for the reply. I suspected as much, I was just wondering if there was an easy fix that didn't involve dropping the entire subscription and having to re-do all the table because of that. Guess my only option is to remove the affected tables from the publisher before the patch,

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

> On 17 Oct 2024, at 11:07, Koen De Groote wrote: > > Hello Muhammad, > > The problem with my scenario is the changes are written as a single > transaction, with a BEGIN and COMMIT. In that transaction, there are first > inserts, then a schema change, and then inserts on the new schema. I

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

Hello Muhammad, The problem with my scenario is the changes are written as a single transaction, with a BEGIN and COMMIT. In that transaction, there are first inserts, then a schema change, and then inserts on the new schema. Doing as you said does not work. The subscriber will first complain it

Re: Logical replication disabled, recovery not possible because of 1 large transaction with schema changes?

Hi, When you execute schema-altering operations on the publisher, these changes are not automatically replicated to the subscriber which causes the following error logical replication target relation "public.dummy_table" is missing replicated columns: "contact_email", "status", "phone_number", "us

Re: Logical Replication Delay

Hi Ram, 29 set 2024, 12:29 Ramakrishna m : *We are planning to set up logical replication from a standby to another > server. When the primary goes down, there is no issue as the standby > becomes the primary and the logical slots are already present. However, > when the standby goes down, these

Re: Logical Replication Delay

Hi Justin and Greg, Thank you for your input and recommendations. We understand your point regarding separating the tables into different publications and subscriptions. However, due to certain business constraints, we are unable to implement this approach at the moment. *We are planning to set u

Re: Logical Replication Delay

On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m wrote: > I would greatly appreciate any suggestions you may have to help avoid > logical replication delays, whether through tuning database or operating > system parameters, or any other recommendations > In addition to the things already answered:

Re: Logical Replication Delay

Hi Ramakrishna, I am not following the reasoning on not separating the tables into different publications and subscriptions. I set up logical replication all the time in many different environments, one of the audits I perform before deploying LR is looking at pg_stat_all_tables and WAL creation

Re: Logical Replication Delay

Hi Justin, Thank you for your suggestions and detailed insights. Due to certain business constraints, we are unable to split the tables into parallel publications. All of the tables involved are primary key tables, which adds further complexity to separating them into multiple publications. That

Re: Logical Replication Delay

Hi Ramakrishna, 4GB of WAL generated per minute is a lot. I would expect the replay on the subscriber to lag behind because it is a single process. PostgreSQL 16 can create parallel workers for large transactions, however if there is a flood of small transactions touching many tables the single

Re: Logical replication without direct link between publisher and subscriber?

I've considered it, but it sounds like a lot of work and failure prone. Even projects like Debezium seem like it's a ton to set up. Thanks for the suggestions. Regards, Koen On Wed, Sep 11, 2024 at 3:20 PM Greg Sabino Mullane wrote: > Dumping changes periodically, sending them directly or uplo

Re: Logical replication without direct link between publisher and subscriber?

> > Dumping changes periodically, sending them directly or uploading to cloud > storage and then downloading and applying them on the subscriber side. > But maybe there's a simpler option someone here knows about? How about using WAL shipping to populate a replica, and either query that directly

Re: Logical replication slots on slaves/replicas?

That´s a great point. Thank you. Piotr Andreassen Blasiak > On 1 Aug 2024, at 12:03, Torsten Förtsch wrote: > > A COMMIT record in the WAL has an LSN. I don't know much about debezuim but > wal2json can give you this LSN. Then there is this function, > pg_last_wal_replay_lsn(). I think you

Re: Logical replication slots on slaves/replicas?

A COMMIT record in the WAL has an LSN. I don't know much about debezuim but wal2json can give you this LSN. Then there is this function, pg_last_wal_replay_lsn(). I think you could run it on the replica to see if you are after the point of commit. If you are, you should be able to see the changes m

Re: Logical replication slots on slaves/replicas?

Piotr Andreassen Blasiak schrieb am 01.08.2024 um 10:42: > I know that currently logical replication slots are available only > for primary servers. Is there any plan to add this feature to read > slaves as well? My problem is this: > > I want to use debezium to stream changes from postgresql. But,

Re: Logical Replication - PG_Wall size is too big, What can I do ?

On Tue, 2024-07-09 at 07:05 +0100, Jaurès FOUTE KUETE wrote: > [image showing a large "pg_wal" directory] > > We are facing this issue and want to know how can I do to clean this folder > without problem. > > What can be the cause of that ? > and How can I clean this folder without stopping Logi

Re: Logical replication with temporary tables

On Tuesday, July 2, 2024, Stuart Campbell wrote: > This is a question for AWS. Community PostgreSQL doesn't have any of >> these concepts, and this is all proprietary modifications to PostgreSQL by >> Amazon. > > > Maybe my question can be re-summarised as: do DDL operations on temporary > table

Re: Logical replication with temporary tables

> On Jul 2, 2024, at 18:16, Stuart Campbell > wrote: > > My understanding was that under the hood, AWS uses the logical replication > features that are present in community Postgres. If that's incorrect then I'm > sorry for the off-topic post. Yes, but: The idea of a "degraded" replication

Re: Logical replication with temporary tables

> > This is a question for AWS. Community PostgreSQL doesn't have any of > these concepts, and this is all proprietary modifications to PostgreSQL by > Amazon. My understanding was that under the hood, AWS uses the logical replication features that are present in community Postgres. If that's in

Re: Logical replication with temporary tables

> On Jul 2, 2024, at 17:47, Stuart Campbell > wrote: > My question is whether there is some workaround that will let me create > indexes on a temporary table, and also let me drop the temporary table, in a > way that doesn't end up "degrading" replication? (Presumably that means > avoiding

RE: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

Hi Laurenz, Thanks a lot! Regards, Meera -Original Message- From: Laurenz Albe Sent: Wednesday, June 5, 2024 3:56 PM To: Meera Nair ; pgsql-general@lists.postgresql.org Cc: Punit Pranesh Koujalgi ; B Ganesh Kishan Subject: Re: Logical replication type- WAL recovery fails and

RE: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

tml> Regards, Meera From: Ron Johnson Sent: Thursday, June 6, 2024 9:56 PM To: Laurenz Albe Cc: Meera Nair ; pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi ; B Ganesh Kishan Subject: Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedi

Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

On Wed, Jun 5, 2024 at 6:26 AM Laurenz Albe wrote: > On Wed, 2024-06-05 at 06:36 +, Meera Nair wrote: > > 2024-06-05 11:41:32.369 IST [54369] LOG: restored log file > "00050001006A" from archive > > 2024-06-05 11:41:33.112 IST [54369] LOG: restored log file > "0005000100

Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir

On Wed, 2024-06-05 at 06:36 +, Meera Nair wrote: > 2024-06-05 11:41:32.369 IST [54369] LOG:  restored log file > "00050001006A" from archive > 2024-06-05 11:41:33.112 IST [54369] LOG:  restored log file > "00050001006B" from archive > cp: cannot stat ‘/home/pgsql/wmast

Re: Logical Replication Delay on Remote Server

Hi Team, adding one more point here, 5. Pg_stat_replication_slot total_txn count reduces when lag occurs .Does decoding stop in case of logical replication delay? On Tue, 23 Apr 2024 at 22:55, Ramakrishna m wrote: > Hi Team, > > I am facing replication lag in postgres16 at times,not able to

Re: Logical replication and AFTER UPDATE triggers [PG 16]

On Fri, 2 Feb 2024 at 13:20, Chris Angelico wrote: > create or replace function send_settings_notification() returns > trigger language plpgsql as $$begin perform > pg_notify('stillebot.settings', ''); return null; end$$; > create trigger settings_update_notify after update on > stillebot.settings

Re: Logical replication claims to work, not working - new tables

On Mon, 22 Jan 2024 at 05:50, Chris Angelico wrote: > > On Mon, 22 Jan 2024 at 05:25, Justin wrote: > > Adding a primary key will fix this issue. Note PG 16 can use indexes to > > find qualifying rows when a table's replica is set to full. > > I'll try dropping the table, creating it again with

Re: Logical replication claims to work, not working - new tables

On Mon, 22 Jan 2024 at 05:25, Justin wrote: > > When using replica set to full this kicks off a full table scan for each > update or delete this is very expensive. If there are no errors being > reported you will find it is working but hung doing full scans. Inserts are > just appended to e

Re: Logical replication claims to work, not working - new tables

On Sat, Jan 20, 2024, 5:43 PM Chris Angelico wrote: > PostgreSQL 15 on Debian, both ends of replication. > > I'm doing logical replication in a bit of a complex setup. Not sure > how much of this is relevant so I'll give you a lot of detail; sorry > if a lot of this is just noise. > > * Bidirecti

Re: Logical replication breaks: "unexpected duplicate for tablespace 0, relfilenode 2774069304"

On Thu, Dec 28, 2023 at 02:03:12PM +0200, Kouber Saparev wrote: >> The first problem that we have here is that we've lost track of the >> patch proposed, so I have added a CF entry for now: >> https://commitfest.postgresql.org/46/4720/ > > Thank you. Is there a bug report or should we file one? It

Re: Logical replication breaks: "unexpected duplicate for tablespace 0, relfilenode 2774069304"

На нд, 24.12.2023 г. в 3:37 Michael Paquier написа: > > What quick solution would fix the replication? Repack of the table? > Reload > > of the database? Killing some backends? > > There may be something you could do as a short-term solution, but it > does not solve the actual root of the problem

Re: Logical replication breaks: "unexpected duplicate for tablespace 0, relfilenode 2774069304"

On Fri, Dec 22, 2023 at 10:55:24AM +0200, Kouber Saparev wrote: > The table for this file node is not even included in any of the > publications we have. I've found a similar issue described [1] before, so I > was wondering whether this patch is applied? Our subscriber database is > PostgreSQL 16.1

Re: Logical Replication vs. Free Replication Slots

On Tue, Oct 3, 2023 at 10:27 AM Don Seiler wrote: > On the source (PG 12.15) instance, we have bumped max_replication_slots > and max_wal_senders to 50, and max_sync_workers_per_subscription to 10. > Forgot to note that on the subscriber (PG 15.4) instance, max_sync_workers_per_subscription is 4

Re: Logical replication restarts

Hi, There should be another line in the log for why the logical replication worker has stopped in the first place. Something like Primary Key or Unique Constraint violation, timeout on WAL sender or receiver process, etc. Unless you can provide that information I doubt someone will be able to help

Re: Logical Replication Sync Validation

On Tue, 2023-04-18 at 10:20 +0200, Robert Sjöblom wrote: > At the moment where we would switch to pg15 being the primary/lead we > will stop writes to pg10; at that point we will validate that we are > fully in sync, tear down pg10 and send writes to pg15. Our question is > how we can validate o

Re: Logical Replication Sync Validation

> On Apr 18, 2023, at 03:45, Robert Sjöblom wrote: > I'm aware of that. But you can, however, do something like: > > SELECT * FROM FOO WHERE CTID = (SELECT MAX(CTID) FROM FOO); > > on both sides. The idea being that if I change FOO, the CTID of the changed > row will not be the same on both

Re: Logical Replication Sync Validation

> On Apr 18, 2023, at 01:20, Robert Sjöblom wrote: > Another idea we've had would be to use CTID to fetch the last row > (update/insert) in each table on both sides and compare row content, is this > feasible? Is it safe to rely on CTIDs across logical replication? No. CTIDs aren't sent ove

Re: Logical replication fails when adding multiple replicas

OK, that makes sense. I think something that is unique to subscribers is sensible, postmaster startup time sounds reasonable! Thanks for looking at it. On Thu, Mar 23, 2023 at 8:17 AM Kyotaro Horiguchi wrote: > At Wed, 22 Mar 2023 09:25:37 +, Will Roper < > will.ro...@democracyclub.org.uk> w

Re: Logical replication fails when adding multiple replicas

At Wed, 22 Mar 2023 09:25:37 +, Will Roper wrote in > Thanks for the response Hou, > > I've had a look and when the tablesync workers are spinning up there are > some errors of the form: > > "2023-03-17 18:37:06.900 UTC [4071] LOG: logical replication table > synchronization worker for su

Re: Logical replication fails when adding multiple replicas

Thanks for the response Hou, I've had a look and when the tablesync workers are spinning up there are some errors of the form: "2023-03-17 18:37:06.900 UTC [4071] LOG: logical replication table synchronization worker for subscription ""polling_stations_0561a02f66363d911"", table ""uk_geo_utils_o

RE: Logical replication fails when adding multiple replicas

On Monday, March 20, 2023 8:46 PM Will Roper wrote: Hi, > We’re having some issues with Postgresql’s logical replication. Specifically > trying to add several replicas at once. Essentially we can add replicas one at > a time, but when we try and add two or more together some of the table > sub

Re: Logical Replication - "invalid ordering of speculative insertion changes"

Hi Joe, On Fri, Feb 3, 2023 at 1:42 AM Joe Wildish wrote: > Just a bump on this --- perhaps the error is a bug with the DBMS? > > From what I can see "speculative insertion changes" in this context means > INSERT..ON CONFLICT DML. Although I have some experience writing > extensions and simple

Re: Logical Replication - "invalid ordering of speculative insertion changes"

Just a bump on this --- perhaps the error is a bug with the DBMS? >From what I can see "speculative insertion changes" in this context means >INSERT..ON CONFLICT DML. Although I have some experience writing extensions >and simple patches for the code base, I don't know anything as a developer

Re: Logical replication versus pglogical on PostgreSQL 14

On Saturday, July 23, 2022, Rory Campbell-Lange wrote: > Is native logical replication and pglogical replication fundamentally > the same? Here is a discussion about the differences between pglogical 2.1 and logical replication in PostgreSQL 10:

Re: Logical replication of large objects

På torsdag 09. juni 2022 kl. 20:24:56, skrev Joshua Drake mailto:j...@commandprompt.com>>: Large objects are largely considered a deprecated feature. Though I like the idea, was there any consensus on -hackers? Nobody seems interested in it… -- Andreas Joseph Krogh CTO / Partner - Visen

Re: Logical replication of large objects

Large objects are largely considered a deprecated feature. Though I like the idea, was there any consensus on -hackers? JD On Sun, Jun 5, 2022 at 2:23 AM Andreas Joseph Krogh wrote: > I started this thread 5 years ago: > https://www.postgresql.org/message-id/flat/7c70d9bd-76fc-70fa-cfec-14f00a

Re: Logical replication 'possible' problem

The logical replication dump of the table I thought was 'stuck' eventually completed after 6+ hours. I guess the replication slot showing active as false is to be expected. I never noticed it before. So there never was an issue - apart from my ignorance. Sorry for the noise. Cheers, Steve On We

Re: Logical replication 'possible' problem

Sorry, I should have added the publisher is on 13.1 and the subscriber 14.2. Both are AWS RDS instances. I checked the log files for the publisher and subscriber and couldn't see any logical replication errors. The publisher is a busy DB though so if there are any errors there, I may have missed th

Re: logical replication worker can't find postgis function

OK thanks for the help, have a nice weekend! On Fri, Apr 22, 2022 at 3:39 PM Laurenz Albe wrote: > On Fri, 2022-04-22 at 15:26 +0200, Willy-Bas Loos wrote: > > On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe > wrote: > > > > > > The trigger function is bad and dangerous, because it relies on the >

Re: logical replication worker can't find postgis function

On Fri, 2022-04-22 at 15:26 +0200, Willy-Bas Loos wrote: > On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe wrote: > > > > The trigger function is bad and dangerous, because it relies on the current > > setting of "search_path". > > > > You notice that with logical replication, because "search_path

Re: logical replication worker can't find postgis function

On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe wrote: > > The trigger function is bad and dangerous, because it relies on the > current setting of "search_path". > > You notice that with logical replication, because "search_path" is empty > to avoid security problems. > Thanks a lot! Do you mean t

Re: logical replication worker can't find postgis function

On Fri, 2022-04-22 at 13:25 +0200, Willy-Bas Loos wrote: > I'm using logical replication on postgresql 13. On the subscriber, there's a > trigger on a table that calculates the area of the geometry that's in another > column. > I enabled the trigger with > ALTER TABLE atable ENABLE ALWAYS TRIGG

Re: Logical replication issue after Postgresql upgrade from 13 to 14

On Wed, Dec 1, 2021 at 5:56 PM Marcos Pegoraro wrote: > >> I have an issue with logical replication after Postgresql upgrade from >> 13 to 14 (upgraded subscriber only using pg_upgrade_cluster -m link 13 >> main). After upgrade all subscriptions were disabled so I have enabled >> them and replicat

Re: Logical replication issue after Postgresql upgrade from 13 to 14

I think there are some bugs in Posgresql logical replication upgrade. Because dropping and recreating subscriptions with manual synchronization has solved the problem for me. But it is not the correct way, IMHO. Sergey Belyashov ср, 1 дек. 2021 г. в 15:26, Marcos Pegoraro : > > >> I have an issue

Re: Logical replication issue after Postgresql upgrade from 13 to 14

> I have an issue with logical replication after Postgresql upgrade from > 13 to 14 (upgraded subscriber only using pg_upgrade_cluster -m link 13 > main). After upgrade all subscriptions were disabled so I have enabled > them and replication workers successfully started. > pg_stat_subscription cont

Re: Logical Replication - Type messages?

Stefen Hillman writes: > Currently, I process Relation messages to get most of the information I > need to work with Inserts, Updates, and Deletes. However, for type > information I'm currently using a SQL query to get the column type > information. I wanted to use the Type messages, but I never s

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

> On Nov 9, 2021, at 1:24 PM, Michael Lewis wrote: > > Is there any advantage to not defining the default on the replica? If it is > not a static value and the publishing database will trigger row updates, I > could see waiting to set the default until after the table re-write is done, > bu

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

Is there any advantage to not defining the default on the replica? If it is not a static value and the publishing database will trigger row updates, I could see waiting to set the default until after the table re-write is done, but otherwise there doesn't seem to be any benefit to skipping column d

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

> On Nov 9, 2021, at 8:02 AM, Avi Weinberg wrote: > > • A third column is added to table with default value on publisher > side, but without default value on subscriber side > • The default value column has value for existing rows on publisher, > but null on the subscriber side.

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

"David G. Johnston" writes: > On Tue, Nov 9, 2021 at 7:50 AM Avi Weinberg wrote: >> Was this done on purpose, that default value for new column is not copied >> for existing data? Does this mean that on destination side we must also >> define the table with default value? > If you run a command

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

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

On Tue, Nov 9, 2021 at 7:50 AM Avi Weinberg wrote: > Was this done on purpose, that default value for new column is not copied > for existing data? Does this mean that on destination side we must also > define the table with default value? > Logical replication has two modes, initial synchroniz

Re: Logical Replication to Older Version

Follow up to this. Turns out we had a table without a primary key which halted the ongoing replication. Reviewing this document in detail now. https://pgdash.io/blog/postgres-replication-gotchas.html - Miles Elam

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

On Thu, Aug 5, 2021 at 12:22 AM Avi Weinberg wrote: > Is it permitted to have different primary key on source and destination > tables? > Sure, but you are still going to want a unique index on the source table that includes the site ID to act as a replica identity. Which of the two unique inde

Re: Logical replication from Rds into on-premise

Awesome, thanks! Can you please share docs my Rds version is 12.3 On Tue, 27 Jul, 2021, 19:00 Miles Elam, wrote: > On Tue, Jul 27, 2021 at 4:38 AM Dave Cramer > wrote: > >> >> Does RDS allow logical replication >> > > Yes, it does. I believe it was patched for v9.6, but v10 and above support >

Re: Logical replication from Rds into on-premise

Hi Dave, Rds support logical replication but the question of how to make replication between RDS into on premise On Tue, 27 Jul, 2021, 17:08 Dave Cramer, wrote: > > Dave Cramer > www.postgres.rocks > > > On Mon, 26 Jul 2021 at 18:43, Cory Nemelka wrote: > >> >> On Mon, Jul 26, 2021 at 12:06 PM

Re: Logical replication from Rds into on-premise

On Tue, Jul 27, 2021 at 4:38 AM Dave Cramer wrote: > > Does RDS allow logical replication > Yes, it does. I believe it was patched for v9.6, but v10 and above support it out of the box, and the RDS version of PostgreSQL shares that support. I have used it with v10 and v11, and it works exactly l

Re: Logical replication from Rds into on-premise

Dave Cramer www.postgres.rocks On Mon, 26 Jul 2021 at 18:43, Cory Nemelka wrote: > > On Mon, Jul 26, 2021 at 12:06 PM Rama Krishnan > wrote: > >> Hi all, >> >> I have a postgres server on Aws RDS no i want to replicate the data or >> logical replication into the on-premise server. I have gone

Re: Logical replication from Rds into on-premise

On Mon, Jul 26, 2021 at 12:06 PM Rama Krishnan wrote: > Hi all, > > I have a postgres server on Aws RDS no i want to replicate the data or > logical replication into the on-premise server. I have gone through DMS > provides the service buy it pricing was high. Do we have any option or > method to

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

On Mon, 5 Jul 2021 at 14:29, Avi Weinberg wrote: > Thanks for the reply, > > > > My question was, what will happen if I have one destination table which > gets data from many source tables. What is the best way to handle changes > in the structure of SOME of the source tables, while other source

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

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

On Sun, 4 Jul 2021 at 15:53, Avi Weinberg wrote: > 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.

Re: Logical Replication: SELECT pg_catalog.set_config Statement appears to be hanging

On Thu, Jun 10, 2021, 09:06 Hannes Kühtreiber wrote: > so we have to wait for psql14, or is there something else to this effect > in an earlier release, that I failed to find? > By definition every feature in a vX.0 release note is new as of that release. The sentence at the top of that section

Re: Logical Replication: SELECT pg_catalog.set_config Statement

Hello Jeremy, thanks for your input (and sorry for the delay). for our monitoring we query like this SELECT EXTRACT(epoch FROM (LOCALTIMESTAMP - pg_stat_activity.query_start))::integer AS age FROM pg_stat_activity WHERE pg_stat_activity.state = 'active' AND query NOT LIKE 'autovacuum:%' ORDER

Re: Logical Replication: SELECT pg_catalog.set_config Statement appears to be hanging

Hello Tom, thanks for the clarification. We are stumbling about this in psql (11.11) I have now searched the release logs to find out when this will be resolved. I found the following matching entry: https://www.postgresql.org/docs/14/release-14.html

Re: logical replication initial copy and WAL generation

We have wal_log_hints = off. Then I thought hint bits are never WAL logged. Or am I missing the part - they are, upon first read ? Thanks again Rado On Wed, May 19, 2021 at 3:56 PM Laurenz Albe wrote: > On Wed, 2021-05-19 at 14:16 +0300, Radoslav Nedyalkov wrote: > > Hello Forum, > > I know it

Re: logical replication initial copy and WAL generation

On Wed, 2021-05-19 at 14:16 +0300, Radoslav Nedyalkov wrote: > Hello Forum, > I know it sounds weird but we have faced it a couple of times already and > have no idea. > We're trying to establish logical replication from RDS(10.9) to RDS(12.5). > We're adding tables to the publication one by one.

Re: Logical Replication: SELECT pg_catalog.set_config Statement

> > We found out because we are monitoring long running queries, and saw it > had been running for a month before the restart yesterday. > I just queried pg_stat_activity and it seems to be running since then. > > taimusz=# SELECT pid, query_start, usename, left(query,70) > FROM pg_stat_activity >

Re: Logical Replication: SELECT pg_catalog.set_config Statement

=?UTF-8?Q?Hannes_K=c3=bchtreiber?= writes: > Hello Tom, thanks for your answer! > We found out because we are monitoring long running queries, and saw it > had been running for a month before the restart yesterday. > I just queried pg_stat_activity and it seems to be running since then. Oh, that

Re: Logical Replication: SELECT pg_catalog.set_config Statement

Hello Tom, thanks for your answer! We found out because we are monitoring long running queries, and saw it had been running for a month before the restart yesterday. I just queried pg_stat_activity and it seems to be running since then. taimusz=# SELECT pid, query_start, usename, left(query,7

Re: Logical Replication: SELECT pg_catalog.set_config Statement

=?UTF-8?Q?Hannes_K=c3=bchtreiber?= writes: > We have tried logical replication in a test-setup, and it appears to > work fine. > However, the following statement keeps running: > SELECT pg_catalog.set_config('search_path', '', false); What makes you think it "keeps running"? It looks to me lik

Re: Logical Replication, CPU load and Locking contention

Sent from my iPhone > On Mar 10, 2021, at 15:03, Ron wrote: >  On 3/10/21 11:56 AM, Martín Fernández wrote: >> >> >>> On 10 Mar 2021, at 11:25, Ron wrote: >>> On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote: On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández wrote: >

Re: Logical Replication, CPU load and Locking contention

On 3/10/21 11:56 AM, Martín Fernández wrote: On 10 Mar 2021, at 11:25, Ron > wrote: On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote: On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández > wrote: Hello, I’m troubleshooting a pro

Re: Logical Replication, CPU load and Locking contention

> On 10 Mar 2021, at 11:25, Ron wrote: > > On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote: >> >> On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández > > wrote: >> Hello, >> >> I’m troubleshooting a problem at my company with a pg 12 cluster that we run. >> >> We are us

Re: Logical Replication, CPU load and Locking contention

On 3/10/21 2:10 AM, Radoslav Nedyalkov wrote: On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández > wrote: Hello, I’m troubleshooting a problem at my company with a pg 12 cluster that we run. We are using Amazon DMS to replicate data from our database

Re: Logical Replication, CPU load and Locking contention

On Wed, Mar 10, 2021 at 3:56 AM Martín Fernández wrote: > Hello, > > I’m troubleshooting a problem at my company with a pg 12 cluster that we > run. > > We are using Amazon DMS to replicate data from our database into S3 > buckets. DMS replicates data by using logical replication slots. > > Afte

Re: Logical replication stuck and no data being copied

After further investigation, I discovered the following. On aurora ( the subscriber). select now()::timestamp(0),a.subname,b.srsubstate,count(*) from pg_subscription_rel b, pg_subscription a where b.srsubid=a.oid group by 1,2,3 order by 2,3; now | subname | srsub

Re: Logical replication gradually slowing down, then hanging.

On 16.11.2020 10:20, Lukasz Biegaj wrote: Hello, I'm encountering a repeating problem with logical replication. [...] And also a gdb backtrace: (gdb) bt #0  0x5623348d3ce0 in hash_seq_search () #1  0x56233473a396 in ReorderBufferQueueChange () #2  0x56233472fb80 in LogicalDecodingP

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 22.07.2020 um 16:42: Or is this a copy/paste issue? That.

Re: Logical replication from 11.x to 12.x and "unique key violations"

On 7/21/20 10:36 PM, Thomas Kellerer wrote: Adrian Klaver schrieb am 21.07.2020 um 17:07: No, as mentioned, those are varchar(20) columns. The values are generated by the application (no default value defined for the column) Aah I see my mistake I was going off your follow up question not the

Re: Logical replication from 11.x to 12.x and "unique key violations"

Thomas Kellerer schrieb am 13.07.2020 um 11:52: > If I create the publication with all needed tables (about 50) at > once, I get "duplicate key value violates unique constraint xxx_pkey" > errors during the initial replication (when creating the > subscription). Turns out the problem was - once ag

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 21.07.2020 um 17:07: >> No, as mentioned, those are varchar(20) columns. >> The values are generated by the application (no default value defined for >> the column) > > Aah I see my mistake I was going off your follow up question not the > original post. In that original p

Re: Logical replication from 11.x to 12.x and "unique key violations"

On 7/20/20 10:42 PM, Thomas Kellerer wrote: Adrian Klaver schrieb am 20.07.2020 um 16:45: On 7/20/20 7:22 AM, Thomas Kellerer wrote: I have a strange error when using logical replication between a 11.2 Where is "xxx_pkey" coming from, e.g. sequence? No, as mentioned, those are varchar(2

Re: Logical replication from 11.x to 12.x and "unique key violations"

Tom Lane schrieb am 21.07.2020 um 09:39: > In any case, we do offer as standard advice that you should reproduce > a problem on the latest minor release before filing a bug report. I know ;) I already told the "powers to be" and it's being addressed (I also went through the 11.x release notes, bu

Re: Logical replication from 11.x to 12.x and "unique key violations"

Thomas Kellerer writes: > Tom Lane schrieb am 20.07.2020 um 20:04: >> Yeah, duplicate keys does seem odd here. Can you provide a self >> contained example? > I'll try, but this is a production system. > Extracting the necessary anonymous data will be tricky. If this is a PG bug, it should be po

Re: Logical replication from 11.x to 12.x and "unique key violations"

John Ashmead schrieb am 20.07.2020 um 20:11: > I have had this problem with logical replication on PG 10 repeatedly. > In a clean build no problem. > > But if I am restarting replication because of some problem I’ve seen > problems with rows already present. > > My own fix, which has worked in my s

Re: Logical replication from 11.x to 12.x and "unique key violations"

Tom Lane schrieb am 20.07.2020 um 20:04: > Adrian Klaver writes: >> I'm probably missing something, but would that not result in a 'key not >> found' type of error. The OP is seeing "duplicate key value violates >> unique constraint xxx_pkey". To me that indicates a doubling up of at >> least some

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver schrieb am 20.07.2020 um 16:45: > On 7/20/20 7:22 AM, Thomas Kellerer wrote: >>> I have a strange error when using logical replication between a 11.2 >>> source database and a 12.3 target. >>> >>> If I create the publication with all needed tables (about 50) at >>> once, I get "duplic

Re: Logical replication from 11.x to 12.x and "unique key violations"

I have had this problem with logical replication on PG 10 repeatedly. In a clean build no problem. But if I am restarting replication because of some problem I’ve seen problems with rows already present. My own fix, which has worked in my shop, is to add replica triggers to check for the row b

Re: Logical replication from 11.x to 12.x and "unique key violations"

Adrian Klaver writes: > I'm probably missing something, but would that not result in a 'key not > found' type of error. The OP is seeing "duplicate key value violates > unique constraint xxx_pkey". To me that indicates a doubling up of at > least some of the data replication. [ squint... ] So

  1   2   3   >