PostgreSQL - fixing it

[darkelder]

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

[bignose]

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.

[erick_paper]

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.
  
  

  6) ALLOWING CONNECTIONS BY TCP/IP

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

  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:

// 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($result, null, PGSQL_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);