PostgreSQL (aka Postgres) is an object-relational database management system (ORDBMS), available for Linux, Microsoft Windows, FreeBSD, OpenBSD, Solaris, and Mac OS.
PostgreSQL is released under the PostgreSQL License, a free and open source permissive software license, developed by the PGDG (PostgreSQL Global Development Group), a group of individual volunteers and corporate companies.
Here, we will go through steps on how to install PostgreSQL 11 / 10 on Ubuntu 18.04 / Ubuntu 16.04 & Linux Mint 19 / Linux Mint 18.
Add PostgreSQL repository
PostgreSQL publishes deb packages for Ubuntu, and their packages are fresher than those available in OS base repository.
Import the PostgreSQL signing key.
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -
Add the PostgreSQL repository to /etc/apt/sources.list.d/postgresql.list file.
### Ubuntu 18.04 / LinuxMint 19 ### echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list ### Ubuntu 16.04 / LinuxMint 18 ### echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
Install PostgreSQL
Execute the below command to update the repository index.
sudo apt-get update
Install the PostgreSQL package using the apt command.
### PostgreSQL 11 ### sudo apt-get install -y postgresql-11 ### PostgreSQL 10 ### sudo apt-get install -y postgresql-10
Control PostgreSQL service
To start PostgreSQL service, run:
sudo systemctl start postgresql
To stop PostgreSQL service, run:
sudo systemctl stop postgresql
To restart PostgreSQL service, run:
sudo systemctl restart postgresql
To check the running status of PostgreSQL service, run:
sudo systemctl status postgresql
Output:
● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sat 2019-02-23 11:30:32 IST; 3min 5s ago Main PID: 5285 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 2323) CGroup: /system.slice/postgresql.service Feb 23 11:30:32 desktop systemd[1]: Starting PostgreSQL RDBMS... Feb 23 11:30:32 desktop systemd[1]: Started PostgreSQL RDBMS.
Configure PostgreSQL Server
PostgreSQL listens on loopback adapter (127.0.0.1) only. Sometimes the application hosted on external may need to connect to the database. For that, we need to configure PostgreSQL to listen on all adaptors.
### PostgreSQL 11 ### sudo nano /etc/postgresql/11/main/postgresql.conf ### PostgreSQL 10 ### sudo nano /etc/postgresql/10/main/postgresql.conf
Set the listen_addresses to * or <IPAddress>.
listen_addresses = '*'
Restart PostgreSQL service.
sudo systemctl restart postgresql
Confirm the PostgreSQL listening on port 5432 using netstat command.
sudo netstat -antup | grep 5432
Output:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 6518/postgres tcp6 0 0 :::5432 :::* LISTEN 6518/postgres
READ: netstat command not found on Ubuntu – Quick Fix
Access PostgreSQL
To manage the database, you need to log in as the postgres user.
sudo su -l postgres
Use the psql, an interactive shell for working with PostgreSQL database.
psql
Output:
postgres@server:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=#
Secure PostgreSQL database
We will now set a password for Linux user (postgres) as well as DB administrator (postgres).
Set password for Linux user (postgres)
sudo passwd postgres
Output:
raj@server:~$ sudo passwd postgres Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully
Set password for DB administrator (postgres)
su - postgres psql
On psql shell, run below command to change database admin password.
postgres=# \password
OR
postgres=# \password postgres
Output:
postgres=# \password postgres Enter new password: Enter it again:
PostgreSQL DB Commands
The syntax for creating a database is:
$ su - postgres $ createdb test
Log in to DB.
$ psql test
The syntax for creating a user is:
test=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'password';
Check whether the user has been created or not.
test=# \du
Output:
test=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
raj | Superuser | {}
Log in with the user “raj”.
psql -h localhost -d test -U raj
Output:
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1)) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. test=#
The syntax for listing databases:
test=# \l
Output:
test=# \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)
That’s All. You have successfully installed PostgreSQL 11 / 10 on Ubuntu 18.04 / Ubuntu 16.04 & Linux Mint 19 / Linux Mint 18.
