---
title: Installing PostgreSQL
description: Learn how to install PostgreSQL on your Scaleway server with this detailed guide. Follow our step-by-step instructions to set up and optimize PostgreSQL for high performance and reliability.
tags: database sql postgresql
products:
  - postgresql-and-mysql
  - instances
dates:
  validation: 2025-07-02
  posted: 2018-08-24
  validation_frequency: 12
difficulty: beginner
usecase:
  - deploy-external-software
ecosystem:
  - third-party
---
import Requirements from '@macros/iam/requirements.mdx'


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 PostgreSQL on a server running Ubuntu.

<Requirements />

- A Scaleway account logged into the [console](https://console.scaleway.com)
- [Owner](/iam/concepts/#owner) status or [IAM permissions](/iam/concepts/#permission) allowing you to perform actions in the intended Organization
- An [SSH key](/organizations-and-projects/how-to/create-ssh-key/)
- An [Instance](/instances/how-to/create-an-instance/) running on Ubuntu Bionic Beaver or later
- `sudo` privileges or access to the root user

## Installing PostgreSQL

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

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

```
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt update
apt install postgresql-16 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 can sign in as that role.

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

### Logging into PostgreSQL from the Postgres account

It is possible to log in from the `postgres` account by using an intermediate bash.

Switch to the `postgres` account:

```
sudo -i -u postgres
```

To access the command prompt, type:

```
psql
```

To exit the prompt, type:

```
\q
```

## Creating new roles

By default, only the **Postgres** role is configured within the database.

1. Add a new role using the following command:
    ```
    createrole
    ```
2. Launch the following command after you are logged as the **Postgres** user:
    ```
    createuser --interactive
    ```
3. Answer the prompts as in the example below:
    ```
    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](https://www.postgresql.org/docs/11/static/app-createuser.html).

## 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 **bill** role in the previous step, Postgres attempts to connect to a database bill by default.

Run the following command, after you log in as the Postgres user:

```
createdb bill
```

For more information, you consult the [official documentation](https://www.postgresql.org/docs/11/static/app-createdb.html).

## 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 the user does not exist yet, create it with the following command:

```
sudo adduser bill
```

Log into the database.

```
sudo -u bill psql
```

## Creating and querying databases

To understand the basic SQL syntax, we will create a phonebook that contains a user's 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 city:
    ```sql
    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:
    ```sql
    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 last name:
    ```sql
    SELECT * FROM phonebook ORDER BY lastname;
    ```

    This will give you a result like the following:

    ```sql
    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:

    ```sql
    SELECT * FROM phonebook WHERE lastname = 'Smith';
    ```

    You will get a result as follows:

    ```sql
    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 the user:
    ```sql
    UPDATE phonebook SET city = 'London, UK', phone = '+44 7123 456789' WHERE firstname = 'Paul' AND lastname = 'Smith';
    ```
6. Delete the user's data:
    ```sql
    DELETE FROM phonebook WHERE firstname = 'Paul' AND lastname = 'Smith';
    ```