The wikis are now using the new authentication system.
If you did not migrate your account yet, visit https://idp-portal-info.suse.com/

SDB:PostgreSQL

Jump to: navigation, search


This Support Data Base (SDB) entry shows how to install and get started with PostgreSQL.

Installation

Postgres Repo

In case you are targeting a simple installation (single node, testing purposes, simple web-browser, etc.), you can skip this optional step. The packages provided by the basic repository are most tested and sufficient for most use cases. The specific postgres repository will provide most stable and feature rich releases and tools with the latest patches.

Add repository (change last dir to fit your distribution):

zypper --non-interactive --quiet addrepo --refresh -p 90  http://download.opensuse.org/repositories/server:database:postgresql/openSUSE_Tumbleweed/ PostgreSQL 

Refresh repositories:

zypper refresh

Or for automated setup:

zypper --gpg-auto-import-keys ref

Installing the packages

Install the postgresql client, server and contrib. -->contrib provides some extensions that are not yet part of root tree

sudo zypper in postgresql postgresql-server postgresql-contrib

Recommendation: install the postgresql-devel as well if you plan to use external libraries like fdws.

Install the optional procedural languages, according to your needs: Perl / Tlc / Python (postgres supports 2.x and 3.x, please check official documentation):

sudo zypper in postgresql-plperl postgresql-plpython postgresql-pltlc

Server Initialisation

--> postgres is not a native service, however systemctl can manage it very well TODO: (database initialisation steps, in case they would be required).

Enable postgres server to start at system boot:

systemctl enable postgresql

Start postgres server:

systemctl start postgresql

Post install configuration

Default PostgreSQL home directory ~postgres for OpenSUSE is: /var/lib/pgsql/

Configuration files are in data directory: ~postgres/data/. This is where you will find postgresql.conf and pg_hba.conf for example.

If not there, use 'find':

find / -name postgresql.conf

if none found: initialise your database: https://www.postgresql.org/docs/current/static/app-initdb.html

Switch to the postgres user

sudo su postgres

Now you should create at least one super user. You can use an exiting linux user to ease authentication (if peer is enabled in pg_hha.conf). It is not recommended to set the password for 'postgres' user or creating 'root' user for productive environments! There are two options to do this: from shell invoking psql, or manually using psql command line.

One liner: psql -c "CREATE ROLE <username> WITH SUPERUSER PASSWORD '<passwd>';"

Manual: Enter the PostgreSQL interactive terminal:

psql
CREATE ROLE <username> WITH PASSWORD '<passwd>';

Use CTRL+d to exit psql

If possible use psql interactive terminal to change passwords, as they might be logged otherwise:

\password <username>

Adopt the configuration files to your needs: /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/pg_hba.conf

For Additional info, you should refer to the PostgreSQL documentation: http://www.postgresql.org/docs/

Recommendations

PgAdmin4

Install python 3 and pip:

 zypper in -y python3 python3-setuptools python3-pip

Download the newest version (whl + whl.sig files) on https://www.pgadmin.org/download/pgadmin-4-python-wheel/ and install it:

 pip install <path>\pgadmin4.whl

Configuration optimization

Use pgtune to tune the configurations of postgres server to optimally use your hardware. There are two ways to use pgtune: you can use the online version at http://pgtune.leopard.in.ua/ to calculate recommended values, or use the pgtune tool coming with your distro.

Install pgtune --> If not found in main repo; pgtune is part of postgres repository

zypper in pgtune

Rename old configuration:

mv /var/lib/pgsql/data/postgresql.conf /var/lib/pgsq/data/postgresql.conf.old

Run pgtune, it will auto-optimize the configuration for your hardware

pgtune -i /var/lib/pgsql/data/postgresql.conf.old /var/lib/pgsq/data/postgresql.conf

RAM optimization with ZRAM and huge pages

Postgres runs well with lots of RAM, and lots of RAM run smoother with enabled huge pages. For huge pages and other optimisation you might use https://www.suse.com/communities/blog/sles-1112-os-tuning-optimisation-guide-part-1/ as starting reference.

ZRAM can further improve your RAM usage and decrease disk swapping. Tested and working repository for Tumbleweed (thanks to malcolmlewis! I'll try to make a dedicated instructions page soon):

zypper ar https://download.opensuse.org/repositories/home:/Mihail-Klr/openSUSE_Tumbleweed/ 

Install:

zypper in zramservice

Enable:

systemctl enable zram

Start:

systemctl start zram

Test: swapon

If you use ZRAM, you can try to increase the Memory values in postgresql.conf above those that are suggested by pgtune or feed higher RAM values to pgtune (don't forget to test new configs!)