pgadmin--pgagent---the process hang by unknow reasons

Started by Zhiyu ZY13 Xuover 5 years ago23 messagesbugs

xuzy13@lenovo.com

over 5 years ago

Hi Support

I using pgagent over 2 years. There are over 30 jobs running by pgagent. Recently. I found a problem that the pgagent hang by unknow reasons.
From the stack information. Look like the pagent get dead-lock issue in code.
If you need more information. Please let me know. I suspect this is a bug.
version:
pgagent_10-3.4.0-10.rhel6.x86_64
PG 10.5

The typical stack information.

[postgres@sltfjfrauxq pgagent_pd]$ cat 23389.stark.1
Thread 7 (Thread 0x7ff745f5c700 (LWP 906)):
#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from /usr/lib64/libwx_baseu-2.8.so.0
#4 0x00007ff74c15b819 in DBconn::Return() ()
#5 0x00007ff74c161217 in Job::Execute() ()
#6 0x00007ff74c162899 in JobThread::Entry() ()
#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) () from /usr/lib64/libwx_baseu-2.8.so.0
#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0
#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6
Thread 6 (Thread 0x7ff72ffff700 (LWP 908)):
#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from /usr/lib64/libwx_baseu-2.8.so.0
#4 0x00007ff74c15b819 in DBconn::Return() ()
#5 0x00007ff74c161217 in Job::Execute() ()
#6 0x00007ff74c162899 in JobThread::Entry() ()
#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) () from /usr/lib64/libwx_baseu-2.8.so.0
#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0
#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6
Thread 5 (Thread 0x7ff74695d700 (LWP 910)):
#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from /usr/lib64/libwx_baseu-2.8.so.0
#4 0x00007ff74c15b819 in DBconn::Return() ()
#5 0x00007ff74c161217 in Job::Execute() ()
#6 0x00007ff74c162899 in JobThread::Entry() ()
#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) () from /usr/lib64/libwx_baseu-2.8.so.0
#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0
#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6
Thread 4 (Thread 0x7ff74735e700 (LWP 1565)):
#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from /usr/lib64/libwx_baseu-2.8.so.0
#4 0x00007ff74c15b819 in DBconn::Return() ()
#5 0x00007ff74c161217 in Job::Execute() ()
#6 0x00007ff74c162899 in JobThread::Entry() ()
#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) () from /usr/lib64/libwx_baseu-2.8.so.0
#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0
#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6
Thread 3 (Thread 0x7ff74555b700 (LWP 1567)):
#0 0x00007ff74ad40403 in poll () from /lib64/libc.so.6
#1 0x00007ff74bd1c28f in ?? () from /usr/lib64/libpq.so.5
#2 0x00007ff74bd1c310 in ?? () from /usr/lib64/libpq.so.5
#3 0x00007ff74bd178e2 in ?? () from /usr/lib64/libpq.so.5
#4 0x00007ff74bd1865f in PQconnectdb () from /usr/lib64/libpq.so.5
#5 0x00007ff74c15ad71 in DBconn::Connect(wxString const&) ()
#6 0x00007ff74c15af73 in DBconn::DBconn(wxString const&, wxString const&) ()
#7 0x00007ff74c15bfe8 in DBconn::Get(wxString const&, wxString const&) ()
#8 0x00007ff74c16108f in Job::Execute() ()
#9 0x00007ff74c162899 in JobThread::Entry() ()
#10 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) () from /usr/lib64/libwx_baseu-2.8.so.0
#11 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0
#12 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6
Thread 2 (Thread 0x7ff744b5a700 (LWP 1569)):
#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from /usr/lib64/libwx_baseu-2.8.so.0
#4 0x00007ff74c15bf6b in DBconn::Get(wxString const&, wxString const&) ()
#5 0x00007ff74c16108f in Job::Execute() ()
#6 0x00007ff74c162899 in JobThread::Entry() ()
#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) () from /usr/lib64/libwx_baseu-2.8.so.0
#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0
#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6
Thread 1 (Thread 0x7ff74c3507e0 (LWP 23389)):
#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from /usr/lib64/libwx_baseu-2.8.so.0
#4 0x00007ff74c15a99d in DBconn::ClearConnections(bool) ()
#5 0x00007ff74c15e908 in MainRestartLoop(DBconn*) ()
#6 0x00007ff74c15f2a3 in MainLoop() ()
#7 0x00007ff74c15e016 in main ()

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

Bruce Momjian

bruce@momjian.us

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#1)

Re: pgadmin--pgagent---the process hang by unknow reasons

On Wed, Nov 25, 2020 at 08:41:32PM +0000, Zhiyu ZY13 Xu wrote:

Hi Support

I using pgagent over 2 years. There are over 30 jobs running by pgagent.
Recently. I found a problem that the pgagent hang by unknow reasons.

From the stack information. Look like the pagent get dead-lock issue in
code.

For pgAdmin support, see:

https://www.pgadmin.org/support/list/

or email

pgadmin-support@postgresql.org

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#1)

Hi Support

I using pgagent over 2 years. There are over 30 jobs running by pgagent. Recently. I found a problem that sometime the pgagent hang by unknow reasons.
From the stack information. Look like the pagent experience dead-lock issue in code.
The stack display many thread stop on this function “in __lll_lock_wait”
If you need more information. Please let me know. I suspect this is a bug.

I collect to pgagent trace log and stack information on the attachment.

pgagent trace log
pg_agent_11_24.log
pg_agent_11_26.log
pgagent process stack
others information.

version:
pgagent_10-3.4.0-10.rhel6.x86_64
PG 10.5

The typical stack information.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#3)

Re: pgadmin--pgagent---the process hang by unknow reasons

Given the libwx* references in your stacktrace, you appear to be using an
old version of pgagent - we removed the dependency on wxWidgets nearly 2.5
years ago and replaced it with Boost.

Please upgrade and try again.

Thanks.

On Thu, Nov 26, 2020 at 8:05 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Support

I using pgagent over 2 years. There are over 30 jobs running by
pgagent. Recently. I found a problem that sometime the pgagent hang by
unknow reasons.

From the stack information. Look like the pagent experience dead-lock
issue in code.

The stack display many thread stop on this function “in __lll_lock_wait”

If you need more information. Please let me know. I suspect this is a
bug.

I collect to pgagent trace log and stack information on the attachment.

pgagent trace log

pg_agent_11_24.log

pg_agent_11_26.log

pgagent process stack

others information.

version:

pgagent_10-3.4.0-10.rhel6.x86_64

PG 10.5

The typical stack information.

[postgres@sltfjfrauxq pgagent_pd]$ cat 23389.stark.1

Thread 7 (Thread 0x7ff745f5c700 (LWP 906)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 6 (Thread 0x7ff72ffff700 (LWP 908)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 5 (Thread 0x7ff74695d700 (LWP 910)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 4 (Thread 0x7ff74735e700 (LWP 1565)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 3 (Thread 0x7ff74555b700 (LWP 1567)):

#0 0x00007ff74ad40403 in poll () from /lib64/libc.so.6

#1 0x00007ff74bd1c28f in ?? () from /usr/lib64/libpq.so.5

#2 0x00007ff74bd1c310 in ?? () from /usr/lib64/libpq.so.5

#3 0x00007ff74bd178e2 in ?? () from /usr/lib64/libpq.so.5

#4 0x00007ff74bd1865f in PQconnectdb () from /usr/lib64/libpq.so.5

#5 0x00007ff74c15ad71 in DBconn::Connect(wxString const&) ()

#6 0x00007ff74c15af73 in DBconn::DBconn(wxString const&, wxString const&)
()

#7 0x00007ff74c15bfe8 in DBconn::Get(wxString const&, wxString const&) ()

#8 0x00007ff74c16108f in Job::Execute() ()

#9 0x00007ff74c162899 in JobThread::Entry() ()

#10 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#11 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#12 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 2 (Thread 0x7ff744b5a700 (LWP 1569)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15bf6b in DBconn::Get(wxString const&, wxString const&) ()

#5 0x00007ff74c16108f in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 1 (Thread 0x7ff74c3507e0 (LWP 23389)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15a99d in DBconn::ClearConnections(bool) ()

#5 0x00007ff74c15e908 in MainRestartLoop(DBconn*) ()

#6 0x00007ff74c15f2a3 in MainLoop() ()

#7 0x00007ff74c15e016 in main ()

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#4)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

Thanks for your quick response.
This env was deploy on Jan 2019 by my team mate. Currently the pgagent have 30 jobs running. The version is pgagent_10-3.4.0
I don’t know how to upgrade the pgagent. I try to find upgrade document. But failed.
Only find that edb ppas could upgrade pgagent.
https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/installation-getting-started/upgrade-guide/11/EDB_Postgres_Advanced_Server_Upgrade_Guide.1.13.html

If I re-install the pgagent with latest version. Whether the old pgagent jobs will drop with old version pgagent ?
Would you like to guide me to make pgagent using new Boost package and no impact currently working pgagent jobs ?
I don’t want to rebuild all pgagent jobs. Thanks in advance.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年11月26日 19:39
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Given the libwx* references in your stacktrace, you appear to be using an old version of pgagent - we removed the dependency on wxWidgets nearly 2.5 years ago and replaced it with Boost.

Please upgrade and try again.

Thanks.

On Thu, Nov 26, 2020 at 8:05 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Support

I collect to pgagent trace log and stack information on the attachment.

pgagent trace log
pg_agent_11_24.log
pg_agent_11_26.log
pgagent process stack
others information.

version:
pgagent_10-3.4.0-10.rhel6.x86_64
PG 10.5

The typical stack information.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#5)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Thu, Nov 26, 2020 at 4:45 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

This env was deploy on Jan 2019 by my team mate. Currently the pgagent
have 30 jobs running. The version is pgagent_10-3.4.0

I don’t know how to upgrade the pgagent. I try to find upgrade document.
But failed.

Only find that edb ppas could upgrade pgagent.

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/installation-getting-started/upgrade-guide/11/EDB_Postgres_Advanced_Server_Upgrade_Guide.1.13.html

The PGAgent that comes with EDB Advanced Server is quite different from the
Open Source version. Assuming you're using the RPM packages on RHEL/CentOS
6, you should just be able to use "yum upgrade ..." to upgrade to the
latest version. Looking at the postgresql-common repository on
yum.postgresql.org, I see that v4.0.0 is available (
https://ftp.postgresql.org/pub/repos/yum/common/redhat/rhel-6-x86_64/)

If I re-install the pgagent with latest version. Whether the old pgagent
jobs will drop with old version pgagent ?

Would you like to guide me to make pgagent using new Boost package and no
impact currently working pgagent jobs ?

I don’t want to rebuild all pgagent jobs. Thanks in advance.

Upgrading pgAgent will not affect the jobs you have defined already.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年11月26日 19:39
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi

Given the libwx* references in your stacktrace, you appear to be using an
old version of pgagent - we removed the dependency on wxWidgets nearly 2.5
years ago and replaced it with Boost.

Please upgrade and try again.

Thanks.

On Thu, Nov 26, 2020 at 8:05 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Support

I using pgagent over 2 years. There are over 30 jobs running by
pgagent. Recently. I found a problem that sometime the pgagent hang by
unknow reasons.

From the stack information. Look like the pagent experience dead-lock
issue in code.

The stack display many thread stop on this function “in __lll_lock_wait
”

If you need more information. Please let me know. I suspect this is a
bug.

I collect to pgagent trace log and stack information on the attachment.

pgagent trace log

pg_agent_11_24.log

pg_agent_11_26.log

pgagent process stack

others information.

version:

pgagent_10-3.4.0-10.rhel6.x86_64

PG 10.5

The typical stack information.

[postgres@sltfjfrauxq pgagent_pd]$ cat 23389.stark.1

Thread 7 (Thread 0x7ff745f5c700 (LWP 906)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 6 (Thread 0x7ff72ffff700 (LWP 908)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 5 (Thread 0x7ff74695d700 (LWP 910)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 4 (Thread 0x7ff74735e700 (LWP 1565)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15b819 in DBconn::Return() ()

#5 0x00007ff74c161217 in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 3 (Thread 0x7ff74555b700 (LWP 1567)):

#0 0x00007ff74ad40403 in poll () from /lib64/libc.so.6

#1 0x00007ff74bd1c28f in ?? () from /usr/lib64/libpq.so.5

#2 0x00007ff74bd1c310 in ?? () from /usr/lib64/libpq.so.5

#3 0x00007ff74bd178e2 in ?? () from /usr/lib64/libpq.so.5

#4 0x00007ff74bd1865f in PQconnectdb () from /usr/lib64/libpq.so.5

#5 0x00007ff74c15ad71 in DBconn::Connect(wxString const&) ()

#6 0x00007ff74c15af73 in DBconn::DBconn(wxString const&, wxString const&)
()

#7 0x00007ff74c15bfe8 in DBconn::Get(wxString const&, wxString const&) ()

#8 0x00007ff74c16108f in Job::Execute() ()

#9 0x00007ff74c162899 in JobThread::Entry() ()

#10 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#11 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#12 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 2 (Thread 0x7ff744b5a700 (LWP 1569)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15bf6b in DBconn::Get(wxString const&, wxString const&) ()

#5 0x00007ff74c16108f in Job::Execute() ()

#6 0x00007ff74c162899 in JobThread::Entry() ()

#7 0x00007ff74ba99021 in wxThreadInternal::PthreadStart(wxThread*) ()
from /usr/lib64/libwx_baseu-2.8.so.0

#8 0x00007ff74affcaa1 in start_thread () from /lib64/libpthread.so.0

#9 0x00007ff74ad49c4d in clone () from /lib64/libc.so.6

Thread 1 (Thread 0x7ff74c3507e0 (LWP 23389)):

#0 0x00007ff74b003334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff74affe5d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff74affe4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff74ba979c9 in wxMutexInternal::Lock() () from
/usr/lib64/libwx_baseu-2.8.so.0

#4 0x00007ff74c15a99d in DBconn::ClearConnections(bool) ()

#5 0x00007ff74c15e908 in MainRestartLoop(DBconn*) ()

#6 0x00007ff74c15f2a3 in MainLoop() ()

#7 0x00007ff74c15e016 in main ()

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#6)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that he install it by yum command and execute a script.
Then those pgagent tables created by this script. There is no extension exist. But those pgagent table exist.

[cid:image005.png@01D6C806.72C28D00]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k
Is this ok [y/N]: y
Downloading Packages:
pgagent_10-4.0.0-4.rhel6.x86_64.rpm | 138 kB 00:08
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : pgagent_10-4.0.0-4.rhel6.x86_64 1/2
Cleanup : pgagent_10-3.4.0-10.rhel6.x86_64 2/2
Verifying : pgagent_10-4.0.0-4.rhel6.x86_64 1/2
Verifying : pgagent_10-3.4.0-10.rhel6.x86_64 2/2

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image006.png@01D6C806.72C28D00]

The 4.0 pgagent start logs.
Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection
Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...
Tue Dec 1 13:57:53 2020 DEBUG: user: postgres
Tue Dec 1 13:57:53 2020 DEBUG: password: *****
Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres
Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1
Tue Dec 1 13:57:53 2020 DEBUG: port: 5432
Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres
Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd
/data/postgres/new_package/pgAgent-4.0.0-Source/sql
[root@slbwcbnos2 sql]# ls -al
total 36
drwxr-xr-x 2 501 games 90 Dec 1 14:25 .
drwxr-xr-x 6 501 games 298 Jul 12 2018 ..
-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql
-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql
-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql
Use "CREATE EXTENSION pgagent UPDATE" to load this file.
postgres=# CREATE EXTENSION pgagent UPDATE;
ERROR: syntax error at or near "UPDATE"
LINE 1: CREATE EXTENSION pgagent UPDATE;
^
postgres=# CREATE EXTENSION pgagent UPDATE;
ERROR: syntax error at or near "UPDATE"
LINE 1: CREATE EXTENSION pgagent UPDATE;
^
postgres=# CREATE EXTENSION pgagent ;
ERROR: extension "pgagent" has no installation script nor update path for version "3.4"
postgres=#

After failed. I try to build and compile the latest version 4.2.
Build and install successfully. But I still on unable upgrade current 3.4 version.
I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql
Use "CREATE EXTENSION pgagent UPDATE" to load this file.
postgres=# CREATE EXTENSION pgagent UPDATE;
ERROR: syntax error at or near "UPDATE"
LINE 1: CREATE EXTENSION pgagent UPDATE;
^
postgres=# CREATE EXTENSION pgagent ;
ERROR: relation "pga_jobagent" already exists
postgres=#

[cid:image007.png@01D6C806.72C28D00]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年11月30日 19:29
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Thu, Nov 26, 2020 at 4:45 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

The PGAgent that comes with EDB Advanced Server is quite different from the Open Source version. Assuming you're using the RPM packages on RHEL/CentOS 6, you should just be able to use "yum upgrade ..." to upgrade to the latest version. Looking at the postgresql-common repository on yum.postgresql.org<http://yum.postgresql.org>, I see that v4.0.0 is available (https://ftp.postgresql.org/pub/repos/yum/common/redhat/rhel-6-x86_64/)

Upgrading pgAgent will not affect the jobs you have defined already.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org<mailto:dpage@pgadmin.org>>
发送时间: 2020年11月26日 19:39
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>>
抄送: pgadmin-support@postgresql.org<mailto:pgadmin-support@postgresql.org>
主题: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Given the libwx* references in your stacktrace, you appear to be using an old version of pgagent - we removed the dependency on wxWidgets nearly 2.5 years ago and replaced it with Boost.

Please upgrade and try again.

Thanks.

On Thu, Nov 26, 2020 at 8:05 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Support

I collect to pgagent trace log and stack information on the attachment.

pgagent trace log
pg_agent_11_24.log
pg_agent_11_26.log
pgagent process stack
others information.

version:
pgagent_10-3.4.0-10.rhel6.x86_64
PG 10.5

The typical stack information.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#7)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that
he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path for
version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current 3.4
version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#8)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not exist.

[cid:image001.png@01D6C80C.7D8E2A40]

[cid:image002.png@01D6C80C.7D8E2A40]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年12月1日 17:48
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

[cid:image003.png@01D6C80C.7D8E2A40]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image004.jpg@01D6C80C.7D8E2A40]

I try to upgrade it by sql. But still on failed.

[cid:image005.png@01D6C80C.7D8E2A40]

I think the message is wrong (which I'll fix if you can confirm) - it should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#10

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#9)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

It sounds like the extension hasn't been installed properly at the
operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not
exist.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 17:48
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that
he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path for
version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current 3.4
version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#11

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#8)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not exist.

[cid:image001.png@01D6C80C.7D8E2A40]

[cid:image002.png@01D6C80C.7D8E2A40]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

[cid:image003.png@01D6C80C.7D8E2A40]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image004.jpg@01D6C80C.7D8E2A40]

I try to upgrade it by sql. But still on failed.

[cid:image005.png@01D6C80C.7D8E2A40]

I think the message is wrong (which I'll fix if you can confirm) - it should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#12

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#10)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

The command output is :

My test system. All commands executed on this system.
Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.
2: install pgagent by yum command .
Yum install pgagent_10
Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.
Failed.

6: try to upgrade by the upgrade script.
Failed.

7: Build and compile pgagent 4.2.
Try to upgrade pgagent. Failed.

[root@slbwcbnos2 ~]# ls -al /usr/pgsql-10/share/extension
total 40
drwxr-xr-x 2 root root 118 Dec 1 14:59 .
drwxr-xr-x 3 root root 23 Dec 1 13:55 ..
-rw-r--r-- 1 root root 538 Jul 12 2018 pgagent--3.4--4.0.sql
-rw-r--r-- 1 root root 27657 Apr 5 2020 pgagent--4.0.sql
-rw-r--r-- 1 root root 139 Apr 5 2020 pgagent.control
-rw-r--r-- 1 root root 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

Our production system.

[root@sltfjfrauxq ~]# ls -al /usr/pgsql-10/share/extension
total 44
drwxr-xr-x 2 root root 4096 Nov 30 23:46 .
drwxr-xr-x 3 root root 4096 Nov 22 2018 ..
-rw-r--r-- 1 root root 27665 Oct 18 2017 pgagent--3.4.sql
-rw-r--r-- 1 root root 139 Oct 18 2017 pgagent.control
-rw-r--r-- 1 root root 2358 Feb 26 2014 pgagent--unpackaged--3.4.sql
[root@sltfjfrauxq ~]#

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年12月1日 18:33
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

It sounds like the extension hasn't been installed properly at the operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not exist.

[cid:image002.png@01D6C821.564421F0]

[cid:image004.png@01D6C821.564421F0]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org<mailto:dpage@pgadmin.org>>
发送时间: 2020年12月1日 17:48
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>>
抄送: pgadmin-support@postgresql.org<mailto:pgadmin-support@postgresql.org>
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

[cid:image006.png@01D6C821.564421F0]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image008.jpg@01D6C821.564421F0]

I try to upgrade it by sql. But still on failed.

[cid:image010.png@01D6C821.564421F0]

I think the message is wrong (which I'll fix if you can confirm) - it should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#13

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#12)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

The command output is :

My test system. All commands executed on this system.

Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.

2: install pgagent by yum command .

Yum install pgagent_10

Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.

Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.

Failed.

With what error?

7: Build and compile pgagent 4.2.

Try to upgrade pgagent. Failed.

[root@slbwcbnos2 ~]# ls -al /usr/pgsql-10/share/extension

total 40

drwxr-xr-x 2 root root 118 Dec 1 14:59 .

drwxr-xr-x 3 root root 23 Dec 1 13:55 ..

-rw-r--r-- 1 root root 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 root root 27657 Apr 5 2020 pgagent--4.0.sql

-rw-r--r-- 1 root root 139 Apr 5 2020 pgagent.control

-rw-r--r-- 1 root root 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not
trying to build it yourself at this stage.

Our production system.

[root@sltfjfrauxq ~]# ls -al /usr/pgsql-10/share/extension

total 44

drwxr-xr-x 2 root root 4096 Nov 30 23:46 .

drwxr-xr-x 3 root root 4096 Nov 22 2018 ..

-rw-r--r-- 1 root root 27665 Oct 18 2017 pgagent--3.4.sql

-rw-r--r-- 1 root root 139 Oct 18 2017 pgagent.control

-rw-r--r-- 1 root root 2358 Feb 26 2014 pgagent--unpackaged--3.4.sql

[root@sltfjfrauxq ~]#

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 18:33
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

It sounds like the extension hasn't been installed properly at the
operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not
exist.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 17:48
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that
he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path for
version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current 3.4
version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#14

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#13)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

Thanks for your update. I collect logs for your reference.
By the way. Is there any upgrade document which mentioned this scenario ? upgrade pgagent_10 3.4 to pgagent_10 4.0.
5: start pgagent process with new version pgagent.
With what error? Anything in the pgAgent log or /var/log/messages etc?
There is no errors.The pgagent process disappear after execute the command.
On pgagent_10 3.4 version. There is a pgagent_10 process running.

[cid:image011.png@01D6C8BB.1778CA80]

The pgagent_10 4.0 trace log also no errors. Look like it terminate unexpected.
[postgres@slbwcbnos2 sql]$ pgagent_10 -v
PostgreSQL Scheduling Agent
Version: 4.0.0
[postgres@slbwcbnos2 ~]$ pgagent_10 hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=******* -s ./pg_agent_4.0_12_02.log -l 2
[postgres@slbwcbnos2 ~]$ ps -ef |grep -i pgagent_10
postgres 5939 30428 0 14:15 pts/0 00:00:00 grep --color=auto -i pgagent_10
[postgres@slbwcbnos2 ~]$ cat pg_agent_4.0_12_02.log
Wed Dec 2 14:15:01 2020 DEBUG: Creating primary connection
Wed Dec 2 14:15:01 2020 DEBUG: Parsing connection information...
Wed Dec 2 14:15:01 2020 DEBUG: user: postgres
Wed Dec 2 14:15:01 2020 DEBUG: password: *****
Wed Dec 2 14:15:01 2020 DEBUG: dbname: postgres
Wed Dec 2 14:15:01 2020 DEBUG: hostaddr: 127.0.0.1
Wed Dec 2 14:15:01 2020 DEBUG: port: 5432
Wed Dec 2 14:15:01 2020 DEBUG: Creating DB connection: user=postgres password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres
Wed Dec 2 14:15:01 2020 DEBUG: Database sanity check
[postgres@slbwcbnos2 ~]$ ps -ef |grep -i pgagent_10
postgres 8534 30428 0 14:16 pts/0 00:00:00 grep --color=auto -i pgagent_10
[postgres@slbwcbnos2 ~]$

6: try to upgrade by the upgrade script.
Failed. With what error?

[postgres@slbwcbnos2 sql]$ pwd
/data/postgres/new_package/pgAgent-4.0.0-Source/sql
[postgres@slbwcbnos2 sql]$ pwd
/data/postgres/new_package/pgAgent-4.0.0-Source/sql
[postgres@slbwcbnos2 sql]$ ls -al
total 36
drwxr-xr-x 2 501 games 90 Dec 1 14:25 .
drwxr-xr-x 6 501 games 298 Jul 12 2018 ..
-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql
-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql
-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql
[postgres@slbwcbnos2 sql]$ psql
psql (10.5)
Type "help" for help.

postgres=# \i pgagent--3.4--4.0.sql
Use "CREATE EXTENSION pgagent UPDATE" to load this file.
postgres=# CREATE EXTENSION pgagent UPDATE;
ERROR: syntax error at or near "UPDATE"
LINE 1: CREATE EXTENSION pgagent UPDATE;
^
postgres=# alter EXTENSION pgagent UPDATE;
ERROR: extension "pgagent" does not exist
postgres=# \q
[postgres@slbwcbnos2 sql]$ pga
pgagent_10 pgawk
[postgres@slbwcbnos2 sql]$ pgagent_10 -v
PostgreSQL Scheduling Agent
Version: 4.0.0

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年12月1日 22:27
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

The command output is :

My test system. All commands executed on this system.
Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.
2: install pgagent by yum command .
Yum install pgagent_10
Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.
Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.
Failed.

With what error?

7: Build and compile pgagent 4.2.
Try to upgrade pgagent. Failed.

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not trying to build it yourself at this stage.

Our production system.

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org<mailto:dpage@pgadmin.org>>
发送时间: 2020年12月1日 18:33
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>>
抄送: pgadmin-support@postgresql.org<mailto:pgadmin-support@postgresql.org>
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

It sounds like the extension hasn't been installed properly at the operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not exist.

[cid:image012.png@01D6C8BB.1778CA80]

[cid:image013.png@01D6C8BB.1778CA80]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

[cid:image014.png@01D6C8BB.1778CA80]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image015.jpg@01D6C8BB.1778CA80]

I try to upgrade it by sql. But still on failed.

[cid:image016.png@01D6C8BB.1778CA80]

I think the message is wrong (which I'll fix if you can confirm) - it should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#15

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#14)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Wed, Dec 2, 2020 at 6:50 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

As a reference point, I installed a fresh CentOS 6.10 VM (which is somewhat
less easy than it used to be, since it went EOL 2 days ago and the repos
and ISOs have all been moved). I then installed postgresql10-server, ran
initdb and started the server. Once that was up and running, I installed
pgagent_10-3.4.0. I was able to login to Postgres and run "CREATE EXTENSION
pgagent;" without any errors. I then started pgAgent to ensure it ran
correctly, which it did.

Then, I ran a "yum upgrade pgagent_10", which installed the 4.0.0 release.
In PostgreSQL, I ran "ALTER EXTENSION pgagent UPDATE;", which successfully
upgraded the schema. I restarted pgAgent and everything looked good.

So, this doesn't appear to be a general problem, but something specific to
your system. Is there anything in the process above that doesn't match with
what you've tried to do?

More comments below....

Thanks for your update. I collect logs for your reference.

By the way. Is there any upgrade document which mentioned this scenario
? upgrade pgagent_10 3.4 to pgagent_10 4.0.

5: start pgagent process with new version pgagent.

With what error? Anything in the pgAgent log or /var/log/messages etc?

There is no errors.The pgagent process disappear after execute the
command.

On pgagent_10 3.4 version. There is a pgagent_10 process running.

If you don't include the "-f" command line option, pgAgent will detach from
the foreground and run as a background process, returning you to a shell
prompt. I would suggest running ps to check you don't have a number of
pgAgent processes running in the background (and if you do, kill them).

Also note that any command line options *must* come before the connection
string, not after it. That would explain why the -s and -l options you've
used above don't seem to do anything.

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# alter EXTENSION pgagent UPDATE;

ERROR: extension "pgagent" does not exist

That implies that pgAgent isn't installed properly. Are you using the
PostgreSQL packages from yum.postgresql.org? If not, how did you install
PostgreSQL? The pgagent_10 package is designed to work with the PostgreSQL
10 RPMs from the same yum.postgresql.org repo.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 22:27
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

The command output is :

My test system. All commands executed on this system.

Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.

2: install pgagent by yum command .

Yum install pgagent_10

Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.

Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.

Failed.

With what error?

7: Build and compile pgagent 4.2.

Try to upgrade pgagent. Failed.

[root@slbwcbnos2 ~]# ls -al /usr/pgsql-10/share/extension

total 40

drwxr-xr-x 2 root root 118 Dec 1 14:59 .

drwxr-xr-x 3 root root 23 Dec 1 13:55 ..

-rw-r--r-- 1 root root 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 root root 27657 Apr 5 2020 pgagent--4.0.sql

-rw-r--r-- 1 root root 139 Apr 5 2020 pgagent.control

-rw-r--r-- 1 root root 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not
trying to build it yourself at this stage.

Our production system.

[root@sltfjfrauxq ~]# ls -al /usr/pgsql-10/share/extension

total 44

drwxr-xr-x 2 root root 4096 Nov 30 23:46 .

drwxr-xr-x 3 root root 4096 Nov 22 2018 ..

-rw-r--r-- 1 root root 27665 Oct 18 2017 pgagent--3.4.sql

-rw-r--r-- 1 root root 139 Oct 18 2017 pgagent.control

-rw-r--r-- 1 root root 2358 Feb 26 2014 pgagent--unpackaged--3.4.sql

[root@sltfjfrauxq ~]#

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 18:33
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

It sounds like the extension hasn't been installed properly at the
operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not
exist.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 17:48
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that
he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path for
version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current 3.4
version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#16

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#15)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

I am appreciate you help on this problem.
The production system installed by my teammate. He told me that. He experience some problem when build and compile pgagent 3.4.0 file.
So he install it by yum resource. Then create pgagent tables by execute a script. Look like he didn’t create pgagent extension.
On the production system. I could find those tables which used by pgagent_10 3.4.

[cid:image014.png@01D6CA5F.790F7B90]

But I can’t list pgagent extension.
[cid:image016.png@01D6CA5F.790F7B90]

Currently. There are over 30 jobs running by pgagent. Most of time pgagent could running well.
I don’t want to re-create those jobs. I want to try to find a solution to upgrade pgagent on this env.
If this env have unknown problem when deploy. Do you have any suggestion to upgrade 4.0 without re-create all pgagent jobs ? Thanks

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年12月2日 21:16
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Wed, Dec 2, 2020 at 6:50 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

As a reference point, I installed a fresh CentOS 6.10 VM (which is somewhat less easy than it used to be, since it went EOL 2 days ago and the repos and ISOs have all been moved). I then installed postgresql10-server, ran initdb and started the server. Once that was up and running, I installed pgagent_10-3.4.0. I was able to login to Postgres and run "CREATE EXTENSION pgagent;" without any errors. I then started pgAgent to ensure it ran correctly, which it did.

Then, I ran a "yum upgrade pgagent_10", which installed the 4.0.0 release. In PostgreSQL, I ran "ALTER EXTENSION pgagent UPDATE;", which successfully upgraded the schema. I restarted pgAgent and everything looked good.

So, this doesn't appear to be a general problem, but something specific to your system. Is there anything in the process above that doesn't match with what you've tried to do?

More comments below....

[cid:image017.png@01D6CA5F.790F7B90]

If you don't include the "-f" command line option, pgAgent will detach from the foreground and run as a background process, returning you to a shell prompt. I would suggest running ps to check you don't have a number of pgAgent processes running in the background (and if you do, kill them).

Also note that any command line options *must* come before the connection string, not after it. That would explain why the -s and -l options you've used above don't seem to do anything.

LINE 1: CREATE EXTENSION pgagent UPDATE;
^
postgres=# alter EXTENSION pgagent UPDATE;
ERROR: extension "pgagent" does not exist

That implies that pgAgent isn't installed properly. Are you using the PostgreSQL packages from yum.postgresql.org<http://yum.postgresql.org>? If not, how did you install PostgreSQL? The pgagent_10 package is designed to work with the PostgreSQL 10 RPMs from the same yum.postgresql.org<http://yum.postgresql.org> repo.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org<mailto:dpage@pgadmin.org>>
发送时间: 2020年12月1日 22:27
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>>
抄送: pgadmin-support@postgresql.org<mailto:pgadmin-support@postgresql.org>
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

The command output is :

My test system. All commands executed on this system.
Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.
2: install pgagent by yum command .
Yum install pgagent_10
Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.
Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.
Failed.

With what error?

7: Build and compile pgagent 4.2.
Try to upgrade pgagent. Failed.

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not trying to build it yourself at this stage.

Our production system.

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

It sounds like the extension hasn't been installed properly at the operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not exist.

[cid:image018.png@01D6CA5F.790F7B90]

[cid:image019.png@01D6CA5F.790F7B90]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

[cid:image020.png@01D6CA5F.790F7B90]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image021.jpg@01D6CA5F.790F7B90]

I try to upgrade it by sql. But still on failed.

[cid:image022.png@01D6CA5F.790F7B90]

I think the message is wrong (which I'll fix if you can confirm) - it should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#17

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#16)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Fri, Dec 4, 2020 at 9:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

I am appreciate you help on this problem.

The production system installed by my teammate. He told me that. He
experience some problem when build and compile pgagent 3.4.0 file.

So he install it by yum resource. Then create pgagent tables by execute a
script. Look like he didn’t create pgagent extension.

On the production system. I could find those tables which used by
pgagent_10 3.4.

Ah, OK. Try running the following (assuming *only* 3.4 is installed):

CREATE EXTENSION pgagent FROM unpackaged;

That should use the pgagent--unpackaged--3.4.sql script to convert the
existing 3.4 installation into an extension.

Then, you should be able to do the 4.0 upgrade by installing the package,
updating the extension, and then restarting pgAgent.

But I can’t list pgagent extension.

Currently. There are over 30 jobs running by pgagent. Most of time pgagent
could running well.

I don’t want to re-create those jobs. I want to try to find a solution
to upgrade pgagent on this env.

If this env have unknown problem when deploy. Do you have any suggestion
to upgrade 4.0 without re-create all pgagent jobs ? Thanks

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月2日 21:16
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Wed, Dec 2, 2020 at 6:50 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

As a reference point, I installed a fresh CentOS 6.10 VM (which is
somewhat less easy than it used to be, since it went EOL 2 days ago and the
repos and ISOs have all been moved). I then installed postgresql10-server,
ran initdb and started the server. Once that was up and running, I
installed pgagent_10-3.4.0. I was able to login to Postgres and run "CREATE
EXTENSION pgagent;" without any errors. I then started pgAgent to ensure it
ran correctly, which it did.

Then, I ran a "yum upgrade pgagent_10", which installed the 4.0.0 release.
In PostgreSQL, I ran "ALTER EXTENSION pgagent UPDATE;", which successfully
upgraded the schema. I restarted pgAgent and everything looked good.

So, this doesn't appear to be a general problem, but something specific to
your system. Is there anything in the process above that doesn't match with
what you've tried to do?

More comments below....

Thanks for your update. I collect logs for your reference.

By the way. Is there any upgrade document which mentioned this scenario
? upgrade pgagent_10 3.4 to pgagent_10 4.0.

5: start pgagent process with new version pgagent.

With what error? Anything in the pgAgent log or /var/log/messages etc?

There is no errors.The pgagent process disappear after execute the
command.

On pgagent_10 3.4 version. There is a pgagent_10 process running.

If you don't include the "-f" command line option, pgAgent will detach
from the foreground and run as a background process, returning you to a
shell prompt. I would suggest running ps to check you don't have a number
of pgAgent processes running in the background (and if you do, kill them).

Also note that any command line options *must* come before the connection
string, not after it. That would explain why the -s and -l options you've
used above don't seem to do anything.

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# alter EXTENSION pgagent UPDATE;

ERROR: extension "pgagent" does not exist

That implies that pgAgent isn't installed properly. Are you using the
PostgreSQL packages from yum.postgresql.org? If not, how did you install
PostgreSQL? The pgagent_10 package is designed to work with the PostgreSQL
10 RPMs from the same yum.postgresql.org repo.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 22:27
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

The command output is :

My test system. All commands executed on this system.

Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.

2: install pgagent by yum command .

Yum install pgagent_10

Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.

Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.

Failed.

With what error?

7: Build and compile pgagent 4.2.

Try to upgrade pgagent. Failed.

[root@slbwcbnos2 ~]# ls -al /usr/pgsql-10/share/extension

total 40

drwxr-xr-x 2 root root 118 Dec 1 14:59 .

drwxr-xr-x 3 root root 23 Dec 1 13:55 ..

-rw-r--r-- 1 root root 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 root root 27657 Apr 5 2020 pgagent--4.0.sql

-rw-r--r-- 1 root root 139 Apr 5 2020 pgagent.control

-rw-r--r-- 1 root root 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not
trying to build it yourself at this stage.

Our production system.

[root@sltfjfrauxq ~]# ls -al /usr/pgsql-10/share/extension

total 44

drwxr-xr-x 2 root root 4096 Nov 30 23:46 .

drwxr-xr-x 3 root root 4096 Nov 22 2018 ..

-rw-r--r-- 1 root root 27665 Oct 18 2017 pgagent--3.4.sql

-rw-r--r-- 1 root root 139 Oct 18 2017 pgagent.control

-rw-r--r-- 1 root root 2358 Feb 26 2014 pgagent--unpackaged--3.4.sql

[root@sltfjfrauxq ~]#

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 18:33
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

It sounds like the extension hasn't been installed properly at the
operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not
exist.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 17:48
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that
he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path for
version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current 3.4
version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#18

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Dave Page (#17)

答复: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Hi Dave

Thanks for your help. I have upgrade to pgagent 4.0 on 12/7 with your recommendation.
CREATE EXTENSION pgagent FROM unpackaged;

Owing to my PostgreSQL server didn’t install by yum. So I copy those file to my extension directory.
Then create extension command work.
cp /usr/pgsql-10/share/extension/pgagent* /data/postgres/share/extension/

After I upgrade pgagent to 4.0. I test some application. It’s work normal. Pgagent 4.0 work as expected.
But when it running almost 1 hour. It experience the problem which same with pgagent 3.4.
I attach the logs and stack for your reference. The pgagent stop on this function “in __lll_lock_wait ()”
The log on the attachment for your reference.
Env: PG 10.5 and OS is Centos 6.9

postgres@sltfjfrauxq ~]$ pstack 21492
Thread 4 (Thread 0x7ff8dffff700 (LWP 24059)):
#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff8ebfbbc01 in DBconn::Return() ()
#4 0x00007ff8ebfc7f32 in Job::Execute() ()
#5 0x00007ff8ebfcac58 in JobThread::operator()() ()
#6 0x00007ff8eb221d47 in thread_proxy () from /usr/lib64/libboost_thread-mt.so.5
#7 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0
#8 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6
Thread 3 (Thread 0x7ff8debfd700 (LWP 28530)):
#0 0x00007ff8ea3a2403 in poll () from /lib64/libc.so.6
#1 0x00007ff8ebb5828f in ?? () from /usr/lib64/libpq.so.5
#2 0x00007ff8ebb58310 in ?? () from /usr/lib64/libpq.so.5
#3 0x00007ff8ebb538e2 in ?? () from /usr/lib64/libpq.so.5
#4 0x00007ff8ebb5465f in PQconnectdb () from /usr/lib64/libpq.so.5
#5 0x00007ff8ebfba6b5 in DBconn::Connect(std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()
#6 0x00007ff8ebfba7c4 in DBconn::DBconn(std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()
#7 0x00007ff8ebfbc9da in DBconn::Get(std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > const&, std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()
#8 0x00007ff8ebfc7d54 in Job::Execute() ()
#9 0x00007ff8ebfcac58 in JobThread::operator()() ()
#10 0x00007ff8eb221d47 in thread_proxy () from /usr/lib64/libboost_thread-mt.so.5
#11 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0
#12 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6
Thread 2 (Thread 0x7ff8de1fc700 (LWP 28531)):
#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff8ebfbc9a7 in DBconn::Get(std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > const&, std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()
#4 0x00007ff8ebfc7d54 in Job::Execute() ()
#5 0x00007ff8ebfcac58 in JobThread::operator()() ()
#6 0x00007ff8eb221d47 in thread_proxy () from /usr/lib64/libboost_thread-mt.so.5
#7 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0
#8 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6
Thread 1 (Thread 0x7ff8ec1e07e0 (LWP 21492)):
#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0
#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3 0x00007ff8ebfbc197 in DBconn::ClearConnections(bool) ()
#4 0x00007ff8ebfd8909 in MainRestartLoop(DBconn*) ()
#5 0x00007ff8ebfd9993 in MainLoop() ()
#6 0x00007ff8ebfdd872 in main ()
[postgres@sltfjfrauxq ~]$ kill -9 21492
[postgres@sltfjfrauxq ~]$ pgagent_10 -s ./pg_agent_12_8_after_upgrade_version_upgrade.log -l 2 hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=abcd-1234
[postgres@sltfjfrauxq ~]$ psql
psql (10.5)

I have a questions about pgagent tables. There are many tables which used by pgagent.
I couldn’t find a document to describe those pgagent system tables. I want to know more detail about the tables and every column.
I want to deploy a monitor script to monitor pgagent state and every job execution result.
Some job failed with “couldn’t get connection with database” . Most of jobs could execute successfully.

Which sql statement to query result to display on the pgadmin page ? I want to use same sql to query latest pgagent jobs status.

[cid:image017.png@01D6CD8D.A032E0A0]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org>
发送时间: 2020年12月4日 17:39
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com>
抄送: pgadmin-support@postgresql.org
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Fri, Dec 4, 2020 at 9:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Ah, OK. Try running the following (assuming *only* 3.4 is installed):

CREATE EXTENSION pgagent FROM unpackaged;

That should use the pgagent--unpackaged--3.4.sql script to convert the existing 3.4 installation into an extension.

Then, you should be able to do the 4.0 upgrade by installing the package, updating the extension, and then restarting pgAgent.

[cid:image018.png@01D6CD8D.A032E0A0]

But I can’t list pgagent extension.
[cid:image019.jpg@01D6CD8D.A032E0A0]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage@pgadmin.org<mailto:dpage@pgadmin.org>>
发送时间: 2020年12月2日 21:16
收件人: Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>>
抄送: pgadmin-support@postgresql.org<mailto:pgadmin-support@postgresql.org>
主题: Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

On Wed, Dec 2, 2020 at 6:50 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

As a reference point, I installed a fresh CentOS 6.10 VM (which is somewhat less easy than it used to be, since it went EOL 2 days ago and the repos and ISOs have all been moved). I then installed postgresql10-server, ran initdb and started the server. Once that was up and running, I installed pgagent_10-3.4.0. I was able to login to Postgres and run "CREATE EXTENSION pgagent;" without any errors. I then started pgAgent to ensure it ran correctly, which it did.

Then, I ran a "yum upgrade pgagent_10", which installed the 4.0.0 release. In PostgreSQL, I ran "ALTER EXTENSION pgagent UPDATE;", which successfully upgraded the schema. I restarted pgAgent and everything looked good.

So, this doesn't appear to be a general problem, but something specific to your system. Is there anything in the process above that doesn't match with what you've tried to do?

More comments below....

[cid:image020.png@01D6CD8D.A032E0A0]

If you don't include the "-f" command line option, pgAgent will detach from the foreground and run as a background process, returning you to a shell prompt. I would suggest running ps to check you don't have a number of pgAgent processes running in the background (and if you do, kill them).

Also note that any command line options *must* come before the connection string, not after it. That would explain why the -s and -l options you've used above don't seem to do anything.

LINE 1: CREATE EXTENSION pgagent UPDATE;
^
postgres=# alter EXTENSION pgagent UPDATE;
ERROR: extension "pgagent" does not exist

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

The command output is :

My test system. All commands executed on this system.
Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.
2: install pgagent by yum command .
Yum install pgagent_10
Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.
Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.
Failed.

With what error?

7: Build and compile pgagent 4.2.
Try to upgrade pgagent. Failed.

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not trying to build it yourself at this stage.

Our production system.

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

It sounds like the extension hasn't been installed properly at the operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not exist.

[cid:image021.png@01D6CD8D.A032E0A0]

[cid:image022.png@01D6CD8D.A032E0A0]

徐志宇（Jack）
Database Engineer

DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@lenovo.com<mailto:Email%3Axuzy13@lenovo.com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com<mailto:xuzy13@lenovo.com>> wrote:
Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.
Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path
version upgrade
status
yum install pgagent_10
\i pgagent--3.4--4.0.sql
create extension pgagent;
3.4 – 4.0
failed
compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql
create extension pgagent;
3.4 – 4.2
failed.

[cid:image023.png@01D6CD8D.A032E0A0]

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s successfully upgrade by yum command.
But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Updated:
pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!
Version: 4.0.0

[cid:image024.jpg@01D6CD8D.A032E0A0]

I try to upgrade it by sql. But still on failed.

[cid:image025.png@01D6CD8D.A032E0A0]

I think the message is wrong (which I'll fix if you can confirm) - it should be:

ALTER EXTENSION pgagent UPDATE;

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#19

Dave Page

dpage@pgadmin.org

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#18)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Neel, can you help with this please? I'm not overly familiar with the
updated Boost code.

On Tue, Dec 8, 2020 at 10:23 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your help. I have upgrade to pgagent 4.0 on 12/7 with your
recommendation.

CREATE EXTENSION pgagent FROM unpackaged;

Owing to my PostgreSQL server didn’t install by yum. So I copy those file
to my extension directory.

Then create extension command work.

cp /usr/pgsql-10/share/extension/pgagent* /data/postgres/share/extension/

After I upgrade pgagent to 4.0. I test some application. It’s work normal.
Pgagent 4.0 work as expected.

But when it running almost 1 hour. It experience the problem which same
with pgagent 3.4.

I attach the logs and stack for your reference. The pgagent stop on this
function “in __lll_lock_wait ()”

The log on the attachment for your reference.

Env: PG 10.5 and OS is Centos 6.9

postgres@sltfjfrauxq ~]$ pstack 21492

Thread 4 (Thread 0x7ff8dffff700 (LWP 24059)):

#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff8ebfbbc01 in DBconn::Return() ()

#4 0x00007ff8ebfc7f32 in Job::Execute() ()

#5 0x00007ff8ebfcac58 in JobThread::operator()() ()

#6 0x00007ff8eb221d47 in thread_proxy () from
/usr/lib64/libboost_thread-mt.so.5

#7 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0

#8 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6

Thread 3 (Thread 0x7ff8debfd700 (LWP 28530)):

#0 0x00007ff8ea3a2403 in poll () from /lib64/libc.so.6

#1 0x00007ff8ebb5828f in ?? () from /usr/lib64/libpq.so.5

#2 0x00007ff8ebb58310 in ?? () from /usr/lib64/libpq.so.5

#3 0x00007ff8ebb538e2 in ?? () from /usr/lib64/libpq.so.5

#4 0x00007ff8ebb5465f in PQconnectdb () from /usr/lib64/libpq.so.5

#5 0x00007ff8ebfba6b5 in DBconn::Connect(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()

#6 0x00007ff8ebfba7c4 in DBconn::DBconn(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()

#7 0x00007ff8ebfbc9da in DBconn::Get(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&,
std::basic_string<wchar_t, std::char_traits<wchar_t>,
std::allocator<wchar_t> > const&) ()

#8 0x00007ff8ebfc7d54 in Job::Execute() ()

#9 0x00007ff8ebfcac58 in JobThread::operator()() ()

#10 0x00007ff8eb221d47 in thread_proxy () from
/usr/lib64/libboost_thread-mt.so.5

#11 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0

#12 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6

Thread 2 (Thread 0x7ff8de1fc700 (LWP 28531)):

#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff8ebfbc9a7 in DBconn::Get(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&,
std::basic_string<wchar_t, std::char_traits<wchar_t>,
std::allocator<wchar_t> > const&) ()

#4 0x00007ff8ebfc7d54 in Job::Execute() ()

#5 0x00007ff8ebfcac58 in JobThread::operator()() ()

#6 0x00007ff8eb221d47 in thread_proxy () from
/usr/lib64/libboost_thread-mt.so.5

#7 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0

#8 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6

Thread 1 (Thread 0x7ff8ec1e07e0 (LWP 21492)):

#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from /lib64/libpthread.so.0

#3 0x00007ff8ebfbc197 in DBconn::ClearConnections(bool) ()

#4 0x00007ff8ebfd8909 in MainRestartLoop(DBconn*) ()

#5 0x00007ff8ebfd9993 in MainLoop() ()

#6 0x00007ff8ebfdd872 in main ()

[postgres@sltfjfrauxq ~]$ kill -9 21492

[postgres@sltfjfrauxq ~]$ pgagent_10 -s
./pg_agent_12_8_after_upgrade_version_upgrade.log -l 2 hostaddr=127.0.0.1
port=5432 dbname=postgres user=postgres password=abcd-1234

[postgres@sltfjfrauxq ~]$ psql

psql (10.5)

I have a questions about pgagent tables. There are many tables which used
by pgagent.

I couldn’t find a document to describe those pgagent system tables. I want
to know more detail about the tables and every column.

I want to deploy a monitor script to monitor pgagent state and every job
execution result.

Some job failed with “couldn’t get connection with database” . Most of
jobs could execute successfully.

Which sql statement to query result to display on the pgadmin page ? I
want to use same sql to query latest pgagent jobs status.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月4日 17:39
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Fri, Dec 4, 2020 at 9:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

I am appreciate you help on this problem.

The production system installed by my teammate. He told me that. He
experience some problem when build and compile pgagent 3.4.0 file.

So he install it by yum resource. Then create pgagent tables by execute a
script. Look like he didn’t create pgagent extension.

On the production system. I could find those tables which used by
pgagent_10 3.4.

Ah, OK. Try running the following (assuming *only* 3.4 is installed):

CREATE EXTENSION pgagent FROM unpackaged;

That should use the pgagent--unpackaged--3.4.sql script to convert the
existing 3.4 installation into an extension.

Then, you should be able to do the 4.0 upgrade by installing the package,
updating the extension, and then restarting pgAgent.

But I can’t list pgagent extension.

Currently. There are over 30 jobs running by pgagent. Most of time pgagent
could running well.

I don’t want to re-create those jobs. I want to try to find a solution
to upgrade pgagent on this env.

If this env have unknown problem when deploy. Do you have any suggestion
to upgrade 4.0 without re-create all pgagent jobs ? Thanks

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月2日 21:16
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Wed, Dec 2, 2020 at 6:50 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

As a reference point, I installed a fresh CentOS 6.10 VM (which is
somewhat less easy than it used to be, since it went EOL 2 days ago and the
repos and ISOs have all been moved). I then installed postgresql10-server,
ran initdb and started the server. Once that was up and running, I
installed pgagent_10-3.4.0. I was able to login to Postgres and run "CREATE
EXTENSION pgagent;" without any errors. I then started pgAgent to ensure it
ran correctly, which it did.

Then, I ran a "yum upgrade pgagent_10", which installed the 4.0.0 release.
In PostgreSQL, I ran "ALTER EXTENSION pgagent UPDATE;", which successfully
upgraded the schema. I restarted pgAgent and everything looked good.

So, this doesn't appear to be a general problem, but something specific to
your system. Is there anything in the process above that doesn't match with
what you've tried to do?

More comments below....

Thanks for your update. I collect logs for your reference.

By the way. Is there any upgrade document which mentioned this scenario
? upgrade pgagent_10 3.4 to pgagent_10 4.0.

5: start pgagent process with new version pgagent.

With what error? Anything in the pgAgent log or /var/log/messages etc?

There is no errors.The pgagent process disappear after execute the
command.

On pgagent_10 3.4 version. There is a pgagent_10 process running.

If you don't include the "-f" command line option, pgAgent will detach
from the foreground and run as a background process, returning you to a
shell prompt. I would suggest running ps to check you don't have a number
of pgAgent processes running in the background (and if you do, kill them).

Also note that any command line options *must* come before the connection
string, not after it. That would explain why the -s and -l options you've
used above don't seem to do anything.

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# alter EXTENSION pgagent UPDATE;

ERROR: extension "pgagent" does not exist

That implies that pgAgent isn't installed properly. Are you using the
PostgreSQL packages from yum.postgresql.org? If not, how did you install
PostgreSQL? The pgagent_10 package is designed to work with the PostgreSQL
10 RPMs from the same yum.postgresql.org repo.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 22:27
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

The command output is :

My test system. All commands executed on this system.

Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.

2: install pgagent by yum command .

Yum install pgagent_10

Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.

Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.

Failed.

With what error?

7: Build and compile pgagent 4.2.

Try to upgrade pgagent. Failed.

[root@slbwcbnos2 ~]# ls -al /usr/pgsql-10/share/extension

total 40

drwxr-xr-x 2 root root 118 Dec 1 14:59 .

drwxr-xr-x 3 root root 23 Dec 1 13:55 ..

-rw-r--r-- 1 root root 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 root root 27657 Apr 5 2020 pgagent--4.0.sql

-rw-r--r-- 1 root root 139 Apr 5 2020 pgagent.control

-rw-r--r-- 1 root root 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest not
trying to build it yourself at this stage.

Our production system.

[root@sltfjfrauxq ~]# ls -al /usr/pgsql-10/share/extension

total 44

drwxr-xr-x 2 root root 4096 Nov 30 23:46 .

drwxr-xr-x 3 root root 4096 Nov 22 2018 ..

-rw-r--r-- 1 root root 27665 Oct 18 2017 pgagent--3.4.sql

-rw-r--r-- 1 root root 139 Oct 18 2017 pgagent.control

-rw-r--r-- 1 root root 2358 Feb 26 2014 pgagent--unpackaged--3.4.sql

[root@sltfjfrauxq ~]#

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 18:33
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

It sounds like the extension hasn't been installed properly at the
operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not
exist.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 17:48
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me that
he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path for
version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current 3.4
version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#20

Neel Patel

neel.patel@enterprisedb.com

over 5 years ago

In reply to: Dave Page (#19)

Re: [External] Re: pgadmin--pgagent---the process hang by unknow reasons

Sure Dave. I will check and update.

On Tue, Dec 8, 2020 at 4:39 PM Dave Page <dpage@pgadmin.org> wrote:

Show quoted text

Neel, can you help with this please? I'm not overly familiar with the
updated Boost code.

On Tue, Dec 8, 2020 at 10:23 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your help. I have upgrade to pgagent 4.0 on 12/7 with your
recommendation.

CREATE EXTENSION pgagent FROM unpackaged;

Owing to my PostgreSQL server didn’t install by yum. So I copy those file
to my extension directory.

Then create extension command work.

cp /usr/pgsql-10/share/extension/pgagent* /data/postgres/share/extension/

After I upgrade pgagent to 4.0. I test some application. It’s work
normal. Pgagent 4.0 work as expected.

But when it running almost 1 hour. It experience the problem which same
with pgagent 3.4.

I attach the logs and stack for your reference. The pgagent stop on this
function “in __lll_lock_wait ()”

The log on the attachment for your reference.

Env: PG 10.5 and OS is Centos 6.9

postgres@sltfjfrauxq ~]$ pstack 21492

Thread 4 (Thread 0x7ff8dffff700 (LWP 24059)):

#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from
/lib64/libpthread.so.0

#3 0x00007ff8ebfbbc01 in DBconn::Return() ()

#4 0x00007ff8ebfc7f32 in Job::Execute() ()

#5 0x00007ff8ebfcac58 in JobThread::operator()() ()

#6 0x00007ff8eb221d47 in thread_proxy () from
/usr/lib64/libboost_thread-mt.so.5

#7 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0

#8 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6

Thread 3 (Thread 0x7ff8debfd700 (LWP 28530)):

#0 0x00007ff8ea3a2403 in poll () from /lib64/libc.so.6

#1 0x00007ff8ebb5828f in ?? () from /usr/lib64/libpq.so.5

#2 0x00007ff8ebb58310 in ?? () from /usr/lib64/libpq.so.5

#3 0x00007ff8ebb538e2 in ?? () from /usr/lib64/libpq.so.5

#4 0x00007ff8ebb5465f in PQconnectdb () from /usr/lib64/libpq.so.5

#5 0x00007ff8ebfba6b5 in DBconn::Connect(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()

#6 0x00007ff8ebfba7c4 in DBconn::DBconn(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&) ()

#7 0x00007ff8ebfbc9da in DBconn::Get(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&,
std::basic_string<wchar_t, std::char_traits<wchar_t>,
std::allocator<wchar_t> > const&) ()

#8 0x00007ff8ebfc7d54 in Job::Execute() ()

#9 0x00007ff8ebfcac58 in JobThread::operator()() ()

#10 0x00007ff8eb221d47 in thread_proxy () from
/usr/lib64/libboost_thread-mt.so.5

#11 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0

#12 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6

Thread 2 (Thread 0x7ff8de1fc700 (LWP 28531)):

#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from
/lib64/libpthread.so.0

#3 0x00007ff8ebfbc9a7 in DBconn::Get(std::basic_string<wchar_t,
std::char_traits<wchar_t>, std::allocator<wchar_t> > const&,
std::basic_string<wchar_t, std::char_traits<wchar_t>,
std::allocator<wchar_t> > const&) ()

#4 0x00007ff8ebfc7d54 in Job::Execute() ()

#5 0x00007ff8ebfcac58 in JobThread::operator()() ()

#6 0x00007ff8eb221d47 in thread_proxy () from
/usr/lib64/libboost_thread-mt.so.5

#7 0x00007ff8ea65eaa1 in start_thread () from /lib64/libpthread.so.0

#8 0x00007ff8ea3abc4d in clone () from /lib64/libc.so.6

Thread 1 (Thread 0x7ff8ec1e07e0 (LWP 21492)):

#0 0x00007ff8ea665334 in __lll_lock_wait () from /lib64/libpthread.so.0

#1 0x00007ff8ea6605d8 in _L_lock_854 () from /lib64/libpthread.so.0

#2 0x00007ff8ea6604a7 in pthread_mutex_lock () from
/lib64/libpthread.so.0

#3 0x00007ff8ebfbc197 in DBconn::ClearConnections(bool) ()

#4 0x00007ff8ebfd8909 in MainRestartLoop(DBconn*) ()

#5 0x00007ff8ebfd9993 in MainLoop() ()

#6 0x00007ff8ebfdd872 in main ()

[postgres@sltfjfrauxq ~]$ kill -9 21492

[postgres@sltfjfrauxq ~]$ pgagent_10 -s
./pg_agent_12_8_after_upgrade_version_upgrade.log -l 2 hostaddr=127.0.0.1
port=5432 dbname=postgres user=postgres password=abcd-1234

[postgres@sltfjfrauxq ~]$ psql

psql (10.5)

I have a questions about pgagent tables. There are many tables which used
by pgagent.

I couldn’t find a document to describe those pgagent system tables. I
want to know more detail about the tables and every column.

I want to deploy a monitor script to monitor pgagent state and every job
execution result.

Some job failed with “couldn’t get connection with database” . Most of
jobs could execute successfully.

Which sql statement to query result to display on the pgadmin page ? I
want to use same sql to query latest pgagent jobs status.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月4日 17:39
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Fri, Dec 4, 2020 at 9:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

I am appreciate you help on this problem.

The production system installed by my teammate. He told me that. He
experience some problem when build and compile pgagent 3.4.0 file.

So he install it by yum resource. Then create pgagent tables by execute
a script. Look like he didn’t create pgagent extension.

On the production system. I could find those tables which used by
pgagent_10 3.4.

Ah, OK. Try running the following (assuming *only* 3.4 is installed):

CREATE EXTENSION pgagent FROM unpackaged;

That should use the pgagent--unpackaged--3.4.sql script to convert the
existing 3.4 installation into an extension.

Then, you should be able to do the 4.0 upgrade by installing the package,
updating the extension, and then restarting pgAgent.

But I can’t list pgagent extension.

Currently. There are over 30 jobs running by pgagent. Most of time
pgagent could running well.

I don’t want to re-create those jobs. I want to try to find a solution
to upgrade pgagent on this env.

If this env have unknown problem when deploy. Do you have any
suggestion to upgrade 4.0 without re-create all pgagent jobs ? Thanks

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月2日 21:16
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Wed, Dec 2, 2020 at 6:50 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

As a reference point, I installed a fresh CentOS 6.10 VM (which is
somewhat less easy than it used to be, since it went EOL 2 days ago and the
repos and ISOs have all been moved). I then installed postgresql10-server,
ran initdb and started the server. Once that was up and running, I
installed pgagent_10-3.4.0. I was able to login to Postgres and run "CREATE
EXTENSION pgagent;" without any errors. I then started pgAgent to ensure it
ran correctly, which it did.

Then, I ran a "yum upgrade pgagent_10", which installed the 4.0.0
release. In PostgreSQL, I ran "ALTER EXTENSION pgagent UPDATE;", which
successfully upgraded the schema. I restarted pgAgent and everything looked
good.

So, this doesn't appear to be a general problem, but something
specific to your system. Is there anything in the process above that
doesn't match with what you've tried to do?

More comments below....

Thanks for your update. I collect logs for your reference.

By the way. Is there any upgrade document which mentioned this
scenario ? upgrade pgagent_10 3.4 to pgagent_10 4.0.

5: start pgagent process with new version pgagent.

With what error? Anything in the pgAgent log or /var/log/messages etc?

There is no errors.The pgagent process disappear after execute the
command.

On pgagent_10 3.4 version. There is a pgagent_10 process running.

If you don't include the "-f" command line option, pgAgent will detach
from the foreground and run as a background process, returning you to a
shell prompt. I would suggest running ps to check you don't have a number
of pgAgent processes running in the background (and if you do, kill them).

Also note that any command line options *must* come before the connection
string, not after it. That would explain why the -s and -l options you've
used above don't seem to do anything.

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# alter EXTENSION pgagent UPDATE;

ERROR: extension "pgagent" does not exist

That implies that pgAgent isn't installed properly. Are you using the
PostgreSQL packages from yum.postgresql.org? If not, how did you install
PostgreSQL? The pgagent_10 package is designed to work with the PostgreSQL
10 RPMs from the same yum.postgresql.org repo.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 22:27
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

The command output is :

My test system. All commands executed on this system.

Before upgrade pgagent. I execute follow in steps.

1: backup and restore production database.

2: install pgagent by yum command .

Yum install pgagent_10

Yum downgrade pgagent_10. (Then pgagent backup to 3.4 )

3: start pgagent process. Make sure pgagent running on normal state.

4: execute yum upgrade command to pgagent 4.0

5: start pgagent process with new version pgagent.

Failed.

With what error? Anything in the pgAgent log or /var/log/messages etc?

6: try to upgrade by the upgrade script.

Failed.

With what error?

7: Build and compile pgagent 4.2.

Try to upgrade pgagent. Failed.

[root@slbwcbnos2 ~]# ls -al /usr/pgsql-10/share/extension

total 40

drwxr-xr-x 2 root root 118 Dec 1 14:59 .

drwxr-xr-x 3 root root 23 Dec 1 13:55 ..

-rw-r--r-- 1 root root 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 root root 27657 Apr 5 2020 pgagent--4.0.sql

-rw-r--r-- 1 root root 139 Apr 5 2020 pgagent.control

-rw-r--r-- 1 root root 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

That indicates that pgAgent 4.0 is installed, not 4.2. I would suggest
not trying to build it yourself at this stage.

Our production system.

[root@sltfjfrauxq ~]# ls -al /usr/pgsql-10/share/extension

total 44

drwxr-xr-x 2 root root 4096 Nov 30 23:46 .

drwxr-xr-x 3 root root 4096 Nov 22 2018 ..

-rw-r--r-- 1 root root 27665 Oct 18 2017 pgagent--3.4.sql

-rw-r--r-- 1 root root 139 Oct 18 2017 pgagent.control

-rw-r--r-- 1 root root 2358 Feb 26 2014 pgagent--unpackaged--3.4.sql

[root@sltfjfrauxq ~]#

Right - that looks like 3.4 is installed, as expected.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 18:33
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

It sounds like the extension hasn't been installed properly at the
operating system level - what is the output from:

ls -al /usr/pgsql-10/share/extension

On Tue, Dec 1, 2020 at 10:00 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your quick response.

I try this command on 4.0 and 4.2. I got a warning that the extension not
exist.

徐志宇（Jack）

Database Engineer

DB Team,ITS. Lenovo China

Phone: 86-18910860709

Email:xuzy13@lenovo.com

No.6 Shangdi West Road, Haidian District Beijing, China, 100085

*发件人:* Dave Page <dpage@pgadmin.org>
*发送时间:* 2020年12月1日 17:48
*收件人:* Zhiyu ZY13 Xu <xuzy13@lenovo.com>
*抄送:* pgadmin-support@postgresql.org
*主题:* Re: [External] Re: pgadmin--pgagent---the process hang by unknow
reasons

Hi

On Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <xuzy13@lenovo.com> wrote:

Hi Dave

Thanks for your update. I try to upgrade pgagent_10-3.4.0. But failed.

Would you like to help me narrow down this upgrade issue ？ Thanks

upgrade path

version upgrade

status

yum install pgagent_10

\i pgagent--3.4--4.0.sql

create extension pgagent;

3.4 – 4.0

failed

compile and build. (install cmake and Boost)

\i pgagent--3.4--4.2.sql

create extension pgagent;

3.4 – 4.2

failed.

Currently pgagent_10 3.4.0 env installed by my teammate. He told me
that he install it by yum command and execute a script.

Then those pgagent tables created by this script. There is no extension
exist. But those pgagent table exist.

Then I try upgrade pgagent_10 3.40 to 4.0 by yum command. It’s
successfully upgrade by yum command.

But the pgagent_10 process unable start.

[root@sltfjfrauxq ~]# yum upgrade pgagent_10

Total download size: 138 k

Is this ok [y/N]: y

Downloading Packages:

pgagent_10-4.0.0-4.rhel6.x86_64.rpm
| 138 kB 00:08

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Updating :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Cleanup :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Verifying :
pgagent_10-4.0.0-4.rhel6.x86_64
1/2

Verifying :
pgagent_10-3.4.0-10.rhel6.x86_64
2/2

Updated:

pgagent_10.x86_64 0:4.0.0-4.rhel6

Complete!

Version: 4.0.0

The 4.0 pgagent start logs.

Tue Dec 1 13:57:53 2020 DEBUG: Creating primary connection

Tue Dec 1 13:57:53 2020 DEBUG: Parsing connection information...

Tue Dec 1 13:57:53 2020 DEBUG: user: postgres

Tue Dec 1 13:57:53 2020 DEBUG: password: *****

Tue Dec 1 13:57:53 2020 DEBUG: dbname: postgres

Tue Dec 1 13:57:53 2020 DEBUG: hostaddr: 127.0.0.1

Tue Dec 1 13:57:53 2020 DEBUG: port: 5432

Tue Dec 1 13:57:53 2020 DEBUG: Creating DB connection: user=postgres
password=abcd-1234 hostaddr=127.0.0.1 port=5432 dbname=postgres

Tue Dec 1 13:57:53 2020 DEBUG: Database sanity check

I try to upgrade it by sql. But still on failed.

[root@slbwcbnos2 sql]# pwd

/data/postgres/new_package/pgAgent-4.0.0-Source/sql

[root@slbwcbnos2 sql]# ls -al

total 36

drwxr-xr-x 2 501 games 90 Dec 1 14:25 .

drwxr-xr-x 6 501 games 298 Jul 12 2018 ..

-rw-r--r-- 1 501 games 538 Jul 12 2018 pgagent--3.4--4.0.sql

-rw-r--r-- 1 501 games 27772 Jul 12 2018 pgagent.sql

-rw-r--r-- 1 501 games 2603 Jul 12 2018 pgagent--unpackaged--4.0.sql

postgres=# \i pgagent--3.4--4.0.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: extension "pgagent" has no installation script nor update path
for version "3.4"

postgres=#

After failed. I try to build and compile the latest version 4.2.

Build and install successfully. But I still on unable upgrade current
3.4 version.

I could create extension successfully on a new database.

postgres=# \i pgagent--3.4--4.2.sql

Use "CREATE EXTENSION pgagent UPDATE" to load this file.

postgres=# CREATE EXTENSION pgagent UPDATE;

ERROR: syntax error at or near "UPDATE"

LINE 1: CREATE EXTENSION pgagent UPDATE;

^

postgres=# CREATE EXTENSION pgagent ;

ERROR: relation "pga_jobagent" already exists

postgres=#

I think the message is wrong (which I'll fix if you can confirm) - it
should be:

ALTER EXTENSION pgagent UPDATE;

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

#21

Neel Patel

neel.patel@enterprisedb.com

over 5 years ago

In reply to: Neel Patel (#20)

#22

Neel Patel

neel.patel@enterprisedb.com

over 5 years ago

In reply to: Zhiyu ZY13 Xu (#1)

#23

Zhiyu ZY13 Xu

xuzy13@lenovo.com

over 5 years ago

In reply to: Neel Patel (#22)

pgadmin--pgagent---the process hang by unknow reasons

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments:

Attachments: