PostgreSQL 9.2 to 9.5 Upgrade

[L1049H]

I'm running WHM current version 11.54.0.19 on a Centos 7 (1511) server and need PostgreSQL databases. I initially installed 9.5 put that did not work so I removed it and ran the cPanel installpostgres script which installs the default version for the distribution - 9.2. Unfortunately 9.2 is fairly old and starting with 9.3 many features were added that really are useful. I decided to try and install 9.5 and get it working with cPanel. The attached file documents how I got it to work and is here in the hopes it will help others. I am not a cPanel expert at all, just a user.

From a user cPanel login all the Postgres functions work such as Database Wizard, Maintenance, and phpPGAdmin. On the WHM side the configure postgres option is available but says the server is not running but since everything works I'm ignoring that although I would like to know what WHM is looking for. Given that things work from the cPanel side I can use that to create databases and users and if I need to I can use the server's command line since I have root access.

 

[cPanelMichael]

Hello

I am happy to see you were able to install it successfully. I've moved this thread to our "Workarounds" forum.

Thank you.

 

[L1049H]

Okay. I didn't realize that's where it should go. Thanks.

 

[L1049H]

Worked great till today. Then cPanel for some reason no longer sees postgresql databases. They exist, cPanel just doesn't see them - doesn't show any of the tools such as database wizard, database tools, phpPGAdmin in the cPanel user apps. It shouldn't be that hard to support current Postgresql stuff.

 

[cPanelMichael]

Worked great till today. Then cPanel for some reason no longer sees postgresql databases. They exist, cPanel just doesn't see them - doesn't show any of the tools such as database wizard, database tools, phpPGAdmin in the cPanel user apps. It shouldn't be that hard to support current Postgresql stuff.

Could you verify which version of cPanel is installed on your system? cPanel version 56 includes a resolution to address an issue that led to missing PostgreSQL options in cPanel.

Fixed case CPANEL-4699: Ensure global cache can be written.

Thank you.

 

[L1049H]

It is

WHM 54.0 (build 21)
cPanel 54.0.21

You suggest an upgrade to 56 then?

 

[cPanelMichael]

cPanel version 56 is still only released to the "Edge" build tier, so it's not intended for production servers. Do you have a test server that you use for implementing and testing your custom workaround? If so, that's the server you can install the "Edge" tier to for testing purposes. Version 56 is scheduled for release to the "Current" build tier next week, but that's only a tentative release date at this time.

Thank you.

 

[L1049H]

Not at this time. I'd like to set up a test environment on my local network eventually if I can find the proper pricing and figure out the virtual vs other stuff.

At this point I'll stick with my current version and wait until sometime after the final release (let others test it <G>). For Postgresql I've abandoned using cPanel interfaces and am doing it via the server command line. It's not that hard to do. After responding to you I connected to the cPanel for the user and the Postgres items showed up but then later disappeared.

Can you tell me why the cPanel Docs (PostgreSQL Databases - 11.48 Documentation - cPanel Documentation) say we should use the database wizard to setup databases? It says we aren't supposed to use phpPGAdmin to do this but that's the alternative tool used to administer databases in lieu of the command line.

In addition it appears cPanel can not see databases created from the command line? I would think it should be able to do so.

Thanks.

 

[cPanelMichael]

Can you tell me why the cPanel Docs (PostgreSQL Databases - 11.48 Documentation - cPanel Documentation) say we should use the database wizard to setup databases? It says we aren't supposed to use phpPGAdmin to do this but that's the alternative tool used to administer databases in lieu of the command line.

In addition it appears cPanel can not see databases created from the command line? I would think it should be able to do so.

cPanel maps databases to the account when you add it via a supported method. This does not happen when using phpPGAdmin. You can still add databases via the command line, however you should utilize the following cPanel UAPI function:

UAPI Functions - Postgresql::create_database - Software Development Kit - cPanel Documentation

A guide on using the cPanel UAPI is found at:

Guide to UAPI - Software Development Kit - cPanel Documentation

Thank you.

 

[L1049H]

Thanks. I'll look up the UAPI functions.

If I understand correctly basically the cPanel part simply maps a user to a database and from what I've seen it creates a role with the same name as the database which cannot login. Then it takes the user who can login and assigns it to the role (i.e. in older Postgress terminology it makes it a member of a that role). Since cPanel setups up hba_pg.conf to have samerole access to the database anyone who is a member of the group/role that has the same name as the database is allowed access assuming it comes from the proper IP address. If this is correct then I should be able to do the same myself via the command line which is what I did. However, I'll dig into the UAPI documentation you referenced.

Thank you for the information. I'll look through it.

 

[cPanelMichael]

If I understand correctly basically the cPanel part simply maps a user to a database and from what I've seen it creates a role with the same name as the database which cannot login. Then it takes the user who can login and assigns it to the role (i.e. in older Postgress terminology it makes it a member of a that role). Since cPanel setups up hba_pg.conf to have samerole access to the database anyone who is a member of the group/role that has the same name as the database is allowed access assuming it comes from the proper IP address. If this is correct then I should be able to do the same myself via the command line which is what I did. However, I'll dig into the UAPI documentation you referenced.

There's also a thread here that you may find helpful:

Roles, Privileges, and Templates in PostgreSQL

Thank you.

 

[L1049H]

Thanks. I'm familiar with that. Basically I did what cPanel does via the command line by creating the group - actually role now <G> - with no login and the access rights and then created a user for my particular database. I created a separate user since I didn't want to use my cPanel user for the user on the database. I then added the user to the role and it works. For what I'm doing (Drupal 8) that works. Drupal sees and setup the database when I installed Drupal and accesses it without problems. It's only cPanel that can't see it. Maybe one day that will get fixed but until then I'll do the admin from the command line. I put my comments on the Feature Request thread requesting cPanel to seriously support PostgreSQL by supporting newer versions and perhaps get cPanel out of the mix except to offer the database/user setup options and phpPGAdmin via cPanel. cPanel needs a simpler way of setting up PostgreSQL instead of the script and it should work with the newer current versions. It's encouraging that EasyApache 4 is trying to leverage the server and yum much more - let the server os do what it's good at and keep cPanel interferance to a minimum - so maybe that will happen with other apps.

Thanks for the information on the UAPI. I'll keep it bookmarked.

 

[julianodls]

I'm running WHM current version 11.54.0.19 on a Centos 7 (1511) server and need PostgreSQL databases. I initially installed 9.5 put that did not work so I removed it and ran the cPanel installpostgres script which installs the default version for the distribution - 9.2. Unfortunately 9.2 is fairly old and starting with 9.3 many features were added that really are useful. I decided to try and install 9.5 and get it working with cPanel. The attached file documents how I got it to work and is here in the hopes it will help others. I am not a cPanel expert at all, just a user.

From a user cPanel login all the Postgres functions work such as Database Wizard, Maintenance, and phpPGAdmin. On the WHM side the configure postgres option is available but says the server is not running but since everything works I'm ignoring that although I would like to know what WHM is looking for. Given that things work from the cPanel side I can use that to create databases and users and if I need to I can use the server's command line since I have root access.

Hello, how much do you charge to upgrade PostgreSQL 9.5 my server

 

[obxion]

[QUOTE = "cPanelMichael, publicación: 2153881, miembro: 256941"]
Hola

Me alegra ver que pudo instalarlo correctamente. Moví este hilo a nuestro foro "Soluciones provisionales".

Gracias.
[/ CITA]


Like now, cpanel is a bit more expensive. So if it is very necessary, Why doesn't Cpanel update its dependencies? I think postgresql 9.5 is very necessary for many users. I also need an integrated Postgresql 9.5 for my customers, and I choosed Cpanel to make my life as an administrator easier, but it is not happening at the moment.