Press "Enter" to skip to content

Category: Replication

Postgres Synchronous Replication Guarantees

Published 2024-12-09 by Kevin Feasel

Kaarel Moppel has a public service announcement:

At last week’s local Postgres user group meetup here in Estonia, one of the topics was HA and recent Patroni (the most popular cluster manager for Postgres) improvements in supporting quorum commit, which by the way on its own has been possible to use for years. Things went deep quickly and we learned quite a bit of course. Including a good reminder that you shouldn’t build your bank on Patroni’s default synchronous mode 🙂

Anyways, during the hallway track (which sometimes are as valuable as the real ones) got an interesting question – with some 3+ quorum nodes, is Postgres then 100% bulletproof against all kinds failures? Excluding meteorites, rouge DBAs and such of course. One could think so, right? Nope.

Read on to learn what might cause failure in that scenario. Guaranteeing synchronous replication between machines over a network is a surprisingly difficult challenge.

Leave a Comment

Bi-Directional Replication in PostgreSQL

Published 2024-10-21 by Kevin Feasel

Semab Tariq does a bit of replication:

Bi-directional replication, often referred to as multi-master replication, allows data to be written and read from multiple database nodes. This means that changes made on one node are automatically replicated to the other node, and vice versa. This setup ensures that all nodes have the same data, providing high availability and fault tolerance.

This example covers replicating a single table, but the code itself was rather easy to follow. I imagine things get a bit more challenging when you add some of the more common types of complexity to this, including what happens when your app tries to insert the same record on both nodes.

Comments closed

Online Upgrade of Logical and Physical Replication Notes in Postgres

Published 2024-09-26 by Kevin Feasel

Amit Kapila notes an improvement in replication capabilities:

In my last blog post, Evolution of Logical Replication, I mentioned the future development of a feature to allow “upgrades of logical replication nodes.” The upcoming release of PostgreSQL 17 includes this feature. Previously, after major version upgrades (via pg_upgrade), users couldn’t immediately connect and write data to logical replication nodes. This was because the slots were lost during upgrades, preventing replication from continuing. As a result, new writes wouldn’t get replicated, causing data on both nodes to become out of sync. As explained in this blog post, users had to block applications from writing until the replication setup was re-enabled after the upgrade.

Read on to see how PostgreSQL 17 changes things.

Comments closed

Troubleshooting Transactional Replication Latency in SQL Server

Published 2024-07-17 by Kevin Feasel

Bobirmirzo Arslanov has a two-parter around troubleshooting transactional replication latency issues. Part 1 looks at architecture and some of the distribution tables:

Before you dive into solving any issue, you need to fully understand the type of environment you have as there might have been changes you are unaware of. An easy way to do that is to run script  SQLServer/Script Replication Topology at master · sqlserver-parikh/SQLServer (github.com) which gives output like below.

Part 2 digs in deeper:

If wait time is high compared to CPU time, check wait type and troubleshoot accordingly. For example, on the above example we faced MEMORY_ALLOCATION_EXT wait_type but duration is 0. So, we are not waiting.

If CPU time is higher, this means log thread is running but latency is being observed because you have high load.

Check out both posts. I’d really like to see a similar article for merge replication, as it’s a lot harder to troubleshoot.

Comments closed

Synchronous and Asynchronous Replication in Postgres

Published 2024-05-20 by Kevin Feasel

Semab Tariq takes us through a pair of replication options:

In the world of database replication, choosing between synchronous and asynchronous methods can have a big impact on how reliable, consistent, and fast your data is.

This blog dives into what these methods are, how they work, and when you might want to use one over the other. Whether you’re trying to keep your data super safe or just want it to move quickly, we’ll break down everything you need to know about synchronous and asynchronous replication in PostgreSQL.

Read on for a quick overview of streaming replication and the differences between asynchronous and synchronous options.

Comments closed

A Primer on Transactional Replication

Published 2024-04-30 by Kevin Feasel

Steve Stedman talks transactional replication:

Ensuring that your databases are synchronized across different locations with minimal delay is not just a convenience—it’s a necessity. This is where transactional replication in SQL Server shines, making it a pivotal strategy for systems that require real-time data replication with high consistency. Our latest video, “Transactional Replication in SQL Server”, dives deep into this topic, offering insights and visual walkthroughs that are invaluable for database administrators and developers.

Click through for the video and how the pieces fit together for transactional replication at a high level.

Comments closed

Transactional Replication Slow: Check Missing Indexes

Published 2024-04-12 by Kevin Feasel

Lori Brown provides a good tip:

Have you ever had transactional replication mysteriously start showing significant latency at a subscriber server?  If so, check to see if the primary keys from the publisher database are missing on the subscriber database!

Replication was showing long latency while the publisher and subscriber servers were not heavily utilized.  Microsoft generated stored procedures that are used to send INSERT, UPDATE and DELETE operations from the publisher database to the subscriber database had no indexes to help query performance to the subscriber tables.  Since there were no indexes that matched the keys of the statements pushing through data changes, tables were being fully scanned and replication statements were piling up.  In other words, the subscriber was missing indexes. 

Read on to see how you can check this and also how you can quickly script those missing indexes.

Comments closed

Setting up Replication with dbatools

Published 2024-03-13 by Kevin Feasel

Jess Pomfret continues a series on replication in dbatools:

This post is focusing on how to setup replication with dbatools. We support all three flavours – snapshot, transactional and even merge replication!

In this article I’ll be creating a transactional publication, but the steps for setup are very similar no matter which flavour you’re implementing.

I’ll walk through and demonstrate all the steps to setup replication in this article as dbatools allows us to complete them all. However, I won’t go into a lot of details on why or how replication works, or provide guidance on best practices. If you need more information on replication as a technology then I recommend visiting the Microsoft Docs.

Read on for a demonstration of how the cmdlets work for adding a publication, articles, subscriptions, and more.

Comments closed

Get- cmdlets in dbatools Replication

Published 2024-03-01 by Kevin Feasel

Jess Pomfret continues a series on replication in dbatools:

This post will show off all the Get- commands that are available within dbatools for replication. When you’re using PowerShell, and especially if you’re new to PowerShell, exploring the Get- commands for a certain module, or area is a great way to get started. As it says in the name, these commands get information about something, they aren’t going to change anything, which means they are pretty safe to run in your environment. Of course, I’m always going to say, you should still run these in your test environment first to make sure you understand what they are doing, and how they behave in your specific environment.

Read on to see some of what you can do with it.

Comments closed

Supporting Replication in dbatools

Published 2024-02-23 by Kevin Feasel

Jess Pomfret makes an announcement:

Replication, everyone loves to hate it, but it’s been our most requested feature as far as adding commands to dbatools. For ages we’ve said ‘sounds great’ and ‘we would love that’, but when we started looking into it the energy soon fizzled away, due to it’s dependency on RMO – Replication Management Objects, as opposed to SMO – SQL Server Management Objects, things weren’t as easy as we had hoped.

That said, it’s now there as of version 2.1.1. It supports snapshot, transactional, and merge replication. No peer-to-peer, but is anyone actually surprised at that?

Comments closed