pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLogical replication alternative
7+ messages / 5 participants
[nested] [flat]
* Logical replication alternative
@ 2020-11-04 10:55 Yambu <hyambu@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: Yambu @ 2020-11-04 10:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi
May i know what is the alternative to logical replication if i want to
replicate a few tables on postgres v10?
regards
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Logical replication alternative
@ 2020-11-04 11:00 Johannes Truschnigg <johannes@truschnigg.info>
parent: Yambu <hyambu@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Johannes Truschnigg @ 2020-11-04 11:00 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Logical replication alternative
@ 2020-11-04 11:27 Holger Jakobs <holger@jakobs.com>
parent: Yambu <hyambu@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Holger Jakobs @ 2020-11-04 11:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Logical replication alternative
@ 2020-11-04 11:34 Yambu <hyambu@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Yambu @ 2020-11-04 11:34 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Logical replication alternative
@ 2020-11-04 11:57 Holger Jakobs <holger@jakobs.com>
parent: Yambu <hyambu@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Holger Jakobs @ 2020-11-04 11:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 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
> <mailto: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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Logical replication alternative
@ 2020-11-04 11:58 Thomas Kellerer <shammat@gmx.net>
parent: Yambu <hyambu@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Thomas Kellerer @ 2020-11-04 11:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Logical replication alternative
@ 2020-11-04 15:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Yambu <hyambu@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2020-11-04 15:35 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-11-04 15:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-04 10:55 Logical replication alternative Yambu <hyambu@gmail.com>
2020-11-04 11:00 ` Johannes Truschnigg <johannes@truschnigg.info>
2020-11-04 11:27 ` Holger Jakobs <holger@jakobs.com>
2020-11-04 11:34 ` Yambu <hyambu@gmail.com>
2020-11-04 11:57 ` Holger Jakobs <holger@jakobs.com>
2020-11-04 15:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-11-04 11:58 ` Thomas Kellerer <shammat@gmx.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