Hometutorials
install postgresql

Jump toUpdate content

Installing PostgreSQL

Reviewed on 01 March 2022Published 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 PostgreSQL on a server running Ubuntu.

Requirements:

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 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 into 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
  1. Launch the following command after you are logged as the postgres user.
createuser --interactive
  1. 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 the 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, for their first and last name, and for 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 last name.

    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 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';