How to install and enable PostgreSQL database server in Plesk

Follow

Comments

22 comments

  • Avatar
    Vivo forta (Edited )

    This procedure has installed PostgreSQL 9.2 on Plesk onyx with centOS 7.3
    How to install PostgreSQL 9.5 or 9.6?

    0
    Comment actions Permalink
  • Avatar
    Bulat Tsydenov

    @Vivo, PostgreSQL is installed from the repositories enabled on the server. You can enable any other repository which provides the required version of PostgreSQL and install it the same way.

    1
    Comment actions Permalink
  • Avatar
    Vitaly Zhidkov

    @Vivo, you should enable a corresponding repo for it. Check their list at https://yum.postgresql.org/

    0
    Comment actions Permalink
  • Avatar
    Peter

    Can someone please tell me how to add PostgreSQL 9.5 repo so i get 9.5 when i install in Plesk GUI?

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    @Peter

    Hello!

    Use the following instructions: for Debian, for CentOS.
    In case of any issues regarding setting the PostreSQL, contact support.

    0
    Comment actions Permalink
  • Avatar
    Ashkan Nasiri

    This file doesn't exist:

    /opt/psa/admin/bin/pg_manage 

    or /opt/psa directory in first place.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    @Ashkan

    As it is written in the article, /opt/psa/admin/bin/pg_manage is a symbolic link to /usr/local/psa/admin/sbin/wrapper. It may be recreated using the command:

    ln -s /usr/local/psa/admin/sbin/wrapper /opt/psa/admin/bin/pg_manage

    Have you tried this command?

    What version of Plesk is installed?

    0
    Comment actions Permalink
  • Avatar
    Ashkan Nasiri

    We have already ran the ln command but since the file didn't exist we got error. here is the result:

    ln: failed to create symbolic link ‘/opt/psa/admin/bin/pg_manage’: No such file or directory 

     

    We have latest plesk onyx version. It's a fresh installation of plesk.

    0
    Comment actions Permalink
  • Avatar
    Alexandr Tumanov

    @Ashkan, I've made some corrections to the article. Please check. The path was Ubuntu specific, now it is for all OSes. 

    0
    Comment actions Permalink
  • Avatar
    Plesk User (Edited )

    It took me 3 days to research and find all these information, so here it's in case you are pulling your hairs.

    How to successfully install PostgreSQL 11 on Plesk (CentOS 7 version):

    1- install PostgreSQL new REPO via SSH:
    rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

    2- Enable PostgreSQL from Plesk > Tools & Settings > Updates and Upgrades > Add/Remove components:

    3- Install version 11:
    yum install postgresql11-server postgresql11 postgresql11-libs postgresql11-devel

    4- make a symbolic link from 11 to no version:
    ln -s /lib/systemd/system/postgresql-11.service /lib/systemd/system/postgresql.service

    5- Copy ver 11 over 9.2:
    cd /usr/pgsql-11/bin/
    for f in *; do echo $f; [ -e /usr/bin/$f ] && mv /usr/bin/$f /usr/bin/$f.old92; ln -s $(pwd)/$f /usr/bin/$f; done

    6- Setup the Database Server:
    /usr/pgsql-11/bin/postgresql-11-setup initdb

    7- Move old data directory and Symbolic Link New Ones:
    cd /var/lib/pgsql
    mv data data.old92
    mv backups backups.old92
    ln -s 11/backups; ln -s 11/data

    8- Start the server:
    systemctl start postgresql.service

    9- Link to Plesk Management:
    rm /usr/local/psa/admin/bin/pg_manage
    ln -s /usr/local/psa/admin/sbin/wrapper /usr/local/psa/admin/bin/pg_manage

    10- Go to Plesk: Tools & Settings > Database Servers

    11- Add a PostgreSQL database server and enter user and password as described above.

    12- You should see a Green checkmark near PostgreSQL name on Tools & Settings > Database Servers.

    Cheers!

    0
    Comment actions Permalink
  • Avatar
    Alisa Kasyanova

    @Plesk User
    As per our documentation here https://docs.plesk.com/release-notes/onyx/software-requirements/ , Plesk now supports PostgreSQL 8.4–9.5.
    So please notice that your manual (thank you for sharing it!) should be used on own will and risk, and if any issues occur, it would be an unsupported case.

    0
    Comment actions Permalink
  • Avatar
    Peter

    @Alisa Kasyanova 

    I run Plesk Onyx Version 17.8.11 on Debian 9.9 and i got PostgreSQL 9.6.12 by default when adding as described in docs.

    Does this mean that my PostgreSQL 9.6.12 is not supported?

     
    0
    Comment actions Permalink
  • Avatar
    Nick Plekhov

    Hello @Peter.

    Thank you for bringing our attention to this matter.
    Indeed according to the Plesk release notes PostgreSQL 9.6 is not supported and exactly this version is installed on Plesk 17.8 with underlying Debian 9.9.
    I have passed this question to our development team for further clarifications.

    I will update you as soon as I get a word from them.

    0
    Comment actions Permalink
  • Avatar
    Weare Borg

    Where to start:

    postgresql-setup initdb does not exist when you follow the installment in Plesk.

    But this can be solved by going to the install directory and run initdb from there.

    After that it runs and you see that when you do a service status.

    But when you go to Plesk Database Servers>Add Database Server you can not select PostgreSQL as database only MySQL exist.

    So i am unable to run PostgreSQL at this time because this How to is no good at all.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Weare Borg,

    Please, let me know Plesk and OS versions. So, we could check the behavior on a test server.

    0
    Comment actions Permalink
  • Avatar
    Weare Borg

    Plesk 17.8.x OS Ubuntu LTS 18.04

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Weare Borg,

    Thank you for the specified information. I've checked how to do this on Ubuntu 18.04, here's the instruction:

    Steps 1 and 2 are the same =)

    After that:

    I've entered as Posgres user:

    # su - postgres

    Created cluster with the command:

    # /usr//lib/postgresql/10/bin/initdb -D /usr/local/pgsql/data

    Create an additional superuser to use it in Plesk:

    # createuser -s -E -P -l plesk_pgadmin

    pg_hba.conf on my installation was at /etc/postgresql/10/main/pg_hba.conf

    After that I've successfully connected this server to Plesk:

    The article will be updated with these details.

    0
    Comment actions Permalink
  • Avatar
    Peter

    Any tip on doing this on:


    Debian 9.11 Stretch
    Plesk Onyx Version 17.8.11

    Very important that all DB functions like backup/restore still works.

     

     

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Peter,

    The article was recently updated with instructions for Ubuntu/Debian.

    Try these steps. In case of additional questions, feel free to ask.

    0
    Comment actions Permalink
  • Avatar
    Peter

    This article is so mixed with old and new stuff i can't figure it out.

    Also i get the question for user postgres password and i don't know this.

    Can you please make a new tutorial with all steps. In step 1 you refer to an KB article that dos nothing for me or i'm just plain stupid :)

    0
    Comment actions Permalink
  • Avatar
    Peter

    Getting a bit frustrated.

    The main reason i bought Plesk it because i'm not skilled enough to do stuff in terminal.

    So my question now is when and how will i be able to run Postgres 10 just by doing stuff in Plesk GUI and NOT in the terminal?

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Peter,

    Thank you for the feedback and sorry for the late response.

    Let me address your questions:

    > Also i get the question for user postgres password and i don't know this.

    On a test server I've had postgres user had no password set, which should be the default behavior. You may use this instruction to reset the password: https://www.tldp.org/LDP/lame/LAME/linux-admin-made-easy/changing-user-passwords.html

    The instruction from Step 1 described the ways any of Pleks components, including Postgres may be installed.

    > So my question now is when and how will i be able to run Postgres 10 just by doing stuff in Plesk GUI and NOT in the terminal?

    Plesk development team s doing its best to make the experience with Plesk easy and smooth. However, currently, Postgres initialization is to be done on customer's side. 

    When getting stuck at some of our instruction, you're always welcome to create a request to Plesk Support for assistance.

     

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request