Thread: Is there any documentation on how to correctly create extensions in HA(primary-standby) setup?

Hi,

I recently did a small experiment to see how one can create extensions
properly in HA(primary-standby) setup.

Here are my findings:
1) ALTER SYSTEM SET or GUC(configuration parameters) settings are not
replicated to standby.
2) CREATE EXTENSION statements are replicated to standby.
3) If the extension doesn't need to be set up in
shared_preload_libraries GUC, no need to create extension on the
standby, it just works.
4) If the extension needs to be set up in shared_preload_libraries
GUC: the correct way to install the extension on both primary and
standby is:
    a) set shared_preload_libraries GUC on primary, reload conf,
restart the primary to make the GUC effective.
    b) set shared_preload_libraries GUC on standby, restart the
standby to make the GUC effective.
    c) create extension on primary (we don't need to create extension
on standby as the create extension statements are replicated).
    d) verify that the extension functions work on both primary and standby.
5) The extensions which perform writes to the database may not work on
standby as the write transactions are not allowed on the standby.
However, the create extension on the standby works just fine but the
functions it provides may not work.

I think I was successful in my experiment, please let me know if
anything is wrong in what I did.

Do we have the documentation on how to create extensions correctly in
HA setup? If what I did is correct and we don't have it documented,
can we have it somewhere in the existing HA related documentation?

Regards,
Bharath Rupireddy.

On Wed, Dec 1, 2021 at 12:31 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Hi,
>
> I recently did a small experiment to see how one can create extensions
> properly in HA(primary-standby) setup.
>
> Here are my findings:
> 1) ALTER SYSTEM SET or GUC(configuration parameters) settings are not
> replicated to standby.
> 2) CREATE EXTENSION statements are replicated to standby.
> 3) If the extension doesn't need to be set up in
> shared_preload_libraries GUC, no need to create extension on the
> standby, it just works.
> 4) If the extension needs to be set up in shared_preload_libraries
> GUC: the correct way to install the extension on both primary and
> standby is:
>     a) set shared_preload_libraries GUC on primary, reload conf,
> restart the primary to make the GUC effective.
>     b) set shared_preload_libraries GUC on standby, restart the
> standby to make the GUC effective.
>     c) create extension on primary (we don't need to create extension
> on standby as the create extension statements are replicated).
>     d) verify that the extension functions work on both primary and standby.
> 5) The extensions which perform writes to the database may not work on
> standby as the write transactions are not allowed on the standby.
> However, the create extension on the standby works just fine but the
> functions it provides may not work.
>
> I think I was successful in my experiment, please let me know if
> anything is wrong in what I did.
>
> Do we have the documentation on how to create extensions correctly in
> HA setup? If what I did is correct and we don't have it documented,
> can we have it somewhere in the existing HA related documentation?

I'm thinking of adding the above steps into the "Additional Supplied
Modules" section documentation. Any thoughts please?

[1] - https://www.postgresql.org/docs/devel/contrib.html

Regards,
Bharath Rupireddy.

On 03.12.21 15:28, Bharath Rupireddy wrote:
> I'm thinking of adding the above steps into the "Additional Supplied
> Modules" section documentation. Any thoughts please?
> 
> [1] - https://www.postgresql.org/docs/devel/contrib.html

The chapter about extensions is probably better: 
https://www.postgresql.org/docs/devel/extend-extensions.html

On Tue, Dec 7, 2021 at 9:16 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
>
> On 03.12.21 15:28, Bharath Rupireddy wrote:
> > I'm thinking of adding the above steps into the "Additional Supplied
> > Modules" section documentation. Any thoughts please?
> >
> > [1] - https://www.postgresql.org/docs/devel/contrib.html
>
> The chapter about extensions is probably better:
> https://www.postgresql.org/docs/devel/extend-extensions.html

Thanks. Attaching v1 patch specifying the notes there. Please review.

Regards,
Bharath Rupireddy.

Hi,

On Thu, Dec 09, 2021 at 08:19:06AM +0530, Bharath Rupireddy wrote:
> 
> Thanks. Attaching v1 patch specifying the notes there. Please review.

I think that the common terminology is "module", not "extension".  That's
especially important here as this information is also relevant for modules that
may come with an SQL level extension.  This should be made clear in that new
documentation, same for the CREATE EXTENSION part that may not be relevant.

It also seems that this documentation is only aimed for physical replication.
It should also be explicitly stated as it might not be obvious for the intended
readers.


+     [...] set it either via <link linkend="sql-altersystem">ALTER SYSTEM</link>
+     command or <filename>postgresql.conf</filename> file on both primary and
+     standys, reload the <filename>postgresql.conf</filename> file and restart
+     the servers.

Isn't the reload a terrible advice?  By definition changing
shared_preload_libraries isn't compatible with a simple reload and will emit
some error.

+     [...] Create the extension on the primary, there is no need to
+     create it on the standbys as the <link linkend="sql-createextension"><command>CREATE EXTENSION</command></link>
+     command is replicated.

The "no need" here is quite ambiguous, as it seems to indicate that trying to
create the extension on the standby will work but is unnecessary.

On Tue, Jan 18, 2022 at 1:26 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> Hi,
>
> On Thu, Dec 09, 2021 at 08:19:06AM +0530, Bharath Rupireddy wrote:
> >
> > Thanks. Attaching v1 patch specifying the notes there. Please review.
>
> I think that the common terminology is "module", not "extension".  That's
> especially important here as this information is also relevant for modules that
> may come with an SQL level extension.  This should be made clear in that new
> documentation, same for the CREATE EXTENSION part that may not be relevant.

Thanks for reviewing this. The aim of the patch is to add how one can
create extensions with "CREATE EXTENSION" command in replication
setup, not sure why I should use the term "module". I hope you have
seen the usage of extension in the extend.sgml.

> It also seems that this documentation is only aimed for physical replication.
> It should also be explicitly stated as it might not be obvious for the intended
> readers.

Yeah, I've changed the title and description accordingly.

> +     [...] set it either via <link linkend="sql-altersystem">ALTER SYSTEM</link>
> +     command or <filename>postgresql.conf</filename> file on both primary and
> +     standys, reload the <filename>postgresql.conf</filename> file and restart
> +     the servers.
>
> Isn't the reload a terrible advice?  By definition changing
> shared_preload_libraries isn't compatible with a simple reload and will emit
> some error.

Yes, it will emit the following messages. I removed the reload part.

2022-02-11 04:07:53.178 UTC [1206594] LOG:  parameter
"shared_preload_libraries" cannot be changed without restarting the
server
2022-02-11 04:07:53.178 UTC [1206594] LOG:  configuration file
"/home/bharath/postgres/inst/bin/data/postgresql.auto.conf" contains
errors; unaffected changes were applied

> +     [...] Create the extension on the primary, there is no need to
> +     create it on the standbys as the <link linkend="sql-createextension"><command>CREATE
EXTENSION</command></link>
> +     command is replicated.
>
> The "no need" here is quite ambiguous, as it seems to indicate that trying to
> create the extension on the standby will work but is unnecessary.

Modified.

Attaching v2, please have a look.

Regards,
Bharath Rupireddy.

On Fri, Feb 11, 2022 at 10:16:27AM +0530, Bharath Rupireddy wrote:
> On Tue, Jan 18, 2022 at 1:26 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > I think that the common terminology is "module", not "extension".  That's
> > especially important here as this information is also relevant for modules that
> > may come with an SQL level extension.  This should be made clear in that new
> > documentation, same for the CREATE EXTENSION part that may not be relevant.
> 
> Thanks for reviewing this. The aim of the patch is to add how one can
> create extensions with "CREATE EXTENSION" command in replication
> setup, not sure why I should use the term "module". I hope you have
> seen the usage of extension in the extend.sgml.

The aim of this patch should be to clarify postgres configuration for
additional modules in physical replication, whether those includes an extension
or not.  Your patch covers implication of modifying shared_preload_libraries,
are you saying that if there's no extension associated with that library it
shouldn't be covered?

A simple example is auto_explain, which also means that the documentation
should probbaly mention that shared_preload_libraries is only an example and
all configuration changes should be reported (and eventually adapted) on the
standby, like session_preload_libraries among others.

This doesn't seem to be getting any further attention. It sounds like
Julien didn't agree with the scope of the text. Bharath do you think
Julien's comments make sense? Will you have a chance to look at this?

On Fri, Mar 25, 2022 at 10:20 AM Greg Stark <stark@mit.edu> wrote:
>
> This doesn't seem to be getting any further attention. It sounds like
> Julien didn't agree with the scope of the text. Bharath do you think
> Julien's comments make sense? Will you have a chance to look at this?

Thanks Greg. I was busy with other features.

Thanks Julien for the off-list discussion. I tried to address review
comments in the v3 patch attached. Now, I've added the notes in
high-availability.sgml which sort of suits more and closer to physical
replicatioin than contrib.sgml or extend.sgml.

Thoughts?

Regards,
Bharath Rupireddy.

Hi,

On Sun, Mar 27, 2022 at 09:07:12AM +0530, Bharath Rupireddy wrote:
> On Fri, Mar 25, 2022 at 10:20 AM Greg Stark <stark@mit.edu> wrote:
> >
> > This doesn't seem to be getting any further attention. It sounds like
> > Julien didn't agree with the scope of the text. Bharath do you think
> > Julien's comments make sense? Will you have a chance to look at this?
> 
> Thanks Greg. I was busy with other features.
> 
> Thanks Julien for the off-list discussion. I tried to address review
> comments in the v3 patch attached. Now, I've added the notes in
> high-availability.sgml which sort of suits more and closer to physical
> replicatioin than contrib.sgml or extend.sgml.

+    [...] Firstly, the module's shared library must be present on
+     both primary and standbys.

I'm a bit confused with it.  It looks like it means that the .so must be
physically present on both servers, but I'm assuming that you're talking about
shared_preload_libraries?

If yes, I still think it's worth documenting that it *needs* to be present on
the standbys *if* you want it to be enabled on the standby, including if it can
be promoted to a primary node.  And that any related GUC also has to be
properly configured on all nodes (so maybe moving the last paragraph just after
this one?).

If no, maybe just saying that the module has to be installed and configured on
all nodes?

+     [...] If the module exposes SQL functions, running
+     <link linkend="sql-createextension"><command>CREATE EXTENSION</command></link>
+     command on primary is sufficient as standbys will receive it via physical
+     replication.

I think it's better to phrase it with something like "CREATE EXTENSION is
replicated in physical replication similarly to other DDL commands".

+     [...] The
+     module's shared library gets loaded upon first usage of any of its
+     functions on primary and standbys.

Is it worth documenting that?  Note that this is only true if the lib isn't in
shared_preload_libraries and if it's a wrapper on top of a C function.

nitpicking: there's a trailing whitespace after "standbys."

+     If the module doesn't expose SQL functions, the shared library has to be
+     loaded separately on primary and standbys, either by
+     <link linkend="sql-load"><command>LOAD</command></link> command or by
+     setting parameter <xref linkend="guc-session-preload-libraries"/> or
+     <xref linkend="guc-shared-preload-libraries"/> or
+     <xref linkend="guc-local-preload-libraries"/>, depending on module's need.

I think this is also confusing.  The need for preloading is entirely orthogonal
to SQL functions in the extension, especially since this is implying SQL
function over C-code.  This should be reworded to go with the first paragraph I
think.