SDB:PostgreSQL

Jump to: navigation, search


This Support Data Base (SDB) entry shows how install PostreSQL 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 basic repository are most tested and sufficient for most usecases. The specific postgres repository will provide most stable and feature rich releases and tools with latest patches.

Add repository:

(change last dir to fit your distribution)

Refresh repositories:

zypper refresh

Installing the packages

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

zypper in postgresql postgresql-server postgresql-contrib

Recommendation: install the postgresql-devel aswell 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):

zypper in postgresql-plperl postgresql-plpython postgresql-plctl

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

The default configuration location is /var/lib/pgsql/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

su - postgres

Enter the PostgreSQL interactive terminal:

psql

Use \q to exit psql.

Create user --> you should create at least one super user, that is not 'postgres'

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

Grant SUPERUSER (includes everything else), you might want to have such linux user as well, to ease use of the psql shell.

ALTER ROLE <username> WITH SUPERUSER;

It is not recommended to set the password for postgres user for security reasons. (Despite most tutorials show how to do this)

For tutorial or test purposes only, you can use:

\password postgres

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/

Reccomendations

PgAdmin4

install python 3 and pip: zypper in -y python3 python3-setuptools python3-pip

download newest version (whl + whl.sig files) on www.pgadmin.org/download/pgadmin-4-python-wheel/

install 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/pgsql/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/pgsql/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!)