Install PostgreSQL Database on Ubuntu
1. Introduction
Document is based on:
- Ubuntu 14, 16, 18, 20
- PostGres 9.6, 10, 11, 12
The post can be applied to Unbuntu with the version of 12, 14 or 16,...
You can find out other versions of PostGres here:
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090436-vi.webp)
2. Install PostGres
First, check which version of Ubuntu you are using. Pay attention to UBUNTU_CODENAME, it's important to you now.
cat /etc/*release
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090451-vi.webp)
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 |
Installing:
Execute the following commands on the Terminal, note: 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 when installing, please see the appendix at the end of this post: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 up a password for the user postgres
Firstly, you need to log in PostGres (With root right of Ubuntu).
sudo -u postgres psql
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090574-vi.webp)
You have logged in PostGres successully:
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090580-vi.webp)
In PostGres, "postgres" is root user, you can set up new password for this user:
ALTER USER postgres PASSWORD 'newpassword';
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090587-vi.webp)
After changing password successfully, you can exit PostGres with the command "\q".
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090593-vi.webp)
After the password of the user "postgres" has been set up, you can log in to the Postgres through the command:
psql -U postgres -h localhost
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090600-vi.webp)
Enter the password that you set up above
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090606-vi.webp)
4. Using PostGreSQL
Make sure that you log in Postgres with user "postgres".
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090620-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090621-vi.webp)
Run 3 following commands to create user, database, and grant all the privileges of using the database to the use that is newly created.
CREATE USER myuser with PASSWORD '123';
CREATE DATABASE mydb;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090628-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090629-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090630-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090631-vi.webp)
See in pgAdmin3:
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090637-vi.webp)
Connect to "mydb" database with user "myuser":
Exit PostGres:
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090648-vi.webp)
Logon "mydb" database with user "myuser":
psql -U myuser -h localhost -d mydb
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090655-vi.webp)
Now you have connected to database "mydb":
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090661-vi.webp)
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;
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090668-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090669-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090670-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090671-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090672-vi.webp)
See on pgAdmin3:
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090678-vi.webp)
5. Install pgAdmin
pgAdmin is a software, visual tool that helps you work with the Postgres database, you can install it according to the instruction below:
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090695-vi.webp)
6. Appendix: Fix Error
If you receive an error message:
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.
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090710-vi.webp)
Run command:
sudo apt-get install apt-transport-https
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090717-vi.webp)
![](https://s1.o7planning.com/web-rs/web-image/en/arf-1090718-vi.webp)