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.
