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.
PostgreSQL provides an APT mirror with the latest builds of the software.
1 . 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
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.
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 tpye:
By default, only the postgres role is configured within the database. Adding of new roles can be done with the command
When you are logged as the postgres user, launch the command as following:
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.
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:
For more information, you may consult the official documentation.
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
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';