PostgreSQL/Debian

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

1. Take root privileges
2. Run the following command:
   pg_dropcluster --stop <version> main
   For example:
   pg_dropcluster --stop 11 main
3. 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

See Also

• PostgreSQL packages in Debian
• DebianGis/UpdatingPostGIS (also describes upgrading postgresql)

External Links

• Official PostgreSQL web site
• http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools
• https://wiki.postgresql.org/wiki/Design_Tools
• Practical PostgreSQL
• https://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
• Enabling core files for PostgreSQL on Debian