How to install and maintain PostgreSQL with phpPgAdmin

Tweet

PostgreSQL is required as Database for OpenNMS. While I was playing with OpenNMS, I need to have a tool in order to empty all testing data and information. Unfortuately, OpenNMS admin console doesn’t provide a function to handle SQL data. I am not a person who likes CLI(commands line interface) much. So, I researched GUI based admin tools for PostgresSQL. Finally, I found a phpPgAdmin on YaST(management tools by OpenSuSE Linux).

Let’s see few tips to get it works!

1. Installation of PostgreSQL

– Well, one of reason that I like OpenSuSE is that it provides very convenient admin tools, called YaST(YaST2). You can install any softwares that are from Repository easily. Actually, not much you need to aftoer install PostgreSQL by YaST.

To start

Server#service postgresql start

To stop

Server#service postgresql stop

 

2. Installation of phpPgAdmin

– Just install by YaST and change few things.

a. go to /srv/www/htdocs/phpPgAdmin/conf and copy config.inc.php-dist to config.inc.php. You don’t need to change anything inside of the file. Just name change.

Server:/srv/www/htdocs/phpPgAdmin/conf #cp config.inc.php-dist config.inc.php

 

b. Change one more file, /var/lib/pgsql/data/pg_hba.conf

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 trust

# IPv6 local connections:
host all all ::1/128 md5

 

c. Now, you can browse phpPgAdmin page thru

http://www.ipBalance.com/phpPgAdmin

 

d. Next, you might see login failed message, then See below.

3. Errors & trobleshooting

a. If you got "login disallowed ….." Basically, phpPgAdmin doesn’t create password for postgres account. For security reason, you better to have password on it. How? see below.

– Go into your pgdata directory and Open pg_hba.conf.
– The lines that say “md5” near the bottom, change to “trust”
– Restart your database instance.
– Login and set your password.

 

psql -d template1 -U postgres (template1 is service database)
alter user postgres with password ‘xxxxxx‘; (xxxxxx is a password)
postgres@server> psql -d template1 -U postgres
postgres=# alter user postgres with password ‘speeder’;
ALTER ROLE
postgres=# q\ or ctl+’z’ (to exit)

– exit out from psql.
– Change the lines back to md5 in your pg_hba.conf file

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only local all all md5
# IPv4 local connections: host all all 127.0.0.1/32 trust
# IPv6 local connections: host all all ::1/128 md5

 

Restart PostgreSQL again.

Try logging in and see if your change worked!

 

b. If error message is below
psql: FATAL: database "root" does not exist

Server#su – postgres
postgres@server> psql -d template1 -U postgres
postgres=#

 

c. if error message is below

VPN:/var/lib/pgsql/data # service opennms start
Starting OpenNMS: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
OpenNMS runs better if you start up the database first. failed

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 md5 —> trust

# IPv6 local connections:
host all all ::1/128 md5

4. Now what?

Access phpPgAdmin and go to table mode and delete or analyze burstable log and data.