PostgreSQL, also often referred to as Postgres, is a powerful, open-source object-relational database system. PostgreSQL has built a strong reputation for reliability, feature robustness, and performance amongst developers.
You will know how to install PostgreSQL on your Ubuntu 20.04 LTS Focal Fossa operating system in the following guide. The same principle will work for the newer version Ubuntu 21.04 (Hirsute Hippo).
Table of Contents
- 1 Prerequisites
- 2 Installing PostgreSQL on Ubuntu 20.04
- 3 Service commands for PostgreSQL
- 4 Configure the PostgreSQL Server
- 5 UFW Firewall enable for PostgreSQL
- 6 Remote Access to PostgreSQL
- 7 Comments and Conclusion:
- Ubuntu 20.04.2.0 LTS (Focal Fossa) Server or Desktop
- Root access or Sudo user privileges.
Check and update your Ubuntu 20.04 operating system.
sudo apt update && sudo apt upgrade -y
Installing PostgreSQL on Ubuntu 20.04
By default, PostgreSQL is available in the Ubuntu main repository. You can install it from the source, which is ahead of the default repository as well.
Install PostgreSQL using apt
To install PostgreSQL using the apt package manager on Ubuntu 20.04, enter the following command:
sudo apt install postgresql-client
You will install the (PostgreSQL contribs package), which contains extras for the PostgreSQL database system.
~$ sudo apt install postgresql postgresql-contrib [sudo] password for bytesboss: Reading package lists… Done Building dependency tree Reading state information… Done The following additional packages will be installed: libllvm10 libpq5 postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common sysstat Suggested packages: postgresql-doc postgresql-doc-12 libjson-perl isag The following NEW packages will be installed: libllvm10 libpq5 postgresql postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common postgresql-contrib sysstat 0 upgraded, 9 newly installed, 0 to remove and 3 not upgraded. Need to get 30.6 MB of archives. After this operation, 121 MB of additional disk space will be used. Do you want to continue? [Y/n]
Enter “Y” to proceed with the install. The process should take less than 20 seconds. Once installed, you can verify the PostgreSQL install by typing the following command:
sudo systemctl status postgresql
~$ sudo systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Tue 2021-06-29 05:00:05 PDT; 3min 29s ago Main PID: 4238 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 4617) Memory: 0B CGroup: /system.slice/postgresql.service Jun 29 05:00:05 ubuntu systemd: Starting PostgreSQL RDBMS… Jun 29 05:00:05 ubuntu systemd: Finished PostgreSQL RDBMS.
If it is not enabled by some chance, simply type the start and enable from the boot command below.
sudo systemctl enable postgresql && sudo systemctl start postgresql
Install PostgreSQL from source
To install PostgreSQL from the source, you will need to install some extra dependencies. First, enter the following command to get your Ubuntu system up to date.
sudo apt install wget curl ca-certificates
Next, download the GPG key required for adding the PostgreSQL source repository:
wget -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \ | sudo apt-key add -
Now add create the custom PPA as follows. Note this is for Ubuntu 20.04 only. You will need to change the focal part to hirsute if you are using Ubuntu 21.04.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ \ focal-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
Proceed to update your repositories and install PostgreSQL and PostgreSQL-Contrib
sudo apt update && sudo apt-get install postgresql postgresql-contrib -y
~$ sudo apt-get install postgresql postgresql-contrib Reading package lists… Done Building dependency tree Reading state information… Done The following additional packages will be installed: libpq5 postgresql-13 postgresql-client-13 Suggested packages: postgresql-doc postgresql-doc-13 The following NEW packages will be installed: postgresql postgresql-13 postgresql-client-13 postgresql-contrib The following packages will be upgraded: libpq5 1 upgraded, 4 newly installed, 0 to remove and 7 not upgraded. Need to get 16.9 MB of archives. After this operation, 56.0 MB of additional disk space will be used. Do you want to continue? [Y/n] y
Enter “Y” and proceed with the installation. Once done, verify the install and service status.
sudo systemctl status postgresql
If installed correctly, you should get a status ok, just as in the previous example using apt manager.
Service commands for PostgreSQL
The PostgreSQL database server runs as a service under the name “postgresql“. You can manage the service by running the commands below.
- Stop PostgreSQL server:
sudo systemctl stop postgresql
- Start PostgreSQL server:
sudo systemctl start postgresql
- Restart PostgreSQL(e.g. after changing configuration settings) server:
sudo systemctl restart postgresql
- Reload PostgreSQL server:
sudo systemctl reload postgresql
- Check PostgreSQL status:
sudo systemctl status postgresql
Congratulations, you have successfully installed the PostgreSQL database server.
Configure the PostgreSQL Server
Switching to Postgres account
Before you continue, do note, only superusers and roles with create role privilege can create new roles in Postgres. During the installation, a user account called Postgres associated with the default Postgres role was created.
To engage with the account, you can do the following commands:
sudo -i -u postgres
Now with entering the above, you can access the PostgreSQL prompt immediately by typing:
You will notice straight away the terminal has changed with (postgres=#) now being in the terminal command line. This means you have successfully connected to the database.
To exit the Postgres database, you can do this by typing the following:
Alternative to switching Postgres account
An alternative way to interact with the Postgres database without changing user accounts is to use a sudo command to connect directly. You can do this by typing:
sudo -u postgres psql
This is ideal to use more, as it saves time logging you directly without having to do extra terminal bash commands.
To exit the terminal, just as the first option, you can exit by typing the following:
Create User & Database
Now you can create user roles if you wish. Note, only superusers and roles with (createrole) privilege can create new roles. To create a new user, type the following:
sudo su - postgres -c "createuser <name>"
Now you can create a PostgreSQL database for the new user you created:
sudo su - postgres -c "createdb <namedb>"
To finish off, you need to switch to the superuser Postgres account to grant permission to the new database.
Connect to Postgres super user account:
sudo -u postgres psql
Grant access to <username> to <usernamedb>:
GRANT ALL PRIVILEGES ON DATABASE <usernamedb> TO <name>;
Once done, to exit, type the following:
UFW Firewall enable for PostgreSQL
You will need to create a UFW firewall rule to allow PostgreSQL to communicate on its default port 5432 and is needed if you are planning on using anything else besides localhost. This can be done by typing the following command:
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432
Remote Access to PostgreSQL
If you require default access to PostgreSQL, this can be easily done by changing the default listening to the local interface of (127.0.0.1) to an IP, subnet or even all interfaces, which can be done in the (postgresql.conf) file.
First, open up the postgresql.conf file using nano:
sudo nano /etc/postgresql/12/main/postgresql.conf
Now scroll down until you find (Connection Settings) and change the (listen_addresses = ‘localhost’) to the <IP> address. Most would opt to listen to all interfaces, especially when running in multiple servers requiring connecting to PostgreSQL database by changing localhost to (*).
Now save the file (CTRL+O), then exit (CTRL+X) and restart your PostgreSQL instance:
sudo service postgresql restart
To check that the changes you have done are live, users can do this with the ss utility built into Ubuntu.
ss -nlt | grep 5432
You should see the following:
To finish off, you can configure the server to accept remote connections by editing the (pg_hba.conf) file.
Open up (pg_hba.conf) using nano:
sudo nano /etc/postgresql/12/main/pg_hba.conf
From this point, you can set up various options. Some examples below:
Comments and Conclusion:
In the guide, you have learnt how to install PostgreSQL using the apt package manager and via source, which is recommended more if you are after newer features and creating a role and database with remote connections set up. Overall, PostgreSQL has many advantages in terms of compatibility, scalability, security, and other features compared to other database management systems. Unlike other Database Management Systems (DBMS), it is backed by a big network of companies that form a strong united community.
For more information on using PostgreSQL, visit the official documentation.