If you did not migrate your account yet, visit https://idp-portal-info.suse.com/
This Support Data Base (SDB) entry shows how to install and get started with PostgreSQL.
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
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
--> 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:
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/
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
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 in zramservice
systemctl enable zram
systemctl start zram
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!)