pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedThe current shape of PG master-slave replication
5+ messages / 3 participants
[nested] [flat]
* The current shape of PG master-slave replication
@ 2018-11-15 18:18 pgsql-admin@kolttonen.fi
0 siblings, 2 replies; 5+ messages in thread
From: pgsql-admin@kolttonen.fi @ 2018-11-15 18:18 UTC (permalink / raw)
To: pgsql-admin
Hello!
I am a happy PostgreSQL user, but I have never tried PG replication at all.
Based on my vague recollections concerning the past, MySQL used to have
the advantage of having a pretty stable and working master-slave
replication when PG did not have such a thing. But PG has been more
SQL-compliant, has had foreign keys forever, and in general, I guess PG
has had a more advanced feature set available.
I have followed the progress of PostgreSQL regarding replication just
occasionally, but not very actively. Master-slave replication is now
officially supported in the newest releases, maybe has been for a long
time, I am not sure.
In any case it is not my intention to spark a heated discussion about the
merits of MySQL/MariaDB vs PostgreSQL. All I am asking is maybe hearing
some observations of PG admins out there.
To be concrete, if I use PG 10.x master-slave replication in production,
is it very reliable when set up properly? Any practical experiences? Well,
I suppose it *must* be reliable, but I am interested in hearing of
possible problems, too.
Thanks for the information.
Best regards,
Unto Sten
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The current shape of PG master-slave replication
@ 2018-11-15 18:41 Payal Singh <payal@omniti.com>
parent: pgsql-admin@kolttonen.fi
1 sibling, 1 reply; 5+ messages in thread
From: Payal Singh @ 2018-11-15 18:41 UTC (permalink / raw)
To: pgsql-admin@kolttonen.fi; +Cc: pgsql-admin
>
> To be concrete, if I use PG 10.x master-slave replication in production,
is it very reliable when set up properly?
It is more reliable than default MySQL replication. Default MySQL
replication is logical whereas default postgres replication is binary. In
the crudest of terms, if something goes wrong you can't just 'skip'
statements in postgres to get replication to work like you can in MySQL.
This ensures better data integrity during replication. As for non-defaults,
pg10 and onwards support logical replication as well, with the usual
caveats that logical replication comes with in any database.
>
> Payal Singh,
Database Administrator,
OmniTI <https://omniti.com>Computer Consulting Inc.
Phone: 240.646.0770 x 253
AWS Certified Solutions Architect
<https://www.certmetrics.com/amazon/public/badge.aspx?i=1&t=c&d=2017-10-30&ci=AWS00354946;
On Thu, Nov 15, 2018 at 1:18 PM <pgsql-admin@kolttonen.fi> wrote:
>
> Hello!
>
> I am a happy PostgreSQL user, but I have never tried PG replication at
> all.
>
> Based on my vague recollections concerning the past, MySQL used to have
> the advantage of having a pretty stable and working master-slave
> replication when PG did not have such a thing. But PG has been more
> SQL-compliant, has had foreign keys forever, and in general, I guess PG
> has had a more advanced feature set available.
>
> I have followed the progress of PostgreSQL regarding replication just
> occasionally, but not very actively. Master-slave replication is now
> officially supported in the newest releases, maybe has been for a long
> time, I am not sure.
>
> In any case it is not my intention to spark a heated discussion about the
> merits of MySQL/MariaDB vs PostgreSQL. All I am asking is maybe hearing
> some observations of PG admins out there.
>
> To be concrete, if I use PG 10.x master-slave replication in production,
> is it very reliable when set up properly? Any practical experiences? Well,
> I suppose it *must* be reliable, but I am interested in hearing of
> possible problems, too.
>
> Thanks for the information.
>
> Best regards,
> Unto Sten
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The current shape of PG master-slave replication
@ 2018-11-15 20:36 Laurenz Albe <laurenz.albe@cybertec.at>
parent: pgsql-admin@kolttonen.fi
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2018-11-15 20:36 UTC (permalink / raw)
To: pgsql-admin@kolttonen.fi; pgsql-admin
pgsql-admin@kolttonen.fi wrote:
> To be concrete, if I use PG 10.x master-slave replication in production,
> is it very reliable when set up properly?
Absolutely.
PostgreSQL has had streaming replication since version 9.0, and by now
it is rock solid technology. It operates on the same principles as the
crash- and point-in-time-recovery that you already trust.
On top of that, it is amazingly simple to configure, especially since
v10, since now all parameter defaults are already set up for replication.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The current shape of PG master-slave replication
@ 2018-11-16 05:01 pgsql-admin@kolttonen.fi
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: pgsql-admin@kolttonen.fi @ 2018-11-16 05:01 UTC (permalink / raw)
To: pgsql-admin
On Thu, 15 Nov 2018, Laurenz Albe wrote:
> PostgreSQL has had streaming replication since version 9.0, and by now
> it is rock solid technology. It operates on the same principles as the
> crash- and point-in-time-recovery that you already trust.
Okay, that is great news. Your help is very much appreciated.
This gives me extra confidence that I have absolutely no need to use
MariaDB anywhere. In my workplace there are probably a few more admins who
go for MariaDB, but PG is certainly closing in even though this is not
a "competition" at all.
And actually the *specialized DB admins* who focus mostly on DB stuff
only, seem to favour PG over MariaDB. I mean in my workplace, I do not
make any claims about this being so in general.
Their Oracle background could explain part of the PG preference, since
they are pretty similar on surface, but I am pretty sure those DB guys
have evaluated MariaDB too, and yet they choose PG over it.
PG documentation is also just fantastic. I cannot believe how complete it
is, and well-organized, too. The scope is broad, it includes a brief
tutorial sections for beginners, so it makes PG accessible to many people
who do not even know SQL yet, and in addition to that the documentation
contains concise information about the advanced topics as well.
With lots of software projects, the information is scattered all over the
world, and you have to use search engines to find out about things. With
PG, I know if I am lagging behind the new releases and their features, I
can always go to to PG website and I will find *all the relevant
information* easily from there.
I have spent some time learning PG during all these years, and I have
always some preferred the PG way to do things, but I bet that MariaDB is
also great for those who like it. I can live with it if I have to, I know
the basics. Lots of folks do. It is good to have competition and working,
stable, efficient relational DB alternatives available.
By the way, speaking of raw, low-level DB technology, I only learned about
LMDB yesterday:
https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Database
It could indeed be a good replacement for BerkeleyDB in many cases. I read
that of MTAs, Postfix has already deprecated BDB in favour of LMDB, and I
suppose OpenLDAP is going the same route. If I remember right, LMDB even
originates from OpenLDAP project's needs.
Modern 64-bit CPUs now enable larger address space and the mmap() model of
LMDB seems to work fine with it, enabling direct pointers to OS
virtual memory. According to Wikipedia and common sense, it makes
things simpler and avoids data copying. And unlike using BDB, needs for
library level caching in userspace are replaced by OS doing the caching?
That's how I understood it. I have used BerkeleyBD since the early 2000s
with Sendmail, but I have little knowledge of its internal working. I
studied the C API years ago, maybe wrote some simple test programs, but
reading the actual BerkeleyDB source code I have feared too much.
> On top of that, it is amazingly simple to configure, especially since
> v10, since now all parameter defaults are already set up for replication.
I have well over twenty years of Unix/Linux experience and I have worked
with many kinds of server software, mostly on Linux. We used to have SPARC
Solaris Unix machines, Tru64 and whatever, but those days are long gone.
It's been the world of AMD64 and Red Hat Enterprise Linux for many, many
years for us.
In any case, I am perhaps deviating too much here.
So it is a great bonus if the PG master-slave replication configuration is
indeed simple and has sane default values! That's good design. It is best
to leave the details to DB experts who know their systems inside and
out.
Unneeded complexity is, well, *unneeded*.
Best regards,
Unto Sten
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The current shape of PG master-slave replication
@ 2018-11-16 05:03 pgsql-admin@kolttonen.fi
parent: Payal Singh <payal@omniti.com>
0 siblings, 0 replies; 5+ messages in thread
From: pgsql-admin@kolttonen.fi @ 2018-11-16 05:03 UTC (permalink / raw)
To: pgsql-admin
On Thu, 15 Nov 2018, Payal Singh wrote:
> It is more reliable than default MySQL replication. Default MySQL
> replication is logical whereas default postgres replication is binary. In
> the crudest of terms, if something goes wrong you can't just 'skip'
> statements in postgres to get replication to work like you can in MySQL.
> This ensures better data integrity during replication. As for non-defaults,
> pg10 and onwards support logical replication as well, with the usual
> caveats that logical replication comes with in any database.
I see. Thanks for that explanation.
I really have to study these DB things a bit more in order to gain better
understanding of replication's basic models and structure.
Best regards,
Unto Sten
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-11-16 05:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-15 18:18 The current shape of PG master-slave replication pgsql-admin@kolttonen.fi
2018-11-15 18:41 ` Payal Singh <payal@omniti.com>
2018-11-16 05:03 ` pgsql-admin@kolttonen.fi
2018-11-15 20:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-16 05:01 ` pgsql-admin@kolttonen.fi
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