This wiki was updated to MediaWiki 1.37. If you notice any issues, please report them to admin[at]opensuse.org

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 / Tcl / Python (postgres supports 2.x and 3.x, please check official documentation):

sudo zypper in postgresql-plperl postgresql-plpython postgresql-pltcl

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/

Upgrading major PostgreSQL version

As there are differences in the binary files used by the database system, an automatic upgrade is not possible. To simplify the upgrade, several PostgreSQL versions can be installed in parallel. The `pg_upgrade` script from postgresNN-contrib will help you migrating upgrading the versions.

The locale for the new and the old installations must match. Elsewise you get the following message executing pg_upgrade: lc_collate values for database "postgres" do not match. This can be solved by executing the `initdb` parameter with the same locale type as the old server's local was using `--locale=C`, or by dumping the database and importing the dump in the new instance.

There are some minor, recommended changes to the default file-system structure to simplify upgrades:

  • Move the configuration files into /etc/postgres
  • Create versioned data directories in /var/lib/psql/

Those steps must only be done at the "first" upgrade, on later upgrades everything should already bin in place.

The following bash code should be checked and executed line for line whenever you do an upgrade. The example shows the upgrade process from an original installation of version 12 up to version 14.

# install the new server as well as the required postgresql-contrib packages:
zypper in postgresql14-server postgresql14-contrib postgresql12-contrib

# If not yet done, copy the configuration create a new PostgreSQL configuration directory...
mkdir /etc/postgresql
# and copy the original file to this global directory
cd /var/lib/pgsql/data
for i in  pg_hba.conf pg_ident.conf postgresql.conf postgresql.auto.conf ; do cp -a $i /etc/postgresql/$i ; done

# Now create a new data-directory and initialize it for usage with the new server
# If your os's locale has changed, append `--locale=C` to initdb to keep the same locale
# for the new installation (read above).
install -d -m 0700 -o postgres -g postgres /var/lib/pgsql/data14
cd /var/lib/pgsql/data14
sudo -u postgres /usr/lib/postgresql14/bin/initdb .

# replace the newly generated files by a symlink to the global files.
# After doing so, you may check the difference of the created backup files and
# the files from the former installation
for i in  pg_hba.conf pg_ident.conf postgresql.conf postgresql.auto.conf ; do old $i ; ln -s /etc/postgresql/$i .; done

# Copy over special thesaurus files if some exists.
cp -a /usr/share/postgresql12/tsearch_data/my_thesaurus_german.ths /usr/share/postgresql14/tsearch_data/

# Now it's time to disable the service...
systemctl stop postgresql.service

# And to start the migration. Please ensure, the directories fit to your upgrade path
sudo -u postgres pg_upgrade --link \
  --old-bindir="/usr/lib/postgresql12/bin" \
  --new-bindir="/usr/lib/postgresql14/bin" \
  --old-datadir="/var/lib/pgsql/data/" \
  --new-datadir="/var/lib/pgsql/data14/"

# After successfully migrating the data...
cd ..
# if not already symlinked move the old data to a versioned directory matching
# your old installation...
mv data data12
# and set a symlink to the new data directory
ln -sf data14/ data

# Now start the new service
systemctl start postgresql.service

# If everything has been sucessful, you should uninstall old packages...
zypper rm -u postgresql12 postgresql13
# and remove old date directories
rm -rf /var/lib/pgsql/data_OLD_POSTGES_VERSION_NUMBER

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!)