Your application needs a database. You could reach for SQLite for a quick experiment, but the moment you need concurrent writes, foreign key integrity you can actually trust, full-text search, or a production-grade backup story, you want a real database server. PostgreSQL is the one that keeps showing up on production stacks from small startups to large enterprises, and for good reason. It is fast, reliable, standards-compliant, and free.
This tutorial walks you through everything you need to go from a blank Ubuntu server to a running PostgreSQL instance: installing the server, understanding how PostgreSQL structures access control, creating your first database and application user, enabling remote connections safely, and backing up and restoring data with pg_dump. By the end you will have a working database server ready for a real application.
What PostgreSQL Is and How It Works
PostgreSQL is a relational database management system (RDBMS). It stores data in tables with rows and columns, enforces relationships between tables using foreign keys, and lets you query that data using SQL.
A few terms you will encounter throughout this tutorial:
- Cluster, the entire PostgreSQL installation on a server, which can contain many databases
- Database, a named container inside the cluster; your application typically gets one database
- Role, PostgreSQL’s word for a user or group; a role can own databases, own tables, and log in
- pg_hba.conf, the host-based authentication file; this file decides who can connect from where and how they must authenticate
- postgresql.conf, the main configuration file; this controls memory, connections, logging, and dozens of other behaviors
When PostgreSQL starts, it reads postgresql.conf for server settings and pg_hba.conf for access rules. Every connection attempt is checked against pg_hba.conf top-to-bottom; the first matching rule decides whether the connection is allowed and which authentication method is required.
Prerequisites
- Ubuntu 20.04, 22.04, or 24.04 server
- A non-root user with
sudoprivileges - Basic familiarity with the Linux command line
- A firewall configured with UFW (optional but recommended, see How to Setup Firewall using UFW in Ubuntu)
Step 1: Install PostgreSQL
Ubuntu’s default apt repository includes PostgreSQL, but it is often one major version behind. For most production use cases you want the latest stable release from PostgreSQL’s own repository.
First, add the PostgreSQL apt repository:
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
Then install PostgreSQL 16 (replace 16 with a newer major version if one is available):
sudo apt install -y postgresql-16
If you prefer to stay with the Ubuntu repository version and do not need the latest release, sudo apt install -y postgresql is enough. The rest of this tutorial works the same either way.
Verify the service started automatically:
sudo systemctl status postgresql
You should see active (running). If it is not running, start it:
sudo systemctl start postgresql
sudo systemctl enable postgresql
Step 2: Connect as the postgres System User
PostgreSQL installation creates a Linux system user called postgres and a matching database superuser role also called postgres. By default, this superuser can only log in from the local machine as the postgres Linux user, no password required yet.
Switch to that user and open the PostgreSQL interactive shell:
sudo -i -u postgres
psql
You should see the psql prompt:
psql (16.x)
Type "help" for help.
postgres=#
You are now connected as the postgres superuser. Run \conninfo to confirm:
\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
Exit psql and the postgres shell when you are ready to move on:
\q
exit
Step 3: Create a Database and Application User
Running your application as the postgres superuser is like running a web app as root. Instead, create a dedicated role with access only to the database it needs.
Switch back to the postgres user and open psql:
sudo -i -u postgres psql
Create a new role. Replace appuser and strongpassword with your own values:
CREATE ROLE appuser WITH LOGIN PASSWORD 'strongpassword';
LOGINmeans this role can authenticate as a database userPASSWORDsets the password used for password-based authentication
Create the application database and assign ownership to the new role:
CREATE DATABASE appdb OWNER appuser;
Verify both exist:
\du
\l
\du lists all roles; \l lists all databases. You should see appuser in the roles list and appdb in the database list with appuser as owner.
Exit psql:
\q
Test that the new user can connect:
psql -U appuser -d appdb -h 127.0.0.1 -W
Enter the password you set. If you get a psql prompt, the user and database are working correctly. Exit with \q.
Step 4: Understand and Configure pg_hba.conf
pg_hba.conf is the most important security file in your PostgreSQL setup. It determines which users can connect, from which hosts, to which databases, and with which authentication method.
Each line follows this format:
TYPE DATABASE USER ADDRESS METHOD
Open the file:
sudo nano /etc/postgresql/16/main/pg_hba.conf
You will see lines like these near the bottom:
# 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
What each method means:
- peer, trusts the Linux system user; if you are logged in as
postgreson the OS, you can connect as thepostgresdatabase role without a password - scram-sha-256, requires a hashed password; this is the current secure default and what you want for application connections
- md5, an older password hash; still works but scram-sha-256 is preferred on PostgreSQL 14+
- trust, allows any connection with no password; never use this on a networked interface
For local application connections over TCP (like a web app running on the same server), the host ... 127.0.0.1/32 ... scram-sha-256 line already handles this. Your application connects to 127.0.0.1:5432 with a username and password.
After any change to pg_hba.conf, reload PostgreSQL to apply it without dropping existing connections:
sudo systemctl reload postgresql
Step 5: Enable Remote Access
By default, PostgreSQL only listens on the local loopback interface. If your application runs on a different server, you need to allow remote connections.
Edit postgresql.conf to listen on all interfaces:
sudo nano /etc/postgresql/16/main/postgresql.conf
Find the listen_addresses line (it may be commented out) and change it:
listen_addresses = '*'
The * means listen on all network interfaces. If you prefer to restrict it to a specific IP, use that IP instead:
listen_addresses = '10.0.0.5'
Edit pg_hba.conf to allow the remote host:
Open pg_hba.conf again and add a rule for your application server. Replace 10.0.0.10/32 with the actual IP of your app server:
host appdb appuser 10.0.0.10/32 scram-sha-256
This rule says: allow TCP connections to appdb from appuser at 10.0.0.10 using password authentication. Only the exact host you specify can connect, not the entire internet.
Allow port 5432 in your firewall:
If you are using UFW, open the port only for the application server IP:
sudo ufw allow from 10.0.0.10 to any port 5432
Never run sudo ufw allow 5432 without a source IP, that opens your database port to the entire internet.
Restart PostgreSQL to apply the listen_addresses change:
sudo systemctl restart postgresql
A reload is not enough here because listen_addresses affects which sockets are opened at startup.
Test the remote connection from your application server:
psql -U appuser -d appdb -h <your-db-server-ip> -W
Step 6: Tune Basic Settings in postgresql.conf
The default postgresql.conf is conservative and suitable for a small test machine. For a dedicated database server, a few settings make a significant difference.
Open the config file:
sudo nano /etc/postgresql/16/main/postgresql.conf
shared_buffers is how much memory PostgreSQL uses for its own cache. Set this to about 25% of total RAM:
shared_buffers = 256MB # for a 1GB server
shared_buffers = 1GB # for a 4GB server
effective_cache_size is a hint to the query planner about how much memory is available for caching (OS cache + shared_buffers). Set to roughly 75% of total RAM:
effective_cache_size = 768MB # for a 1GB server
effective_cache_size = 3GB # for a 4GB server
max_connections is the maximum number of simultaneous connections. The default is 100. Each connection consumes memory, so do not raise this blindly. If you need more than 100 concurrent connections, a connection pooler like PgBouncer is the right answer, not a higher max_connections.
log_min_duration_statement is to log any query that takes longer than this threshold. Helpful for finding slow queries in production:
log_min_duration_statement = 1000 # log queries slower than 1 second
After editing, reload the service:
sudo systemctl reload postgresql
For shared_buffers, a full restart is required:
sudo systemctl restart postgresql
Step 7: Backup and Restore with pg_dump
pg_dump creates a logical backup of a single database. It is the simplest way to back up PostgreSQL and works regardless of PostgreSQL version or platform.
Create a backup:
sudo -u postgres pg_dump appdb > /tmp/appdb_backup.sql
This writes a plain SQL file containing all the CREATE TABLE, INSERT, and other statements needed to recreate the database from scratch.
For a compressed backup (much smaller for large databases):
sudo -u postgres pg_dump -Fc appdb > /tmp/appdb_backup.dump
The -Fc flag uses PostgreSQL’s custom format, which is compressed and supports parallel restore.
Restore a plain SQL backup:
sudo -u postgres psql appdb < /tmp/appdb_backup.sql
Restore a custom-format backup:
sudo -u postgres pg_restore -d appdb /tmp/appdb_backup.dump
Schedule a daily backup with cron:
sudo crontab -u postgres -e
Add this line to run a backup every day at 2:00 AM:
0 2 * * * pg_dump -Fc appdb > /var/backups/appdb_$(date +\%Y\%m\%d).dump
For production, you should also ship backup files off the server to object storage, another server, or a dedicated backup service. A backup that lives only on the database server is not a real backup.
Common Mistakes and Troubleshooting
“role does not exist” or “peer authentication failed”
You ran psql appdb without specifying a user and PostgreSQL tried to authenticate using the Linux username you are logged in as. Always specify -U <rolename> explicitly when connecting as an application user.
“FATAL: password authentication failed for user”
The password is wrong, or pg_hba.conf does not have a matching host rule for this connection. Check the rule order in pg_hba.conf. PostgreSQL uses the first matching rule, so a broad peer rule above your scram-sha-256 rule can shadow it.
“could not connect to server: Connection refused” from a remote host
Either listen_addresses is still set to localhost, or the firewall is blocking port 5432. Verify with ss -tlnp | grep 5432 on the database server. If you see 127.0.0.1:5432 instead of 0.0.0.0:5432, PostgreSQL is not listening on the public interface.
Changes to pg_hba.conf have no effect
You need to reload PostgreSQL after editing pg_hba.conf. Changes to listen_addresses require a full restart, not just a reload.
Application gets “too many connections”
Lower max_connections than expected, or the application is opening a new connection for every request without pooling them. Add a connection pool (PgBouncer or the pooling built into your application framework) rather than raising max_connections further.
Best Practices
Use a dedicated role per application. Never use the postgres superuser in your application’s connection string. If that credential leaks, an attacker has full access to every database on the server.
Keep pg_hba.conf as specific as possible. Use /32 masks for individual IPs rather than broad subnet ranges when you know exactly which hosts need access.
Enable SSL for remote connections. PostgreSQL ships with SSL support compiled in. To enforce encrypted remote connections, set hostssl instead of host in pg_hba.conf lines that allow remote access. You can use a self-signed certificate or a Let’s Encrypt certificate (see How to Secure Nginx with Let’s Encrypt SSL Using Certbot on Ubuntu).
Test your backups. A backup you have never restored is a backup you do not actually have. Restore into a test database periodically to confirm the dump is valid.
Monitor disk space. PostgreSQL does not automatically reclaim space from deleted rows; it marks them dead and reclaims them later during a process called VACUUM. On a busy database, monitor that autovacuum is keeping up and that your data directory is not filling the disk.
Do not store passwords in plain text in connection strings. Use environment variables, a .pgpass file, or a secrets manager (HashiCorp Vault, AWS Secrets Manager) to supply the password at runtime.
Conclusion
You now have a PostgreSQL server running on Ubuntu with a dedicated database and user, properly configured access control through pg_hba.conf, optional remote access locked down to a specific IP, sensible memory settings, and a working backup strategy with pg_dump.
For next steps, consider:
- PgBouncer, a lightweight connection pooler that sits between your application and PostgreSQL, dramatically reducing connection overhead at scale
- Streaming replication, set up a standby server that receives a continuous stream of changes from the primary, giving you a hot spare and the ability to offload read queries
- pg_activity or pgBadger, real-time query monitoring and log analysis to find slow queries before they become production incidents