In this tutorial, we’ll cover how to install PostgreSQL 16 on Ubuntu 22.04. In addition, we’ll examine some fundamental setups to enable remote connections, turn on password authentication, and start creating users and databases.
PostgreSQL is an open-source relational database management system (RDBMS) with more than 35 years of continuous development. It is renowned for its scalability, high reputation for data integrity, optimum performance, and adherence to SQL standards.
Since its initial development at the University of California, Berkeley, in 1986, it has undergone significant changes thanks to contributions from an active open-source community.
PostgreSQL announced another significant release on September 14, 2023; PostgreSQL 16. This latest release comes with enhanced security, extra server configuration options, and optimum performance. It also includes improvements in access policy management, monitoring SQL/JSON syntax, and others.
[ez-toc]
Prerequisites
- An active and running Ubuntu 22.04
- Root privileges or sudo access
1. Add the PostgreSQL Repository
We’ll begin by updating the software repository and installing the necessary packages using apt command.
$ sudo apt update $ sudo apt install gnupg2 wget vim -y
Now, run beneath command to add postgresql apt repository.
$ sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Next, import the repository signing key, run below wget command.
$ sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Update the package index again:
$ sudo apt update
2. Install PostgreSQL 16 on Ubuntu 22.04
To install PostgreSQL16 and contrib modules, run the following command:
$ sudo apt install postgresql-16 postgresql-contrib-16
Next, start and enable the PostgreSQL service:
$ sudo systemctl start postgresql $ sudo systemctl enable postgresql
Confirm if the PostgreSQL service is up and running:
$ sudo systemctl status postgresql
To check the PostgreSQL version, execute the command as shown below:
$ sudo psql --version
3. Configure PostgreSQL 16 on Ubuntu 22.04
You can now continue and set up PostgreSQL. PostgreSQL has multiple authentication mechanisms. Ident authentication is the default mechanism. By using this default authentication method, the Postgres role is linked to the UNIX user.
The other authentication mechanisms include:
- Password authentication: Uses a password to establish a connection to PostgreSQL.
- Trust authentication: This authentication method enables a role to connect, provided that the requirements listed in the pg_hba.conf are fulfilled.
- Peer authentication: This authentication method is similar to ident; the difference is that it is only limited to local connections.
Let’s start by configuring PostgreSQL 16 to allow remote connections. To accomplish this, edit the file below:
$ sudo vim /etc/postgresql/16/main/postgresql.conf
Here, set the listen_addresses to * and remove the comment as shown below:
After making the necessary adjustments, save the file.
Proceed and use the following sed command to permit password authentication:
$ sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf
Now use the following command to switch the authentication mechanism from peer to trust:
$ sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf
Finish the configuration by adding the following lines to enable remote access to PostgreSQL:
$ sudo vim /etc/postgresql/16/main/pg_hba.conf
Save the modifications and restart the PostgreSQL service:
$ sudo systemctl restart postgresql
If your firewall is enabled, allow the PostgreSQL port to pass through it:
$ sudo ufw allow 5432/tcp
4. Connect to PostgreSQL
To connect to this database engine, run the following command to connect as the Postgres user:
$ sudo -u postgres psql
5. Get started with PostgreSQL 16
There are various activities you can carry out once you’ve established a connection to the database. Among them are:
Configuring the Postgres user’s password
To set the password for the Postgres user, run the following command:
ALTER USER postgres PASSWORD 'StronGlinuxbuzz@254';
To test if the password is working, run the following command and enter the password you created:
$ psql -h localhost -U postgres
Creating a PostgreSQL Database
Use the following command to create a test database:
CREATE DATABASE sampleDB;
In addition, you use the following command to create a user with permission to manage the database:
CREATE USER pradeep with encrypted password 'deza@1123'; GRANT ALL PRIVILEGES ON DATABASE sampleDB to pradeep;
To exit from the PostgreSQL database, you can use this command \q.
Conclusion
That’s it! We have successfully installed PostgreSQL 16 on Ubuntu 22.04. With PostgreSQL, you can now create sophisticated, reliable databases for your applications.