The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

PostgreSQL - fixing it

Discussion in 'General Discussion' started by darkelder, Jun 7, 2005.

  1. darkelder

    darkelder Well-Known Member

    Joined:
    Oct 8, 2004
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    This troubleshoting will help you if:
    • If you have databases on postgres and they're not being showed on cpanel
    • If you're trying to create a database or an user on postgres and they're not being showed as created on cpanel.
    • It is possible that mysql is giving same results if postgres is with problem.


    PROBLEM

    Postgres isn't showing any errors on cpanel, because it does not return any error. The most common error is that postgres cannot connect with cpanel password.


    RESOLUTION

    You can try just go to section OPTIONAL - SKIPING POSTGRES PERMISSION and then on Cpanel and change by Cpanel postgres password or, if it doesnt work, do all this tutorial steps.




    # loged as root
    $ loged as postgres user



    1) MAKING BACKUP OF OLD DATABASE

    # su - postgres
    $ pg_dumpall > /tmp/dbdo28.out
    $ exit

    If an error like 'pg_dumpall: could not connect to database template1: FATAL: Password authentication failed for user "postgres"' appear, you'll need to do section 1.1 above, otherwise skip to section 2.

    OPTIONAL - SKIPING POSTGRES PERMISSION

    Modify /var/lib/pgsql/data/pg_hba.conf from 'md5' to 'trust'

    It should contain the following:

    local all all trust
    host all all 127.0.0.1 255.255.255.255 trust

    Restart postgres:
    # service postgresql restart


    2) REBUILDING POSTGRES DEFAULT DATABASES

    # mv -f /var/lib/pgsql /var/lib/pgsql.old
    # yes | /scripts/installpostgres

    3) RESTORING YOUR BACKUP

    # su - postgres
    $ psql -f /tmp/dbdo28.out template1
    $ exit


    4) CHANGING POSTGRES CONFIG AT CPANEL

    change pgsql password on cpanel "postgres config" option to something random and click on intall config


    5) MOVING OLD POSTGRES DATABASE TO A SAFER PLACE

    make sure you have an old copy
    # mv /tmp/dbdo28.out /root


    6) ALLOWING CONNECTIONS BY TCP/IP

    Edit /var/lib/pgsql/data/postgresql.conf and change tcpip_socket from false to true.


    7) RESTARTING POSTGRES

    Restart postgres with
    # service postgres restart

    http://forums.cpanel.net/showthread.php?t=36992
    http://forums.cpanel.net/showthread.php?t=37219
    http://forums.cpanel.net/showthread.php?t=27416
     
    #1 darkelder, Jun 7, 2005
    Last edited: Jun 8, 2005
  2. bignose

    bignose Active Member

    Joined:
    Jan 3, 2007
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Hi,

    I'm on FreeBSD

    I recently had to reinstall pgsql on a different file system, I set the password to be the same etc.

    I can create databases in cPanel no problems and psql -l shows they are there, however they do not get listed in the interface.

    I should mention that the users I have created Do get displayed.

    Jeff.
     
  3. erick_paper

    erick_paper Well-Known Member

    Joined:
    Apr 19, 2005
    Messages:
    245
    Likes Received:
    0
    Trophy Points:
    16
    Hi,

    My Postgresql is working through CPANEL etc. I can even use psql in the root user ssh without any problem. But when I use it on a hosted domain as a PHP code, it is not working.

    My code is very simple (included at the end of this note) and it works on my local windows machine, but the same code does not work on my CentOS hosting server.


    • The password, db name, username are all the same.
    • In pg_hba.conf I have changed MD5 to trust.
    • The tcpip_socket option you suggested is wrong.

      There is nothing like "tcpip_socket" in my postgresql.conf and if I manually include it and set it to true, the postgresql service doesn't even start.


    Any ideas why this may be happening and what I can do to fix it?

    Many thanks!

    Just FYI, the code:

    PHP:
    // Connecting, selecting database
    $dbconn pg_connect("host=localhost port=5432 dbname=DOMAIN_DOMAIN user=DOMAIN_DOMAIN password=DOMAINPASS")
        or die(
    'Could not connect: ' pg_last_error());

    // Performing SQL query
    $query 'SELECT * FROM table LIMIT 15';
    $result pg_query($query) or die('Query failed: ' pg_last_error());

    // Printing results in HTML
    echo "<table>\n";
    while (
    $line pg_fetch_array($resultnullPGSQL_ASSOC)) {
        echo 
    "\t<tr>\n";
        foreach (
    $line as $col_value) {
            echo 
    "\t\t<td>$col_value</td>\n";
        }
        echo 
    "\t</tr>\n";
    }
    echo 
    "</table>\n";

    // Free resultset
    pg_free_result($result);

    // Closing connection
    pg_close($dbconn);
     
    #3 erick_paper, Jul 26, 2007
    Last edited: Jul 26, 2007
Loading...

Share This Page