pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPgSQL 14 - Logical Rep - Single table multiple publications?
4+ messages / 2 participants
[nested] [flat]
* PgSQL 14 - Logical Rep - Single table multiple publications?
@ 2022-08-02 13:57 Robert Blayzor <rblayzor.bulk@inoc.net>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Blayzor @ 2022-08-02 13:57 UTC (permalink / raw)
To: pgsql-performance
Using logical replication is it possible to have a single table
subscriber connect to multiple publishers of the same table ? This
would be for INSERT's only.
Think multiple DB publishers just inserting records (audit transaction
logs)....
Is it possible to have a single subscriber table contact multiple
publishers and just insert all of the data into a single table on the
subscriber? ie: merge type replication. There are no primary/FK
constraints, etc. The records are just time based audit log type data...
--
inoc.net!rblayzor
XMPP: rblayzor.AT.inoc.net
PGP: https://pgp.inoc.net/rblayzor/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PgSQL 14 - Logical Rep - Single table multiple publications?
@ 2022-08-02 14:57 Rory Campbell-Lange <rory@campbell-lange.net>
parent: Robert Blayzor <rblayzor.bulk@inoc.net>
0 siblings, 1 reply; 4+ messages in thread
From: Rory Campbell-Lange @ 2022-08-02 14:57 UTC (permalink / raw)
To: Robert Blayzor <rblayzor.bulk@inoc.net>; +Cc: pgsql-performance
On 02/08/22, Robert Blayzor (rblayzor.bulk@inoc.net) wrote:
> Is it possible to have a single subscriber table contact multiple publishers
> and just insert all of the data into a single table on the subscriber? ie:
> merge type replication. There are no primary/FK constraints, etc. The
> records are just time based audit log type data...
Your use case meets, I think, the third "typical use case" listed at
https://www.postgresql.org/docs/current/logical-replication.html, namely
"Consolidating multiple databases into a single one (for example for
analytical purposes)."
I've just been testing aggregating all the data in one schema across 300
publisher databases into 5 subscriber schemas on two Postgresql 14 clusters on
the same machine. Each of 60 publisher tables are aggregating into a
single table on the subscriber.
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.
Rory
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PgSQL 14 - Logical Rep - Single table multiple publications?
@ 2022-08-02 16:09 Robert Blayzor <rblayzor.bulk@inoc.net>
parent: Rory Campbell-Lange <rory@campbell-lange.net>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Blayzor @ 2022-08-02 16:09 UTC (permalink / raw)
To: Rory Campbell-Lange <rory@campbell-lange.net>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PgSQL 14 - Logical Rep - Single table multiple publications?
@ 2022-08-02 19:18 Rory Campbell-Lange <rory@campbell-lange.net>
parent: Robert Blayzor <rblayzor.bulk@inoc.net>
0 siblings, 0 replies; 4+ messages in thread
From: Rory Campbell-Lange @ 2022-08-02 19:18 UTC (permalink / raw)
To: Robert Blayzor <rblayzor.bulk@inoc.net>; +Cc: pgsql-performance
On 02/08/22, Robert Blayzor (rblayzor.bulk@inoc.net) wrote:
> 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.
>
> 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?
On the page referenced above is the following:
"INSERT operations can proceed regardless of any replica identity."
So you should be good.
Rory
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-08-02 19:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-02 13:57 PgSQL 14 - Logical Rep - Single table multiple publications? Robert Blayzor <rblayzor.bulk@inoc.net>
2022-08-02 14:57 ` Rory Campbell-Lange <rory@campbell-lange.net>
2022-08-02 16:09 ` Robert Blayzor <rblayzor.bulk@inoc.net>
2022-08-02 19:18 ` Rory Campbell-Lange <rory@campbell-lange.net>
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