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(at)lenovo(dot)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(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> 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(at)lenovo(dot)com
>
> No.6 Shangdi West Road, Haidian District Beijing, China, 100085
>
>
>
> *发件人:* Dave Page <dpage(at)pgadmin(dot)org>
> *发送时间:* 2020年12月1日 22:27
> *收件人:* 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 Tue, Dec 1, 2020 at 12:38 PM Zhiyu ZY13 Xu <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
>
> No.6 Shangdi West Road, Haidian District Beijing, China, 100085
>
>
>
> *发件人:* Dave Page <dpage(at)pgadmin(dot)org>
> *发送时间:* 2020年12月1日 18:33
> *收件人:* 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
>
>
>
> 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> 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(at)lenovo(dot)com
>
> No.6 Shangdi West Road, Haidian District Beijing, China, 100085
>
>
>
> *发件人:* Dave Page <dpage(at)pgadmin(dot)org>
> *发送时间:* 2020年12月1日 17:48
> *收件人:* 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 Tue, Dec 1, 2020 at 9:17 AM Zhiyu ZY13 Xu <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.
>
>
>
>
>
> 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
>
>
>
>
>
>
>
> 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=#
>
>
>
>
>
>
>
>
>
> 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