<div dir="ltr"><div>Hello,</div><div><br></div><div>I'm new member in pgpool..:)</div><div><br></div><div>I had problem when setting up load balancing on pgpool (i use version 3.5.4) connected to postgresql 9.5 with stream replication ( using repmgr). I always getting error "ERROR: cannot execute nextval() in a read-only transaction" while i had configure black_function_list for sequences. Kindly advice whats going wrong..</div><div>Below is my pgpool configuration :</div><div><br></div><div># - pgpool Connection Settings -</div><div><br></div><div>listen_addresses = 'localhost'</div><div> # Host name or IP address to listen on:</div><div> # '*' for all, '' for no TCP/IP connections</div><div> # (change requires restart)</div><div>port = 15982</div><div> # Port number</div><div> # (change requires restart)</div><div>socket_dir = '/var/run/postgresql'</div><div> # Unix domain socket path</div><div> # The Debian package defaults to</div><div> # /var/run/postgresql</div><div> # (change requires restart)</div><div>listen_backlog_multiplier = 2</div><div> # Set the backlog parameter of listen(2) to</div><div> # num_init_children * listen_backlog_multiplier.</div><div> # (change requires restart)</div><div>serialize_accept = off</div><div> # whether to serialize accept() call to avoid thundering herd problem</div><div> # (change requires restart)</div><div><br></div><div># - pgpool Communication Manager Connection Settings -</div><div><br></div><div>pcp_listen_addresses = '*'</div><div> # Host name or IP address for pcp process to listen on:</div><div> # '*' for all, '' for no TCP/IP connections</div><div> # (change requires restart)</div><div>pcp_port = 9898</div><div> # Port number for pcp</div><div> # (change requires restart)</div><div>pcp_socket_dir = '/var/run/postgresql'</div><div> # Unix domain socket path for pcp</div><div> # The Debian package defaults to</div><div> # /var/run/postgresql</div><div> # (change requires restart)</div><div># - Backend Connection Settings -</div><div><br></div><div>backend_hostname0 = 'pg-prod-01a'</div><div> # Host name or IP address to connect to for backend 0</div><div>backend_port0 = 5432</div><div> # Port number for backend 0</div><div>backend_weight0 = 0</div><div> # Weight for backend 0 (only in load balancing mode)</div><div>backend_data_directory0 = '/opt/ebs/postgresql/9.5/main'</div><div> # Data directory for backend 0</div><div>backend_flag0 = 'ALLOW_TO_FAILOVER'</div><div> # Controls various backend behavior</div><div> # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER</div><div>backend_hostname1 = 'pg-prod-01b'</div><div>backend_port1 = 5432</div><div>backend_weight1 = 1</div><div>backend_data_directory1 = '/opt/ebs/postgresql/9.5/main'</div><div>backend_flag1 = 'ALLOW_TO_FAILOVER'</div><div><br></div><div># - Authentication -</div><div><br></div><div>enable_pool_hba = on</div><div> # Use pool_hba.conf for client authentication</div><div>pool_passwd = 'pool_passwd'</div><div> # File name of pool_passwd for md5 authentication.</div><div> # "" disables pool_passwd.</div><div> # (change requires restart)</div><div>authentication_timeout = 60</div><div> # Delay in seconds to complete client authentication</div><div> # 0 means no timeout.</div><div>#------------------------------------------------------------------------------</div><div># LOAD BALANCING MODE</div><div>#------------------------------------------------------------------------------</div><div><br></div><div>load_balance_mode = on</div><div> # Activate load balancing mode</div><div> # (change requires restart)</div><div>ignore_leading_white_space = on</div><div> # Ignore leading white spaces of each query</div><div>white_function_list = ''</div><div> # Comma separated list of function names</div><div> # that don't write to database</div><div> # Regexp are accepted</div><div>black_function_list = 'nextval,setval'</div><div> # Comma separated list of function names</div><div> # that write to database</div><div> # Regexp are accepted</div><div><br></div><div>database_redirect_preference_list = ''</div><div> # comma separated list of pairs of database and node id.</div><div> # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'</div><div> # valid for streaming replicaton mode only.</div><div><br></div><div>app_name_redirect_preference_list = ''</div><div> # comma separated list of pairs of app name and node id.</div><div> # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'</div><div> # valid for streaming replicaton mode only.</div><div>allow_sql_comments = off</div><div> # if on, ignore SQL comments when judging if load balance or</div><div> # query cache is possible.</div><div> # If off, SQL comments effectively prevent the judgment</div><div> # (pre 3.4 behavior).</div><div><br></div><div><br></div><div>Thanks in advance,</div><div><br></div><div>Regards</div><div><br></div><div>Agus</div><div>
</div></div>