How to upgrade PostgreSql 9.2 to PostgreSql 9.6

[IdleServ]

Just upgraded from PostgreSQL 9.6 to 10.3 on CloudLinux 6.9 but cPanel seems to only partially work with it.

In cPanel, all of the PostgreSQL links have gone.

But in WHM, its still visible such as the Configure PostgreSQL page and its listed in the Service Manager.

However, if I run Install Postgres Config I get this error:
Failed to determine postgresql version: psql (PostgreSQL) 10.3

All symlinks I had in place with 9.6 have been updated to the 10.3 paths.

Any solutions?

 

[cPanelLauren]

Hello,

At WHM>>Packages>>Feature Manger>>Edit Feature List -> select feature list assigned to one of the account's packages. Do you see PostgreSQL checked as included in the feature list?

Thank you,

 

[IdleServ]

Yup PostgreSQL still enabled in the feature list.

 

[cPanelLauren]

Thank you for that, I re-read and the config error indicates that the system is not seeing it.

Can you tell me which PostGreSQL rpm's you have installed?

rpm -qa |grep post

and how specifically you installed it?

 

[IdleServ]

Here's whats installed at the moment (I haven't removed the 9.6 RPMs yet):

cpanel-postgresql-libs-9.0.18-1.cp1156.x86_64
postgresql10-10.3-1PGDG.rhel6.x86_64
cpanel-postgresql-9.0.18-1.cp1156.x86_64
postgresql96-9.6.8-1PGDG.rhel6.x86_64
postgresql10-devel-10.3-1PGDG.rhel6.x86_64
postgresql96-server-9.6.8-1PGDG.rhel6.x86_64
postgresql96-libs-9.6.8-1PGDG.rhel6.x86_64
postgresql10-server-10.3-1PGDG.rhel6.x86_64
postgresql96-devel-9.6.8-1PGDG.rhel6.x86_64
postgresql10-libs-10.3-1PGDG.rhel6.x86_64

The RPMs are provided by download.postgresql.org.

These are the steps taken to upgrade:

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-x86_64/pgdg-centos10-10-2.noarch.rpm

yum install postgresql10 postgresql10-devel postgresql10-libs postgresql10-server

(if upgrading from system packages)
yum remove postgresql postgresql-devel postgresql-libs postgresql-server

mv /var/lib/pgsql/.bash_profile.rpmsave /var/lib/pgsql/.bash_profile

ln -sfn /etc/init.d/postgresql-10 /etc/init.d/postgresql
service postgresql initdb
chkconfig postgresql on

cd /var/lib/pgsql; ln -sfn 10/backups; ln -sfn 10/data; ln -sfn 10/pgstartup.log

cd /usr/bin
ln -sfn /usr/pgsql-10/bin/postgres
ln -sfn /usr/pgsql-10/bin/postmaster
ln -sfn /usr/pgsql-10/bin/pg_config
ln -sfn /usr/pgsql-10/bin/pg_ctl
ln -sfn /etc/alternatives/pgsql-pg_dump pg_dump
ln -sfn /etc/alternatives/pgsql-pg_dumpall pg_dumpall
ln -sfn /etc/alternatives/pgsql-pg_restore pg_restore
ln -sfn /etc/alternatives/pgsql-psql psql

These are the same steps used when we upgraded to 9.6 which worked fine. Maybe cPanel has an issue with parsing the new version number?

 

[cPanelLauren]

Hello,


I see, cPanel only provides the RPM's that ship with the OS

cPanel Inc. does not supply PostgreSQL or provide RPMs to update PostgreSQL to the latest version.

what version is being noted in

/var/lib/pgsql/data/

 

[IdleServ]

/var/lib/pgsql/data/PG_VERSION contains 10

 

[IdleServ]

I've been digging around the cPanel scripts and found the possible cause.

/usr/local/cpanel/bin/build_global_cache:

Building global cache for cpanel...Use of uninitialized value $pmajor in numeric lt (<) at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 65.
Use of uninitialized value $pmajor in concatenation (.) or string at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 66.
Use of uninitialized value $pminor in concatenation (.) or string at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 66.
Done

/usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm is expecting a version number with in the format of x.x.x but the current version is only 10.3.

So previously postmaster --version was outputting

postgres (PostgreSQL) 9.6.8

But its now giving

postgres (PostgreSQL) 10.3

 

[IdleServ]

I hacked /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm so it gets the version number. This brought the links back in cPanel.

However, they report: The PostgreSQL server is currently offline.

The pages in WHM also still fail.

Seems to be an error in the PING command cPanel is using:

warn [cpanel] Cpanel::Wrap::send_cpwrapd_request The adminbin “postgres” in the “Cpanel” namespace call to function “PING” ended prematurely: The subprocess reported error number 1 when it ended.: namespace=[Cpanel] module=[postgres] function=[PING]: set error in context : raw_response=[{"version":"2.4","timeout":0,"error":1,"exit_code":256,"data":"","statusmsg":"The adminbin “postgres” in the “Cpanel” namespace call to function “PING” ended prematurely: The subprocess reported error number 1 when it ended.","status":1,"action":"run","mode":"simple"}] at /usr/local/cpanel/Cpanel/Wrap.pm line 120, <$socket> line 1.

Cpanel::Wrap::send_cpwrapd_request("namespace", "Cpanel", "module", "postgres", "function", "PING", "data", "", ...) called at /usr/local/cpanel/Cpanel/AdminBin.pm line 58

Cpanel::AdminBin::adminrun("postgres", "PING") called at /usr/local/cpanel/Cpanel/ExpVar/Utils.pm line 39

Cpanel::ExpVar::Utils::chomped_adminrun("postgres", "PING") called at /usr/local/cpanel/Cpanel/ExpVar/MultiPass.pm line 431

Cpanel::ExpVar::MultiPass::__ANON__(undef) called at /usr/local/cpanel/Cpanel/ExpVar/MultiPass.pm line 503

Cpanel::ExpVar::MultiPass::expand(HASH(0x3963ae8), undef) called at /usr/local/cpanel/Cpanel/ExpVar.pm line 178

Cpanel::ExpVar::expvar("\$pgrunning") called at /usr/local/cpanel/base/frontend/paper_lantern/psql/wizard1.html.tt line 61

eval {...} called at /usr/local/cpanel/base/frontend/paper_lantern/psql/wizard1.html.tt line 61

eval {...} called at /usr/local/cpanel/base/frontend/paper_lantern/psql/wizard1.html.tt line 16

Template::Provider::__ANON__(Template::Context=HASH(0x38c86c0)) called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template/Document.pm line 163

eval {...} called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template/Document.pm line 161

Template::Document::process(Template::Document=HASH(0x395d220), Template::Context=HASH(0x38c86c0)) called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template/Context.pm line 351

eval {...} called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template/Context.pm line 321

Template::Context::process(Template::Context=HASH(0x38c86c0), Template::Document=HASH(0x395d220)) called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template/Service.pm line 94

eval {...} called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template/Service.pm line 91

Template::Service::process(Template::Service=HASH(0x38c8288), "/usr/local/cpanel/base/frontend/paper_lantern/psql/wizard1.ht"..., HASH(0x19eb7d8)) called at /usr/local/cpanel/3rdparty/perl/524/lib64/perl5/cpanel_lib/x86_64-linux-64int/Template.pm line 64

Template::process(Template=HASH(0x38c7f10), "/usr/local/cpanel/base/frontend/paper_lantern/psql/wizard1.ht"..., HASH(0x19eb7d8), SCALAR(0x1b06618)) called at /usr/local/cpanel/Cpanel/Template.pm line 524

Cpanel::Template::process_template("cpanel", HASH(0x19eb7d8), HASH(0x1af28a8)) called at cpanel.pl line 1027

cpanel::cpanel::cptt_exectag("/usr/local/cpanel/base/frontend/paper_lantern/psql/wizard1.ht"..., 1) called at cpanel.pl line 4632

cpanel::cpanel::run_standard_mode() called at cpanel.pl line 860

cpanel::cpanel::script("cpanel::cpanel", "./frontend/paper_lantern/psql/wizard1.html.tt") called at cpanel.pl line 275

warn [cpanel] Cpanel::Wrap::send_cpwrapd_request The adminbin “postgres” in the “Cpanel” namespace call to function “PING” ended prematurely: The subprocess reported error number 1 when it ended.: namespace=[Cpanel] module=[postgres] function=[PING]: set error in context : raw_response=[{"version":"2.4","timeout":0,"error":1,"exit_code":256,"data":"","statusmsg":"The adminbin “postgres” in the “Cpanel” namespace call to function “PING” ended prematurely: The subprocess reported error number 1 when it ended.","status":1,"action":"run","mode":"simple"}]

Are the devs unlikely to fix this due to not supporting non system version of postgres?

 

[cPanelLauren]

Hi @IdleServ

I had actually been trying the same thing in hopes of finding a suitable workaround for you. Unfortunately, because we only support installing the version that is shipped with the OS at this time, this wouldn't be something that our development would be able to resolve.

I did find another thread that is similar to yours (maybe what they did will help you) though they weren't on v10.x

installpostgres script never runs as intended

We also have a feature request I would encourage you to vote on for this - based on the comments there it looks there's not enough community interest in this yet:
Improved PostgreSQL support

Thank you,

 

[IdleServ]

Thank you. Looks like we'll have to stick with 9.6 for now.

 

[cPanelLauren]

In the meantime please do vote on that feature request if you can!


Thank you,

 

[IdleServ]

I have, thanks.

I've downgraded back to 9.6 but cPanel seems to have cached the 10.3 version value that is obtained by
Cpanel::GlobalCache::cachedcommand( 'cpanel', $postmaster, '--version' );

Any ideas on how to clear the cache?

 

[IdleServ]

Found the cache, I cleared /var/cpanel/globalcache/cpanel.cache

Yet running /usr/local/cpanel/bin/build_global_cache still produces the 10.3 error despite it not existing on the server anymore.

Running /usr/bin/postmaster --version does give:
postgres (PostgreSQL) 9.6.8

 

[cPanelLauren]

Hello,

Does rebuilding the global cache help?

/usr/local/cpanel/bin/build_global_cache

 

[cPanelLauren]

It looks like we're on the same track!

Whats the output you get when you run:

/usr/local/cpanel/bin/build_global_cache

If you run

/usr/local/cpanel/bin/cpsessetup

first is it different?

Thank you,

 

[IdleServ]

Both produce the same error:

# /usr/local/cpanel/bin/build_global_cache
Building global cache for cpanel...Use of uninitialized value $pmajor in numeric lt (<) at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 65.
Use of uninitialized value $pmajor in concatenation (.) or string at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 66.
Use of uninitialized value $pminor in concatenation (.) or string at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 66.
Done

# /usr/local/cpanel/bin/cpsessetup
Use of uninitialized value $pmajor in numeric lt (<) at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 65.
Use of uninitialized value $pmajor in concatenation (.) or string at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 66.
Use of uninitialized value $pminor in concatenation (.) or string at /usr/local/cpanel/Cpanel/PostgresAdmin/Check.pm line 66.

 

[cPanelLauren]

which is:

52     # Determine if PostgreSQL is installed
     53     my $psql_bin   = Cpanel::DbUtils::find_psql();
     54     my $postmaster = Cpanel::DbUtils::find_postmaster();
     55     if ( !$psql_bin || !$postmaster ) {
     56         return { 'status' => 0, 'message' => 'PostgreSQL is not installed. Could not locate executable psql client or postmaster daemon.' };
     57     }
     58
     59     my $short_version;
     60     my $psqlversion = Cpanel::GlobalCache::cachedcommand( 'cpanel', $postmaster, '--version' );
     61     if ($psqlversion) {
     62         $psqlversion =~ m/(\d+)\.(\d+)\.\d+/;
     63         my $pmajor = $1;
     64         my $pminor = $2;
     65         if ( $pmajor < 7 || ( $pmajor == 7 && $pminor < 3 ) ) {
     66             return { 'status' => 0, 'message' => "PostgreSQL version is not sufficient. Version $pmajor.$pminor is not supported." };
     67         }
     68         $short_version = $pmajor . '.' . $pminor;
     69     }
     70     else {
     71         return { 'status' => 0, 'message' => 'Failed to determine PostgreSQL version from server.' };

Can you open a ticket using the link in my signature so we can take a closer look?


Thank you,

 

[IdleServ]

Fixed it.

Had to clear /root/.cpanel/datastore/ then run /usr/local/cpanel/bin/build_global_cache twice.

Thanks!

 

[cPanelLauren]

Hi @IdleServ


I'm glad to hear that! Thank you for providing the solution as well.