Hi Team,

We are getting the below issue, while altering the below statement’s on the replica server.

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements, pg_stat_monitor';

CREATE EXTENSION pg_stat_statements SCHEMA public;

CREATE EXTENSION pg_stat_monitor;

Can someone please help to fix the issue.

Hey @Naresh9999 ,

It seems that the error is omitted in the text. Would you please share the error you see?

Thanks,
Ninad

FYI: a little example on a regular read-write server:

postgres=# show default_transaction_read_only;
 default_transaction_read_only
-------------------------------
(1 row)
postgres=# set default_transaction_read_only=on;
postgres=# create extension dblink;
ERROR:  cannot execute CREATE EXTENSION in a read-only transaction
postgres=# set default_transaction_read_only=off;
postgres=# create extension dblink;
CREATE EXTENSION
Basically, you need to make the change on a read-write server i.e. NOT the replica but the primary.
Hope this helps 
–checkpoint=fast 

Ver Cluster Port Status          Owner    Data directory             Log file

16  pg1     5437 online          postgres /var/lib/postgresql/16/pg1 /var/log/postgresql/postgresql-16-pg1.log

16  pg2     5438 online,recovery postgres /var/lib/postgresql/16/pg2 /var/log/postgresql/postgresql-16-pg2.log
as postgres

psql -p 5437 -c “alter system set shared_preload_libraries=‘pg_stat_statements’”

psql -p 5438 -c “alter system set shared_preload_libraries=‘pg_stat_statements’”
as root

systemctl restart postgresql@16-pg1

systemctl restart postgresql@16-pg2
as postgres

createdb -p 5437 db01

psql -p 5437 -c ‘create extension pg_stat_statements’

psql -p 5438 -c ‘select * from pg_extension’
              
@Robert_Bernier
Thanks for the detailed steps.
Can I implement the above steps on the existing Replica server?
Primary server (test121): We have already completed the alter command.

Secondary server (test122): Unable to run the alter statement due to the issue.
I don’t see a problem adapting the example. You can accomplish your requirements but it sounds like you need to understand your steps.

I suggest you test out your steps on a development environment first.
              
Hi @Robert_Bernier
Now, I realize that we don’t need to create the extension on the secondary server, as we already created the extension on the primary server. So it will replicate to the secondary server from the primary server.
Thanks Robert for all the help.