PostgreSQL is not replicating some changes(extensions)

Question

I'm reproducing following this doc a master-slave replication with PostgreSQL DB servers. In front of the cluster there is a pgpool instance as load balancer. So far so good.

The problem is when I query the database from the app and use specific functions from database which use some extensions like pg_trgm or pg_prewarm for giving an example. Every time the query is balanced to one of the slaves I receive an Exception telling me that the extension I'm trying to use is missing.

Could not access file $libdir/pg_trgm

When I check the extension list with \dx on every database on master I receive the full list but on slaves it just pop plpgsql.

As the slaves are read-only servers I can't create the extensions there.

Is there a way I could replicate the extention creation to slaves servers?

Thanks in advance!

Answer 1

You forgot to install the “contrib” PostgreSQL package on the standby machine. As a consequence, the extensions cannot be created.

Answer 2

The slave servers think the extension is installed (because the references to it were copied to it along with the rest of the master's catalogs), but when it goes to load the meat and potatoes of the extension, it actually isn't there.

You need to install the binary objects which make up the extensions onto the replica servers. How you do this depends on how you installed the software on those servers to start with.

When I check the extension list with \dx on every database on master I receive the full list but on slaves it just pop plpgsql.

This isn't possible based on your description. If the replica is a copy of the master created by pg_basebackup, then \dx should return the same results on both master and replica. \dx just checks the system catalog to see what it thinks is installed. If the underlying binaries are missing, it doesn't care, it reports it anyway. If you get different results, then you are not connected to the instance you think you are.