Installing PostgreSQL on openSUSE

Introduction

I want to try some real life style CRUD applications with Haskell and co.
To do this I wanted to install PostgreSQL as I heard lot’s of good stuff about it.

Disclaimer

I am in no way an expert or even competent with PostgreSQL but this got me a working instance together with an UI tool to administrate it.

Packages to install

I installed the followig packages following this and some other tutorials on the net:

  • postgresql-init
  • libpq5
  • postgres92
  • libqt4-sql-postgresql
  • postgresl92-server
  • postgresql
  • postgresql-server
  • libwx-gtk2u_stc-2_8-0-wxcontainer
  • libwx-gtk2u_xrc-2_8-0-wxcontainer
  • pgadmin3
  • pgadmin3-lang
  • libreoffice-base-drivers-postgresql

Obviously some of those got auto-selected (using YAST) and I added pgadmin3 and the plugin for libre-office as I saw it in the repose 😉

Post-Install (setting up the user, password, authentication method and data-path)

Following the short guide I

  • created a data-directory mkdir /usr/local/pgsql/data
  • made (already configured user) postgres the owner of this chown postgres /usr/local/pgsql/data
  • switched to this user su - postgres
  • initialised the database initdb -D /usr/local/pgsql/data
  • started postgres using this folder postgres -D /usr/local/pgsql/data
  • created a test database createdb test

(do not forget using godmode for this before you go – su)

Next I had to change the default password (again as su) – following the steps here:

  • start postgres (if not already) rcpostgresql start
  • switch to the user postgres and open the master-db: su postgres -c psql postgres
  • in the sql-command alter the defaultusers password: ALTER USER postgres WITH PASSWORD '****' (of course using you password instead of the stars)

Next on the checklist is changing the authentication mode (I guess this is only a good idea for your local test-instance – but this way it’s much easier to get everything going – indeed pgAdminIII will propose the same if it has problems connecting).
So open the file /var/lib/pgsql/data/pg_hba.conf (you will need administrative rights to access it) and change the uncommented occurrences of ident to md5.
Save the file and restart the database with rcpostgresql restart

Now everything should be fine to use pgAdminIII.

Setting up pgAdmin III

Start pgAdminIII and add a new server using

  • Host = localhost
  • Port = 5432 (this is configured in /var/lib/pgsql/data/postgres.conf),
  • User = postgres (of course you can create another user with createuser -D -p <username> – without -p if you do not need a password)
  • Passwort = whatever you choose in the last step

That’s it – now you should be able to access everything using this tool or the sql-command (pgsql)

Useful Links

As always: have fun 😀