This Support Data Base (SDB) entry shows how install PostreSQL 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 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.
zypper addrepo http://download.opensuse.org/repositories/server:database:postgresql/openSUSE_Tumbleweed/ PostgreSQL (change last dir to fit your distribution)
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 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):
sudo zypper in postgresql-plperl postgresql-plpython postgresql-plctl
--> 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:
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:
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 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!)