Обсуждение: PG_Cron - Error Message Connection failed
Hello everyone,
![image.png](/media/maillist_attaches/pgsql-general/2023/05/12/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com/image.png)
--
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.png](/media/maillist_attaches/pgsql-general/2023/05/12/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3+0CJo4h8PFHVw@mail.gmail.com/image.png)
Any help is welcome
Jaurès FOUTE
Вложения
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 showAny help is welcome--Jaurès FOUTE
Fabrício Pedroso Jorge.
Database Architect / Database Engineer
Database Architect / Database Engineer
Вложения
hello Fabricio,
![image.png](/media/maillist_attaches/pgsql-general/2023/05/12/CAHQ1jffs1+Gzy3OtOgkscCH0U7WkVkNCmy384uSWKOWojQjh7Q@mail.gmail.com/image-1.png)
![image.png](/media/maillist_attaches/pgsql-general/2023/05/12/CAHQ1jffs1+Gzy3OtOgkscCH0U7WkVkNCmy384uSWKOWojQjh7Q@mail.gmail.com/image-2.png)
the listen_addresses is set to *
the result of psql command line
![image.png](/media/maillist_attaches/pgsql-general/2023/05/12/CAHQ1jffs1+Gzy3OtOgkscCH0U7WkVkNCmy384uSWKOWojQjh7Q@mail.gmail.com/image-1.png)
The job on pg_cron
![image.png](/media/maillist_attaches/pgsql-general/2023/05/12/CAHQ1jffs1+Gzy3OtOgkscCH0U7WkVkNCmy384uSWKOWojQjh7Q@mail.gmail.com/image-2.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 showAny help is welcome--Jaurès FOUTE--Fabrício Pedroso Jorge.
Database Architect / Database Engineer
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 .
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
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.png
What 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.png > > What 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 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.
Hello @Adrian Klaver 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.png
>
> What 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: https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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
https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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: https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com there was an image of the result of: SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$); Provide that result as text. > > > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com <https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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:jobid | schedule | command | nodename | nodeport | database | username | active | jobname
>
>
> 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:
https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com
there was an image of the result of:
SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$);
Provide that result as text.
-------+------------+----------+-----------+----------+--------------------------------------------+----------+--------+---------
8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename | postgres | t | TEST
(1 row)
>
>
> https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com <https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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
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: > > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com <https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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:
>
> https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com <https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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.
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)
-------+-------+---------+--------------------------------------------+----------+----------+--------+-------------------+------------+----------
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:
>
> https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com <https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.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.Okay, Understands now, this result come from cron.job_run_details that show the status of running jobSelect * from cron.job_run_details where jobid =8 order by runid desc limit 5jobid | 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
I've just finished a test on PostgreSQL 14 and everything works as expected.
--
Jaurès FOUTE