I am trying to use pg_cron in a scenario with Stolon and have been getting the log message “Connection failed,” even though I have the correct permissions in pg_hba.conf and have enabled the extension in postgresql.conf (performed via stolonctl update --patch and restarting the keeper). PostgreSQL cron was successfully installed, and the initialization of the extension on the master was replicated to the other servers in the cluster. Some troubleshooting attempts I made include:
- Changing the listen_address in the keeper initialization from the server’s IP to *;
- Changing the nodename in cron.job to the server’s IP instead of localhost or null (however, I have concerns about how this would work with other servers, as changing the master in case of a failure would keep the query’s nodename as the previously defined IP);
- Allowing all addresses in pg_hba.conf;
- Changing password_encryption to md5 in postgresql.conf;
Below are the default configurations of pg_hba.conf and postgresql.conf when starting the stolon-keeper.
postgresql.conf
min_wal_size = '80MB'
log_filename = 'postgresql-%Y-%m-%d.log'
unix_socket_directories = '/tmp'
wal_keep_size = '128'
log_rotation_size = '10MB'
listen_addresses = 'xxx.xxx.xxx.xxx'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_min_messages = 'ERROR'
lc_messages = 'C.UTF-8'
default_text_search_config = 'pg_catalog.english'
log_statement = 'all'
lc_monetary = 'C.UTF-8'
cron.database_name = 'postgres'
log_destination = 'stderr'
synchronous_standby_names = ''
hot_standby = 'on'
max_wal_senders = '47'
log_truncate_on_rotation = 'on'
wal_level = 'replica'
log_min_error_statement = 'ERROR'
shared_preload_libraries = 'pg_cron'
log_rotation_age = '1d'
dynamic_shared_memory_type = 'posix'
timezone = 'Etc/UTC'
lc_numeric = 'C.UTF-8'
max_connections = '100'
max_wal_size = '1GB'
lc_time = 'C.UTF-8'
log_directory = '/tmp/log'
log_timezone = 'Etc/UTC'
logging_collector = 'on'
shared_buffers = '128MB'
datestyle = 'iso, mdy'
port = '5432'
max_replication_slots = '20'
primary_conninfo = 'application_name=stolon_070814d1 host=xxx.xxx.xxx.xxx password=mysupersecret port=5432 sslmode=prefer user=stolon_replica'
primary_slot_name = 'stolon_070814d1'
recovery_target_timeline = 'latest'
pg_hba.conf
local postgres stolon_keeper md5
local replication stolon_replica md5
host all stolon_keeper 0.0.0.0/0 md5
host all stolon_keeper ::0/0 md5
host replication stolon_replica 0.0.0.0/0 md5
host replication stolon_replica ::0/0 md5
host all all 0.0.0.0/0 md5
host all all ::0/0 md5
Followed by the configurations with all the aforementioned troubleshooting changes.
postgresql.conf
password_encryption = 'md5'
lc_monetary = 'C.UTF-8'
log_filename = 'postgresql-%Y-%m-%d.log'
lc_numeric = 'C.UTF-8'
log_rotation_size = '10MB'
datestyle = 'iso, mdy'
lc_messages = 'C.UTF-8'
log_min_error_statement = 'ERROR'
cron.database_name = 'postgres'
log_directory = '/tmp/log'
min_wal_size = '80MB'
logging_collector = 'on'
hot_standby = 'on'
port = '5432'
synchronous_standby_names = ''
lc_time = 'C.UTF-8'
dynamic_shared_memory_type = 'posix'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_rotation_age = '1d'
wal_keep_size = '128'
max_replication_slots = '20'
shared_preload_libraries = 'pg_cron'
log_timezone = 'Etc/UTC'
wal_level = 'replica'
log_truncate_on_rotation = 'on'
default_text_search_config = 'pg_catalog.english'
timezone = 'Etc/UTC'
listen_addresses = '*'
log_statement = 'all'
log_min_messages = 'ERROR'
unix_socket_directories = '/tmp'
max_connections = '100'
shared_buffers = '128MB'
max_wal_size = '1GB'
max_wal_senders = '47'
log_destination = 'stderr'
pg_hba.conf
local postgres stolon_keeper md5
local replication stolon_replica md5
host all stolon_keeper 0.0.0.0/0 md5
host all stolon_keeper ::0/0 md5
host replication stolon_replica 0.0.0.0/0 md5
host replication stolon_replica ::0/0 md5
host all all all md5
host all all all trust
Also included are the queries performed to enable the extension and create the select function for testing with now.
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE TABLE timestamp_logs (
id SERIAL PRIMARY KEY,
log_time TIMESTAMP NOT NULL
);
SELECT cron.schedule('select_now_job', '0 * * * * *', 'INSERT INTO timestamp_logs (log_time) VALUES (now());');
I’m using Ubuntu 20.04 and I’ve tested this on PostgreSQL 12, 14 and 16.
Thank you in advance for your attention.