How to Install Postgresql 12 in Ubuntu 18 04 bionic

Linux

Install and configure Postgresql 12 in ubuntu 18.04

Postgres is an open-source relational database.PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

Ubuntu 18.04 default repository comes with PostgreSQL packages

Step 1: install postgresql with default repository

apt install postgresql postgresql-contrib

root@ubuntu18:~# apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc locales-all postgresql-doc-10 libjson-perl isag
The following NEW packages will be installed:
  postgresql postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common postgresql-contrib sysstat
0 upgraded, 7 newly installed, 0 to remove and 62 not upgraded.
Need to get 5,186 kB of archives.
After this operation, 20.5 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Press y to install all packages.

once packages are installed check postgresql version and service status.

root@ubuntu18:~# sudo -u postgres psql -c "SELECT version();"
PostgreSQL 10.10 (Ubuntu 10.10-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)

root@ubuntu18:~# systemctl status postgresql

Also Read -> How to Install Postgresql on Ubuntu 20 04

Step 2: Install postgresql 12 using apt repo

By using PostgreSQL apt repository from its official site we can install a specific version of our choice.To use the apt repository, follow below steps

download and add the GPG key from the repository to further secure the installation.

root@ubuntu18:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Then create postgresql repository.

root@ubuntu18:~# nano /etc/apt/sources.list.d/pgdg.list

Add following line to above file and save it.

deb http://apt.postgresql.org/pub/repos/apt/bionic-pgdg main

Also Read -> How to Install PHP Composer on Ubuntu 20 04 and 18 04

If you use a different Ubuntu version that 18.04 (Bionic), then replace 'bionic' with the name of the used Ubuntu version.Now update repository and install postgresql

root@ubuntu18:~# apt update
root@ubuntu18:~# apt install postgresql-12

we can check postgrsql status and version with following commands.

root@ubuntu18:~# systemctl status postgresql
root@ubuntu18:~# sudo -u postgres psql -c "SELECT version();"

Step 3: postgresql authentication

nano /etc/postgresql/12/main/pg_hba.conf

Modify below line from peer to md5

# "local" is for Unix domain socket connections only
local   all             all                                     md5

this means any user will connect to all databases with password only.Also you can set with specific location by adding  ip with md5 password method.

Also set listen address in postgresql.conf to allow remote connections

nano /etc/postgresql/12/main/postgresql.conf

uncomment below line and chnage as below

listen_addresses = '*'

save it and restart postgresql service with following command.

systemctl restart postgresql

Also Read -> How to Install JAVA on Ubuntu 18 04 bionic

Step 4: create database

Now, execute the command below to change to the default Postgres account and run psql command to log into the PostgreSQL prompt.

root@ubuntu18:~# su - postgres
postgres@ubuntu18:~$ psql
psql (12.1 (Ubuntu 12.1-1.pgdg18.04+1))
Type "help" for help.
postgres=#

create database and user

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# CREATE ROLE testuser;
CREATE ROLE
postgres=# \password testuser
Enter new password:
Enter it again:
postgres=#

you can check list of databases

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

To list the existing user roles, use below command.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Cannot login                                               | {}

quit and exit from postgres shell

postgres-# \q
postgres@ubuntu18:~$ exit
logout

That's it. Now we have successfully installed postgresql on ubuntu 18.4 system.