Instructions for installing and configuring PostgreSQL Database on Ubuntu

PostgreSQL

1- Introduction

Documentation is based on
  • Ubuntu 14, 16, 18, 20

  • PostGres 9.6, 10, 11, 12

This documentation is applicable to Ubuntu 12, 14 or 16, …
You can find versions of PostGres here:

2- Install PostGreSQL

First of all check which version of Ubuntu you are using . Pay attention to UBUNTU_CODENAME , it’s important to you right now.
first
cat /etc/*release
Version Code name Release date End of Life date
Ubuntu 20.4 LST focal August 13, 2020
Ubuntu 18.04.1 LTS bionic July 26, 2018 April 2023
Ubuntu 18.04 LTS bionic April 26, 2018 April 2023
Ubuntu 16.04.4 LTS xenial March 1, 2018 April 2021
Ubuntu 16.04.3 LTS xenial August 3, 2017 April 2021
Ubuntu 16.04.2 LTS xenial February 16, 2017 April 2021
Ubuntu 16.04.1 LTS xenial July 21, 2016 April 2021
Ubuntu 16.04 LTS xenial April 21, 2016 April 2021
Ubuntu 14.04.5 LTS trusty August 4, 2016 April 2019
Ubuntu 14.04.4 LTS trusty February 18, 2016 HWE August 2016
Ubuntu 14.04.3 LTS trusty August 6, 2015 HWE August 2016
Ubuntu 14.04.2 LTS trusty February 20, 2015 HWE August 2016
Ubuntu 14.04.1 LTS trusty July 24, 2014 April 2019
Ubuntu 14.04 LTS trusty April 17, 2014 April 2019

Setting:

Execute the following commands on the Terminal , notice: Replace {UBUNTU_CODENAME} by your UBUNTU_CODENAME .
sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ {UBUNTU_CODENAME}-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-12
Ubuntu 20:
sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ focal-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-12
Ubuntu 18:
sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-12
Ubuntu 16:
sudo add-apt-repository "deb https://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6
If you receive an error while installing, see the appendix at the end of this article:
E: The method driver /usr/lib/apt/methods/https could not be found.
N: Is the package apt-transport-https installed?
E: Failed to fetch https://apt.postgresql.org/pub/repos/apt/dists/xenial-pgdg/InRelease
E: Some index files failed to download. They have been ignored, or old ones used instead.
Start postgres:
sudo service postgresql start

3- Set password for postgres user

First of all you need to log in to PostGres (С Ubuntu доступа корневой).
sudo -u postgres psql
You are logged into PostGres :
In PostGres , “postgres” is the root user, you can set a password for this user:
LTER USER postgres PASSWORD 'newpassword';
After successfully changing your password, you can exit PostGres with the command “\ q” .
After the password of the user “postgres” has been set, you can login to Postgres via the command:
psql -U postgres -h localhost
Enter the password you have set up in the above step

4- Using PostGreSQL

Make sure you are logged into Postgres as the user “postgres” .
Run the following three commands to create the user, the database, and assign all the rights to use that database to the newly created user.
CREATE USER myuser with PASSWORD '123';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
See on pgAdmin3 :

Connect to the database “mydb” with the user “myuser”:

Exit PostGres :
Login to the database “mydb” with the user “myuser” :
first
psql -U myuser -h localhost  -d mydb
You are now connected to the database “mydb” :

Create Table

-- Create table Account
Create table Account (User_Name varchar(30), Full_Name varchar(64) ) ;
-- Insert 2 row to Account.
Insert into Account(user_name, full_name) values ('gates', 'Bill Gate');
Insert into Account(user_name, full_name) values ('edison', 'Thomas Edison');
-- Query
Select * from Account;
See on pgAdmin3 :

5- Install pgAdmin

pgAdmin is a software, a visual tool to help you work with the Postgres database , you can install it according to the instructions below:

6- Appendix: Error correction

If you receive the error:
E: The method driver /usr/lib/apt/methods/https could not be found.
N: Is the package apt-transport-https installed?
E: Failed to fetch https://apt.postgresql.org/pub/repos/apt/dists/xenial-pgdg/InRelease
E: Some index files failed to download. They have been ignored, or old ones used instead.
Run the following command:
sudo apt-get install apt-transport-https