Installing PostgreSQL and pgAdmin3 in OpenSuse

This is a guide I made for my work colleagues:

1. Install PostgreSQL.
   Do it from the repositories using yast2 and then follow the instructions given in: http://dev-loki.blogspot.com/2008/04/postgresql-on-opensuse.html and http://www.susegeek.com/database/install-and-configure-postgresql-in-opensuse-110/
   
2. Populate the data directory.
   Once the package is installed, we must stop and start the server to populate the data directory, which is /var/lib/pgsql/data/ in this case.
   

   # rcpostgresql stop
   # rcpostgresql start
   

3. Change the random password assigned to postgres user during installation.
   First in postgresql server:
   

   # sudo su postgres
   postgres@linux-2dqb:/var/lib> psql
   psql (8.4.4)
   Digite «help» para obtener ayuda.
   postgres=# alter user postgres with password 'pk84';
   ALTER ROLE
   postgres=# \q
   

   And then in Linux:
   

   # postgres@linux-2dqb:/var/lib> exit
   exit
   # passwd -d postgres
   # passwd postgres
   

4. Change authentication mode from ident to md5.
   First stop the server.
   

   # /etc/init.d/postgresql stop 
   

   Then modify the following lines in /var/lib/pgsql/data/pg_hba.conf (the initial lines remain in the file but commented)

   # "local" is for Unix domain socket connections only 
   #local all all ident local all all md5 
   # IPv4 local connections: 
   #host all all 127.0.0.1/32 ident host all all 127.0.0.1/32 md5 
   # IPv6 local connections: 
   #host all all ::1/128 ident host all all ::1/128 md5  
   

   After saving the changes in pg_hba.conf, restart the server for them to take effect.

   # /etc/init.d/postgresql start
   

   You can find an explanation about pg_hba.conf in: http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html
   
5. Install pgadmin3 from the repositories
   
6. Install instrumentation functions to allow pgadmin3 to modify configuration files.
   When connecting to a PostgreSQL database using pgAdmin3 you may receive an error letting you know that the server instrumentation is not installed.
   To install it I followed the instructions given in:
   http://www.question-defense.com/2010/09/17/postgresql-server-instrumentation-not-installed-the-server-lacks-instrumentation-functions
   
   • Install PostgreSQL Contrib.
     First install the postgresql-contrib package using the yum package manager with the below syntax.
     

     # zypper install postgresql-contrib   
     

     Another way to do it would be using yast2 to get it from the repositories.
     
   • Locate adminpack.sql.
     

     # find / -name adminpack.sql /usr/share/postgresql/contrib/adminpack.sql  
     

   • Install adminpack.
     

     # /etc/init.d/postgresql start 
     # su postgres postgres@linux-2dqb:/usr/share/postgresql/contrib> postgres@linux-2dqb:/usr/share/postgresql/contrib> psql -U postgres < adminpack.sql 
     

   • Verify that adminpack is working.
     Launch pgAdmin3 and connect to the Postgres server you just installed adminpack on. Once connected to the PSQL server click Tools in the top navigation menu, select Server Configuration from the drop down, and then select either pg_hba.conf or postgresql.conf to view a configuration file. You shouldn't get any error. With the adminpack contribution installed you will now be able to edit pg_hba.conf and postgresql.conf configuration files directly from pgAdmin3.