PostgreSQL (aka Postgres) is an open-source
object-relational database management system (ORDBMS), developed by PostgreSQL Global Development Group, a group of individual volunteers and corporate companies.
PostgreSQL is available for
Linux,
Microsoft Windows, FreeBSD, OpenBSD, Solaris, and Mac OS.
Here, we will see how to install
PostgreSQL on
Ubuntu 20.04.
Add PostgreSQL repository
PostgreSQL is available from both Ubuntu (sudo apt update && sudo apt install -y postgresql) and PostgreSQL’s repository.
PostgreSQL’s repository packages are fresher than those available in the Ubuntu repository. So, here we will install PostgreSQL from the PostgreSQL’s repository.
Import the PostgreSQL’s signing key.
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -
Add the PostgreSQL repository information to postgresql.list file.
echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
Install PostgreSQL
Execute the below command to update the repository index.
sudo apt update
Install the PostgreSQL package using the
apt command.
### PostgreSQL 12 ### sudo apt install -y postgresql-12 ### PostgreSQL 11 ### sudo apt install -y postgresql-11
By default, the PostgreSQL service is started automatically after the installation.
To check the status of PostgreSQL service, run:
Replace status with start, stop, restart to star, stop, and restart PostgreSQL service respectively.
sudo systemctl status postgresql
Output: ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Tue 2020-05-05 06:02:21 EDT; 4min 25s ago Main PID: 3603 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 2319) Memory: 0B CGroup: /system.slice/postgresql.service May 05 06:02:21 ubun2004 systemd[1]: Starting PostgreSQL RDBMS... May 05 06:02:21 ubun2004 systemd[1]: Finished PostgreSQL RDBMS.
Configure PostgreSQL Server
PostgreSQL server listens on localhost (127.0.0.1) by default. Sometimes, we need to configure PostgreSQL to listen on all adaptors or system IP address to allow applications hosted on external machines to connect to the database
### PostgreSQL 12 ### sudo nano /etc/postgresql/12/main/postgresql.conf ### PostgreSQL 11 ### sudo nano /etc/postgresql/11/main/postgresql.conf
Set the listen_addresses to * or
listen_addresses="*"
Restart the PostgreSQL service.
sudo systemctl restart postgresql
Confirm that the PostgreSQL listening on port 5432 using the
netstat command.
sudo netstat -antup | grep 5432
Output: tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 6172/postgres tcp6 0 0 :::5432 :::* LISTEN 6172/postgres
READ:
netstat command not found on Ubuntu 20.04 – Quick Fix
Access PostgreSQL
To manage the database, you need to log in as the postgres user.
sudo su -l postgres
Use the psql command to enter into the interactive shell for working with the PostgreSQL database.
psql
Output:
postgres@ubuntu2004:~$ psql psql (12.2 (Ubuntu 12.2-4)) Type "help" for help. postgres=#
Secure PostgreSQL database
We will now set a password for postgres (Unix User) as well as postgres (DB administrator).
Set password for Unix user (postgres)
sudo passwd postgres
Set password for DB administrator (postgres)
sudo su - postgres
psql
On psql shell, run below command to change the database administrator (postgres) password.
postgres=# password
OR
postgres=# password postgres
Output:
postgres=# password postgres Enter new password: Enter it again:
PostgreSQL DB Commands
Create Database
To create a database:
postgres=# CREATE DATABASE test;
Create DB user
To creating a user:
postgres=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'password';
List Database users
Check whether the user has been created or not.
postgres=# du
Output:
postgres=# du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
raj | Superuser | {}
List Database
To list databases:
postgres=# l
Output:
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
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
Login to Database
Log in with the user “raj”.
$ psql -h localhost -d test -U raj
Output: psql (12.2 (Ubuntu 12.2-4)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. test=#
Delete User
To remove a user:
postgres=# DROP USER raj;
Delete Database
To remove a database:
postgres=# DROP DATABASE test;
Conclusion
That’s All. You have successfully installed
PostgreSQL on
Ubuntu 20.04.
