Installing PostgreSQL

• database
• sql
• postgresql

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.

Before you start

To complete the actions presented below, you must have:

• A Scaleway account logged into the console
• Owner status or IAM permissions allowing you to perform actions in the intended Organization
• An SSH key
• 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.

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.

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:

   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 last name:

   SELECT * FROM phonebook ORDER BY lastname;
   

   This will give you a 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 a result as follows:

   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:

   UPDATE phonebook SET city = 'London, UK', phone = '+44 7123 456789' WHERE firstname = 'Paul' AND lastname = 'Smith';
   

6. Delete the user’s data:

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