Jump toUpdate content

Installing PostgreSQL

Reviewed on 10 May 2021Published on 24 August 2018
  • compute
  • networking
  • database
  • sql
  • postgresql
  • server

PostgreSQL is a mature and advanced open-source relational database system. With more than 30 years of active development, the software has earned a strong reputation for reliability, feature robustness, and performance.

It runs on all major operating systems, including Linux, different Unix variants, MacOS X and Windows. This tutorial describes how to run it on an Ubuntu based server.


Installing PostgreSQL

PostgreSQL provides an APT mirror with the latest builds of the software.

Add the Postgres mirror, update the APT packet cache and install the required packages:

apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
apt-get update
apt-get upgrade
apt-get install postgresql-10 postgresql-contrib

Using PostgreSQL Roles and Databases

Postgres uses a concept similar to regular Unix-style accounts, called roles. However Postgres does not distinguish between users and groups and uses the more flexible term role.

By default Postgres is configured to use ident authentication, which means matching a role with a Unix/Linux system account

If a role exists in Postgres a system user with the same name is able to sign in as that role.

During the installation a user called postgres has been created and is used to log into that account having the default Postgres role.

Logging into PostgreSQL from the postgres Account

It is possible to login from the postgres account by using an intermediate bash.

Switch into the postgres account:

sudo -i -u postgres

To access the command prompt, type:


To exit the prompt, type:


Creating New Roles

By default, only the postgres role is configured within the database. Adding of new roles can be done with the command createrole.

When you are logged as the postgres user, launch the command as following:

createuser --interactive

You will be asked to enter the name of the new role and some additional questions:

root@pgsql:~# sudo -u postgres createuser --interactive
Enter name of role to add: bill
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

More information about the usage of the createuser command is available in the official documentation.

Creating New Databases

The authentication system of Postgres assumes by default that a database exists with the same name as the role .

This means if you have created a role bill in the previous step, Postgres will by attempt by default to connect to a database bill.

When you are logged directly as the postgres user, run the following command:

createdb bill

For more information, you may consult the official documentation.

Connecting to a Postgres Prompt with the new Role

The ident based authentication requires a matching Linux user with the same name as the Postgres role and database. If it does not exist yet, create the user with the following command:

sudo adduser bill

If the previous steps have been followed, login now into the database with the following command:

sudo -u bill psql

Creating and Querying Databases

To understand the basic SQL syntax, we create a phonebook that contains a user personal details:

  1. Connect to the Postgres prompt.

  2. Create a new table called phonebook, that contains fields for the phone number, first and last name and the city:

    CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), city VARCHAR(64));
  3. Insert data into the table. You may repeat this command if you want to add more than one set of data:

    INSERT INTO phonebook(phone, firstname, lastname, city) VALUES('+33 1 23 45 67 89', 'Paul', 'Smith', 'Paris, FR');
  4. Select data from the table. Run the following command to retrieve all data sets, sorted by the lastname

    SELECT * FROM phonebook ORDER BY lastname;

    This will give you an result like the following:

    bill=# SELECT * FROM phonebook ORDER BY lastname;
    phone | firstname | lastname | city
    +33 1 23 45 67 89 | Paul | Smith | Paris, FR
    +33 9 87 65 43 21 | Jessica | White | Marseille, FR
    (2 rows)

    To retrieve all entries in the phonebook where the family name is Smith, run:

    SELECT * FROM phonebook WHERE lastname = 'Smith';

    You will get an result as the following:

    bill=# SELECT * FROM phonebook WHERE lastname = 'Smith';
    phone | firstname | lastname | city
    +33 1 23 45 67 89 | Paul | Smith | Paris, FR
    (1 row)
  5. Update the city of a user:

    UPDATE phonebook SET city = 'London, UK', phone = '+44 7123 456789' WHERE firstname = 'Paul' AND lastname = 'Smith';
  6. Delete the data of a user:

    DELETE FROM phonebook WHERE firstname = 'Paul' AND lastname = 'Smith';