Wednesday, May 25, 2011

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.

1 comment:

  1. Eu uso uma ferramenta gratuita Valentina Estúdio com PostgreSQL. Será que tudo que você precisa, e faz isso muito bem. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete