How To Install PostgreSQL 17 on Ubuntu/Debian

Michail

Introduction

PostgreSQL (Postgres) is one of the popular DBMS for projects of various levels: from a startup to a high-load system. It performs many functions that help in developing applications, protecting data integrity and managing them.

In this guide we will install PostgreSQL 17 version on Ubuntu (starting from 22.04 version) and Debian 12.

1. Update System Packages

Before installing PostgreSQL, update your package list:

apt update

2. Add PostgreSQL Repository

By default, Ubuntu and Debian have an older version of PostgreSQL in their repositories. To install the latest version, add the official PostgreSQL repository:

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

3. Import PostgreSQL GPG Key

Import the official PostgreSQL signing key:

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

4. Update Package List Again

After adding the new repository, update the package list:

apt update

5. Install PostgreSQL 17

Now install PostgreSQL 17:

apt install postgresql-17

6. Start and Enable PostgreSQL Service

Start PostgreSQL and enable it to run on system startup:

systemctl start postgresql
systemctl enable postgresql

7. Verify Installation

Check the installed PostgreSQL version:

psql --version

8. Configure PostgreSQL for Remote Access

By default, PostgreSQL only listens on localhost. To allow remote connections, edit the PostgreSQL configuration file:

vi /etc/postgresql/17/main/postgresql.conf

Find the line:

listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

Save and exit the editor.

9. Update Authentication Methods

Modify pg_hba.conf to allow remote authentication. Replace ident with md5 for host-based authentication:

sed -i '/^host/s/ident/md5/' /etc/postgresql/17/main/pg_hba.conf

Replace peer with trust for local authentication:

sed -i '/^local/s/peer/trust/' /etc/postgresql/17/main/pg_hba.conf

Allow all remote connections:

echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/17/main/pg_hba.conf

10. Restart PostgreSQL

Apply the configuration changes by restarting PostgreSQL:

systemctl restart postgresql

11. Allow PostgreSQL Port in Firewall

If UFW (Uncomplicated Firewall) is enabled, allow traffic on port 5432:

ufw allow 5432/tcp

If you are using IPtables as your firewall, run this command to open port 5432:

iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

Then run the following command:

netfilter-persistent save

If netfilter-persistent is not installed, you can use another command to save the changes:

iptables-save | tee /etc/iptables/rules.v4

Using PostgreSQL 17

Access PostgreSQL Command Line

Switch to the postgres user and enter the PostgreSQL shell:

sudo -u postgres psql

Set a Password for PostgreSQL User

Inside the PostgreSQL shell, set a password for the default postgres user:

ALTER USER postgres PASSWORD 'YourPassword';

Replace 'YourPassword' with a strong password.

Create a New Database (Optional)

To create a new database, run:

CREATE DATABASE mydatabase;

Create a New User (Optional)

Create a new user with a password:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

Grant Privileges to the New User (Optional)

Grant all privileges on the database to the new user:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Exit PostgreSQL

Exit the PostgreSQL shell:

\q

Conclusion

You have successfully installed PostgreSQL 17 on Ubuntu/Debian, configured it for remote access, and learned basic commands to manage databases and users. You can now connect to PostgreSQL using a client like psql or a GUI tool like pgAdmin.