PostgreSQL: How are functions and stored procedures replicated?

07.2019 / Category: / Tags:

When looking at our PostgreSQL support activities, we notice that people often ask about functions, stored procedures and replication. Are functions replicated? How are functions replicated? What happens to shared libraries and do we have to install an extension everywhere? These topics seems to be really interesting to many people and so I decided to write a small post about this issue to hopefully shed some light on it.

How PostgreSQL “stores” functions and procedures

To understand how PostgreSQL “replicates” functions, it is first important to understand how functions and procedures are actually stored. To demonstrate this, I have created a small function that just returns a number and quits:

PostgreSQL stores the code of a function in a system table. In case the function is written in an “interpreted” language, the code is just stored there in plain text. The following listing shows, how this works:

What we see here is that “demo” has been written in language number 14 (which happens to be SQL), it is a “function” and not a procedure. Then there is the code of the function. Note that “probin” is empty – I will get back to that one a bit later. Now: What does that imply? If you use streaming replication (perhaps together with Patroni or some other tools) the system catalog will be replicated just like any other table. The function will therefore be sent to the remote side just like any other change. So, the function will be fully replicated and there is nothing else to do.

Dealing with compiled stored procedure languages

What happens if you want to use compiled functions? - Maybe a stored procedure written in C, PostGIS or any other extension that was not written in SQL or PL/pgSQL.
Here is what happens:

To show how things work, I have enabled the pg_trgm extension that is shipped as part of PostgreSQL. What we see is that C code is not stored in prosrc. Instead, it contains the name of the function in the shared object listed in probin. If we add the extension and thus the function, the changes made to the system table are still replicated to your standbys, just like any other change.
But: YOU have to ensure that the shared object is actually installed on the other side. It is not enough to install, say, PostGIS binaries on the primary and magically hope that shared objects will be replicated too. That won't happen and it never will.

It is important to mention: If you create a function on the primary written in C and your standby does not have the desired library, the changes in the system tables will be present – there is just no way to call these functions on the standby.

Conclusion - How are stored procedures and functions replicated?

Make sure that libraries are installed on all hosts in your cluster. Otherwise, things will break. PostgreSQL will replicate changes made to the system tables, but does not magically send libraries over the wire.

UPDATE 28.02.2023:

Related blog post available - Stored Procedures in PostgreSQL - Getting Started by Hans-Jürgen Schönig

If you want to learn about the addition of multiranges  as a range type in PostgreSQL 14, check out our post Multiranges in PostgreSQL 14.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes
Article Rating
Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments
Luca Ferrari
4 years ago

Interesting. Does this also mean that the slave cannot have its own library configuration, since it is replicated from the master? I mean, what if the library is installed in $libdir/slave/pg_trgm?
Do you believe that, having binary compatibilies and a good network, sharing $libdir could help mitigate the problem?

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram