PostgreSql

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance

PostgreSQL has extensive and good help that should be the first source of information regarding this database product

This page outlines main differences to generic PostgreSQL installation used by Debian

Installation

Required packages: postgresql postgresql-client

# apt install postgresql postgresql-client

Recommended packages:

postgresql-doc - PostgreSQL documentation
phppgadmin - PostgreSQL web-based administration tool

Please note that the procedural languages are installed separately (plpgsql comes by default)

Search Debian packages to find the list of possibilities:

# apt search postgresql

PGDG Repository

If you need to use another Version than is currently available for your running DebianRelease you can use Postgresql's official repository with a script maintained in postgresql-common located at /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

More information can be obtained here: https://wiki.postgresql.org/wiki/Apt

User access

Both the default database user and default database are called postgres

You will need administrative rights

As root:

# su -c /usr/bin/psql postgres

If your system uses sudo to get administrative rights:

$ sudo -u postgres psql

New User and database

Create a regular system user account using adduser (skip this step to use an existing account):

# adduser mypguser #from regular shell

Switch to user postgres and create a new database user and a database:

# su - postgres
$ createuser --pwprompt mypguser #from regular shell
$ createdb -O mypguser mypgdatabase

Connect as user mypguser to new database

# su - mypguser
$ psql mypgdatabase

or, if the OS user name is not the same as the database user name:

$ psql -d mypgdatabase -h localhost -U mypguser

you can also use a ~/.pgpass file

Add line for auth :

$ echo 'hostname:port:mypgdatabase:mypguser:mypgpassword' >> ~/.pgpass

Secure the file

chmod 600 ~/.pgpass

Now you can easily connect with

$ psql -d mypgdatabase -h localhost -U mypguser

More info on the syntax can be found here : https://www.postgresql.org/docs/current/libpq-pgpass.html

Migration

See /usr/share/doc/postgresql-common/README.Debian.gz section "Default clusters and upgrading"

Alternatively, you can adapt and use :

https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian

Documentation

To get an overview about Debian's PostgreSQL architecture, instructions for a quick start, and pointers to the programs and manpages, have a look at /usr/share/doc/postgresql-common/README.Debian.gz

Tutorial files

PostgreSQL documentation points to tutorial, which is included in the postgresql-doc package

Once the package is installed, to get more information look at /usr/share/doc/postgresql-doc-[version]/tutorial/README

Listing existing database clusters

Use pg_lsclusters command to check installed clusters and obtain some basic information such as: version (major version), name, port, status (online or down), owner, data directory and log file

pg_lsclusters

pg_ctl replacement

pg_ctl is a PostgreSQL command line control program that can be used to control the database

Debian has made a Perl-wrapper for the pg_ctl called /usr/bin/pg_ctlcluster
Use the pg_ctlcluster whenever you need the pg_ctl
To customize the behavior check the /etc/postgresql/[version]/[cluster]/pg_ctl.conf

Debian installs SysV-init compatible (standard) start-up script /etc/init.d/postgresql-[version]

It can be used to start, stop, restart and reload the system
It calls pg_ctlcluster internally

File locations

Debian splits the database configuration from the database files, opposed to generic PostgreSQL installation that puts everything under same directory

Note that Debian allows multiple clusters and even different versions of PostgreSQL to co-exist in same host

Configuration files: /etc/postgresql/[version]/[cluster]/Binaries: /usr/lib/postgresql/[version]Data files: /var/lib/postgresql/[version]/[cluster]

Log files: Installing PostgreSQL creates log directory /var/log/postgresql/

Starting the database engine creates log file with name postgresql-[version]-[cluster].log

Changing Debian default installation

Debian PostgreSQL installation automatically calls initdb i.e

it initializes the cluster with default encoding and locale
To change the locale (and possibly other options in initdb), delete the existing default cluster and create a new one:

Warning: The following operation obviously deletes everything you had in cluster databases

Perform this operation right after you have installed the base package

Take root privileges
Run the following command:
pg_dropcluster --stop <version> main
For example:
pg_dropcluster --stop 11 main
Run initdb with your options

For example:
pg_createcluster --locale de_DE.UTF-8 --start 11 main

Check the Locale page to see how to add more locales

External Links

PostgreSQL/Debian