Обсуждение: Logical replication alternative
Hi
May i know what is the alternative to logical replication if i want to replicate a few tables on postgres v10?
regards
Hi Yambu, On Wed, Nov 04, 2020 at 12:55:02PM +0200, Yambu wrote: > Hi > > May i know what is the alternative to logical replication if i want to > replicate a few tables on postgres v10? You may want to look into SkyTools[0], which provide the infrastructure to enable trigger-based replication approaches via PgQ/Londiste. [0]: https://wiki.postgresql.org/wiki/SkyTools -- with best regards: - Johannes Truschnigg ( johannes@truschnigg.info ) www: https://johannes.truschnigg.info/ phone: +43 650 2 133337 xmpp: johannes@truschnigg.info Please do not bother me with HTML-email or attachments. Thank you.
Вложения
Hi, Why do you need an alternative to logical replication? Please tell us why logical replication isn't for you. Regards, Holger Am 04.11.20 um 11:55 schrieb Yambu: > Hi > > May i know what is the alternative to logical replication if i want to > replicate a few tables on postgres v10? > > regards -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Hi Holger
1. We want to update the few tables once a day
2. We want to avoid restart the mission critical database, changing wal_level requires a restart
regards
On Wed, Nov 4, 2020 at 1:28 PM Holger Jakobs <holger@jakobs.com> wrote:
Hi,
Why do you need an alternative to logical replication? Please tell us
why logical replication isn't for you.
Regards,
Holger
Am 04.11.20 um 11:55 schrieb Yambu:
> Hi
>
> May i know what is the alternative to logical replication if i want to
> replicate a few tables on postgres v10?
>
> regards
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Okay,
updating some tables once a day isn't actually replication.
For this to happen, I would suggest you connect the two machines by using the Foreign Data Wrapper and then:
- set up a foreign table
- create a materialized view on the "slave" as "select * from foreign_table"
- set up a job for refreshing the materialized view
Regards,
Holger
Am 04.11.20 um 12:34 schrieb Yambu:
Hi Holger1. We want to update the few tables once a day2. We want to avoid restart the mission critical database, changing wal_level requires a restartregardsOn Wed, Nov 4, 2020 at 1:28 PM Holger Jakobs <holger@jakobs.com> wrote:Hi,
Why do you need an alternative to logical replication? Please tell us
why logical replication isn't for you.
Regards,
Holger
Am 04.11.20 um 11:55 schrieb Yambu:
> Hi
>
> May i know what is the alternative to logical replication if i want to
> replicate a few tables on postgres v10?
>
> regards
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Yambu schrieb am 04.11.2020 um 11:55: > May i know what is the alternative to logical replication if i want > to replicate a few tables on postgres v10? What about using foreign tables? Then no replication would be required. If that is to slow, you could create a materialized view on the "secondary" server that is based on the foreign tables. You can refresh the MVIEW multiple times a day. The downside is that this isn't incremental, you would always copy all the data. Thomas
On Wed, 2020-11-04 at 13:34 +0200, Yambu wrote: > > Why do you need an alternative to logical replication? Please tell us > > why logical replication isn't for you. > > 1. We want to update the few tables once a day > 2. We want to avoid restart the mission critical database, changing wal_level requires a restart I think that you should plan the restart - it is only a short downtime. All the other solutions (Skytools, Slony, ...) are way more invasive: they will create triggers on your precious database, which can slow down data modifications considerably. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com