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