By clicking or navigating this website site, you agree to allow our collection of information on Scaleway to offer you an optimal user experience and to keep track of statistics through cookies. Learn more about our Cookie Policy.

Installation of PostgreSQL

PostgreSQL Overview

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.

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:

psql

To exit the prompt tpye:

\q

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