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.