codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>Chapter 31. Logical Replication</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="wal-internals.html" title="30.6. WAL Internals" /><link rel="next" href="logical-replication-publication.html" title="31.1. Publication" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">Chapter 31. Logical Replication</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="wal-internals.html" title="30.6. WAL Internals">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="admin.html" title="Part III. Server Administration">Up</a></td><th width="60%" align="center">Part III. Server Administration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logical-replication-publication.html" title="31.1. Publication">Next</a></td></tr></table><hr /></div><div class="chapter" id="LOGICAL-REPLICATION"><div class="titlepage"><div><div><h2 class="title">Chapter 31. Logical Replication</h2></div></div></div><div class="toc"><p><strong>Table of Contents</strong></p><dl class="toc"><dt><span class="sect1"><a href="logical-replication-publication.html">31.1. Publication</a></span></dt><dt><span class="sect1"><a href="logical-replication-subscription.html">31.2. Subscription</a></span></dt><dd><dl><dt><span class="sect2"><a href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT">31.2.1. Replication Slot Management</a></span></dt><dt><span class="sect2"><a href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES">31.2.2. Examples: Set Up Logical Replication</a></span></dt><dt><span class="sect2"><a href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT">31.2.3. Examples: Deferred Replication Slot Creation</a></span></dt></dl></dd><dt><span class="sect1"><a href="logical-replication-row-filter.html">31.3. Row Filters</a></span></dt><dd><dl><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-RULES">31.3.1. Row Filter Rules</a></span></dt><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-RESTRICTIONS">31.3.2. Expression Restrictions</a></span></dt><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-TRANSFORMATIONS">31.3.3. UPDATE Transformations</a></span></dt><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-PARTITIONED-TABLE">31.3.4. Partitioned Tables</a></span></dt><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-INITIAL-DATA-SYNC">31.3.5. Initial Data Synchronization</a></span></dt><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-COMBINING">31.3.6. Combining Multiple Row Filters</a></span></dt><dt><span class="sect2"><a href="logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-EXAMPLES">31.3.7. Examples</a></span></dt></dl></dd><dt><span class="sect1"><a href="logical-replication-col-lists.html">31.4. Column Lists</a></span></dt><dd><dl><dt><span class="sect2"><a href="logical-replication-col-lists.html#LOGICAL-REPLICATION-COL-LIST-EXAMPLES">31.4.1. Examples</a></span></dt></dl></dd><dt><span class="sect1"><a href="logical-replication-conflicts.html">31.5. Conflicts</a></span></dt><dt><span class="sect1"><a href="logical-replication-restrictions.html">31.6. Restrictions</a></span></dt><dt><span class="sect1"><a href="logical-replication-architecture.html">31.7. Architecture</a></span></dt><dd><dl><dt><span class="sect2"><a href="logical-replication-architecture.html#LOGICAL-REPLICATION-SNAPSHOT">31.7.1. Initial Snapshot</a></span></dt></dl></dd><dt><span class="sect1"><a href="logical-replication-monitoring.html">31.8. Monitoring</a></span></dt><dt><span class="sect1"><a href="logical-replication-security.html">31.9. Security</a></span></dt><dt><span class="sect1"><a href="logical-replication-config.html">31.10. Configuration Settings</a></span></dt><dd><dl><dt><span class="sect2"><a href="logical-replication-config.html#LOGICAL-REPLICATION-CONFIG-PUBLISHER">31.10.1. Publishers</a></span></dt><dt><span class="sect2"><a href="logical-replication-config.html#LOGICAL-REPLICATION-CONFIG-SUBSCRIBER">31.10.2. Subscribers</a></span></dt></dl></dd><dt><span class="sect1"><a href="logical-replication-quick-setup.html">31.11. Quick Setup</a></span></dt></dl></div><p>3  Logical replication is a method of replicating data objects and their4  changes, based upon their replication identity (usually a primary key).  We5  use the term logical in contrast to physical replication, which uses exact6  block addresses and byte-by-byte replication.  PostgreSQL supports both7  mechanisms concurrently, see <a class="xref" href="high-availability.html" title="Chapter 27. High Availability, Load Balancing, and Replication">Chapter 27</a>.  Logical8  replication allows fine-grained control over both data replication and9  security.10 </p><p>11  Logical replication uses a <em class="firstterm">publish</em>12  and <em class="firstterm">subscribe</em> model with one or13  more <em class="firstterm">subscribers</em> subscribing to one or more14  <em class="firstterm">publications</em> on a <em class="firstterm">publisher</em>15  node.  Subscribers pull data from the publications they subscribe to and may16  subsequently re-publish data to allow cascading replication or more complex17  configurations.18 </p><p>19  Logical replication of a table typically starts with taking a snapshot20  of the data on the publisher database and copying that to the subscriber.21  Once that is done, the changes on the publisher are sent to the subscriber22  as they occur in real-time.  The subscriber applies the data in the same23  order as the publisher so that transactional consistency is guaranteed for24  publications within a single subscription.  This method of data replication25  is sometimes referred to as transactional replication.26 </p><p>27  The typical use-cases for logical replication are:28 29  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>30     Sending incremental changes in a single database or a subset of a31     database to subscribers as they occur.32    </p></li><li class="listitem"><p>33     Firing triggers for individual changes as they arrive on the34     subscriber.35    </p></li><li class="listitem"><p>36     Consolidating multiple databases into a single one (for example for37     analytical purposes).38    </p></li><li class="listitem"><p>39     Replicating between different major versions of PostgreSQL.40    </p></li><li class="listitem"><p>41     Replicating between PostgreSQL instances on different platforms (for42     example Linux to Windows)43    </p></li><li class="listitem"><p>44     Giving access to replicated data to different groups of users.45    </p></li><li class="listitem"><p>46     Sharing a subset of the database between multiple databases.47    </p></li></ul></div><p>48 </p><p>49  The subscriber database behaves in the same way as any other PostgreSQL50  instance and can be used as a publisher for other databases by defining its51  own publications.  When the subscriber is treated as read-only by52  application, there will be no conflicts from a single subscription.  On the53  other hand, if there are other writes done either by an application or by other54  subscribers to the same set of tables, conflicts can arise.55 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="wal-internals.html" title="30.6. WAL Internals">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="admin.html" title="Part III. Server Administration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logical-replication-publication.html" title="31.1. Publication">Next</a></td></tr><tr><td width="40%" align="left" valign="top">30.6. WAL Internals </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 31.1. Publication</td></tr></table></div></body></html>