How to Install PostgreSQL on Ubuntu 18.04

Install PostgreSQL on Ubuntu

Install PostgreSQL on Ubuntu by using following command.

sudo apt update

sudo apt install postgresql postgresql-contrib

Output:

root@vps:~# sudo apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libpq5 libsensors4 postgresql-10 postgresql-client-10
postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl
openssl-blacklist isag
The following NEW packages will be installed:

Verifying PostgreSQL Installation

sudo -u postgres psql -c "SELECT version();"

Output:

root@vps:~# sudo -u postgres psql -c "SELECT version();"
could not change directory to "/root": Permission denied
                                                            version
                 ----------------------------------------------------------------------------------------------------------------------
   PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1)  7.4.0, 64-bit
   (1 row)

To log in to the PostgreSQL server as the postgres user first you need to switch to the user postgres

and then you can access a PostgreSQL prompt using the psql utility

sudo su - postgres

psql

Output:

postgres@vps:~$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

To exit out of the PostgreSQL shell type.

\q

You can also access the PostgreSQL prompt without switching users using the sudo command.

sudo -u postgres psql

The postgres user is typically used only from the local host and it is recommended not to set the password for this user.

Creating PostgreSQL Role and Database

Create a new PostgreSQL Role.

sudo su - postgres -c "createuser john"

Create a new PostgreSQL Database.

sudo su - postgres -c "createdb johndb"

Grant privileges.

sudo -u postgres psql

grant all privileges on database johndb to john;

Output:

postgres=# grant all privileges on database johndb to john;
GRANT

To exit out of the PostgreSQL shell type.

\q

Enable remote access to PostgreSQL server.

By default, the PostgreSQL server listens only on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section.

sudo vim /etc/postgresql/10/main/postgresql.conf

Output:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = '*'         # what IP address(es) to listen on;
                                    # comma-separated list of addresses;
                                    # defaults to 'localhost'; use '*' for all
                                    # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

Restart the PostgreSQL service.

sudo service postgresql restart

Verify the changes with the ss utility.

ss -nlt | grep 5432

Output:

root@vps:~# ss -nlt | grep 5432
LISTEN   0         128               127.0.0.1:5432             0.0.0.0:*
LISTEN   0         128                   [::1]:5432                [::]:*