Troubleshooting: PostgreSQL installations

From CPWiki

Jump to: navigation, search

Comment this article at [cPanel Forum]


Warning: Only for postgresql 7.3!!


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.


Contents

[edit] 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.

[edit] 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. 

# logged as root
$ logged as postgres user

[edit] Making old database backup 

# 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 next section..

[edit] 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

[edit] Rebuilding postgres default databases 

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

[edit] Restoring your backup 

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

[edit] Changing Postgres config at cPanel

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

[edit] Moving old Postgres databases to a safer place

make sure you have an old copy 

# mv /tmp/dbdo28.out /root

[edit] Allowing TCP/IP connections

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

[edit] Restarting Postgres

Restart postgres with 

# service postgresql restart

[edit] Read more

Retrieved from "http://www.cpwiki.org/wiki/Troubleshooting:_PostgreSQL_installations"
Personal tools