PostgreSQL

PostgreSQL is an object-relational database system that has the features of traditional commercial database systems with enhancements to be found in next-generation DBMS systems.

Installation

To install PostgreSQL, run the following command in the command prompt:

sudo apt install postgresql

Once the installation is complete, you should configure the PostgreSQL server based on your needs, although the default configuration is viable.

Configuration

PostgreSQL supports multiple client authentication methods. IDENT authentication method is used for postgres and local users, unless otherwise configured. Please refer to the PostgreSQL Administrator's Guide if you would like to configure alternatives like Kerberos.

The following discussion assumes that you wish to enable TCP/IP connections and use the MD5 method for client authentication. PostgreSQLconfiguration files are stored in the /etc/postgresql/<version>/main directory. For example, if you install PostgreSQL 9.5, the configuration files are stored in the /etc/postgresql/9.5/main directory.

To configure ident authentication, add entries to the /etc/postgresql/9.5/main/pg_ident.conf file. There are detailed comments in the file to guide you.

To enable other computers to connect to your PostgreSQL server, edit the file /etc/postgresql/9.5/main/postgresql.conf

Localisez la ligne #listen_addresses = 'localhost' et changez-la en :

listen_addresses = '*'

To allow both IPv4 and IPv6 connections replace 'localhost' with '::'

You may also edit all other parameters, if you know what you are doing! For details, refer to the configuration file or to the PostgreSQL documentation.

Now that we can connect to our PostgreSQL server, the next step is to set a password for the postgres user. Run the following command at a terminal prompt to connect to the default PostgreSQL template database:

sudo -u postgres psql template1

The above command connects to PostgreSQL database template1 as user postgres. Once you connect to the PostgreSQL server, you will be at a SQL prompt. You can run the following SQL command at the psql prompt to configure the password for the user postgres.

ALTER USER postgres with encrypted password 'votre_mot_de_passe';

After configuring the password, edit the file /etc/postgresql/9.5/main/pg_hba.conf to use MD5 authentication with the postgres user:

local   all         postgres                          md5

Enfin, vous devez redémarrer le service PostgreSQL pour initialiser la nouvelle configuration. À partir d'un terminal, tapez ceci pour redémarrer PostgreSQL:

sudo systemctl restart postgresql.service

The above configuration is not complete by any means. Please refer to the PostgreSQL Administrator's Guide to configure more parameters.

You can test server connections from other machines by using the PostgreSQL client.

sudo apt install postgresql-client
psql -h postgres.example.com -U postgres -W

Remplacez le nom de domaine par votre nom de domaine de serveur actuel.

Sauvegardes

PostgreSQL databases should be backed up regularly. Refer to the PostgreSQL Administrator's Guide for different approaches.

Ressources

  • As mentioned above the PostgreSQL Administrator's Guide is an excellent resource. The guide is also available in the postgresql-doc-9.5 package. Execute the following in a terminal to install the package:

    sudo apt install postgresql-doc-9.5
    

    To view the guide enter file:///usr/share/doc/postgresql-doc-9.5/html/index.html into the address bar of your browser.

  • Pour des informations générales sur SQL, consultez Using SQL Special Edition (en anglais) par Rafe Colburn

  • La page du wiki anglophone d'Ubuntu sur PostgreSQL contient également des informations utiles.