1

Installed pg_cron and scheduled job, though there is connection error because job is not done and cron.job_run_details table shows connection failed error message.

As doc says:

Important: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable trust authentication for connections coming from localhost in pg_hba.conf for the user running the cron job. Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection.

Created file .pgpass with needed parameters, but not sure how to "tell" to libpq that for this user, for this host and DB read this .pgpass file.

Any help appreciated.

5
  • For my case, ( taken from here: github.com/citusdata/pg_cron/issues/48 ) changing cron.job.nodename to empty string helped: UPDATE cron.job SET nodename = ''; Aug 11, 2021 at 10:28
  • Please include any supplementary information in the question itself and not (only) in comments - there's a certain amount of cognitive dissonance in having to trawl through comments to obtain all relevant information! Aug 11, 2021 at 11:04
  • @Vérace - My above comment is actually solution for my problem, it is not part of question. May be you mean that I should post that as an answer ? Aug 11, 2021 at 11:25
  • Just to be clear so - it **wasn't a file location/permissions issue as @PhilW. suggested? By all means, if you've found the answer to your problem - post it as an answer - I might even upvote it if it's good! :-) Aug 11, 2021 at 11:37
  • @Vérace - Probably there was location/permissions issue for .pgpass file, but after little bit more googling, I found different solution which worked for me. Aug 11, 2021 at 12:05

2 Answers 2

2

I probably had .pgpass with wrong permissions or wrong place (as @PhilW suggested) but I found completely different way which worked for me (btw, I just removed .pgpass file).

Solution was simply changing cron.job.nodename to empty string:

UPDATE cron.job SET nodename = '';

Source: https://github.com/citusdata/pg_cron/issues/48

1

not sure how to "tell" to libpq that for this user, for this host and DB read this .pgpass file.

You don't need to.

The .pgpass file is always read when the account that "owns" it runs any postgres process that requires a password.
That ownership is determined by the file's location (i.e. in the account's home directory) and permissions (read/write to owner only).

The file contains host, database, username, etc. and the password to be used for each combination. Postgres will extract and use the password found when all the other "bits" match what's being requested.

From The Docs, the file contains:

hostname:port:database:username:password

So, for your HOST, DB and USER, you'd have something more like:

HOST:5432:DB:USER:password
2
  • "The .pgpass file is always read when the account that "owns" it runs any postgres process that requires a password." This seems my problem, because I created file as Ubuntu admin user, but that DB process (cron job) is running by postgres user Aug 11, 2021 at 9:34
  • Read the Documentation (link in my reply) on how and where the Postgres Password File should be stored. It sounds like either you've created it in the wrong place or with the wrong permissions (both are critical for the file to be used).
    – Phill W.
    Aug 11, 2021 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.