From: Robert Blayzor <rblayzor.bulk@inoc.net>
To: Rory Campbell-Lange <rory@campbell-lange.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: PgSQL 14 - Logical Rep - Single table multiple publications?
Date: Tue, 2 Aug 2022 12:09:58 -0400
Message-ID: <8bd0f6cd-cbfc-2033-431c-d4ac613c1878@inoc.net> (raw)
In-Reply-To: <Yuk7YwXqd2ETishS@campbell-lange.net>
References: <3d5996ac-a181-21a5-62a0-1cd34e6b3f1a@inoc.net>
<Yuk7YwXqd2ETishS@campbell-lange.net>
On 8/2/22 10:57, Rory Campbell-Lange wrote:
> Special care must be taken with the "replica identity" of published
> tables, as set out at
> https://www.postgresql.org/docs/current/logical-replication-publication.html.
> For example, you may need a unique identifying column for each source
> table in addition to the normal row identifier to differentiate*this*
> table's id 1 row from the*other* table's id 1 row, otherwise the
> subscriber won't be able to identify the row to delete if this table's
> id 1 row is deleted (for example).
>
> Although this seems to work fine with native replication, the pglogical
> extension has more knobs. For instance, the
> pglogical.wait_for_subscription_sync_complete function is useful to ensure that
> sync finishes when part of a migration.
We would literally just be merging bulk data rows that are considered
immutable, meaning they would never be updated or deleted. We would
replicate only inserts, not deletes, updates, etc.
Would the table identifier still be required in this case?
We have a half a dozen DB's that just collect call records, they are in
different locations. They get their local call data and store it into
their local table. We would want to aggregate all that data in a central
subscription database table for pure analytics/reporting purposes...
--
inoc.net!rblayzor
XMPP: rblayzor.AT.inoc.net
PGP: https://pgp.inoc.net/rblayzor/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rblayzor.bulk@inoc.net, rory@campbell-lange.net
Subject: Re: PgSQL 14 - Logical Rep - Single table multiple publications?
In-Reply-To: <8bd0f6cd-cbfc-2033-431c-d4ac613c1878@inoc.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox