Postgresql cannot execute CREATE EXTENSION in a read-only transaction

PostgreSQL
1

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.

2

Hey @Naresh9999,

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

Thanks,
Ninad

3

Hi @Ninad_Shah

Thanks for the reply.
Here are the error details.

CREATE EXTENSION pg_stat_monitor;
ERROR: cannot execute CREATE EXTENSION in a read-only transaction

4

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

postgres=# show default_transaction_read_only;
 default_transaction_read_only
-------------------------------
 off
(1 row)
postgres=# set default_transaction_read_only=on;
SET
postgres=# create extension dblink;
ERROR:  cannot execute CREATE EXTENSION in a read-only transaction
postgres=# set default_transaction_read_only=off;
SET
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 :slightly_smiling_face:

5

Hi @Robert_Bernier

Thanks for the reply.

But I need this on Replica server as well. Is there any workaround to enable it on the Replica server?

6

pg_basebackup
-d ‘user=postgres port=5437’
-D /var/lib/postgresql/16/pg2
–checkpoint=fast
-RvP

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’

1 Like
7

@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.

8

Hi,
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.

9

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.

10

I’m glad I could help you out with your question :grinning:

1 Like