答复: [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(dot)png(at)01D6CA5F(dot)790F7B90]

But I can’t list pgagent extension.
[cid:image016(dot)png(at)01D6CA5F(dot)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(at)lenovo(dot)com
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage(at)pgadmin(dot)org>
发送时间: 2020年12月2日 21:16
收件人: Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com>
抄送: pgadmin-support(at)postgresql(dot)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(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)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.

[cid:image017(dot)png(at)01D6CA5F(dot)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(at)lenovo(dot)com<mailto:Email%3Axuzy13(at)lenovo(dot)com>
No.6 Shangdi West Road, Haidian District Beijing, China, 100085

发件人: Dave Page <dpage(at)pgadmin(dot)org<mailto:dpage(at)pgadmin(dot)org>>
发送时间: 2020年12月1日 22:27
收件人: Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)com>>
抄送: pgadmin-support(at)postgresql(dot)org<mailto:pgadmin-support(at)postgresql(dot)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(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)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(at)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(at)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(at)sltfjfrauxq ~]#

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

徐志宇（Jack）
Database Engineer

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

发件人: Dave Page <dpage(at)pgadmin(dot)org<mailto:dpage(at)pgadmin(dot)org>>
发送时间: 2020年12月1日 18:33
收件人: Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)com>>
抄送: pgadmin-support(at)postgresql(dot)org<mailto:pgadmin-support(at)postgresql(dot)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(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)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(dot)png(at)01D6CA5F(dot)790F7B90]

[cid:image019(dot)png(at)01D6CA5F(dot)790F7B90]

徐志宇（Jack）
Database Engineer

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

发件人: Dave Page <dpage(at)pgadmin(dot)org<mailto:dpage(at)pgadmin(dot)org>>
发送时间: 2020年12月1日 17:48
收件人: Zhiyu ZY13 Xu <xuzy13(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)com>>
抄送: pgadmin-support(at)postgresql(dot)org<mailto:pgadmin-support(at)postgresql(dot)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(at)lenovo(dot)com<mailto:xuzy13(at)lenovo(dot)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.

[cid:image020(dot)png(at)01D6CA5F(dot)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(at)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:image021(dot)jpg(at)01D6CA5F(dot)790F7B90]

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(at)slbwcbnos2 sql]# pwd
/data/postgres/new_package/pgAgent-4.0.0-Source/sql
[root(at)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:image022(dot)png(at)01D6CA5F(dot)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