Thread: The current shape of PG master-slave replication

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

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

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

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