[HOWTO] Install Postgres 9.1 and PhpPgAdmin 5.0.4

    This Howto is to be used with Squeeze and i-MSCP 1.1.0-rc1.3, and I widely recommend to do a back up of the files that we will modify.

    Steps
    1.- Install PostgreSQL
    2.- Create PostgreSQL Administrator
    3.- Install PhpPgAdmin
    4.- Config PhpPgAdmin
    5.- Create Alias
    6.- Eliminate some restrictions
    7.- Create Users and Database in our server
    8.- Managing the PostgreSQL server


    1.- Install PostgreSQL
    Edit /etc/apt/sources.list and add

    Quote

    deb http://backports.debian.org/debian-backports squeeze-backports main


    save and exit

    Quote

    apt-get update
    apt-get -t squeeze-backports install postgresql-9.1
    apt-get install php5-pgsql



    2.- Create PostgreSQL Administrator
    Now we create the postgres administrator with the following steps / the admin username is just an example, you can change this username as you wish :

    Quote

    su postgres
    createuser admin
    ¿Shall the new role be a superuser? (y/n) ? y


    After that we set a password for it

    Quote

    psql postgres


    This enters us into the postgreSQL shell and now we can set the password into the ' '

    Quote

    alter user admin with password 'superstrong_password';


    And then exit

    Quote

    \q
    exit



    3.- Install PhpPgAdmin
    Download, unpack and copy the last PhpPgadmin version



    4.- Config PhpPgAdmin
    Now we need to configure some specific settings modifying the following file

    Quote

    /var/www/imscp/gui/public/tools/pga/conf/config.inc.php


    Search and replace the following directives to look like this

    Quote

    $conf['servers'][0]['host'] = 'localhost';
    $conf['owned_only'] = true;
    $conf['owned_reports_only'] = true;
    $conf['min_password_length'] = 5;


    PhpPgAdmin has a warning in the process option. In order to fix this bugs, we need to edit

    Quote

    /var/www/imscp/gui/public/tools/pga/classes/database/Postgres.php


    Search in line 6473 aprox

    Quote

    function isSuperUser($username) {


    and change it for

    Quote

    function isSuperUser($username = '') {


    And change this (in line 6476 aprox)

    Quote

    if (function_exists('pg_parameter_status')) {


    for this

    Quote

    if (empty($username)) {



    5.- Create Alias
    Now we have to add some new aliases to the control panel. For this, we need to modify the following 3 files:


    a.- In /etc/imscp/apache/00_master.conf after the last Alias directive, we add

    Quote

    Alias /pga {ROOT_DIR}/gui/public/tools/pga


    b.- In /etc/imscp/apache/parts/domain.tpl after the last Alias directive, we add

    Quote

    RedirectMatch permanent ^/pga[\/]?$ {BASE_SERVER_VHOST_PREFIX}{BASE_SERVER_VHOST}/pga/


    c.- In /etc/apache2/sites-available/00_master.conf after the last Alias directive, we add

    Quote

    Alias /pga /var/www/imscp/gui/public/tools/pga/


    After that, we need to rebuild the customer's configuration files

    Quote

    perl /var/www/imscp/engine/setup/imscp-setup


    At this point it is necesary to restart Apache web server to activate the new aliases
    etc/init.d/apache2 restart


    And Now we can acces to the PhpPgAdmin through

    Quote

    http://www.client-domain.tld/pga



    6.- Eliminate some restrictions to be able to export the databases
    In order to permit the export of the database, we need to remove the pssthru and exec restrictions of the disable_functions list.


    For Apache FCGI or FastCgi installation we found this restrictions in

    Quote

    /var/www/fcgi/master/php5/php.ini


    For Apache ITK installation we found this restrictions in

    Quote

    /etc/php5/apache2/php.ini


    After that is necesary to restart the Apache server
    etc/init.d/apache2 restart



    7.- Create users and databases in our Server
    Creating users and databases may now be done through phpPgAdmin; And now we login as the admin user that we've just created.
    User: admin
    Password: superstrong_password


    Remember to create the client users with no inherited permissions nor rights to create databases and roles, but allowing them to login.



    8.- Managing the PostgreSQL server
    PostgreSQL may be restarted or stopped using it's init.d script, as any other service:

    Quote

    /etc/init.d/postgresql start
    /etc/init.d/postgresql stop
    /etc/init.d/postgresql restart


    That is all


    Good luck


    Victor

    Hi Victor,


    thanks for your detailed installations-steps.


    There is a problem with the Key:
    W: GPG error: http://backports.debian.org squeeze-backports InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 8B48AD6246925553



    I have run this steps:
    gpg --keyserver pgpkeys.mit.edu --recv-key 8B48AD6246925553
    gpg -a --export 8B48AD6246925553 | sudo apt-key add -
    apt-get update


    Greeting Detlev