How to Install PostgreSQL and pgAdmin on Ubuntu 22.04

PostgreSQL or Postgres is an open-source object-relational database management that implements the SQL querying language. It has advanced features like reliable transactions and concurrency without read locks, allowing us to build fault-tolerant environments and complex applications.

pgAdmin is an open-source administration tool for PostgreSQL. It supports multiple platforms, including Windows, macOS, and Linux.

This tutorial will teach you how to install PostgreSQL 14 and pgAdmin 4 on a Ubuntu 22.04 server.

Prerequisites

• A server running Ubuntu 20.04.

• A non-root sudo user.

• A fully qualified domain name (FQDN) like pgadmin.example.com.

• Make sure everything is updated.

  $ sudo apt update
  $ sudo apt upgrade
  

• Your system needs a few packages.

  $ sudo apt install wget curl nano software-properties-common dirmngr apt-transport-https gnupg2 ca-certificates lsb-release ubuntu-keyring unzip -y
  

  Some of these packages may already be installed on your system.

Step 1 - Install PostgreSQL

Ubuntu 22.04 ships with the PostgreSQL 14 by default. To install, issue the following command.

$ sudo apt install postgresql postgresql-contrib

The postgresql-contrib package contains some extra utilities.

You can also use PostgreSQL's official APT repository to install. Run the following command to add the PostgreSQL GPG key.

$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql-key.gpg >/dev/null

Add the APT repository to your sources list.

$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/postgresql-key.gpg arch=amd64] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Update the system repository.

$ sudo apt update

Now, you can install PostgreSQL using the command mentioned above.

Check the status of the PostgreSQL service.

$ sudo systemctl status postgresql
? postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2022-05-16 11:20:35 UTC; 5 days ago
    Process: 30544 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 30544 (code=exited, status=0/SUCCESS)
        CPU: 2ms

May 16 11:20:35 postgresql systemd[1]: Starting PostgreSQL RDBMS...
May 16 11:20:35 postgresql systemd[1]: Finished PostgreSQL RDBMS.

You can see that the service is enabled and running by default.

Step 2 - Using PostgreSQL roles and Authentication methods

Postgres uses a concept called roles to handle authentication and database access. A role represents a user or a group of users. Postgres doesn't differentiate between users and groups.

Postgres supports multiple authentication methods, such as the following

• Trust - allows connection without any password as long as the conditions defined in the configuration file are met.
• Password - This is self-explanatory and requires a password for access.
• Ident - this method is supported only on TCP/IP connections and is useful for remote connections.
• Peer - same as Ident but is used only for local connections.

By default, the installation of Postgres uses the peer authentication method. Under this method, it associates a Postgres role with a matching Linux system account. If a role exists, then you can log in using a Linux account with the same username.

The PostgreSQL install creates a user account  postgres associated with the default role. You can switch to the postgres account on your system using the following command.

$ sudo -i -u postgres

Access the PostgreSQL shell by using the psql command.

postgres@howtoforge:~$ psql

You will be logged in to the PostgreSQL shell.

psql (14.3 (Ubuntu 14.3-1.pgdg22.04+1))
Type "help" for help.

postgres=#

Type exit or \q to close the shell.

You can also access the PostgreSQL shell in a single command. The following command runs the shell under the postgres account. Exiting the shell will bring you to your current system user.

$ sudo -u postgres psql

Step 3 - Create a new role

You can create new roles using the createuser command or you can create them from the psql shell. To use the createuser command, you need to be logged in to the postgres account. If logged in to postgres, run the following command to create a new role.

postgres@howtoforge:~$ createuser --interactive

The --interactive flag will prompt you for the role's name and ask whether it should have superuser permissions.

You can also run the command directly from your account.

$ sudo -u postgres createuser --interactive

You will get the following output.

Enter name of role to add: navjot
Shall the new role be a superuser? (y/n) y

To create the role from the psql shell, use the following command once you are in the shell.

postgres-# CREATE ROLE username WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'yourpassword';

Here, we are creating the role with permissions like Superuser, Create database, Create role, and Login.

You can check the list of all the users with the \du command.

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 username  | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Step 4 - Create a database

To create a database, we will use the createdb command. If logged in to the postgres account, run the following command to create a new database.

postgres@howtoforge:~$ createdb howtoforge

You can also run the command from your account.

$ sudo -u postgres createdb howtoforge

You can also create the database using the psql shell. To do that, run the following command from the shell.

postgres-# CREATE DATABASE howtoforge;

We can grant privileges to the user we just created on the database by running the following command.

postgres-# GRANT ALL PRIVILEGES ON DATABASE howtoforge TO username;

You can check the list of all databases with the \l command.

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 howtoforge| postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | username=CTc/postgres
 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
(4 rows)

Step 5 - Enable remote access

By default, the PostgreSQL server only listens on the localhost interface. To enable remote access, open the file /etc/postgresql/14/main/postgresql.conf and change the line #listen_addresses = 'localhost' by uncommenting it and modifying as shown below in the CONNECTIONS and AUTHENTICATION section.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

Save the file by pressing Ctrl + X and entering Y when prompted.

Restart the PostgreSQL service.

$ sudo systemctl restart postgresql

To verify the change, run the following command.

$ ss -nlt | grep 5432

You will get the following output that shows PostgreSQL is listening on all interfaces.

LISTEN 0      244          0.0.0.0:5432      0.0.0.0:*
LISTEN 0      244             [::]:5432         [::]:*

The next step is to configure the server to accept remote connections. This is done by editing the file /etc/postgresql/14/main/pg_hba.conf. Open the file for editing.

$ sudo nano /etc/postgresql14/main/pg_hba.conf

Here you can configure access to individual or all databases and define which users can access them over what interfaces. For the password authentication to work, choose scram-sha-256 as the authentication method. Here are some examples that you can use.

# TYPE  DATABASE        USER                ADDRESS                 METHOD

# The username can access all databases from all locations using scram-sha256 password
host    all             username            0.0.0.0/0                scram-sha256

# The username can access only the howtoforge from all locations using scram-sha256 password
host    howtoforge      username            0.0.0.0/0                scram-sha256

# The username can access all databases from a trusted location (192.168.1.134) without a password
host    all             username            192.168.1.134            trust

After you are finished, save the file by pressing Ctrl + X and entering Y when prompted.

There is one last step remaining before remote access can work. You will need to open the firewall port 5432. Run the following command to open the port.

$ sudo ufw allow 5432/tcp

This will allow any IP address to access PostgreSQL. You should however restrict it to trusted IP ranges. To do that, run the following command instead. The following command allows access to PostgreSQL from the 192.168.1.0/24 subnet.

$ sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Step 6 - Install pgAdmin 4

pgAdmin can be installed either as an Ubuntu package or using Python. Installing it as an ubuntu package installs the Apache server by default. However, we will be using Nginx for our tutorial. Therefore, we will install it using the Python wheel.

Create the data and log directories for pgAdmin.

$ sudo mkdir /var/lib/pgadmin
$ sudo mkdir /var/log/pgadmin

Create an app folder for pgAdmin.

$ sudo mkdir /opt/pgadmin

Switch to the root user.

$ sudo su -

First, install several important packages required by Python.

$ apt install build-essential python3-dev python3-venv python3-pip

Create a python virtual environment and activate it.

$ python3 -m venv /opt/pgadmin/venv
$ source /opt/pgadmin/venv/bin/activate

Install pgAdmin and uWSGI.

(venv) root@postgresql:-$ pip install --upgrade pip
(venv) root@postgresql:-$ pip install --upgrade setuptools
(venv) root@postgresql:-$ pip install wheel
(venv) root@postgresql:-$ pip install pgadmin4
(venv) root@postgresql:-$ pip install uwsgi

Run pgAdmin's setup file. Check the path. Ubuntu 22.04 ships with Python 3.10 by default and therefore it is visible in the path. Make sure you choose the correct path.

(venv) root@postgresql:-$ python3 /opt/pgadmin/venv/lib/python3.10/site-packages/pgadmin4/setup.py

Follow setup instructions and take note of your email and password here.

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: [email protected]
Password: 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Deactivate the virtual environment.

(venv) root@postgresql:-$ deactivate

Switch back to the normal user.

(venv) root@postgresql:-$ exit

Create a new system user pgadmin. It will be used to run the pgAdmin processes.

$ sudo adduser --system --group --home /var/lib/pgadmin --disabled-login --shell /usr/sbin/nologin pgadmin

Since we will be using Nginx, give it ownership of the above directories along with the newly created user.

$ sudo chown -R pgadmin:nginx /var/lib/pgadmin /var/log/pgadmin /opt/pgadmin

Step 7 - Integrate uWSGI and pgAdmin

The next step is to create a WSGI server that listens at the UNIX socket /tmp/pgadmin.sock. Create a uWSGI configuration file called pgadmin-uwsgi.ini inside the folder /opt/pgadmin.

$ sudo nano /opt/pgadmin/pgadmin-uwsgi.ini

Add the following code to it. Make sure the python version in the path matches the version installed in your system.

[uwsgi]
socket = /var/lib/pgadmin/pgadmin4.sock
chdir = /opt/pgadmin/venv/lib/python3.10/site-packages/pgadmin4/
module = pgAdmin4:application
threads = 20
processes = 1
mount = /=pgAdmin4:app
manage-script-name = true
chmod-socket = 660

Save the file by pressing Ctrl + X and entering Y when prompted.

Step 8 - Create the Service file

Now that we have configured pgAdmin and uWSGI, we need to create a systemd service unit file for the same. The service file will allow the server to automatically start uWSGI and serve the pgAdmin 4 application during boot time.

Create and open the service file for editing.

$ sudo nano /etc/systemd/system/pgadmin-uwsgi.service

Paste the following code in it.

[Unit]
Description=pgadmin4 on uWSGI
Requires=network.target
After=network.target
[Service]
User=pgadmin
Group=nginx
Environment="PATH=/opt/pgadmin/venv/bin"
ExecStart=/opt/pgadmin/venv/bin/uwsgi --ini /opt/pgadmin/pgadmin-uwsgi.ini
[Install]
WantedBy=multi-user.target

Save the file by pressing Ctrl + X and entering Y when prompted.

As you can see, we have chosen nginx as the group to run the pgAdmin process since we will be using it for proxying.

Enable and start the uWSGI service.

$ sudo systemctl enable pgadmin-uwsgi --now

Check the status of the service.

$ sudo systemctl status pgadmin-uwsgi

Step 9 - Install SSL

Before configuring Nginx, we need to set up the SSL certificate.

To install an SSL certificate using Let's Encrypt, we need to download the Certbot tool. We will use the Snapd package installer for that.

Install Snap installer.

$ sudo apt install snapd

Ensure that your version of Snapd is up to date.

$ sudo snap install core 
$ sudo snap refresh core

Install Certbot.

$ sudo snap install --classic certbot

Use the following command to ensure that the Certbot command runs by creating a symbolic link to the /usr/bin directory.

$ sudo ln -s /snap/bin/certbot /usr/bin/certbot

Stop the Nginx server as it interferes with the Certbot tool.

$ sudo systemctl stop nginx

Generate an SSL certificate.

$ sudo certbot certonly --standalone --agree-tos --no-eff-email --staple-ocsp --preferred-challenges http -m [email protected] -d pgadmin.example.com

The above command will download a certificate to the /etc/letsencrypt/live/pgadmin.example.com directory on your server.

Generate a Diffie-Hellman group certificate.

$ sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 4096

Create a challenge web root directory for Let's Encrypt auto-renewal.

$ sudo mkdir -p /var/lib/letsencrypt

Create a Cron Job to renew the SSL. It will run every day to check the certificate and renew it if needed. For that, first, create the file /etc/cron.daily/certbot-renew and open it for editing.

$ sudo nano /etc/cron.daily/certbot-renew

Paste the following code.

#!/bin/sh
certbot renew --cert-name pgadmin.example.com --webroot -w /var/lib/letsencrypt/ --post-hook "systemctl reload nginx"

Save the file by pressing Ctrl + X and entering Y when prompted.

Change the permissions on the task file to make it executable.

$ sudo chmod +x /etc/cron.daily/certbot-renew

Step 10 - Install and Configure Nginx

Ubuntu ships with an older version of Nginx. To install the latest version, you need to download the official Nginx repository.

Import Nginx's signing key.

$ curl https://nginx.org/keys/nginx_signing.key | gpg --dearmor \
	| sudo tee /usr/share/keyrings/nginx-archive-keyring.gpg >/dev/null

Add the repository for Nginx's stable version.

$ echo "deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg arch=amd64] \
http://nginx.org/packages/ubuntu `lsb_release -cs` nginx" \
    | sudo tee /etc/apt/sources.list.d/nginx.list

Update the system repositories.

$ sudo apt update

Install Nginx.

$ sudo apt install nginx

Verify the installation.

$ nginx -v
nginx version: nginx/1.20.2

Configure Nginx

Create and open the file /etc/nginx/conf.d/pgadmin.conf for editing.

$ sudo nano /etc/nginx/conf.d/pgadmin.conf

Paste the following code in it.

server {
    listen 80;
    listen [::]:80;
    server_name pgadmin.example.com;
    return 301 https://$host$request_uri;
}

server {
    listen 443 ssl http2;
    listen [::]:443 ssl http2;    
    server_name pgadmin.example.com;
    
    ssl_certificate /etc/letsencrypt/live/pgadmin.example.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/pgadmin.example.com/privkey.pem;
    ssl_trusted_certificate /etc/letsencrypt/live/pgadmin.example.com/chain.pem;
    ssl_session_cache builtin:1000 shared:SSL:10m;
    ssl_session_timeout  5m;
    ssl_session_tickets off;

    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384;
    ssl_prefer_server_ciphers on;
    ssl_ecdh_curve X25519:prime256v1:secp384r1:secp521r1;
    ssl_stapling on;
    ssl_stapling_verify on;
    ssl_dhparam /etc/ssl/certs/dhparam.pem;

    location /pgadmin4/ {
        include /etc/nginx/uwsgi_params;
        uwsgi_pass unix:/tmp/pgadmin4.sock;
    }
}

Save the file by pressing Ctrl + X and entering Y when prompted.

Open the file /etc/nginx/nginx.conf for editing.

$ sudo nano /etc/nginx/nginx.conf

Add the following line before the line include /etc/nginx/conf.d/*.conf;.

server_names_hash_bucket_size  64;

Save the file by pressing Ctrl + X and entering Y when prompted.

Verify the Nginx configuration file syntax.

$ sudo nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Restart the Nginx service to enable the new configuration.

$ sudo systemctl restart nginx

Step 11 - Configure Firewall

Before we access pgAdmin, we need to make sure the firewall ports are open. If you are using the ufw firewall, run the following commands to open the HTTP and HTTPs ports.

$ sudo ufw allow 80/tcp
$ sudo ufw allow 443/tcp

Check the status of the firewall.

$ sudo ufw status

You should see a similar output.

Status: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
80/tcp                     ALLOW       Anywhere
443/tcp                    ALLOW       Anywhere
5432/tcp                   ALLOW       192.168.1.0/24
OpenSSH (v6)               ALLOW       Anywhere (v6)
80/tcp (v6)                ALLOW       Anywhere (v6)
443/tcp (v6)               ALLOW       Anywhere (v6)

Step 11 - Access and Configure pgAdmin

Before accessing pgAdmin, we need to set a password for the default postgres user.

Open the psql shell.

$ sudo -u postgres psql

Run the following command to set a password.

postgres-# \password postgres
Enter new password for user "postgres":
Enter it again:

Exit the shell.

postgres-# exit

Also, change the ownership of the UNIX socket.

$ sudo chown nginx:nginx /var/lib/pgadmin/pgadmin4.sock

Open the URL https://pgadmin.example.com in your browser and you will see the following login screen.

Enter your user credentials created in step 6 to log in to access the following dashboard.

The next step is to add the local PostgreSQL server to it. Click the Add New Server button to get started.

Give a name to the connection and switch to the Connection tab.

Enter localhost as the Hostname and leave the port field blank. Add the password created before and check the option Save password?. Click the Save button to finish adding the server.

Once finished, you will see the following dashboard screen for your PostgreSQL server.

Let us create a new table and add some data to our database.

From the dashboard, click on the arrow sign next to PostgreSQL - Local to expand the tree menu. Next, expand the Databases, click on the name of the database you added (howtoforge, in our example), and then Schemas. You should see the following menu.

Right-click the Tables option and then select Create >> Table menu options as shown.

This will open the Create table popup window as shown below. Enter a name for the table.

Switch to the Columns tab. Here we will define some basic columns for the table.

Click the plus sign to add the first column. We have named it ID with integer as its data type. Every table should have a primary key. To make the ID the primary key for the table, toggle the button for the Primary key.

Next, we add another column for Name with character varying as the data type. To restrict the name length, we have defined 30 as its length. For now, this is enough for our example. Click the Save button when finished to create the table.

Now that we have our table with some columns, the next step is to fill the table with some values. To add the data, right-click on the name of your table in the Browser, move your cursor over Scripts, and select the option INSERT Script.

It will open a new tab on the dashboard with a partially filled INSERT command. Replace the question mark with the appropriate data you want to fill. Here is the data, we used for our table.

INSERT INTO public."table-01"(
"ID", "Name")
VALUES (1, 'Navjot'), (2, Adam), (3, 'Roxy');

Click on the tilted triangle button to execute the SQL query and add the data.

Next, you would want to view the data you just added. To do that, right-click on the table name and select the option View/Edit Data and then click on the option All Rows.

This will open another tab in the dashboard with your data visible in the lower panel's Data output tab.

You have finally created a database and added some data to it using pgAdmin. There is a lot more you can do using pgAdmin.

PostgreSQL comes with in-built utilities to backup and restore data. pgAdmin supports them natively but for it to work, you may need to add the path to your Postgre binary files. To do that, open the File menu at the top and select the Preferences option.

It will open a new popup window with lots of preferences. Select the option Paths >> Binary paths from the left sidebar. Next, scroll down to the PostgreSQL Binary Path section of the popup.

Enter /usr/bin as the path in front of PostgreSQL 14 server entry and select the checkmark across it. Click the Save button when you are done. You should be able to use the Backup and restore commands via pgAdmin's interface.

Conclusion

You learned to install PostgreSQL and pgAdmin on a Ubuntu 22.04 server. You also configured pgAdmin to connect with the PostgreSQL service and perform basic operations. If you have questions, post them in the comments below.