tux

In the examples in this blog I often use the PostgreSQL database. These are the essential notes on how to install it on Ubuntu server 16.04 and configure it for use.

Installation:

sudo apt install postgresql

Probably we will query the database from other machines so enable remote access. Edit the contents of the configuration file with an editor:

/etc/postgresql/9.5/main$ vim postgresql.conf

Modifying the following line as indicated:

listen_addresses = '*'

Now you have to set the password for the postgres user. During installation this user was created as a linux user, to connect to the database you must launch the psql program as postgres user:

sudo -u postgres psql

To modify the password for postgres user:

postgres=# \password

You will be asked to enter the new password twice.

You must make a last change to the configuration pg_hba.conf file to enable MD5 authentication for the postgres user; exit the postgres console with the \q command:

/etc/postgresql/9.5/main$ sudo vim pg_hba.conf

Locate and edit the line as follows:

# IPv4 local connections:
host    all       all       0.0.0.0/0         md5

The next step is to get confident with the database, a very useful GUI tool can be pgAdmin 4 which obviously should be installed on a computer connected to the server as the server has no graphical interface.

With the changes you just made to the configuration, you can remotely access the server via pgAdmin 4. Another way is to use the server console to access the db with shell commands.

To simplify typing, we become postgres user, then try to create and destroy a database:

$ sudo su postgres
$ createdb paolo
$ dropdb paolo

Being a MySQL user for a long time I took a moment to understand that these commands should be given without launching a client from which to operate; the only condition is that you become postgres user before working on the db, then launch the commands directly from the shell.

At this point to access the database (I've recreated the db paolo previously destroyed) you enter into a specific client:

$ psql paolo
psql (9.5.10)
Type "help" for help.
paolo=# 

From this console you can see the list of existing databases and also change the database in use. To see the list type \l, to change db \c <dbname>.

                                  List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | 
 paolo     | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | 
 postgres  | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | 
 template0 | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | it_IT.UTF-8 | it_IT.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

Into paolo db try to create the table prova with two columns: id and description.

paolo=# CREATE TABLE prova (
paolo(# id int,
paolo(# descrizione varchar(80));

You can safely go head-to-head because the query is examined only after you have typed the semicolon. To check what's just entered, you can use the psql \d+ command that returns the extended description of the table (with only \d the description is reduced):

paolo=# \d+ prova
                                 Table "public.prova"
   Column    |         Type          | Modifiers | Storage  | Stats target | Description 
-------------+-----------------------+-----------+----------+--------------+-------------
 id          | integer               |           | plain    |              | 
 descrizione | character varying(80) |           | extended |              | 

Try to insert a record inside table prova:

paolo=# insert into prova (id, descrizione) values (1, 'primo record');

For sql statements, you can use both uppercase characters, as before, and lowercase characters as in the present query. With a new query, we verify the data entered:

paolo=# select * from prova;
 id | descrizione  
----+--------------
  1 | primo record
(1 row)

The table you just created can be destroyed with the drop command.

paolo=# drop table prova;