How to Install PostgreSQL on a VPS: Security, Remote Access, and Backup

In this guide, we will install PostgreSQL on a VPS, create a dedicated database and user, and then configure secure remote access, backups, and a few basic performance settings.

In the process, we will:

• Install PostgreSQL and verify that the service is running;
• Create a dedicated user and database;
• Configure postgresql.conf and pg_hba.conf;
• Cover secure access over a private network and via an SSH tunnel;
• Configure the firewall so that port 5432 is not exposed to the entire internet;
• Create a database backup using pg_dump;
• Restore data from a backup file;
• Check the key PostgreSQL performance settings.

By default, PostgreSQL will not be directly accessible from the internet. For remote connections, we will use an SSH tunnel, and for communication between your own servers, we will use a private network if the provider’s infrastructure supports it.

What We’ll Configure

In this guide, we’ll deploy PostgreSQL on a separate VPS and configure it not only for local use, but also for secure remote access, backups, and basic optimization.

The key principle is not to expose PostgreSQL directly to the internet unless necessary. For connections from a workstation, we’ll use an SSH tunnel; for server-to-server communication, we’ll use a private network if one is available in the provider’s infrastructure.

Final access model

By default, PostgreSQL will listen for local connections, while external connections will be allowed only in a controlled manner.

The SSH tunnel setup looks like this:

If the database is used by multiple VPS instances within the same infrastructure, you can use a private network instead of the public internet:

In this case, port 5432 does not need to be exposed to the entire internet. It is sufficient to allow connections only from a trusted private subnet.

What you’ll need

To follow this guide, you will need:

• A VPS running Ubuntu 24.04;
• SSH access with sudo privileges;
• PostgreSQL from the Ubuntu repositories;
• A dedicated database and user;
• Access to the postgresql.conf and pg_hba.conf files;
• A firewall to restrict network connections;
• An SSH client on the local computer;
• The pg_dump, pg_restore, and psql utilities.

For demonstration purposes, we will assume that PostgreSQL is running on a single VPS and that the administrator connects through an SSH tunnel.

Installing PostgreSQL

Let’s start by installing PostgreSQL and verifying that the database server has started correctly.

Installing PostgreSQL packages

Update the package index: sudo apt update

Install PostgreSQL and the additional utilities: sudo apt install -y postgresql postgresql-contrib

After installation, check the client version:

psql –version

Next, check the service status:

sudo systemctl status postgresql –no-pager

In the output, the service should be in the active (exited) or active (running) state, depending on how the cluster is started in the Ubuntu version you are using.

You can also check the active clusters: pg_lsclusters

After installation, a cluster named main is typically created and runs on port 5432.

If PostgreSQL is installed and the cluster status is online, you can proceed to verify the connection.

Checking the Cluster and Connecting via localhost

On Ubuntu, PostgreSQL creates a system user named postgres by default, which can be used to perform administrative operations.

Open the PostgreSQL console: sudo -u postgres psql

If the connection succeeds, the prompt will change to something like: postgres=#

Check the server information: SELECT version();

Check the current connection address as well: SELECT inet_server_addr(), inet_server_port();

When connecting locally through a Unix socket, the address may be displayed as NULL. This is normal.

To exit psql, use the command: \q

At this stage, PostgreSQL is already running locally, but there is no separate user database or remote access yet. We will configure both next.

Creating a Database and User

Applications should not use the built-in postgres role as their operational account. We will create a separate user with its own password and a separate database owned by that user.

Creating a Dedicated PostgreSQL User

Open psql as the postgres system user: sudo -u postgres psql

Create a new user: CREATE USER appuser WITH PASSWORD ‘change_this_password’;

In a production environment, use a long, unique password and do not store it directly in plaintext in public documentation.

You can verify the created role with the following command: \du

The appuser role should appear in the list.

Creating a Database and Assigning an Owner

Now create a separate database and make the new user its owner: CREATE DATABASE appdb OWNER appuser;

Check the list of databases: \l

The table should include the appdb database, with appuser shown in the owner column.

After that, exit the administrative console: \q

Testing the connection to the new database

Verify that the new user can connect to their database via localhost: psql -h 127.0.0.1 -U appuser -d appdb

Enter the password set when the role was created.

If the connection is successful, the prompt will change to something like: appdb=>

Check the current user and database: SELECT current_user, current_database();

Expected result: appuser | appdb

Exit psql: \q

The database and user are now ready. The next step is PostgreSQL network and server settings.

Configuring postgresql.conf

The main configuration for a PostgreSQL instance is stored in postgresql.conf. This file defines network settings, memory usage, connection limits, and many other server parameters.

Before editing it, determine the exact path to the configuration file, because it depends on the PostgreSQL version.

Where the main configuration file is located

You can find the path directly from PostgreSQL: sudo -u postgres psql -t -P format=unaligned -c “SHOW config_file;”

On Ubuntu, the path usually looks something like this: /etc/postgresql/16/main/postgresql.conf

The version number in the directory may differ.

You can find the path to pg_hba.conf in the same way: sudo -u postgres psql -t -P format=unaligned -c “SHOW hba_file;”

Before changing the configuration, it is a good idea to create a backup:

sudo cp /etc/postgresql/16/main/postgresql.conf \

/etc/postgresql/16/main/postgresql.conf.bak

Replace 16 with the version that is actually installed.

Configuring listen_addresses

By default, PostgreSQL on Ubuntu usually accepts TCP connections only locally. You can check the current value with: sudo -u postgres psql -c "SHOW listen_addresses;"

If you use only an SSH tunnel for administration, you do not need to change listen_addresses at all: the client connects to a local port on the VPS through a secure SSH channel.

If PostgreSQL must accept connections from another VPS over a private network, open the configuration file: sudo nano /etc/postgresql/16/main/postgresql.conf

Then set, for example: listen_addresses = ‘localhost,172.30.16.205’

Here, 172.30.16.205 is provided only as an example private address of the server running the DBMS. In your configuration, use the actual address of the private network interface.

You can specify: listen_addresses = ‘*’

but this will cause PostgreSQL to listen on all available interfaces. By itself, this still does not allow connections, but this option requires especially careful configuration of pg_hba.conf and the firewall. For a database server, it is safer to limit the list to the required interfaces.

After changing this parameter, PostgreSQL must be restarted.

Basic performance settings

In addition to network configuration, postgresql.conf lets you set several basic memory usage parameters.

For a small VPS with a few gigabytes of RAM, you can use the following as a starting point, for example:

shared_buffers = 512MB

Up to €20,000 in EU-Based IaaS Credits

Frankfurt infrastructure, predictable pricing, and expert migration support.

Get

work_mem = 8MB

maintenance_work_mem = 128MB

effective_cache_size = 1536MB

max_connections = 100

These values are not a universally optimal configuration. They should be adjusted based on the amount of RAM, the number of concurrent connections, the nature of the queries, and the application workload.

A brief overview of the parameters:

• shared_buffers — memory allocated by PostgreSQL for its own page cache;
• work_mem — the memory limit for individual sorting and hashing operations;
• maintenance_work_mem — memory for maintenance operations, including VACUUM, index creation, and certain other tasks;
• effective_cache_size — an estimate of the available file system cache that the planner uses when choosing a query plan;
• max_connections — the maximum number of concurrent connections.

After editing, save the file and restart PostgreSQL: sudo systemctl restart postgresql

Check that the service started without errors: sudo systemctl status postgresql –no-pager

Then make sure the parameters have been applied:

sudo -u postgres psql -c "SHOW listen_addresses;"

sudo -u postgres psql -c "SHOW shared_buffers;"

sudo -u postgres psql -c "SHOW work_mem;"

At this stage, PostgreSQL is ready for the next layer of network configuration. Next, we will use pg_hba.conf to restrict exactly which clients are allowed to connect.

Configuring pg_hba.conf

The pg_hba.conf file defines which clients can connect to PostgreSQL, which databases they can access, and which users they can connect as. Even if the server is listening on a network interface via listen_addresses, the connection itself will not be allowed unless there is a matching rule in pg_hba.conf.

How PostgreSQL Authenticates Clients

Rules in pg_hba.conf are processed from top to bottom. PostgreSQL uses the first rule that matches the connection type, database, user, and client address.

A line typically has the following format: TYPE  DATABASE  USER  ADDRESS  METHOD

For example: host  appdb  appuser  10.10.0.0/24  scram-sha-256

This rule means:

• Allow a TCP connection;
• Only to the appdb database;
• Only for user appuser;
• Only from the 10.10.0.0/24 subnet;
• Use scram-sha-256 for authentication.

You can check the current path to the file with the following command: sudo -u postgres psql -t -P format=unaligned -c "SHOW hba_file;"

Before editing, it is best to create a backup copy:

sudo cp /etc/postgresql/16/main/pg_hba.conf \

/etc/postgresql/16/main/pg_hba.conf.bak

Replace 16 with the installed PostgreSQL version.

Allowing Local Connections

The standard Ubuntu configuration already includes rules for local connections via a Unix socket and localhost.

For example:

For connections through an SSH tunnel, the rule for 127.0.0.1/32 is especially important because, after passing through the tunnel, PostgreSQL sees the connection as local.

If the user appuser connects with a password, you can keep the general localhost rule or make it more restrictive: host    appdb    appuser    127.0.0.1/32    scram-sha-256

This allows access only to the required database and only for the required user.

Access from a private network

If an application running on another VPS needs to connect to PostgreSQL over a private network, add a rule for the specific subnet.

For example: host    appdb    appuser    172.30.16.0/24    scram-sha-256

Instead of allowing the entire subnet, it is even more secure to allow only the specific IP address of the application server: host    appdb    appuser    172.30.16.50/32    scram-sha-256

This approach reduces the number of hosts from which PostgreSQL will accept authentication attempts at all.

After changing pg_hba.conf, simply reload the configuration: sudo systemctl reload postgresql

Alternatively, run: sudo -u postgres psql -c "SELECT pg_reload_conf();"

Why you should not allow 0.0.0.0/0

In some examples, you may see a rule such as: host    all    all    0.0.0.0/0    scram-sha-256

It allows connection attempts from any IPv4 address.

Even with password authentication, this configuration unnecessarily increases the attack surface: a publicly exposed PostgreSQL instance can be continuously scanned, subjected to credential brute-force attempts, and burdened with unnecessary connections.

For production infrastructure, it is better to use one of three options:

• localhost + SSH tunnel;
• a specific private IP address;
• a trusted private subnet.

pg_hba.conf rules should also be supplemented with firewall-level restrictions.

Configuring the firewall

A firewall lets you drop unwanted connections before they reach PostgreSQL.

For this example, we’ll use ufw.

If the package is not already installed: sudo apt install -y ufw

Allowing SSH

Before enabling the firewall, be sure to allow SSH; otherwise, you may lose access to the VPS: sudo ufw allow OpenSSH

Check the rules: sudo ufw status

After that, you can enable the firewall: sudo ufw enable

Restricting PostgreSQL Access to a Trusted Network

If PostgreSQL is used only through an SSH tunnel, no rule for port 5432 is needed at all: the database remains accessible locally only.

If another VPS connects to it over a private network, allow the port only for the required subnet: sudo ufw allow from 172.30.16.0/24 to any port 5432 proto tcp

Even better, if the specific application server address is known: sudo ufw allow from 172.30.16.50 to any port 5432 proto tcp

Check the resulting configuration: sudo ufw status numbered

Example of the expected logic:

22/tcp     ALLOW IN    Anywhere

5432/tcp   ALLOW IN    172.30.16.50

Why port 5432 should not be exposed to the entire Internet

A command such as sudo ufw allow 5432/tcp

will allow connections to PostgreSQL from any address, provided the corresponding PostgreSQL network settings also permit external access.

This is not required in most scenarios.

If an administrator connects to the database from their own computer, it is safer to use an SSH tunnel. If another server in the same infrastructure needs PostgreSQL, use a private network and a rule that allows access only from a specific IP address or subnet.

This creates multi-layered access control:

Even if one layer is configured too broadly, the others continue to restrict access. In practice, however, it is better to scope each layer as narrowly as possible for the specific scenario from the outset.

Connecting through an SSH tunnel

An SSH tunnel lets you work with PostgreSQL remotely without exposing port 5432 to the internet. The client connects to a local port on your workstation, and SSH forwards the traffic to PostgreSQL on the VPS over a secure connection.

How an SSH Tunnel Works

In our case, the setup looks like this:

With this setup, there is no need to expose port 5432 publicly. The VPS only needs to make its SSH port accessible externally, while PostgreSQL can continue to accept connections only on localhost.

Port 15432 is used as an example. You can use any other available local port.

Creating a local tunnel

On your workstation, open a separate PowerShell or terminal window and run: ssh -i "C:\path\to\private-key.pem" -N -L 15432:127.0.0.1:5432 [email protected]

Here:

• -N — do not start a remote shell;
• -L — create local port forwarding;
• 15432 — the port on the workstation;
• 127.0.0.1:5432 — PostgreSQL on the VPS;
• 203.0.113.10 — an example public IP address of the VPS from the documentation range.

In the actual command, use the real server address and the path to the SSH key.

After it starts, the command may not print any messages and may simply remain active in the terminal. This is normal behavior for an SSH tunnel: as long as the process is running, the local port remains available.

In another window, you can verify that the port is listening. For example, on Windows: netstat -ano | findstr :15432

Full Control Over Your Kubernetes Infrastructure

Deploy production-ready Kubernetes with full control over nodes, storage, and networking on predictable-cost EU infrastructure with direct access to your resources.

Get

Connecting to PostgreSQL through a local port

You can now use PostgreSQL as if it were running on your local machine: psql -h 127.0.0.1 -p 15432 -U appuser -d appdb

After you enter the password, the database console will open: appdb=>

Verify the connection: SELECT current_user, current_database(), inet_server_addr(), inet_server_port();

The first two values should confirm that the connection was established as appuser to the appdb database.

Locally, the client connects to port 15432, while PostgreSQL itself continues to run on the standard port 5432 inside the VPS.

You can close the PostgreSQL connection with the following command: \q

To close the SSH tunnel itself, press Ctrl+C in the window where the ssh command was run.

Creating a Backup with pg_dump

Having PostgreSQL configured does not, by itself, protect against accidental data deletion, application errors, or failed schema changes. Therefore, the next step is to create a logical backup of the database using the standard pg_dump utility.

Backing Up a Single Database

First, add a small dataset to the demo database so you can verify the restore later.

Connect to it: psql -h 127.0.0.1 -U appuser -d appdb

Create a test table:

CREATE TABLE demo_notes (

id SERIAL PRIMARY KEY,

title TEXT NOT NULL

);

Add a few rows:

INSERT INTO demo_notes (title)

VALUES

(‘PostgreSQL VPS guide’),

(‘Backup test’),

(‘Restore test’);

Check the contents: SELECT * FROM demo_notes;

Then exit: \q

Create a directory for backups: mkdir -p ~/postgresql-backups

To save the database in custom format, run:

pg_dump -h 127.0.0.1 -U appuser -d appdb \

-F c \

-f ~/postgresql-backups/appdb.dump

After you enter the password, pg_dump will create the backup file.

Verify it: ls -lh ~/postgresql-backups/appdb.dump

For an additional check, you can list the archive contents without restoring it: pg_restore -l ~/postgresql-backups/appdb.dump | head

Dump file formats

pg_dump supports several backup formats. In practice, two of them are the most useful.

A plain SQL file is created with the following command:

pg_dump -h 127.0.0.1 -U appuser -d appdb \

-F p \

-f ~/postgresql-backups/appdb.sql

This is a text file containing SQL commands. It is easy to review and edit if needed, and restores are performed with psql.

Custom format:

pg_dump -h 127.0.0.1 -U appuser -d appdb \

-F c \

-f ~/postgresql-backups/appdb.dump

This format is designed for use with pg_restore. It is more convenient for flexible restores: you can select individual database objects and control the restore process.

For most regular backups of a single database, the custom format is a convenient option.

It is important to note that pg_dump creates a logical backup of a specific database, not a full copy of the entire PostgreSQL instance. In particular, global cluster objects, such as roles, are not saved separately by a standard pg_dump.

In the next step, we will restore this dump into a separate database and verify that the table and test records were actually preserved.

Restoring the Database

A backup should be verified not only by confirming that the file was created, but also by performing an actual restore. To do this, we will restore the dump to a separate test database and make sure that the tables and data have been preserved correctly.

Creating a Test Database for Restore

Create a new database into which we will restore appdb.dump.

Open psql with administrator privileges: sudo -u postgres psql

Create the database: CREATE DATABASE appdb_restore OWNER appuser;

Verify that it has been created: \l

Then exit: \q

Restoring with pg_restore or psql

If the backup was created in custom format (-F c), use pg_restore to restore it:

pg_restore \

-h 127.0.0.1 \

-U appuser \

-d appdb_restore \

~/postgresql-backups/appdb.dump

After you enter the password, the database objects and data will start being restored.

To see more detailed progress, add –verbose:

pg_restore \

–verbose \

-h 127.0.0.1 \

-U appuser \

-d appdb_restore \

~/postgresql-backups/appdb.dump

For a regular SQL file, use psql instead of pg_restore:

psql \

-h 127.0.0.1 \

-U appuser \

-d appdb_restore \

-f ~/postgresql-backups/appdb.sql

In other words, the command you choose depends on the backup format: custom archives are restored with pg_restore, while text-based SQL files are restored with psql.

Verifying the restored data

Connect to the restored database: psql -h 127.0.0.1 -U appuser -d appdb_restore

Check the list of tables: \dt

It should include the following table: demo_notes

Now verify that the records have also been restored: SELECT * FROM demo_notes;

Expected result:

High-Performance Virtual Machines

Deploy in seconds with full root access, reserved resources, built-in high availability, and predictable monthly pricing.

Get

If the database structure and data are present, the backup can be considered suitable for recovery.

For production systems, it makes sense to run these checks periodically, either automatically or on a separate test server. The mere presence of backup files does not guarantee that they can actually be restored.

Basic Performance Tuning

PostgreSQL performs reasonably well with its default settings, but several parameters are usually worth adjusting to match the resources of a specific VPS.

There are no universal values that work for every server. Performance depends on the amount of RAM, the number of CPUs, the database size, the query patterns, and the number of concurrent clients.

shared_buffers

The shared_buffers parameter defines the amount of memory PostgreSQL uses for its own buffer cache.

You can check the current value with the command: SHOW shared_buffers;

For small dedicated servers, a value of around 20–25% of available RAM is often used as a starting point, but this is not a hard rule.

For example: shared_buffers = 512MB

You should not automatically allocate nearly all RAM to PostgreSQL: the operating system, file cache, and other processes also need some RAM.

work_mem

work_mem sets the amount of memory that can be used by an individual sort operation, hash operation, or operation that builds certain intermediate results.

Check: SHOW work_mem;

Example: work_mem = 8MB

The specific nature of this parameter is that the limit applies not to the entire connection, but to individual operations within queries. A single complex query can use several such memory areas at the same time.

Therefore, setting work_mem too high with a large number of connections can lead to a noticeable increase in RAM usage.

maintenance_work_mem

maintenance_work_mem is used for maintenance operations, such as index creation, VACUUM, and some ALTER TABLE variants.

Check the value: SHOW maintenance_work_mem;

For a small VPS, you might start, for example, with: maintenance_work_mem = 128MB

Because these operations are usually performed less frequently than regular user queries, you can often allocate more memory to this setting than to work_mem.

effective_cache_size

effective_cache_size does not reserve memory directly.

This parameter tells the PostgreSQL planner how much data is expected to be available in the PostgreSQL and operating system caches.

Check the value: SHOW effective_cache_size;

For example: effective_cache_size = 1536MB

The planner uses this estimate when choosing between different query execution methods, including when deciding whether to use indexes.

Therefore, effective_cache_size should be treated specifically as an estimate of the available cache, not as a separate block of RAM allocated by PostgreSQL.

max_connections

max_connections sets the maximum number of concurrent connections to PostgreSQL.

Check: SHOW max_connections;

For example: max_connections = 100

Increasing this parameter “just in case” is not always helpful. Each connection requires resources, so hundreds or thousands of direct connections can significantly increase memory usage.

If the application requires a large number of short-lived connections, it is often better to use a connection pool, such as PgBouncer, rather than simply increasing max_connections.

After changing parameters that require a restart, apply the configuration with: sudo systemctl restart postgresql

Then check the values all at once with a single command:

sudo -u postgres psql -c "

SHOW shared_buffers;

SHOW work_mem;

SHOW maintenance_work_mem;

SHOW effective_cache_size;

SHOW max_connections;

"

Alternatively, from an already open psql console:

SHOW shared_buffers;

SHOW work_mem;

SHOW maintenance_work_mem;

SHOW effective_cache_size;

SHOW max_connections;

These parameters provide a basic starting point, but comprehensive PostgreSQL optimization should be based on the actual workload, query monitoring, and available server resources.

Conclusion

PostgreSQL can be deployed on a VPS in a few minutes, but installation alone is not enough for a production environment. It is important to plan from the outset which hosts the database should accept connections from, which users are granted access, and how recovery will be performed after a failure.

In this guide, we installed PostgreSQL, created a separate database and user, configured postgresql.conf and pg_hba.conf, restricted network access with a firewall, and reviewed two secure connection scenarios: a private network between servers and an SSH tunnel for remote administration. This meant we did not have to expose port 5432 to the entire internet.

We also created a database backup using pg_dump, restored it to a separate database, and verified that the data was intact. Finally, we configured several basic memory and connection settings. For a real workload, these values should be treated only as a starting point and adjusted based on the monitoring results for both PostgreSQL and the VPS itself.

FAQ

Do I need to expose port 5432 to the internet?

In most cases, no. For administrative access, it is more convenient to use an SSH tunnel; for communication between your own VPS instances, use a private network.

Public access to port 5432 only makes sense when it is genuinely required by the application architecture. In that case, it should be restricted using a firewall, pg_hba.conf rules, and trusted IP addresses.

How do postgresql.conf and pg_hba.conf differ?

postgresql.conf controls general PostgreSQL server settings, such as network interfaces, memory, the number of connections, and other configuration options.

pg_hba.conf defines exactly who is allowed to connect: to which database, as which user, from which address, and using which authentication method.

For external connections, both files usually need to be configured correctly.

Do I need to change listen_addresses for an SSH tunnel?

No. If the SSH tunnel forwards the connection to 127.0.0.1:5432 on the VPS side, PostgreSQL can continue listening only on localhost.

You need to change listen_addresses, for example, when another server needs to connect directly to the database over a private network.

Why does PostgreSQL refuse connections after listen_addresses is changed?

The listen_addresses setting alone is not enough. Check the following:

• A matching rule in pg_hba.conf;
• Firewall settings;
• Whether the IP address and port are correct;
• Whether PostgreSQL was restarted after changing parameters that require a restart.

It is also useful to check the service status and the PostgreSQL log.

Which format is better for backups: SQL or custom?

An SQL file is convenient because it is plain text containing SQL commands and can be restored using psql.

The custom format, pg_dump -F c, is used with pg_restore and provides more options when restoring individual objects. For regular backups of a single database, it is often more convenient.

Does pg_dump preserve PostgreSQL users?

A standard pg_dump backs up the contents and structure of a specific database, but not all global cluster objects.

Roles and other global objects can be backed up separately if needed, for example by using pg_dumpall –globals-only.

How often should backups be performed?

It depends on how much data loss is acceptable in the event of a failure. If the acceptable loss is one day, daily backups may be sufficient. If it is several hours or minutes, more frequent backups or additional mechanisms, such as WAL archiving and Point-in-Time Recovery, will be required.

It is important not only to create backups but also to periodically test actual recovery.

How should you choose values for shared_buffers and work_mem?

There are no universal values. They depend on the amount of RAM, the number of connections, and the nature of the queries.

shared_buffers is typically configured as a significant but not dominant portion of available memory, while work_mem requires particular care: this limit can be used simultaneously by multiple operations and multiple connections.

For a heavily loaded database, it is better to tune these parameters based on monitoring results rather than relying only on ready-made formulas from the internet.

Sources

1. PostgreSQL Documentation — Server Administration: Client Authentication
2. PostgreSQL Documentation — Server Configuration
3. PostgreSQL Documentation — pg_dump
4. PostgreSQL Documentation — pg_restore