Feedback-ID: i0c21471d:Fastmail
User-Agent: Cyrus-JMAP/3.9.0-alpha0-221-gec32977366-fm-20230306.001-gec329773
Mime-Version: 1.0
Message-Id: <22c275cc-a2d5-4cee-ac81-bbb6950248b8@app.fastmail.com>
In-Reply-To: <e378fb636a694c81b354d3c405f0179d@amazon.com>
References: <db02e6773adb4dbcb5b9bb3803ebe340@amazon.com>
 <20230318120638.3z7usvxwncs47tdy@alvherre.pgsql>
 <e378fb636a694c81b354d3c405f0179d@amazon.com>
Date: Mon, 20 Mar 2023 23:01:32 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Kumar, Sachin" <ssetiya@amazon.com>,
 "Alvaro Herrera" <alvherre@alvh.no-ip.org>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Initial Schema Sync for Logical Replication
Content-Type: multipart/alternative;
 boundary=c1d343a5b3e74326a6ecba9901afee51
Archived-At: <https://www.postgresql.org/message-id/22c275cc-a2d5-4cee-ac81-bbb6950248b8%40app.fastmail.com>
Precedence: bulk

--c1d343a5b3e74326a6ecba9901afee51
Content-Type: text/plain

On Mon, Mar 20, 2023, at 10:10 PM, Kumar, Sachin wrote:
> > From: Alvaro Herrera <alvherre@alvh.no-ip.org>
> > Subject: RE: [EXTERNAL]Initial Schema Sync for Logical Replication
> > On 2023-Mar-15, Kumar, Sachin wrote:
> > 
> > > 1. In  CreateSubscription()  when we create replication
> > > slot(walrcv_create_slot()), should use CRS_EXPORT_SNAPSHOT, So that we
> > can use this snapshot later in the pg_dump.
> > >
> > > 2.  Now we can call pg_dump with above snapshot from CreateSubscription.
> > 
> > Overall I'm not on board with the idea that logical replication would depend on
> > pg_dump; that seems like it could run into all sorts of trouble (what if calling
> > external binaries requires additional security setup?  what about pg_hba
> > connection requirements? what about max_connections in tight
> > circumstances?).
> > what if calling external binaries requires additional security setup
> I am not sure what kind of security restriction would apply in this case, maybe pg_dump
> binary can be changed ? 
Using pg_dump as part of this implementation is not acceptable because we
expect the backend to be decoupled from the client. Besides that, pg_dump
provides all table dependencies (such as tablespaces, privileges, security
labels, comments); not all dependencies shouldn't be replicated. You should
exclude them removing these objects from the TOC before running pg_restore or
adding a few pg_dump options to exclude these objects. Another issue is related
to different version. Let's say the publisher has a version ahead of the
subscriber version, a new table syntax can easily break your logical
replication setup. IMO pg_dump doesn't seem like a good solution for initial
synchronization.

Instead, the backend should provide infrastructure to obtain the required DDL
commands for the specific (set of) tables. This can work around the issues from
the previous paragraph:

* you can selectively choose dependencies;
* don't require additional client packages;
* don't need to worry about different versions.

This infrastructure can also be useful for other use cases such as:

* client tools that provide create commands (such as psql, pgAdmin);
* other logical replication solutions;
* other logical backup solutions.


--
Euler Taveira
EDB   https://www.enterprisedb.com/

--c1d343a5b3e74326a6ecba9901afee51
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Mon, Mar 20,=
 2023, at 10:10 PM, Kumar, Sachin wrote:<br></div><blockquote type=3D"ci=
te" id=3D"qt" style=3D""><div>&gt; From: Alvaro Herrera &lt;<a href=3D"m=
ailto:alvherre@alvh.no-ip.org">alvherre@alvh.no-ip.org</a>&gt;<br></div>=
<div>&gt; Subject: RE: [EXTERNAL]Initial Schema Sync for Logical Replica=
tion<br></div><div>&gt; On 2023-Mar-15, Kumar, Sachin wrote:<br></div><d=
iv>&gt;&nbsp;<br></div><div>&gt; &gt; 1. In&nbsp; CreateSubscription()&n=
bsp; when we create replication<br></div><div>&gt; &gt; slot(walrcv_crea=
te_slot()), should use CRS_EXPORT_SNAPSHOT, So that we<br></div><div>&gt=
; can use this snapshot later in the pg_dump.<br></div><div>&gt; &gt;<br=
></div><div>&gt; &gt; 2.&nbsp; Now we can call pg_dump with above snapsh=
ot from CreateSubscription.<br></div><div>&gt;&nbsp;<br></div><div>&gt; =
Overall I'm not on board with the idea that logical replication would de=
pend on<br></div><div>&gt; pg_dump; that seems like it could run into al=
l sorts of trouble (what if calling<br></div><div>&gt; external binaries=
 requires additional security setup?&nbsp; what about pg_hba<br></div><d=
iv>&gt; connection requirements? what about max_connections in tight<br>=
</div><div>&gt; circumstances?).<br></div><div>&gt; what if calling exte=
rnal binaries requires additional security setup<br></div><div>I am not =
sure what kind of security restriction would apply in this case, maybe p=
g_dump<br></div><div>binary can be changed ?&nbsp;<br></div></blockquote=
><div><div>Using pg_dump as part of this implementation is not acceptabl=
e because we<br></div></div><div>expect the backend to be decoupled from=
 the client. Besides that, pg_dump<br></div><div>provides all table depe=
ndencies (such as tablespaces, privileges, security<br></div><div>labels=
, comments); not all dependencies shouldn't be replicated. You should<br=
></div><div>exclude them removing these objects from the TOC before runn=
ing pg_restore or<br></div><div>adding a few pg_dump options to exclude =
these objects. Another issue is related<br></div><div>to different versi=
on. Let's say the publisher has a version ahead of the<br></div><div>sub=
scriber version, a new table syntax can easily break your logical<br></d=
iv><div>replication setup. IMO pg_dump doesn't seem like a good solution=
 for initial<br></div><div>synchronization.<br></div><div><div><br></div=
><div>Instead, the backend should provide infrastructure to obtain the r=
equired DDL<br></div><div>commands for the specific (set of) tables. Thi=
s can work around the issues from<br></div><div>the previous paragraph:<=
br></div><div><br></div><div>* you can selectively choose dependencies;<=
br></div><div>* don't require additional client packages;<br></div><div>=
* don't need to worry about different versions.<br></div><div><br></div>=
<div>This infrastructure can also be useful for other use cases such as:=
<br></div><div><br></div><div>* client tools that provide create command=
s (such as psql, pgAdmin);<br></div><div>* other logical replication sol=
utions;<br></div><div>* other logical backup solutions.<br></div></div><=
div><br></div><div><br></div><div id=3D"sig112409080"><div class=3D"sign=
ature">--<br></div><div class=3D"signature">Euler Taveira<br></div><div =
class=3D"signature">EDB&nbsp;&nbsp; <a href=3D"https://www.enterprisedb.=
com/">https://www.enterprisedb.com/</a><br></div></div><div><br></div></=
body></html>
--c1d343a5b3e74326a6ecba9901afee51--