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:
2. Install PostGres
No ADS
First, check which version of Ubuntu you are using. Pay attention to UBUNTU_CODENAME, it's important to you now.
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 |
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-12Ubuntu 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-12Ubuntu 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-12Ubuntu 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.6If 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 start3. Set up a password for the user postgres
No ADS
Firstly, you need to log in PostGres (With root right of Ubuntu).
sudo -u postgres psql
You have logged in PostGres successully:
In PostGres, "postgres" is root user, you can set up new password for this user:
ALTER USER postgres PASSWORD 'newpassword';
After changing password successfully, you can exit PostGres with the command "\q".
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
Enter the password that you set up above
4. Using PostGreSQL
No ADS
Make sure that you log in Postgres with user "postgres".
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;
See in pgAdmin3:
Connect to "mydb" database with user "myuser":
Exit PostGres:
Logon "mydb" database with user "myuser":
psql -U myuser -h localhost -d mydb
Now you have connected to 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
No ADS
pgAdmin is a software, visual tool that helps you work with the Postgres database, you can install it according to the instruction below:
6. Appendix: Fix Error
No ADS
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.
Run command:
sudo apt-get install apt-transport-https
No ADS