How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 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 1 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 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 2 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 ust tr # IPv6 local connections: host all all ::1/128 md5 3 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 c. Now, you can browse phpPgAdmin page thru http://www.cisconet.com/phpPgAdmin d. Next, you might see login failed message, then See below. 4 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 3. Errors & trobleshooting a. If you got "login disallowed .....& quot; 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. 5 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 psql -d template1 -U postgres (tem plate1 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'; 6 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 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 7 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: ho st all all 127.0.0.1/32 trust # IPv6 local connections: ho st all all ::1/128 md5 8 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 Restart PostgreSQL again. Try logging in and see if your change worked! 9 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 b. If error message is below psql: FATAL: database "root" does not exist Server#su - postgres postgres@server> psql -d 10 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 template1 -U postgres postgres=# c. if error message is below 11 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 VPN:/var/lib/pgsql/data # service opennms star t Starting OpenNMS: org.postgresql.util.PSQLEx ception: FATAL: password authentication failed for user "postgres" OpenNMS runs better if you start up the database first. failed 12 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 13 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 # IPv4 local connections: host all all 127.0.0.1/32 md5 ---> trust # IPv6 local connections: host all all ::1/128 md5 14 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 4. Now what? Access phpPgAdmin and go to table mode and delete or analyze burstable log and data. 15 / 16 How to install and maintain PostgreSQL with phpPgAdmin Written by CiscoNET Wednesday, 06 May 2009 12:57 - Last Updated Wednesday, 06 May 2009 13:04 16 / 16