PG_Cron - Error Message Connection failed
Hello everyone,
Can someone help me to understand this issue. I installed and configured
pg_cron on the server and I was able to connect without providing a
password using psql command line.
But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30
seconds', $$SELECT 1$$);
the result show
[image: image.png]
Any help is welcome
--
Jaurès FOUTE
Attachments:
image.pngimage/png; name=image.pngDownload
Try checking the view cron.job, column "nodename", if it's set to
"localhost", change it (via an UPDATE) to the address defined in
"listen_address" parameter and see if this works.
Em sex., 12 de mai. de 2023 às 17:00, FOUTE K. Jaurès <jauresfoute@gmail.com>
escreveu:
Hello everyone,
Can someone help me to understand this issue. I installed and configured
pg_cron on the server and I was able to connect without providing a
password using psql command line.
But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30
seconds', $$SELECT 1$$);
the result show
[image: image.png]Any help is welcome
--
Jaurès FOUTE
--
*Fabrício Pedroso Jorge.*
Database Architect / Database Engineer
*LinkedIn Profile*
http://br.linkedin.com/in/fabriciojorge
*Contacts:*
+ 353 085 8221706
fpjbito@gmail.com
Attachments:
image.pngimage/png; name=image.pngDownload
hello Fabricio,
the listen_addresses is set to *
the result of psql command line
[image: image.png]
The job on pg_cron
[image: image.png]
Le ven. 12 mai 2023 à 17:05, Fabricio Pedroso Jorge <fpjbito@gmail.com> a
écrit :
Try checking the view cron.job, column "nodename", if it's set to "localhost", change it (via an UPDATE) to the address defined in "listen_address" parameter and see if this works.
Em sex., 12 de mai. de 2023 às 17:00, FOUTE K. Jaurès <
jauresfoute@gmail.com> escreveu:Hello everyone,
Can someone help me to understand this issue. I installed and configured
pg_cron on the server and I was able to connect without providing a
password using psql command line.
But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30
seconds', $$SELECT 1$$);
the result show
[image: image.png]Any help is welcome
--
Jaurès FOUTE--
*Fabrício Pedroso Jorge.*
Database Architect / Database Engineer*LinkedIn Profile*
http://br.linkedin.com/in/fabriciojorge*Contacts:*
+ 353 085 8221706
fpjbito@gmail.com
--
Jaurès FOUTE
On 5/12/23 09:22, FOUTE K. Jaurès wrote:
1) Please do not top post. Use inline posting
2) Do not use images, copy and paste as text.
hello Fabricio,
the listen_addresses is set to *
the result of psql command line
image.png
The job on pg_cron
image.png
What does the Postgres log show when pg_cron is trying to make connections?
--
Adrian Klaver
adrian.klaver@aklaver.com
Understand @Adrian Klaver <adrian.klaver@aklaver.com> .
the log show:
2023-05-12 17:30:19.327 WAT [46190] LOG: cron job 8 starting: SELECT 1
2023-05-12 17:30:19.339 WAT [46190] LOG: cron job 8 connection failed
Le ven. 12 mai 2023 à 17:23, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :
On 5/12/23 09:22, FOUTE K. Jaurès wrote:
1) Please do not top post. Use inline posting
2) Do not use images, copy and paste as text.
hello Fabricio,
the listen_addresses is set to *
the result of psql command line
image.png
The job on pg_cron
image.pngWhat does the Postgres log show when pg_cron is trying to make connections?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Jaurès FOUTE
On 5/12/23 09:41, FOUTE K. Jaurès wrote:
Understand @Adrian Klaver <mailto:adrian.klaver@aklaver.com> .
the log show:
2023-05-12 17:30:19.327 WAT [46190] LOG: cron job 8 starting: SELECT 1
2023-05-12 17:30:19.339 WAT [46190] LOG: cron job 8 connection failed
Well that did not go anywhere.
In your original post in the image under the database column it looks
like, to my old eyes, there is a list of database names.
Is that the case?
Copy and paste that content in your reply.
Le ven. 12 mai 2023 à 17:23, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :On 5/12/23 09:22, FOUTE K. Jaurès wrote:
1) Please do not top post. Use inline posting
2) Do not use images, copy and paste as text.
hello Fabricio,
the listen_addresses is set to *
the result of psql command line
image.png
The job on pg_cron
image.pngWhat does the Postgres log show when pg_cron is trying to make
connections?--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>--
Jaurès FOUTE
--
Adrian Klaver
adrian.klaver@aklaver.com
Le ven. 12 mai 2023 à 20:21, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :
On 5/12/23 09:41, FOUTE K. Jaurès wrote:
Understand @Adrian Klaver <mailto:adrian.klaver@aklaver.com> .
the log show:
2023-05-12 17:30:19.327 WAT [46190] LOG: cron job 8 starting: SELECT 1
2023-05-12 17:30:19.339 WAT [46190] LOG: cron job 8 connection failedWell that did not go anywhere.
In your original post in the image under the database column it looks
like, to my old eyes, there is a list of database names.Is that the case?
Copy and paste that content in your reply.
Hello @Adrian Klaver <adrian.klaver@aklaver.com> sorry for the delais.
Copy of content of cron.job ?
Le ven. 12 mai 2023 à 17:23, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :On 5/12/23 09:22, FOUTE K. Jaurès wrote:
1) Please do not top post. Use inline posting
2) Do not use images, copy and paste as text.
hello Fabricio,
the listen_addresses is set to *
the result of psql command line
image.png
The job on pg_cron
image.pngWhat does the Postgres log show when pg_cron is trying to make
connections?--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>--
Jaurès FOUTE--
Adrian Klaver
adrian.klaver@aklaver.com
--
Jaurès FOUTE
On 5/13/23 00:54, FOUTE K. Jaurès wrote:
In your original post in the image under the database column it looks
like, to my old eyes, there is a list of database names.Is that the case?
Copy and paste that content in your reply.
Hello @Adrian Klaver <mailto:adrian.klaver@aklaver.com> sorry for the
delais.
Copy of content of cron.job ?
The text version of whatever was in the image in this post:
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
Or at least what was in the database column.
--
Jaurès FOUTE
--
Adrian Klaver
adrian.klaver@aklaver.com
Le sam. 13 mai 2023 à 15:59, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :
On 5/13/23 00:54, FOUTE K. Jaurès wrote:
In your original post in the image under the database column it looks
like, to my old eyes, there is a list of database names.Is that the case?
Copy and paste that content in your reply.
Hello @Adrian Klaver <mailto:adrian.klaver@aklaver.com> sorry for the
delais.
Copy of content of cron.job ?The text version of whatever was in the image in this post:
i'm using Ubuntu server 22.04 LTS with PostgreSQL 13
I install pg_cron using the command: sudo apt-get -y install
postgresql-13-cron
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
Or at least what was in the database column.
--
Jaurès FOUTE--
Adrian Klaver
adrian.klaver@aklaver.com
--
Jaurès FOUTE
On 5/14/23 00:20, FOUTE K. Jaurès wrote:
Le sam. 13 mai 2023 à 15:59, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :On 5/13/23 00:54, FOUTE K. Jaurès wrote:
In your original post in the image under the database column
it looks
like, to my old eyes, there is a list of database names.
Is that the case?
Copy and paste that content in your reply.
Hello @Adrian Klaver <mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> sorry for the
delais.
Copy of content of cron.job ?The text version of whatever was in the image in this post:
i'm using Ubuntu server 22.04 LTS with PostgreSQL 13
I install pg_cron using the command: sudo apt-get -y install
postgresql-13-cron
Not what was requested.
In the post linked to here:
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
there was an image of the result of:
SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$);
Provide that result as text.
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com </messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com>
Or at least what was in the database column.
--
Jaurès FOUTE--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>--
Jaurès FOUTE
--
Adrian Klaver
adrian.klaver@aklaver.com
Le dim. 14 mai 2023 à 16:12, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :
On 5/14/23 00:20, FOUTE K. Jaurès wrote:
Le sam. 13 mai 2023 à 15:59, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :On 5/13/23 00:54, FOUTE K. Jaurès wrote:
In your original post in the image under the database column
it looks
like, to my old eyes, there is a list of database names.
Is that the case?
Copy and paste that content in your reply.
Hello @Adrian Klaver <mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> sorry for the
delais.
Copy of content of cron.job ?The text version of whatever was in the image in this post:
i'm using Ubuntu server 22.04 LTS with PostgreSQL 13
I install pg_cron using the command: sudo apt-get -y install
postgresql-13-cronNot what was requested.
In the post linked to here:
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
there was an image of the result of:
SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$);
Provide that result as text.
jobid | schedule | command | nodename | nodeport |
database | username | active | jobname
-------+------------+----------+-----------+----------+--------------------------------------------+----------+--------+---------
8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename |
postgres | t | TEST
(1 row)
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
<
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.comOr at least what was in the database column.
--
Jaurès FOUTE--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>--
Jaurès FOUTE--
Adrian Klaver
adrian.klaver@aklaver.com
--
Jaurès FOUTE
On 5/14/23 13:04, FOUTE K. Jaurès wrote:
Le dim. 14 mai 2023 à 16:12, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :
Not what was requested.
In the post linked to here:
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com </messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com>
there was an image of the result of:
SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$);
Provide that result as text.
jobid | schedule | command | nodename | nodeport |
database | username | active | jobname
-------+------------+----------+-----------+----------+--------------------------------------------+----------+--------+---------
8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename |
postgres | t | TEST
(1 row)
That is not the same result as in the image, it had additional fields:
runid, job_pid, status, return_message, start_time.
Also the database field had not been edited. In the original image it
looks like there was multiple databases named. From what I understand of
pg_cron a job can only run on one database at a time.
--
Adrian Klaver
adrian.klaver@aklaver.com
Le dim. 14 mai 2023 à 21:12, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :
On 5/14/23 13:04, FOUTE K. Jaurès wrote:
Le dim. 14 mai 2023 à 16:12, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :Not what was requested.
In the post linked to here:
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
<
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.comthere was an image of the result of:
SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$);
Provide that result as text.
jobid | schedule | command | nodename | nodeport |
database | username | active | jobname-------+------------+----------+-----------+----------+--------------------------------------------+----------+--------+---------
8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename |
postgres | t | TEST
(1 row)That is not the same result as in the image, it had additional fields:
runid, job_pid, status, return_message, start_time.
Okay, Understands now, this result come from cron.job_run_details that show
the status of running job
Select * from cron.job_run_details where jobid =8 order by runid desc limit
5
jobid | runid | job_pid | database |
username | command | status | return_message | start_time | end_time
-------+-------+---------+--------------------------------------------+----------+----------+--------+-------------------+------------+----------
8 | 9276 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9275 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9274 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9272 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9271 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
(5 rows)
Also the database field had not been edited. In the original image it
looks like there was multiple databases named. From what I understand of
pg_cron a job can only run on one database at a time.
No, it's just one database.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Jaurès FOUTE
Le lun. 15 mai 2023 à 05:12, FOUTE K. Jaurès <jauresfoute@gmail.com> a
écrit :
Le dim. 14 mai 2023 à 21:12, Adrian Klaver <adrian.klaver@aklaver.com> a
écrit :On 5/14/23 13:04, FOUTE K. Jaurès wrote:
Le dim. 14 mai 2023 à 16:12, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> a écrit :Not what was requested.
In the post linked to here:
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com
<
/messages/by-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.comthere was an image of the result of:
SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$);
Provide that result as text.
jobid | schedule | command | nodename | nodeport |
database | username | active | jobname-------+------------+----------+-----------+----------+--------------------------------------------+----------+--------+---------
8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename |
postgres | t | TEST
(1 row)That is not the same result as in the image, it had additional fields:
runid, job_pid, status, return_message, start_time.Okay, Understands now, this result come from cron.job_run_details that
show the status of running job
Select * from cron.job_run_details where jobid =8 order by runid desc
limit 5
jobid | runid | job_pid | database |
username | command | status | return_message | start_time | end_time-------+-------+---------+--------------------------------------------+----------+----------+--------+-------------------+------------+----------
8 | 9276 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9275 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9274 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9272 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
8 | 9271 | | inov_victoria_v71_rdec21_00_ibackuppro_rep |
postgres | SELECT 1 | failed | connection failed | |
(5 rows)Also the database field had not been edited. In the original image it
looks like there was multiple databases named. From what I understand of
pg_cron a job can only run on one database at a time.No, it's just one database.
--
Adrian Klaver
adrian.klaver@aklaver.com--
Jaurès FOUTE
Hello @Adrian Klaver <adrian.klaver@aklaver.com> .
I've just finished a test on PostgreSQL 14 and everything works as expected.
--
Jaurès FOUTE