Posts Tagged ‘ PostgreSQL ’

Postgresql basics

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

Advertisements