0

I have three servers. One is running pgpool, another two in master-slave mode streaming replication. When installing pgpool, I was suggested to install the pgpool_regclass on my database servers as well. There's no problem installing it in the master node, but when I tried to do the same in the slave, I got error ERROR: cannot execute CREATE EXTENSION in a read-only transaction.

I think it's because the slave is a hot standby, and SELECT pg_is_in_recovery(); returns true. So I wonder am I supposed to install pgpool_regclass on the slave or not. It seems not, but pgpool doc says I should install it on every database pgpool is going to access.

J Freebird
  • 3,664
  • 7
  • 46
  • 81

1 Answers1

0

I found the cause. Delete the recovery.conf file in the slave database, and then run pgpool_regclass. Otherwise, the slave is in recovery mode and cannot execute write commands.

J Freebird
  • 3,664
  • 7
  • 46
  • 81
  • I am confused by your answer. Shouldn't slave alway be in recovery mode and only accept read-only commands? maybe you are looking at multil master solution not master/slave. In that case, wouldn't it better if you use pgpool's replication (forwarding statements) instead of postgres's streaming replication (shipptin wal files)? – wolf97084 Sep 20 '16 at 17:59