You're reading from Learn PostgreSQL - Second Edition

Product typeBook

Published inOct 2023

PublisherPackt

ISBN-139781837635641

Edition2nd Edition

Concepts

Data Analysis

Authors (2):

Luca Ferrari

Enrico Pirozzi

View More author details

Logical Replication

In the previous chapter, we talked about WAL segments and physical replication in synchronous, asynchronous, and cascading modes. In this chapter, we will cover the topic of logical replication. We will look at how to perform a logical replica, how a logical replication is different from a physical replication, and when it’s better to use logical replication instead of physical. We’ll also see that logical replication can be used to make a PostgreSQL hot upgrade. This chapter is intended as an introduction to logical replication; for further information, refer to more advanced texts, such as Mastering PostgreSQL, by Hans-Jürgen Schönig.

This chapter covers the following topics:

Understanding the basic concepts of logical replication
Comparing logical replication and physical replication
Exploring a logical replication setup and new logical replication features on PostgreSQL 16

Technical requirements

For this chapter, you will find three Docker environments in the repository:

chapter18_logical_clear: contains two PostgreSQL installations ready for the configuration of a new logical replication.
chapter18_logical_ready: contains two PostgreSQL installations with a new logical replication already active.
chapter18_physical_logical: contains three PostgreSQL installations with a new logical replication made using a physical replication.

If you want to understand how to configure a new logical replication, you should use the first Docker environment; however, if you want to skip all the topics about configuration, you can use the second one. In the second Docker environment, which you can find on the publication server, you will find the forumdb database that we’ve used so far in the book; you’ll also find a logical replication of just the users table. Finally, the chapter18_physical_logical Docker environment will...

Understanding the basic concepts of logical replication

Logical replication is a method that we can use to replicate data based on the concept of identity replication. REPLICA IDENTITY is a parameter present in table management commands (such as CREATE TABLE and ALTER TABLE); this parameter is used by PostgreSQL to obtain additional information within WAL segments, to recognize which tuples have been eliminated and which tuples have been updated. The REPLICA IDENTITY parameter can take four values:

DEFAULT
USING INDEX index_name
FULL
NOTHING

The concept behind logical replication is to pass the logic of the commands executed on the primary machine to the server and not the exact copy of the blocks to be replicated, byte by byte. At the heart of logical replication, there is a reverse engineering process that, starting from the WAL segments and using a logical decoding process, is able to extrapolate the original SQL commands and pass them on to...

Comparing logical replication and physical replication

Let’s now examine how a logical replica differs from a physical replica:

One of the positive characteristics of physical replicas is their speed. However, a distinct disadvantage is that we have to replicate all the databases in the cluster. Using a physical replica, it is not possible to replicate a single database belonging to an instance of PostgreSQL, and it is not possible to replicate only some tables of a database. Logical replication is a little bit slower than physical replication, but by using logical replication, we can decide which databases we want to replicate within a cluster and/or which tables we want to replicate within a single database.
Physical replication is only possible if the two servers have the same version of PostgreSQL. With logical replication, since the logical instruction to be executed is passed to the replica server, it is also possible to perform replications between different...

Exploring a logical replication setup and new logical replication features on PostgreSQL 16

Let’s now explore how to perform logical replication. In this section, we will prepare the environment we need to be able to perform our logical replication.

Logical replication environment settings

Suppose we have two machines, which we will call pg_pub and pg_sub. We must remember to set our internal DNS, or the /etc hosts file, so that pg_pub can reach pg_sub; for example, for the pg_pub server, the primary server will have an IP of 192.168.144.3, and for the pg_sub server, the replica server will have an IP of 192.168.144.2. If you use the chapter18 container, you can execute:

chapter_18$ bash run-pg-docker.sh chapter18_logical_clear

Once you are inside the first container, you can open another bash terminal and execute:

chapter_18$ bash run-pg-docker_replica.sh chapter18_logical_clear

Now, let’s check whether there is a connection between the two...

Summary

In this chapter, we discussed logical replication. We saw that logical replication is based on a concept of reverse engineering, starting with the analysis of WAL segments to extract the logical commands that have to be passed to a replica server. We saw that logical replication is useful when we want to replicate parts of databases and when we want to make hot migrations between different versions of PostgreSQL. Logical replication makes this possible because it does not binarily replicate data but, rather, extracts the logical DML commands from WAL files, which are then replicated on the replica server.

We saw how to make a logical replica in practice and have addressed some of the issues that can occur when we work with logical replication.

In the next chapter, we’ll talk about useful tools and extensions. We will see which tools are best to make life easier for a PostgreSQL DBA.

Verify your knowledge

Is it possible to write queries on a subscription of a logical replication server?
Yes, it is. See the section Exploring logical replication setup for more details.

Is it possible to have different fields on a subscription of a logical replication server?
Yes, it is possible to have more fields than we have on the publication server.

See the Exploring logical replication setup section for more details.

Do I have to configure the pg_hba.conf file before starting logical replication?
Yes, you do. See the Exploring logical replication setup section for more details.

What do I have to do if, after a DDL statement on the publication server, the subscription server does not replicate any data?
You have to replicate the DDL statement on the subscription server. See the DDL commands section for more details.

Is it possible to make a logical replication starting...

References

Slony website: https://www.slony.info
Logical replication: https://www.postgresql.org/docs/current/logical-replication.html

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku

The rest of the chapter is locked

You have been reading a chapter from

Learn PostgreSQL - Second Edition

Published in: Oct 2023Publisher: PacktISBN-13: 9781837635641

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Authors (2)

Luca Ferrari

Luca Ferrari has been passionate about computer science since the Commodore 64 era, and today holds a master's degree (with honors) and a Ph.D. from the University of Modena and Reggio Emilia. He has written several research papers, technical articles, and book chapters. In 2011, he was named an Adjunct Professor by Nipissing University. An avid Unix user, he is a strong advocate of open source, and in his free time, he collaborates with a few projects. He met PostgreSQL back in release 7.3; he was a founder and former president of the Italian PostgreSQL Community (ITPUG). He also talks regularly at technical conferences and events and delivers professional training.
Read more about Luca Ferrari

Enrico Pirozzi

Enrico Pirozzi, EnterpriseDB certified on implementation management and tuning, with a master's in computer science, has been a PostgreSQL DBA since 2003. Based in Italy, he has been providing database advice to clients in industries such as manufacturing and web development for 10 years. He has been training others on PostgreSQL since 2008. Dedicated to open source technology since early in his career, he is a cofounder of the PostgreSQL Italian mailing list, PostgreSQL-it, and of the PostgreSQL Italian community site, PSQL
Read more about Enrico Pirozzi

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages