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

===== Installation =====


Required packages: postgresql postgresql-client


<code bash>
# apt-get install postgresql postgresql-client
</code>

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:

<code bash>
# aptitude search postgresql
</code>

===== User access =====


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

You will need administrative rights.

As root:

<code bash>
# su -s /bin/bash postgres
</code>
If your system uses sudo to get administrative rights:
<code bash>
$ sudo -u postgres bash
</code>
Then, just start the client:

<code bash>
$ psql
</code>

===== New User and database =====

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

<code bash>
# adduser mypguser    #from regular shell
</code>

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

<code bash>
# su - postgres
# createuser mypguser    #from regular shell
# createdb -O mypguser mypgdatabase
</code>

Connect as user mypguser to new database

<code bash>
# su - mypguser
$ psql mypgdatabase
</code>
or
<code bash>
# psql -d mypgdatabase -U mypguser
</code>
If you get errors like:

<code bash>
psql: FATAL:  Ident authentication failed for user "mypguser"
</code>

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

<code bash>
local   all         all             trust     # replace ident or peer with trust
</code>
reload postgresql

<code bash>
# /etc/init.d/postgresql reload
</code>

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

<code bash>
pg_lsclusters
</code>