Обсуждение: PG_Cron - Error Message Connection failed

Поиск

Список

Период

Сортировка

PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

12 мая 2023 г., 19:03:28

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

Any help is welcome

Jaurès FOUTE

Вложения

image.png

Re: PG_Cron - Error Message Connection failed

От

Fabricio Pedroso Jorge

Дата:

12 мая 2023 г., 19:05:03

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

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

Вложения

image.png

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

12 мая 2023 г., 19:22:02

hello Fabricio,

the listen_addresses is set to *

the result of psql command line

The job on pg_cron

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

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

Вложения

Re: PG_Cron - Error Message Connection failed

От

Adrian Klaver

Дата:

12 мая 2023 г., 19:23:27

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

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

12 мая 2023 г., 19:41:12

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

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

Re: PG_Cron - Error Message Connection failed

От

Adrian Klaver

Дата:

12 мая 2023 г., 22:20:58

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

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

13 мая 2023 г., 10:54:22

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

Re: PG_Cron - Error Message Connection failed

От

Adrian Klaver

Дата:

13 мая 2023 г., 17:59:35

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

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

14 мая 2023 г., 10:20:32

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

Re: PG_Cron - Error Message Connection failed

От

Adrian Klaver

Дата:

14 мая 2023 г., 18:12:53

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

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

14 мая 2023 г., 23:04:18

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-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.

(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

Re: PG_Cron - Error Message Connection failed

От

Adrian Klaver

Дата:

14 мая 2023 г., 23:12:27

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

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

15 мая 2023 г., 07:12:20

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

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

Re: PG_Cron - Error Message Connection failed

От

FOUTE K. Jaurès

Дата:

15 мая 2023 г., 14:58:55

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 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 .

I've just finished a test on PostgreSQL 14 and everything works as expected.

Jaurès FOUTE

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: PG_Cron - Error Message Connection failed

Вложения

Вложения

Вложения