Scheduled job fails to run.

[GHNewbiee]

Hi,

I have the following DB cluster in PG11:

• postgres, and
• test

I have installed pg_cron in postgres as per instructions.

Config files are as follow:

postgresql.conf

listen_addresses = 'localhost`
port = 5433
password_encryption = md5
shared_preload_libraries = 'pg_cron'

pg_hba.conf

local   all             postgres                                md5
local   all             all                                     md5
host    all             all             localhost               md5

.pgpass @ home localhost:5433:*:postgres:the_password

I create a new job which should run in test as postgres:

INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
VALUES ('*/5 * * * *', '$$SELECT schema_name.function_name(...)$$', 'localhost', 5433, 'test', 'postgres');

but, unfortunately it does not run!

Please, advise.

Tia

[GHNewbiee]

In the log file, I always get connection failed.

Tia

[arquillos]

Hi GHNewbiee! I tried a simple test job in a PostgreSQL v10 DB and it worked:

postgres=# select * from cron.job;
 jobid | schedule  |           command           | nodename  | nodeport | database | username | active 
-------+-----------+-----------------------------+-----------+----------+----------+----------+--------
     6 | * * * * * | VACUUM (ANALYZE) tasks | localhost |     1025 | pgtest   | testusr  | t
(1 row)

In the logs I've got the following lines:

...
	2018-12-18T13:50:00.000+0000 INFO - 0 - ManageCronTask {"@message":"cron job 6 starting: VACUUM (ANALYZE) tasks","@data":{}}
	2018-12-18T13:50:00.002+0000 INFO - 0 - BackendInitialize {"@message":"connection received: host=127.0.0.1 port=35754","@data":{}}
...

So your problem may be the DB autentication config.

The postgresql.conf parameters are:

listen_addresses = '*'
port = 1025
#password_encryption = md5		# md5 or scram-sha-256
shared_preload_libraries = 'logs_hook, pg_cron'

And pg_hba.conf:

...
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
...

[GHNewbiee]

¡Hola Juan!

Muchas gracias for your good intention to help me! I know that the way you mention (removing authentication - is it prudent??!!) works well;

BUT BUT BUT

in README.md the following is mentioned:

Important: ... Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection.

So, what I would like to know from the creator(s) of the extension is, whether or not the alternative way does really work, too and how.

[swuecho]

@GHNewbiee check https://github.com/citusdata/pg_cron/issues/48#issuecomment-390864505 this is an alternate way, not have to use trust, but still .pgpass way not work.

[hazardland]

PostgreSQL 12.4 Ubuntu (Windows WSL) /etc/postgresql/12/main/pg_hba.conf

Only changing this to:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

This

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

Worked for me

[ssyrota]

PostgreSQL 12.4 Ubuntu (Windows WSL) /etc/postgresql/12/main/pg_hba.conf

Only changing this to:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

This

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

Worked for me

For me too! Thanks!

[Lemmynjash]

Worked for me!! Asanti Sana