Differences between revisions 43 and 44
Revision 43 as of 2015-01-24 17:23:45
Size: 6107
Editor: ?Diego Alonso
Comment: added Spanish into menu
Revision 44 as of 2016-08-12 21:13:33
Size: 5907
Editor: ?MartinMonperrus
Comment: remove the low level method which fails (no role is created)
Deletions are marked like this. Additions are marked like this.
Line 43: Line 43:
Connect to database Connect to database and create a new database user and a database:
Line 46: Line 46:
$ psql

Create a new database user and a database:
postgres=# CREATE USER mypguser WITH PASSWORD 'mypguserpass';
postgres=# CREATE DATABASE mypgdatabase OWNER mypguser;
Line 60: Line 50:
Quit from the database
postgres=# \q

Translation(s): English - español - Italiano - Русский

PostgreSQL Overview

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.


Required packages: postgresql postgresql-client

# apt-get install postgresql postgresql-client

Recommended packages:

  • postgresql-doc - PostgreSQL documentation.
  • pgadmin3 - PostgreSQL administration GUI.
  • phppgadmin - PostgreSQL web-based administration tool.

Please note that the procedural languages are installed separately (plpgsql comes by default). Perform search packaging database to find the list of possibilities:

# aptitude search postgresql

User access

Both the default database user and default database are called postgres.

You may connect to the database using the following command:

# su - 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

Connect to database and create a new database user and a database:

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

Connect as user mypguser to new database

# su - mypguser
$ psql mypgdatabase


# psql -d mypgdatabase -U mypguser

If you get errors like:

psql: FATAL:  Ident authentication failed for user "mypguser"

edit pg_hba.conf in /etc/postgresql/X.Y/main/pg_hba.conf

local   all         all                               trust     # replace ident or peer with trust

reload postgresql

# /etc/init.d/postgresql reload


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 postgresql-doc package. 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_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 the initdb i.e. it initializes the cluster with default encoding and locale. Encoding can be changed later but the locale cannot. To change the locale (an possibly other options in initdb), delete the existing default cluster and create a new one:

  1. Take root privileges.
  2. Run the following command:
    pg_dropcluster --stop <version> main
    For example:
    pg_dropcluster --stop 8.3 main
  3. Run the initdb with your options. For example:
    pg_createcluster --locale de_DE.UTF-8 --start 8.3 main


The previous operation obviously deletes everything you had in cluster databases. Perform this operation right after you have installed the base package. Check the PostgreSQL manual if you need to change locale for an existing database (it is not a trivial operation).

Check the Locale page to see how to add more locales.

See Also