Re: Records count mismatch with logical replication

On 1/23/25 10:21, Durgamahesh Manne wrote: At subscription side getting this error repeatedly  So Do we have any solution without resync full data again from scratch * |2025-01-23 18:11:46 UTC::@:[507]:DEBUG: logical replication did not find row to be updated in replication target

Re: Records count mismatch with logical replication

On 1/23/25 09:54, Durgamahesh Manne wrote: See comments in line below. Source Publication Side: archiving=> select * from pg_stat_replication; There is missing information here. Am I right in assuming this is for slot cls_eva_msa? And that it going to same client_addr 10.80.0.168? cl

Re: Records count mismatch with logical replication

_time | 2025-01-23 17:41:11.933344+00 > latest_end_lsn| 16C7/FB48DFE0 > latest_end_time | 2025-01-23 17:41:11.924562+00 > > archiving=> select * from pg_stat_subscription where subname = > 'cle_clm_mka'; > -[ RECORD 1 ]-+--

Re: Records count mismatch with logical replication

g_send_time| 2025-01-23 17:41:17.375879+00 last_msg_receipt_time | 2025-01-23 17:41:17.378932+00 latest_end_lsn| 16C7/FB8CDF68 latest_end_time | 2025-01-23 17:41:17.375879+00 If you need to see more stats based on your info will give you Your response in this regard is valuable Are you r

Re: Records count mismatch with logical replication

On 1/22/25 18:53, Durgamahesh Manne wrote: > But records count varies with difference of more than 10 thousand Have you looked at the I/0 statistics between the Postgres instances? Seems everything looks good with pg replication slots Except the subscriber is lagging behind the publish

Re: Records count mismatch with logical replication

On Wed, 22 Jan, 2025, 03:11 Adrian Klaver, wrote: > On 1/21/25 11:40, Durgamahesh Manne wrote: > > > > > > On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, > > wrote: > > > > > > > > On 1/21/25 10:06 AM, Durgamahesh Manne wrote: > > > > > > > > Hi Adrian

Re: Records count mismatch with logical replication

On 1/21/25 11:40, Durgamahesh Manne wrote: On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, > wrote: On 1/21/25 10:06 AM, Durgamahesh Manne wrote: > > Hi Adrian Klaver > > 22,906,216 bytes/10,846 rows  works out to 2112 bytes per row.

Re: Records count mismatch with logical replication

On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, wrote: > > > On 1/21/25 10:06 AM, Durgamahesh Manne wrote: > > > > > Hi Adrian Klaver > > > > 22,906,216 bytes/10,846 rows works out to 2112 bytes per row. > > > > Is that a reasonable per row estimate? > > > > Yes sometimes would be vary > > If I am f

Re: Records count mismatch with logical replication

On 1/21/25 10:06 AM, Durgamahesh Manne wrote: Hi Adrian Klaver 22,906,216 bytes/10,846 rows  works out to 2112 bytes per row. Is that a reasonable per row estimate? Yes  sometimes would be vary If I am following the lag went from 350GB behind to 22MB. Is the issue that the lag has stal

Re: Records count mismatch with logical replication

On Tue, Jan 21, 2025 at 11:26 PM Adrian Klaver wrote: > On 1/21/25 09:38, Durgamahesh Manne wrote: > > > > > > > > > Hi Adrian Klaver > > > > Really Thanks for your quick response > > > > This happened during repack lag went to more than 350Gb then gradually > > decreased to minimal lag after run

Re: Records count mismatch with logical replication

On 1/21/25 09:38, Durgamahesh Manne wrote: Hi Adrian Klaver Really Thanks for your quick response This happened during repack lag went to more than 350Gb then gradually decreased to minimal lag after running pg_repack I don't use pg_repack so I don't know what effect it would have on t

Re: Records count mismatch with logical replication

On Tue, Jan 21, 2025 at 9:24 PM Adrian Klaver wrote: > On 1/21/25 04:08, Durgamahesh Manne wrote: > > Hi Team, > > > > I have publication and subscription servers .So seems data replication > > running with minimal lag but records count mismatch with more than 10 > > thousand records between sou

Re: Records count mismatch with logical replication

On 1/21/25 04:08, Durgamahesh Manne wrote: Hi  Team, I have publication and subscription servers .So seems data replication running with minimal lag but records count mismatch with more than 10 thousand records between source and destination tables Could you please help in resolving this iss

Records count mismatch with logical replication

Hi Team, I have publication and subscription servers .So seems data replication running with minimal lag but records count mismatch with more than 10 thousand records between source and destination tables Could you please help in resolving this issue? Regards, Durga Mahesh

Re: Need help in logical replication

criber table empty?? > > Thank you, > > On Mon, Jan 13, 2025 at 7:30 AM Divyansh Gupta JNsThMAudy < > ag1567...@gmail.com> wrote: > >> Hii PostgreSQL Community, >> >> I am setting up logical replication between two clusters in the same >> subnet g

Re: Need help in logical replication

> Hii PostgreSQL Community, > > I am setting up logical replication between two clusters in the same > subnet group. I’ve created a publication on the primary and a subscription > on the secondary, and the replication slot has been created. However, the > slot remains inactive, and chan

Re: Need help in logical replication

> On Jan 13, 2025, at 5:30 AM, Divyansh Gupta JNsThMAudy > wrote: > >  > Hii PostgreSQL Community, > > I am setting up logical replication between two clusters in the same subnet > group. I’ve created a publication on the primary and a subscription on the > sec

Need help in logical replication

Hii PostgreSQL Community, I am setting up logical replication between two clusters in the same subnet group. I’ve created a publication on the primary and a subscription on the secondary, and the replication slot has been created. However, the slot remains inactive, and changes aren’t being

Re: Query related to Logical Replication using test_decoding and unchanged-toast-datum

On Mon, Jan 6, 2025 at 7:21 AM Kiran K V wrote: > could you please tell me whether PostgreSQL will truly log these values to > WAL or not ? If not, what will be entered into WAL for the JSON column if > it remains unchanged? > > Not quite sure what you are looking for but: https://www.postgresql

Query related to Logical Replication using test_decoding and unchanged-toast-datum

Hi, I am currently using PostgreSQL version 16 and the test_decoding plugin to perform logical replication (using replication slots). I have a simple table with integer column and JSON column. When a non-JSON column is updated, the value "unchanged-toast-datum" for the JSON column i

Re: Starting logical replication at arbitrary point that's available in WAL

On 12/27/24 13:20, Zhijie Hou (Fujitsu) wrote: On Friday, December 27, 2024 7:39 PM Alexander Uvizhev wrote: Hi, I'm doing a logical replication using streaming replication protocol and I'm trying to start a stream from a certain arbitrary point that's available in WAL

RE: Starting logical replication at arbitrary point that's available in WAL

On Friday, December 27, 2024 7:39 PM Alexander Uvizhev wrote: Hi, > I'm doing a logical replication using streaming replication protocol and > I'm trying to start a stream from a certain arbitrary point that's > available in WAL. However, both CRE

Starting logical replication at arbitrary point that's available in WAL

Hi, I'm doing a logical replication using streaming replication protocol and I'm trying to start a stream from a certain arbitrary point that's available in WAL. However, both CREATE_REPLICATION_SLOT and pg_create_logical_replication_slot() create slot with nearly last L

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

Re: pg_upgrade vs. logical replication

On Mon, Dec 9, 2024 at 6:43 AM Joe Wildish wrote: Overall, your solution seems okay, but: > a fix has gone in to PG17 that sorts this problem. > > However, we can't go to 17 yet, so need a solution for 15 and 16. Honestly, this would seem like a really, really strong reason to push for v17.

pg_upgrade vs. logical replication

We maintain c.50 logical replicas. Typically the producer version is 12 or 13, and the subscriber version is 14. We intend to upgrade the subscribers to 15 using pg_upgrade. However, we ran into an unexpected problem with that approach. I couldn't find much being mentioned about it on the web,

Re: PostgreSQL logical replication

On Tue, Nov 12, 2024 at 7:47 PM Ron Johnson wrote: > The documentation tells you, down in the Notes section. > > https://www.postgresql.org/docs/16/sql-createpublication.html > Thank you.

Re: PostgreSQL logical replication

The documentation tells you, down in the Notes section. https://www.postgresql.org/docs/16/sql-createpublication.html On Tue, Nov 12, 2024 at 5:46 AM Jayadevan M wrote: > Hello all, > > I am using PostgreSQL 16.4. I tried to set up logical replication with > "postgres" us

PostgreSQL logical replication

Hello all, I am using PostgreSQL 16.4. I tried to set up logical replication with "postgres" user and all worked fine. Then I created a new user (my_replication) to be used for the purpose and couldn't figure out which privileges are necessary. Initially I got an error like &quo

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

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. > >

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

on the new schema. I guess until logical replication of DDL is available you’re out of luck. The best you can do is to have a separate table for recording and replaying schema changes. Create triggers that perform actual DDL operations based on DML in this table. Publish this table on the

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

table from publication. On Thu, Oct 17, 2024 at 5:49 AM Muhammad Usman Khan wrote: > 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

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", &qu

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

ut this runs into the same error. I remove the table from the publication and retry. Same error. Even with the table no longer in the publication, and the table on the subscriber side dropped and re-created, I'm still getting the exact same errors of "logical replication target relation "p

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

Re: Logical Replication Delay

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 slots are not copied to the third node or the primary by Patroni. Is there

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 alre

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

al_segment_size 1073741824 B >> wal_sender_timeout 60 ms >> >> *Optimizations applied:* >> >>1. Vacuum freeze is managed during off-hours; no aggressive vacuum is >>triggered during business hours. >>2. Converted a few tables to unlogged. >>

Re: Logical Replication Delay

bles. > > *Pending:* > >1. Turning off/tuning autovacuum parameters to avoid triggering during >business hours. > > *Not possible: *We are running all tables in a single publication, and it > is not possible to separate them. > > I would greatly appreciate any s

Logical Replication Delay

running all tables in a single publication, and it is not possible to separate them. 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 -- Thanks & Reg

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

Logical replication without direct link between publisher and subscriber?

I want to have a PG instance receive logical replication from a publisher. However, the subscriber should not have network access to the publisher, in any way. This is for security reasons. No VPN or any setup that allows the subscriber to send traffic to the publisher host. The publisher

Re: Monitoring logical replication

Mostly to close the loop on this, now that I have things going seemingly reliably... On Tue, 18 Jun 2024 at 14:33, Ron Johnson wrote: > On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque > wrote: > >> Hi all, >> >> Is there an "official" pairing of LSN values on the publication and >> subscription s

Re: Postgres Logical Replication - how to see what subscriber is doing with received data?

t; >>> On Sun, Sep 1, 2024 at 9:22 PM Shaheed Haque >>> wrote: >>> >>>> Since nobody more knowledgeable has replied... >>>> >>>> I'm very interested in this area and still surprised that there is no >>>> official/con

Re: Postgres Logical Replication - how to see what subscriber is doing with received data?

. >>> >>> Based partly on that thread, I ended up with a script that connects to >>> both ends of the replication, and basically loops while comparing the >>> counts in each table. >>> >>> On Fri, 30 Aug 2024, 12:38 Michael Jaskiewicz, >&g

Re: Postgres Logical Replication - how to see what subscriber is doing with received data?

mail.com >> ). >> >> Based partly on that thread, I ended up with a script that connects to >> both ends of the replication, and basically loops while comparing the >> counts in each table. >> >> On Fri, 30 Aug 2024, 12:38 Michael Jaskiewicz, >> wrote: >

Re: Postgres Logical Replication - how to see what subscriber is doing with received data?

. > > On Fri, 30 Aug 2024, 12:38 Michael Jaskiewicz, > wrote: > >> I've got two Postgres 13 databases on AWS RDS. >> >>- One is a master, the other a slave using logical replication. >>- Replication has fallen behind by about 350Gb. >>- The sla

Re: Postgres Logical Replication - how to see what subscriber is doing with received data?

, the other a slave using logical replication. >- Replication has fallen behind by about 350Gb. >- The slave was maxed out in terms of CPU for the past four days >because of some jobs that were ongoing so I'm not sure what logical >replication was able to replica

Postgres Logical Replication - how to see what subscriber is doing with received data?

I've got two Postgres 13 databases on AWS RDS. * One is a master, the other a slave using logical replication. * Replication has fallen behind by about 350Gb. * The slave was maxed out in terms of CPU for the past four days because of some jobs that were ongoing so I'm not

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 s

Re: pg 16.4, logical replication, use case = data archiving

My experience with oracle streams (upto version 12.1). there is a tag at capture, by default tag is null (if not defined). at the session level we set DBMS_STREAMS.SET_TAG ; so whatever this session does, will not replicate. Maybe this kind of "tag" is planned in the upcoming logical r

pg 16.4, logical replication, use case = data archiving

Hi all, I Play logical replication with row filter feature, my use case is data archiving. in lab env, it worked, my questions: - during alter subscription, what about on-going DML? Can it be lost? - any lock during alter subscription should I aware? scenario: # on source tutorial=# ALTER

Re: Logical replication slots on slaves/replicas?

t; On Thu, Aug 1, 2024 at 10:42 AM Piotr Andreassen Blasiak <mailto:pi...@attendium.com>> wrote: >> Hi, >> >> 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 >&

Re: Logical replication slots on slaves/replicas?

n the master as soon as it is reported committed in the wal stream. All the best, Torsten On Thu, Aug 1, 2024 at 10:42 AM Piotr Andreassen Blasiak < pi...@attendium.com> wrote: > Hi, > > I know that currently logical replication slots are available only for > primary servers. I

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

Logical replication slots on slaves/replicas?

Hi, 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, if I stream changes from the master I can not query my

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

an this folder without stopping Logical Replication ? The cause is logical replication (probably the initial synchronization of the tables), and the only way to clean that directory is to abort logical replication. PostgreSQL has to retain all WAL (transaction log) until copying the data is done and

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,

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 Po

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

Logical replication with temporary tables

Hello, I'm trying to use a blue/green deployment in Amazon RDS, which uses Postgres logical replication to keep blue and green environments in sync. Some parts of our app use temporary tables, and in some cases we CREATE INDEX on those tables and sometimes DROP and recreate them. The C

Re: Monitoring logical replication

On Tue, Jun 18, 2024 at 5:03 AM Shaheed Haque wrote: > Hi all, > > Is there an "official" pairing of LSN values on the publication and > subscription sides that should be used to track the delta between the two > systems? I ask because Google is full of different pairs being used. I > tried to id

Re: Monitoring logical replication

_error()."\n"); >> exit(1); >> } >> >> $dbq = pg_query("DELETE FROM lsn2data WHERE lsn < (". >>"SELECT lsn FROM lsn2data WHERE lsn < (". >> &q

Re: Is a VACUUM or ANALYZE necessary after logical replication?

that table already > functional? > The whole point of “logical” replication is that the inserts/updates/deletes are reapplied on the secondary against the table and the whatever triggers, indexes, or whatnot exist on that table in the secondary behave just as if you connected to the server d

Re: Is a VACUUM or ANALYZE necessary after logical replication?

Στις 16/6/24 02:13, ο/η Adrian Klaver έγραψε: On 6/15/24 15:55, Koen De Groote wrote: I've gone over all of https://www.postgresql.org/docs/current/logical-replication.html and the only mentions of the word "index" I could find

Re: Is a VACUUM or ANALYZE necessary after logical replication?

On 6/15/24 15:55, Koen De Groote wrote: I've gone over all of https://www.postgresql.org/docs/current/logical-replication.html and the only mentions of the word "index" I could find was in relation to replica identity and examp

Is a VACUUM or ANALYZE necessary after logical replication?

I've gone over all of https://www.postgresql.org/docs/current/logical-replication.html and the only mentions of the word "index" I could find was in relation to replica identity and examples of table definitions showing primary key indexes. Nothing is said about indexes. Maybe for good reason, may

Re: Questions on logical replication

be a serious concern, depending on the size of the table. > The number of initial sync workers can be controlled via max_sync_workers_per_subscription see https://www.postgresql.org/docs/current/logical-replication-config.html if you want to do one table at a time just set sync workers to 1. I

Re: Questions on logical replication

ding the gigantic tables as well. Disk IO and Network traffic are a serious concern, increased CPU usage affecting queries of the live system, as well as transaction wraparound. Initial sync can be a serious concern, depending on the size of the table. Here's a nice guide where people

Re: Questions on logical replication

On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote wrote: > > If there are any errors during the replay of WAL such as missing indexes > for Replica Identities during an Update or Delete this will cause the main > subscriber worker slot on the publisher to start backing up WAL files > > And also if

Re: Questions on logical replication

refreshing the subscriber each time. I'm not planning on using "REPLICA IDENTITY FULL" anywhere. On Sat, Jun 8, 2024 at 10:33 PM Justin wrote: > > On Sat, Jun 8, 2024 at 1:41 PM Koen De Groote wrote: > >> What I'm trying to do is upgrade a PG11 database to PG16, us

Re: Questions on logical replication

there. On Sat, Jun 8, 2024 at 7:46 PM Adrian Klaver wrote: > On 6/8/24 10:40, Koen De Groote wrote: > > What I'm trying to do is upgrade a PG11 database to PG16, using logical > > replication. > > Have you looked at pg_upgrade?: > > https://www.postgresql.org/docs/cur

Re: Questions on logical replication

On Sat, Jun 8, 2024 at 1:41 PM Koen De Groote wrote: > What I'm trying to do is upgrade a PG11 database to PG16, using logical > replication. > > The PG11 has an active and a standby, there are a handful of databases. On > particular one has a few tables just over 100GB, t

Re: Questions on logical replication

On 6/8/24 10:40, Koen De Groote wrote: What I'm trying to do is upgrade a PG11 database to PG16, using logical replication. Have you looked at pg_upgrade?: https://www.postgresql.org/docs/current/pgupgrade.html The PG11 has an active and a standby, there are a handful of databases

Re: Questions on logical replication

What I'm trying to do is upgrade a PG11 database to PG16, using logical replication. The PG11 has an active and a standby, there are a handful of databases. On particular one has a few tables just over 100GB, then a few 100 tables near 1GB. What I'd do is start a publication with no

Re: Questions on logical replication

ory, that sounds like it could slowly but surely fill up disk space. But again, I'll give them a read. I've read all of logical replication already, and I feel like I didn't get my answer there. It would be a good idea to provide an a fairly specific outline of what you are tr

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: Questions on logical replication

that sounds like it > could slowly but surely fill up disk space. > Hi Yes that is a consideration with logical replication but the possible cast out weight the benefit. The kept WAL file size will only increase if the standby is offline. Regards Kashif Zeeshan Bitnine Global > >

Re: Questions on logical replication

reading that as: "if there is a replication slot, if the standby is disconnected, WAL is kept" And if we know WAL is kept in the "pg_wal" directory, that sounds like it could slowly but surely fill up disk space. But again, I'll give them a read. I've read all of logical repl

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: Questions on logical replication

ce, after a checkpoint, WAL segments preceding the one containing the redo record are no longer needed and can be recycled or removed. (When WAL archiving is being done, the WAL segments must be archived before being recycled or removed.)" And this is the same for logical

Re: Questions on logical replication

> needed and can be recycled or removed. (When WAL archiving is being > done, the WAL segments must be archived before being recycled or removed.)" > And this is the same for logical replication and physical replication, I take it. Thus, if a leader has a standby of the same versio

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

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

Hi team, With wal_level = 'logical', backup was taken using non-exclusive backup method. Following procedure here for restore and recovery - PostgreSQL: Documentation: 16: 26.3. Continuous Archiving and Point-in-Time Recovery (PITR)

Re: Questions on logical replication

On 6/4/24 15:55, Koen De Groote wrote: I recently read the entire documentation on logical replication, but am left with a question on the buildup of WAL On this page: https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT <ht

Re: Questions on logical replication

M Koen De Groote wrote: > I recently read the entire documentation on logical replication, but am > left with a question on the buildup of WAL > > On this page: > https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT >

Questions on logical replication

I recently read the entire documentation on logical replication, but am left with a question on the buildup of WAL On this page: https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT It is written: " When dropping a subscription

Re: Unexpected data when subscribing to logical replication slot

Thank you all for your input. We have solved the problem by - 1. Configuring wal2json to include xids . 2. Updating our enrichment queries to return the xmin . 3.

Re: Unexpected data when subscribing to logical replication slot

On 5/9/24 00:32, Daniel McKenzie wrote: We've had this running in live now for years without a hiccup so we are surprised to learn that we have this massive race condition and it just so happens that the hardware is fast enough to process the transaction before the .NET application can react t

Re: Unexpected data when subscribing to logical replication slot

Sorry, to correct myself. The pg_xact bit is written with the next checkpoint. But the COMMIT record in the WAL is there. On Thu, May 9, 2024 at 5:14 PM Torsten Förtsch wrote: > I would not find this behavior surprising in particular if you have a > synchronous replica. According to the document

Re: Unexpected data when subscribing to logical replication slot

I would not find this behavior surprising in particular if you have a synchronous replica. According to the documentation of synchronous_commit: The local behavior of all non-off modes is to wait for local flush of WAL to disk. This is when the logical decoder sees the item. But that does not

Re: Unexpected data when subscribing to logical replication slot

On 5/9/24 00:32, Daniel McKenzie wrote: Asynchronous commit introduces the risk of data loss. There is a short time window between the report of transaction completion to the client and the time that the transaction is truly committed. To get anywhere with this issue you will need

Re: Unexpected data when subscribing to logical replication slot

> > Asynchronous commit introduces the risk of data loss. There is a short > time window between the report of transaction completion to the client > and the time that the transaction is truly committed. The documentation speaks about synchronous_commit changing how transactions change behaviour

Re: Unexpected data when subscribing to logical replication slot

On 5/8/24 08:24, Daniel McKenzie wrote: It's running both (in docker containers) and also quite a few more docker containers running various .NET applications. I think what you found is that the r7a.medium instance is not capable enough to do all that it is asked without introducing lag under

Re: Unexpected data when subscribing to logical replication slot

It's running both (in docker containers) and also quite a few more docker containers running various .NET applications. Daniel McKenzie Software Developer Office: +1 403.910.5927 x 251 Mobile: +44 7712 159045 Website: www.curvedental.com *Curve Dental Confidentiality Notice* This message is inte

Re: Unexpected data when subscribing to logical replication slot

We have found two things that appear to resolve the problem - * Using a more powerful EC2 instance. We can reproduce the issue with a r7a.medium instance but not with a r7a.large EC2 instance. * Changing the Postgres synchronous_commit parameter from "on" to "off". We cannot repro

  1   2   3   4   5   6   >