PostgreSQL on a VPS: Install, Create Users, Secure and Back Up

PostgreSQL has a reputation for correctness, strong standards support and a rich feature set, including advanced indexing, JSON storage and full-text search. Many modern frameworks, Django among them, treat it as a first-class choice. Running it on your own VPS is straightforward if you understand three things: how roles and authentication work, how to keep it private, and how to back it up. This guide covers each on Debian or Ubuntu.

Install and connect

sudo apt update
sudo apt install postgresql
sudo systemctl status postgresql
sudo -u postgres psql

The package creates a cluster, starts it, and adds an operating-system user named postgres. The last command opens the psql shell as that superuser using local "peer" authentication, which means the system user name is trusted for local connections, so no password is needed. Type \q to leave. On Debian-based systems the version-specific files live under /etc/postgresql/<version>/main/.

Create a role and a database

PostgreSQL calls users "roles". As with any database, give each application its own role and its own database, and never let the app use the superuser:

CREATE ROLE appuser WITH LOGIN PASSWORD 'replace-with-a-long-random-password';
CREATE DATABASE appdb OWNER appuser ENCODING 'UTF8';

Because the owner of a database can create and change everything inside it, this single command set is enough for most applications. Test the login over TCP with psql -h 127.0.0.1 -U appuser appdb; it will prompt for the password.

Control access: listen_addresses and pg_hba.conf

Two files decide who can connect:

• postgresql.conf contains listen_addresses. The default is localhost, so PostgreSQL does not accept outside connections out of the box. Leave it that way when the application runs on the same server.
• pg_hba.conf is a list of rules matching connection type, database, user, address and authentication method. The first matching rule applies.

A rule that lets one remote application server connect with a password, using the modern scram-sha-256 method, looks like this:

host    appdb    appuser    203.0.113.20/32    scram-sha-256

To use it you would also set listen_addresses to include the server's address and open port 5432 only to that one IP in your firewall. Reload with sudo systemctl reload postgresql after changing pg_hba.conf; changes to listen_addresses need a restart. Avoid broad rules such as 0.0.0.0/0.

Backups with pg_dump

The standard logical backup tool is pg_dump. The custom format is compressed and lets you restore selectively:

sudo -u postgres pg_dump -Fc appdb > /var/backups/appdb-$(date +%F).dump

Make sure the target folder is writable by the shell redirect (it runs as your user, not as postgres), or use the -f option, which writes the file as the postgres user. To restore into a fresh database:

sudo -u postgres createdb -O appuser appdb_restore
sudo -u postgres pg_restore -d appdb_restore /var/backups/appdb-2026-01-15.dump

Use your own file name. Roles and global objects are not part of a single-database dump; pg_dumpall --globals-only exports them. For large databases or point-in-time recovery, PostgreSQL supports continuous archiving of its write-ahead log, and tools such as pgBackRest or Barman build on it. Whichever method you choose, copy backups off the server and rehearse a restore.

First tuning steps

Before tuning, find out what the server actually does. A database serving a small blog needs almost no tuning, while one behind a busy shop may. Measure first, and change only what the measurements justify.

The defaults are deliberately modest so PostgreSQL starts on small machines. Three settings usually deserve attention:

• shared_buffers: the PostgreSQL documentation suggests about 25% of system memory as a reasonable starting value on a dedicated server. On a VPS shared with a web server, choose a smaller share.
• work_mem: memory per sort or hash operation, per query. Raising it helps complex queries, but it is multiplied by concurrent operations, so increase it carefully.
• effective_cache_size: a hint to the planner about how much the operating system can cache, not an allocation.

Change settings in small steps and measure with real queries using EXPLAIN ANALYZE. Autovacuum is enabled by default and should stay that way, since it keeps tables from bloating. Changes to postgresql.conf take effect after a reload, except for a few memory settings such as shared_buffers, which need a restart. The SHOW command in psql (for example SHOW shared_buffers;) confirms what value is active.

Connections, upgrades and monitoring

To see how many connections are open right now, run SELECT count(*) FROM pg_stat_activity; in psql; compare it with the max_connections setting (100 by default) before you raise anything.

Each PostgreSQL connection is a separate process, so hundreds of idle application connections waste memory. Use your framework's connection pool, and consider PgBouncer if many app processes connect. Minor updates (for example 16.1 to 16.2) are installed by your package manager and are quick. Major version upgrades need planning: Debian's tooling provides pg_upgradecluster, and the dump-and-restore route remains valid. Monitor disk space closely, because a full disk is one of the few ways to seriously hurt a PostgreSQL server. The extension pg_stat_statements shows which queries consume the most time.

Before upgrading or changing configuration on a production database, take a fresh dump and keep it until the change has proven stable. Our security page explains how this fits into a wider protection plan.

PostgreSQL on Zebra Hosting

PostgreSQL is a service you install and manage yourself, which suits a VPS or a dedicated server. If you are unsure which size fits, our team can help you decide.