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

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

You will need administrative rights.

As root:

# su -s /bin/bash postgres

If your system uses sudo to get administrative rights:

$ sudo -u postgres bash

Then, just start the client:

$ psql

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

or

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

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.

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