How to Install PostgreSQL: Linux Server Guide

Installing PostgreSQL on Ubuntu and Debian

After connecting to your server with SSH:

sudo apt update
sudo apt install postgresql
sudo systemctl status postgresql

The distribution's repository installs the PostgreSQL version that ships with that release of the distribution. If you need a newer major version, you can add PostgreSQL's official APT repository.

On AlmaLinux and Rocky Linux:

sudo dnf install postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Roles and the first connection

In PostgreSQL, users are called roles. The installation creates an admin role named postgres and an operating system user with the same name. Local connections use peer authentication by default: the name of your operating system user has to match the name of the role. That's why you connect as the admin like this:

sudo -u postgres psql

Commands you'll use often inside psql:

Creating a database and a role

Create a separate role for each application, and a database that belongs only to that role:

CREATE ROLE app WITH LOGIN PASSWORD 'a-strong-password';
CREATE DATABASE app OWNER app;

The role that owns the database can create and manage tables in it; it can't access your other databases. Your application's connection string looks like this:

postgresql://app:a-strong-password@127.0.0.1:5432/app

To connect with this role from the command line, add -h 127.0.0.1; this way you connect over TCP with a password instead of peer:

psql -h 127.0.0.1 -U app app

Access rules with pg_hba.conf

The pg_hba.conf file decides who can connect, from where and with which method. On Ubuntu and Debian, the file is at /etc/postgresql/<version>/main/pg_hba.conf. Each line is a rule, and the first rule that matches from top to bottom is applied:

# TYPE  DATABASE    USER       ADDRESS           METHOD
local   all         postgres                     peer
local   all         all                          peer
host    all         all        127.0.0.1/32      scram-sha-256
host    all         all        ::1/128           scram-sha-256

• local: Connections made from inside the server over a Unix socket.
• host: Connections made over TCP/IP.
• scram-sha-256: Secure password authentication. Use this instead of the older md5 method.

After changing the file, reload the settings:

sudo systemctl reload postgresql

Secure remote connections

The recommended way is an SSH tunnel. PostgreSQL keeps listening only on the server itself, and port 5432 is never exposed to the internet:

ssh -L 5433:127.0.0.1:5432 user@203.0.113.10

While this command is running, you connect with pgAdmin or DBeaver to 127.0.0.1:5433 on your own computer. Both tools also have a built-in SSH tunnel option in their connection settings.

If you need a direct connection (for example, for an application on another server):

1. In the postgresql.conf file, change the line listen_addresses = 'localhost' to listen_addresses = '*'.
2. Add a rule to the pg_hba.conf file for only that server's IP address:

   host    app    app    198.51.100.7/32    scram-sha-256
   

3. Restart PostgreSQL and open the port in the firewall only to that address:

   sudo systemctl restart postgresql
   sudo ufw allow from 198.51.100.7 to any port 5432 proto tcp
   

Opening port 5432 to everyone means your server will face automated password-guessing attacks. We cover port settings in our What is a port? article.

Backup and restore

pg_dump takes a consistent backup of a running database without locking it. The compressed custom format (-Fc) is both small and flexible:

sudo -u postgres pg_dump -Fc app > app-$(date +%F).dump

To restore:

pg_restore -h 127.0.0.1 -U app -d app --clean app-2026-10-20.dump

Roles and other server-wide settings aren't included in a pg_dump backup; back them up separately with pg_dumpall --globals-only. We cover automating backups with cron and keeping them somewhere independent of the server in our Server backups guide.

Basic performance settings

PostgreSQL's default settings are chosen for very small servers. These settings in the postgresql.conf file make a big difference:

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB

The example values are for a server with 8 GB of RAM that only runs the database:

• shared_buffers: PostgreSQL's own cache. About a quarter of the RAM is a good starting point.
• effective_cache_size: Tells the query planner how much memory there is, including the operating system's file cache; it's set to between half and three quarters of the RAM.
• work_mem: The memory set aside for each sort and join operation. Very high values can use up memory when there are many concurrent queries.

Restart PostgreSQL after making changes. To find slow queries, the log_min_duration_statement = 500 setting writes queries that take longer than 500 milliseconds to the log; use EXPLAIN ANALYZE to see why a query is slow.

Nodesty servers for PostgreSQL

Database performance depends largely on memory and disk speed. Nodesty AMD EPYC 7763 VPS plans, with high RAM, are a good fit for keeping large databases in memory, while AMD Ryzen 9 9950X VPS plans, with high single-core speed, suit applications that need to respond quickly. Both plans include NVMe storage, nShield DDoS protection and free daily backups kept for 7 days.