How To Install PostgreSQL 14 on Ubuntu 20.04
In this tutorial, we learn how to install PostgreSQL 14 on Ubuntu 20.04 (Focal Fossa) from official PostgreSQL repository.
Introduction
In this tutorial, we learn how to install PostgreSQL 14 on Ubuntu 20.04 (Focal Fossa).
PostgreSQL, or usually called Postgres, is an open-source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance.
PostgreSQL is ACID-compliant and transactional. It is developed by PostgreSQL Global Development Group (PGDG) that consists of many companies and individual contributors. PostgreSQL released under the terms of PostgreSQL license.
PostgreSQL 14 was released on 30 September 2021. Major enhancements in PostgreSQL 14 includes:
- Stored procedures can now return data via
OUTparameters. - The SQL-standard
SEARCHandCYCLEoptions for common table expressions have been implemented. - Subscripting can now be applied to any data type for which it is a useful notation, not only arrays. In this release, the
jsonbandhstoretypes have gained subscripting operators. - Range types have been extended by adding multiranges, allowing representation of noncontiguous data ranges.
- Numerous performance improvements have been made for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming.
- B-tree index updates are managed more efficiently, reducing index bloat.
VACUUMautomatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition.- Extended statistics can now be collected on expressions, allowing better planning results for complex queries.
- libpq now has the ability to pipeline multiple queries, which can boost throughput over high-latency connections.
Prerequisites
This tutorial assume you already satisfy the following requirements:
- A machine with fresh install of Ubuntu Server 20.04. It can be A virtual machine locally using Virtualbox, VMWare workstation or VMWare Fusion, KVM, Xen, or a virtual machine in the cloud like DigitalOcean or Linode.
- A non-root user with sudo privileges or a root user access. If you’re usig root user remove
sudofrom each command on this tutorial.
Step 1 — Update Base Systems
Since this is a fresh install of Ubuntu Server 20.04, before we install PostgreSQL 14, let’s update our system to the latest update.
sudo apt-get update
sudo apt-get -y upgrade
```bash
After we upgrade our base system, now it's time to install PostgreSQL 14.
Since Ubuntu 20.04 doesn't have PostgreSQL 14 in [its repository][4], we need to add oficial PostgreSQL repository so we can install PostgreSQL 14.
## Step 2 — Import Repository Signing Key {#h-step-2-import-repository-signing-key}
The PostgreSQL team is using GPG to sign downloadable packages from the PostgreSQL website.
We add PostgreSQL public GPG key so apt can verify that packages downloaded from PostgreSQL repository are not tampered or corrupt.
Add PostgreSQL GPG public key using the command below.
```bash
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -You should get OK output for command above.
If you got the different output, you need to fix the error first before continue to the next step.
Step 3 — Add PostgreSQL 14 repository
After adding PostgreSQL release keys, we create a new repository configuration for PostgreSQL using the command below.
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
```bash
Refresh apt metadata using the command below.
```bash
sudo apt-get updateStep 4 — Install PostgreSQL 14 on Ubuntu 20.04
We’re ready to install PostgreSQL 14. Use the command below to install PostgreSQL 14 on Ubuntu 20.04.
sudo apt-get install postgresql-14
```bash
Alternative method to install PostgreSQL 14 on Ubuntu 20.04 is using apt command
```bash
sudo apt install postgresql-14Only 4 steps needed to install PostgreSQL 14 on Ubuntu 20.04. We learn how to check and verify and managing PostgreSQL 14 installation in the next few sections.
Verify PostgreSQL 14 Installation on Ubuntu 20.04
We can check PostgreSQL 14 packages that already installed using the command below.
dpkg -l | grep postgresql
```bash
## Check PostgreSQL 14 Listening Ports {#h-check-postgresql-14-listening-ports}
By default, PostgreSQL listens on port 5432. We can use netstat to check whether there is a process listening on port 5432.
```bash
sudo netstat -naptu | grep 5432Another method is to check whether there is a process named postgres listening on a port.
sudo netstat -naptu | grep postgres
```bash
Alternative to `netstat`, We can use `ss` command below to check whether there is a process listen on PostgreSQL default port 5432.
```bash
sudo ss -atnp | grep 5432We can also use ss to check whether there is a process named postgres listening on a port.
sudo ss -atp | grep postgres
```bash
The last output above shows there is also a connection to ephemeral UDP port from local. According to this thread on PostgreSQL mailing lists, this is a PostgreSQL stats collector that uses UDP to send and receive stats data locally.
## Checking PostgreSQL Process {#h-checking-postgresql-process}
We can ue `ps` command to check what processes currently running with name contain postgres.
```bash
ps -aux | grep postgresCheck PostgreSQL 14 resource usage like CPU and memory usingtop command.
top -u postgres
```bash
## Managing PostgreSQL 14 Services on Ubuntu 20.04 {#h-managing-postgresql-14-services-on-ubuntu-20-04}
Check `postgres` service status using command below.
```bash
sudo service postgresql statusWe can also use systemctl command to check PostgreSQL service status.
sudo systemctl status postgresql
```bash
Using both commands above you see `active (exited)` on the `Active:` line. This happens because systemd doesn't know the status of each PostgreSQL cluster running on the instance. One instance/installation of PostgreSQL can run multiple clusters of PostgreSQL.
We can check, list of PostgreSQL clusters running on our machine using the command below.
```bash
pg_lsclustersThere is one cluster running on our machine. The cluster named main running PostgreSQL 14. To check this cluster service status, we can use the command below.
sudo service postgresql@14-main status
```bash
As an alternative to service, we can use systemctl to check this cluster status.
```bash
sudo systemctl status postgresql@14-mainStop PostgreSQL service using the command below.
sudo systemctl stop postgresql
```bash
Start PostgreSQL service using the command below.
```bash
sudo systemctl start postgresqlRestart PostgreSQL service using the command below.
sudo systemctl restart postgresql
```bash
Reload PostgreSQL service using the command below.
```bash
sudo systemctl reload postgresqlConnect to PostgreSQL using psql
Now let’s connect to the PostgreSQL 14 server using psql client application.
By default, postgres create system user named postgres. There is also role in postgresql database named postgres wth superuser permission.
Switch to postgresql user.
sudo su - postgres
```bash
After switching to postgres user, we can connect using `psql` command.
To list all databases in PostgreSQL we can use `\l` command.
To list all users in PostgreSQL we can use `\du` command.
To exit from `psql` we can use `\q` command.
To exit from `postgres` user type `exit` or `CTRL+D`
## Change postgres User Password {#h-change-postgres-user-password}
Now we're on our own linux user. Let's try to login to `localhost` or `127.0.0.1`.
```bash
$ psql -U postgres -W -h 127.0.0.1
Password for user postgres: It prompts for a password. However, since we haven’t set any password, let’s leave it empty. We get the error message below.
psql: fe_sendauth: no password supplied
```bash
Since we cannot login without providing password, we set new password for `postgres` user. Change user to `postgres` user like shown above and use psql to connect to PostreSQL.
After logging in, type `\password`. Enter new password and confirmation password for user `postgres`.
When we login again to `localhost` or `127.0.0.1` and provide a password, we can log in successfully.
## Uninstall PostgreSQL 14 from Ubuntu 20.04 {#h-uninstall-postgresql-14-from-ubuntu-20-04}
In this section, we learn how to uninstall PostgreSQL 14 and its dependencies on Ubuntu 20.04 completely.
Please be careful when running steps in this section since this will stop your PostgreSQL database server.
Use the command below to uninstall all PostgreSQL 14 packages, both server, and client.
```bash
sudo apt-get remove postgresql-14 \
postgresql-client-14 \
postgresql-client-common \
postgresql-commonRemove the PostgreSQL data directory using the command below.
rm -rf /var/lib/postgresql/
```bash
Remove PostgreSQL 14 configuration directories using the command below.
```bash
rm -rf /etc/postgresql
rm -rf /etc/postgresql-commonPostgreSQL 14 References
You can follow the links below to learn more about PostgreSQL 14 from PostgreSQL official documentation.
Conclusion
In this tutorial, we learn how to install PostgreSQL 14 on ubuntu 20.04. We also learn managing PostgreSQL services, using tools like netstat, ss, ps, and top to check PostgreSQL service.
After that, we learn how to connect to PostgreSQL 14 server using psql client, changing postgres superuser password, and in the end, we learn how to uninstall PostgreSQL 14. I hope this is useful for initiating your journey to PostgreSQL 14.