Home
Blog
PostgreSQL
How to Deploy PostgreSQL on Dedicated Server for SaaS

How to Deploy PostgreSQL on Dedicated Server for SaaS

Q: How much RAM does PostgreSQL need for a SaaS application?

There&#039;s no fixed number. Size it against your working set, the portion of your data accessed often enough to benefit from caching. A common starting point is enough RAM to set `shared_buffers` to roughly 25 percent of total memory while leaving room for the OS page cache and `work_mem` across concurrent connections. Revisit it once real traffic arrives and you can see how much of your data is being read from disk.

Q: How do you upgrade PostgreSQL major versions on Ubuntu?

Install the new version&#039;s packages, drop the empty cluster it creates, then run `pg_upgradecluster -m upgrade` against your existing cluster. Rehearse the process on a restored backup first and read the release notes for behavior changes affecting your queries. Minor version updates need only a package update and a restart.

How to Deploy PostgreSQL on Dedicated Server for SaaS

Goodness Chris-Ugari

#PostgreSQL

Published on Aug 19, 2026 Updated on Aug 20, 2026

Managed PostgreSQL plans cap your shared_buffers, block certain extensions, and charge more the moment your SaaS product needs a bigger instance or a read replica in another region. A dedicated server removes those limits. You take on the production build yourself: storage layout, security, pooling, backups, replication, and monitoring.

This guide will walk you through the build from a bare Ubuntu server to a PostgreSQL 18 deployment ready for real SaaS traffic. It also covers moving an existing database off a managed service.

#Prerequisites

To follow along, you need:

A dedicated server running Ubuntu 24.04 LTS
Root access or sudo privileges
A static private IP address shared with your application servers
A second server on the same private network if you want the standby replica
Basic familiarity with the Linux command line

This walkthrough uses a dedicated server from Cherry Servers. You can use any provider you want, the PostgreSQL commands work the same.

Decide now whether you are building one server or two. A single server takes you through every section except the standby replica, which needs a second machine on the same private network with the same specifications. You can add it later, and the standby section notes what changes when you do.

Commands in this guide use angle-bracket placeholders like <your_private_ip> where an address belongs. Substitute your own; the security section shows how to find them.

Rent Dedicated Servers

Deploy custom or pre-built dedicated bare metal. Get full root access, AMD EPYC and Ryzen CPUs, and 24/7 technical support from humans, not bots.

Deploy a Dedicated Server

#Why Run PostgreSQL on a Dedicated Server?

Before you touch the server, it helps to know what you're trading managed convenience for.

Full resource control: Every CPU core, all your RAM, your own disks. PostgreSQL caches data in memory, and a shared or virtualized host won't promise that memory stays yours under load.
Configuration control: Managed services cap shared_buffers, block superuser access and restrict which extensions you install. The limits are concrete: Azure Database for PostgreSQL states that you cannot configure io_method = io_uring on PostgreSQL 18, and publishes a fixed list of the extensions it supports. On your own server, you set every value and install whatever your application needs.
Cost at higher tenant counts: Managed pricing climbs fast once you need a bigger instance or a cross-region replica. A dedicated server runs the same PostgreSQL engine for a fraction of that cost, because you're paying for hardware rather than a management layer sitting on top of it.
Data residency: Some SaaS contracts require customer data to stay on infrastructure you control, with no third party holding your encryption keys or backup copies. A dedicated server gives you the infrastructure to meet that requirement. You still pick the data center location, set up encryption yourself, and manage your own backup storage.

#Understanding the Deployment Architecture

Before you provision anything, it helps to see the whole system you're building toward.

Application servers never talk to PostgreSQL directly. They connect to PgBouncer on port 6432 over the private network, and PgBouncer runs on the database host and reaches PostgreSQL over loopback on 5432. That means the only port your application subnet ever needs is 6432.

The primary handles every write and streams its write-ahead log continuously to a standby, ready to promote if the primary fails. Backups run against the primary on a schedule and get copied to storage outside the server itself, so a hardware failure cannot take out your production data and its backups in the same event.

Each section below builds one part of that, in the order a real deployment needs.

#Installing PostgreSQL on a Dedicated Server

Here you're going to go through the steps for provisioning the server, preparing the disks and kernel, then installing PostgreSQL 18.

#Step 1: Provision the server

If you're using Cherry Servers, sign in to your portal and click New instance. Pick a server from the dedicated servers section, choose Ubuntu 24.04 as the operating system, then deploy it.

Once it's ready, you'll see the server's public and private IP addresses and its root password. On a different provider, you need the same two things: a public IP for SSH, and a private IP shared with your application servers.

Size the server around your working set, meaning the portion of your data queried often enough to benefit from caching. RAM matters most, since performance changes sharply once that working set stops fitting in memory. Choose NVMe over SATA, and enough cores to cover your real concurrency rather than your tenant count.

If you are starting without these metrics, a server with 32 GB of RAM and 8 cores runs a small SaaS product comfortably and leaves room to grow. Revisit it once monitoring gives you actual figures.

Then connect over SSH:

ssh root@<server_ip>

Accept the host key prompt on first connection.

#Step 2: Create a non-root user

If you're logged in as root, don't stay that way. Create a sudo user instead:

adduser dbadmin
usermod -aG sudo dbadmin
su - dbadmin

Outputinfo: Adding user `dbadmin' ...
info: Selecting UID/GID from range 1000 to 59999 ...
info: Adding new group `dbadmin' (1001) ...
info: Adding new user `dbadmin' (1001) with group `dbadmin (1001)' ...
info: Creating home directory `/home/dbadmin' ...
info: Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
Changing the user information for dbadmin
Enter the new value, or press ENTER for the default
        Full Name []:
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [Y/n] Y
info: Adding new user `dbadmin' to supplemental / extra groups `users' ...
info: Adding user `dbadmin' to group `users' ...

Then update the system:

sudo apt update && sudo apt upgrade -y

OutputHit:1 http://repo.cherryservers.com/ubuntu noble InRelease
Hit:2 http://repo.cherryservers.com/ubuntu noble-updates InRelease
Hit:3 http://repo.cherryservers.com/ubuntu noble-backports InRelease
Hit:4 http://repo.cherryservers.com/ubuntu noble-security InRelease
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
133 packages can be upgraded. Run 'apt list --upgradable' to see them.
...
133 upgraded, 16 newly installed, 0 to remove and 0 not upgraded.
105 standard LTS security updates
Need to get 1,186 MB of archives.
After this operation, 658 MB of additional disk space will be used.

An update this size on a freshly provisioned server is normal. Reboot before you continue:

sudo reboot

#Step 3: Prepare storage and kernel settings

Do this before installing PostgreSQL. Mounting a disk at /var/lib/postgresql after installation hides the cluster underneath the mount point, and PostgreSQL fails to start with a data directory it can no longer see.

Read your disk layout before you touch anything:

lsblk -o NAME,SIZE,TYPE,FSTYPE,MOUNTPOINTS

You are looking for one thing: a disk with no FSTYPE, no mount point, and no md parent above it. That is an unused disk, and it is the only thing safe to format.

Everything else on that list is in use, whatever it looks like. A linux_raid_member partition is half a mirror, and formatting it destroys your root array. Anything carrying a mount point is holding something already.

A two-drive dedicated server usually ships with both disks mirrored and nothing spare:

OutputNAME          SIZE TYPE  FSTYPE            MOUNTPOINTS
nvme0n1     894.3G disk
├─nvme0n1p1   488M part  vfat              /boot/efi
└─nvme0n1p2 893.8G part  linux_raid_member
  └─md0     893.6G raid1 ext4              /
nvme1n1     894.3G disk
├─nvme1n1p1   488M part  vfat              /boot/efi/ubuntu2
└─nvme1n1p2 893.8G part  linux_raid_member
  └─md0     893.6G raid1 ext4              /

Read nvme1n1 from the bottom up and every byte is accounted for: a 488M EFI partition and 893.8G mirroring the drive you are running on. No unused disk here.

First, if your lsblk output showed an md device, confirm the array is healthy before putting a database on it:

cat /proc/mdstat

You want [2/2] [UU]. A freshly provisioned server often shows a resync line alongside it, which is the initial mirror build rather than a fault, and writes are mirrored normally while it runs:

OutputPersonalities : [raid0] [raid1] [raid4] [raid5] [raid6] [raid10] [linear]
md0 : active raid1 nvme0n1p2[0] nvme1n1p2[1]
      937049088 blocks super 1.2 [2/2] [UU]
      bitmap: 1/7 pages [4KB], 65536KB chunk

unused devices: <none>

[U_] or [_U] instead of [UU] means a degraded array, worth resolving with your provider before you continue. Otherwise carry on while the resync runs, but hold off on the performance baseline at the end of this guide until it finishes, or you will be measuring the rebuild rather than your server.

Then take one of the three paths below:

No unused disk

Your data directory stays where it is, on the root filesystem. On mirrored NVMe that is a good home for a database, and there is nothing to do here. Skip to the kernel settings.

An unused disk

Put the data directory on it. NVMe cuts read latency and checkpoint write time against SATA SSD, and XFS handles large files and concurrent writes better than ext4 under parallel I/O:

sudo apt install -y xfsprogs
sudo mkfs.xfs <device>
sudo mkdir -p /var/lib/postgresql
sudo mount <device> /var/lib/postgresql

Add it to /etc/fstab with noatime, which stops the filesystem writing access-time metadata on every read:

<device>  /var/lib/postgresql  xfs  defaults,noatime  0  2

Confirm it mounts cleanly:

sudo mount -a && df -h /var/lib/postgresql

An unused disk, with PostgreSQL already installed

Stop the server first and copy the data across. Mounting over a live data directory hides it rather than moving it:

sudo systemctl stop postgresql
sudo mount <device> /mnt/newdisk
sudo rsync -av /var/lib/postgresql/ /mnt/newdisk/
sudo umount /mnt/newdisk
sudo mount <device> /var/lib/postgresql
sudo systemctl start postgresql

Two kernel settings matter whichever layout you have, since the defaults assume a general-purpose machine rather than a database server. Write them to a file under /etc/sysctl.d/ so a package update never overwrites them:

echo "vm.swappiness=1" | sudo tee /etc/sysctl.d/60-postgresql.conf
sudo sysctl --system

Outputvm.swappiness=1
* Applying /usr/lib/sysctl.d/10-apparmor.conf ...
* Applying /etc/sysctl.d/10-bufferbloat.conf ...
* Applying /etc/sysctl.d/10-console-messages.conf ...
* Applying /etc/sysctl.d/10-ipv6-privacy.conf ...
* Applying /etc/sysctl.d/10-kernel-hardening.conf ...
* Applying /etc/sysctl.d/10-magic-sysrq.conf ...
* Applying /etc/sysctl.d/10-map-count.conf ...
* Applying /etc/sysctl.d/10-network-security.conf ...
* Applying /etc/sysctl.d/10-ptrace.conf ...
* Applying /etc/sysctl.d/10-zeropage.conf ...
* Applying /usr/lib/sysctl.d/50-pid-max.conf ...
* Applying /etc/sysctl.d/60-postgresql.conf ...
* Applying /etc/sysctl.d/99-ping.conf ...
* Applying /usr/lib/sysctl.d/99-protect-links.conf ...
* Applying /etc/sysctl.d/99-sysctl.conf ...
* Applying /etc/sysctl.conf ...
kernel.apparmor_restrict_unprivileged_userns = 1
net.core.default_qdisc = fq_codel
kernel.printk = 4 4 1 7
kernel.kptr_restrict = 1
kernel.sysrq = 176
vm.max_map_count = 1048576
net.ipv4.conf.default.rp_filter = 2
net.ipv4.conf.all.rp_filter = 2
kernel.yama.ptrace_scope = 1
vm.mmap_min_addr = 65536
kernel.pid_max = 4194304
vm.swappiness = 1
net.ipv4.ping_group_range = 0 2147483647
fs.protected_fifos = 1
fs.protected_hardlinks = 1
fs.protected_regular = 2
fs.protected_symlinks = 1

Check the running value rather than trusting the file you wrote. sysctl --system reads its directories in basename order and applies /etc/sysctl.conf last, so a vm.swappiness set in a higher-numbered file silently overrides yours:

sysctl vm.swappiness

Outputvm.swappiness = 1

A low vm.swappiness keeps the kernel from swapping PostgreSQL's shared memory to disk under pressure. Skip this and you will eventually see it as a sudden latency spike unrelated to your queries. Treat the value as a starting point and check it against your own workload once monitoring is live.

Transparent huge pages cause a similar spike during memory allocation, though only in one of their three states. Check which one you are on:

cat /sys/kernel/mm/transparent_hugepage/enabled

Outputalways [madvise] never

All three words print every time and the brackets mark the active one. Only one of the three needs anything from you.

always madvise [never] or always [madvise] never are both fine, and you can move on. Under madvise the kernel hands huge pages only to processes that ask through madvise(), and PostgreSQL's normal allocations never do.

[always] madvise never needs turning off. That mode gives huge pages to every eligible allocation, and the compaction stalls that follow show up as latency with no matching slow query. Writing to /sys directly lasts until the next reboot, so use a systemd unit:

sudo tee /etc/systemd/system/disable-thp.service > /dev/null <<'EOF'
[Unit]
Description=Disable transparent huge pages
DefaultDependencies=no
After=sysinit.target local-fs.target
Before=postgresql.service

[Service]
Type=oneshot
ExecStart=/bin/sh -c "echo never > /sys/kernel/mm/transparent_hugepage/enabled"
RemainAfterExit=yes

[Install]
WantedBy=basic.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now disable-thp.service
cat /sys/kernel/mm/transparent_hugepage/enabled

You want the [never] state, and the same result after a reboot.

#Step 4: Install PostgreSQL

Ubuntu ships an older PostgreSQL than the current release, so pull it from the PostgreSQL Global Development Group (PGDG) repository instead:

sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

OutputReading package lists... Done
Building dependency tree... Done
Reading state information... Done
ca-certificates is already the newest version (20260601~24.04.1).
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libtypes-serialiser-perl
  postgresql-client-common postgresql-common
0 upgraded, 6 newly installed, 0 to remove and 0 not upgraded.
...
Setting up postgresql-common (257build1.1) ...

Creating config file /etc/postgresql-common/createcluster.conf with new version

OutputThis script will enable the PostgreSQL APT repository on apt.postgresql.org on
your system. The distribution codename used will be noble-pgdg.

Press Enter to continue, or Ctrl-C to abort.

Using keyring /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
Writing /etc/apt/sources.list.d/pgdg.sources ...

Running apt-get update ...
Hit:1 http://repo.cherryservers.com/ubuntu noble InRelease
Hit:2 http://repo.cherryservers.com/ubuntu noble-updates InRelease
Hit:3 http://repo.cherryservers.com/ubuntu noble-backports InRelease
Hit:4 http://repo.cherryservers.com/ubuntu noble-security InRelease
Get:5 https://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease [189 kB]
Get:6 https://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 Packages [1,090 kB]
Fetched 1,279 kB in 6s (202 kB/s)
Reading package lists... Done

You can now start installing packages from apt.postgresql.org.

Have a look at https://wiki.postgresql.org/wiki/Apt for more information;
most notably the FAQ at https://wiki.postgresql.org/wiki/Apt/FAQ

The script writes /etc/apt/sources.list.d/pgdg.sources, installs the signing key and runs apt-get update for you. Confirm the prompt when it appears.

PostgreSQL 18 is the current stable release as this is written, and this guide assumes it throughout, including in paths like /etc/postgresql/18/main. On a newer major version, substitute that number everywhere it appears.

Then install the server:

sudo apt update
sudo apt install -y postgresql-18

OutputHit:1 http://repo.cherryservers.com/ubuntu noble InRelease
Hit:2 http://repo.cherryservers.com/ubuntu noble-updates InRelease
Hit:3 http://repo.cherryservers.com/ubuntu noble-backports InRelease
Hit:4 http://repo.cherryservers.com/ubuntu noble-security InRelease
Hit:5 https://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
2 packages can be upgraded. Run 'apt list --upgradable' to see them.

OutputThe following NEW packages will be installed:
  libllvm19 libpq5 liburing2 postgresql-18 postgresql-18-jit postgresql-client-18
2 upgraded, 6 newly installed, 0 to remove and 0 not upgraded.
Need to get 48.8 MB of archives.
...
Setting up postgresql-18 (18.6-1.pgdg24.04+2) ...
Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --auth-local peer --auth-host scram-sha-256 --no-instructions

Data page checksums are enabled.

selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB

Once the installation finishes, check the cluster it created:

pg_lsclusters

OutputVer Cluster Port Status Owner    Data directory              Log file
18  main    5432 down   postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Status: down is normal here. Many provider images block services from starting during package installation, so PostgreSQL installed correctly and simply never started.

Start the PostgreSQL service and check its status:

sudo systemctl start postgresql
sudo systemctl status postgresql

Output● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Thu 2026-07-30 18:06:26 UTC; 9s ago
    Process: 51563 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 51563 (code=exited, status=0/SUCCESS)
        CPU: 733us
Jul 30 18:06:26 pg1 systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
Jul 30 18:06:26 pg1 systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.

Confirm the cluster is up:

pg_lsclusters

OutputVer Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Status now reads online. If it still reads down, check the unit for that specific cluster, which is where startup errors appear:

sudo systemctl status postgresql@18-main

Now connect as the postgres user and confirm the version:

sudo -u postgres psql -P pager=off -c "SELECT version();"

Outputversion                         
-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

Four extensions used later in this guide ship with the server package. Check they are present, so you know there is no separate package to install:

sudo -u postgres psql -c "SELECT name FROM pg_available_extensions WHERE name IN ('pg_stat_statements','pgcrypto','pg_trgm','citext') ORDER BY name;"

Output        name
--------------------
 citext
 pg_stat_statements
 pg_trgm
 pgcrypto

These extensions are not active yet, and you enable them inside your application database in a later step.

Next, check the data directory permissions, since PostgreSQL refuses to start if they are wrong:

ls -ld /var/lib/postgresql/18/main

Outputdrwx------ 19 postgres postgres 4096 Jul 30 17:57 /var/lib/postgresql/18/main

Verify that the owner and group are both postgres, and the permissions are set to drwx------ (700). If they do not match, fix them immediately by running:

sudo chown -R postgres:postgres /var/lib/postgresql/18/main
sudo chmod 700 /var/lib/postgresql/18/main

Finally, verify that data page checksums are enabled, as the subsequent WAL configuration relies on this setting:

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

Output data_checksums
----------------
 on
(1 row)

#How to Configure PostgreSQL for Production

By default, a fresh PostgreSQL installation uses a conservative 128 MB of shared memory, regardless of your server's actual RAM. To optimize performance for production, you will gradually replace about twenty default settings by building a custom tuning configuration file step-by-step.

#Step 1: Check your hardware

Every memory and worker value below scales from two numbers:

free -g
nproc

Output               total        used        free      shared  buff/cache   available
Mem:              93           1          89           0           3          91
Swap:              7           0           7

Output16

That's 93 GB of RAM and 16 cores. Size against the figure free reports, not the number on the spec sheet. A 64 GB server typically reports 61 after firmware reservation, and PostgreSQL can only use what the operating system hands out.

#Step 2: Create the tuning file

Leave postgresql.conf alone and put your changes in a separate file inside the conf.d directory. PostgreSQL reads postgresql.conf first and conf.d second, applying the last value it reads.

Confirm your installation reads the conf.d directory

grep -n include_dir /etc/postgresql/18/main/postgresql.conf

Output879:include_dir = 'conf.d'			# include files ending in '.conf' from

If you see this line, create the tuning file:

sudo -u postgres nano /etc/postgresql/18/main/conf.d/10-tuning.conf

Running the editor as the postgres user assigns the correct file permissions.

If the grep command returned nothing, your installation has no conf.d directory. Copy the main configuration file (postgresql.conf) to create a backup:

sudo cp /etc/postgresql/18/main/postgresql.conf /etc/postgresql/18/main/postgresql.conf.orig

Then open it and add the blocks below at the end of the file, leaving the commented defaults untouched:

sudo nano /etc/postgresql/18/main/postgresql.conf

Either way, keep the editor open. The next four steps each add a block, and you save at the end.

#Step 3: Add memory settings

Work these out from the RAM free reported. shared_buffers takes 25 percent, effective_cache_size 75 percent, and maintenance_work_mem roughly 6 percent, with autovacuum_work_mem at a quarter of that last figure. work_mem stays at 64MB regardless of server size, since it applies per operation rather than per server.

On a server reporting 93 GB, that gives 24GB of shared_buffers, 70GB of effective_cache_size, 6GB and 1GB for the two maintenance settings, and 64MB of work_mem.

Add your values to the file, using those figures as the example:

# Memory
shared_buffers = 24GB
effective_cache_size = 70GB
work_mem = 64MB
maintenance_work_mem = 6GB
autovacuum_work_mem = 1GB

effective_cache_size allocates nothing. It only tells the planner how much cache exists across PostgreSQL and the operating system, so a low value pushes it toward sequential scans.

work_mem is allocated per sort or hash, so one query with three sorts takes three times the value. Raise it later only if the logging you add below shows queries spilling to disk.

autovacuum_work_mem inherits maintenance_work_mem unless you set it, and three autovacuum workers run at once. Left alone, 6GB quietly becomes 18GB.

#Step 4: Add planner and execution settings

These three are the same on any hardware:

# Planner and execution
random_page_cost = 1.1
effective_io_concurrency = 200
jit = off

random_page_cost defaults to 4.0, a figure from the spinning disk era where random reads cost far more than sequential ones. On NVMe the two are close, and the default makes the planner skip indexes it should use.

effective_io_concurrency tells PostgreSQL how many reads your storage handles at once, so it prefetches more aggressively than the conservative default.

jit compiles query plans above jit_above_cost, which pays off on long analytical scans and costs you on short transactions by adding compile time to the response. Turn it back on if you add heavy reporting queries.

#Step 5: Add connection and worker limits

Set max_worker_processes and max_parallel_workers to your core count, and max_parallel_maintenance_workers to a quarter of it. The example below is for the 16 cores nproc reported:

# Connections and workers
max_connections = 200
client_connection_check_interval = 10000
max_worker_processes = 16
max_parallel_workers = 16
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 4

max_connections = 200 is sized for real concurrent load, not tenant count. PgBouncer sits in front of it later and caps actual concurrency around 25.

client_connection_check_interval arrived in PostgreSQL 14. Without it, a backend serving a crashed client keeps running until it tries to reply and finds nobody listening, holding its memory the whole time.

max_parallel_workers_per_gather stays at 2 on any core count. Parallel query adds worker startup to every plan that qualifies, which costs more than it returns on short transactions.

max_parallel_maintenance_workers is the one that helps here. It speeds the CREATE INDEX and REINDEX CONCURRENTLY operations in the maintenance section, which run during quiet hours anyway.

#Step 6: Add WAL, logging and statistics settings

The last block, identical on any hardware:

# WAL and checkpoints
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_log_hints = on

# Logging 
log_min_duration_statement = 500
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 0
log_temp_files = 0

# Query statistics
shared_preload_libraries = 'pg_stat_statements'

wal_level = replica is required for the streaming replication you set up later. checkpoint_completion_target spreads checkpoint writes across the interval instead of bursting them, avoiding a periodic I/O spike. wal_log_hints is what makes pg_rewind work when you add automatic failover.

log_min_duration_statement = 500 logs anything slower than half a second, which is how you find the slow query nobody reported. log_lock_waits catches blocking you'd otherwise see only as unexplained latency. log_temp_files = 0 logs every spill to disk, the direct signal that work_mem is too low.

shared_preload_libraries loads pg_stat_statements, which records query timings. You create the extension itself later, once the application database exists.

#Step 7: Save and restart

Save and exit, then read the file back:

sudo cat /etc/postgresql/18/main/conf.d/10-tuning.conf

Confirm every setting you added is there. Restart, since shared_buffers and shared_preload_libraries both need a full restart rather than a reload:

sudo systemctl restart postgresql
pg_lsclusters

OutputVer Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Status reads online. Confirm the server came up clean rather than trusting the status column:

sudo tail -5 /var/log/postgresql/postgresql-18-main.log

A healthy start ends like this:

Output2026-07-31 01:17:36.507 UTC [48817] LOG:  listening on IPv6 address "::1", port 5432
2026-07-31 01:17:36.507 UTC [48817] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-07-31 01:17:36.507 UTC [48817] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-07-31 01:17:36.510 UTC [48823] LOG:  database system was shut down at 2026-07-31 01:17:36 UTC
2026-07-31 01:17:36.512 UTC [48817] LOG:  database system is ready to accept connections

If Status reads down instead, PostgreSQL rejected something in the file. The reason is in the log:

sudo tail -30 /var/log/postgresql/postgresql-18-main.log

Move the file aside to get a working server back while you fix it:

sudo mv /etc/postgresql/18/main/conf.d/10-tuning.conf /tmp/
sudo systemctl restart postgresql

#Step 8: Verify the settings applied

Check the values PostgreSQL is actually running with. Two things override your file: a duplicate setting further down it, and anything set through ALTER SYSTEM.

sudo -u postgres psql -P pager=off -c \
  "SELECT name, setting, unit FROM pg_settings WHERE name IN
   ('shared_buffers','work_mem','max_connections','wal_level','jit') ORDER BY name;"

Output        name       | setting | unit
-----------------+---------+------
 jit             | off     |
 max_connections | 200     |
 shared_buffers  | 3145728 | 8kB
 wal_level       | replica |
 work_mem        | 65536   | kB
(5 rows)

shared_buffers reads as 3145728 because the unit is 8kB blocks. That is 24GB, not a failed setting.

#Step 9: Decide on your input and output method

PostgreSQL 18 added io_method, which controls how it reads from disk. worker is the default and uses background processes. io_uring talks to the kernel directly through a shared ring buffer.

Benchmarked on a 16 core server with 24GB of shared_buffers, the two came within 1.5 percent of each other on both write-heavy and read-only workloads, which is inside run-to-run variation. The test data fit entirely in shared_buffers, so PostgreSQL barely touched storage, and io_method governs the storage read path.

That describes most SaaS databases under a few hundred gigabytes. Leave the default in place and revisit this only once your working set outgrows memory.

If it has, io_uring needs Linux 5.6 or newer and a build compiled with liburing. Check the kernel with uname -r, then test the build:

sudo -u postgres psql -c "ALTER SYSTEM SET io_method = 'io_uring';"
sudo systemctl restart postgresql
sudo -u postgres psql -P pager=off -c "SHOW io_method;"

ALTER SYSTEM

 io_method
-----------
 io_uring

Without liburing, the first command fails immediately with invalid value for parameter. Run these commands to revert the setting:

sudo -u postgres psql -c "ALTER SYSTEM RESET io_method;"
sudo systemctl restart postgresql

#How to Secure PostgreSQL for Remote Access

PostgreSQL listens on loopback after installation. Your application servers need to reach it, which means a firewall, roles scoped to what they do, and TLS on every connection.

#Step 1: Find your private IP

Your server has at least two addresses. Only one of them belongs in firewall rules:

ip -4 -br addr show

Outputlo               UNKNOWN        127.0.0.1/8
bond0            UP             84.32.32.40/26
bond0.1592@bond0 UP             10.175.44.42/24

Private IP addresses begin with 10., 172.16. through 172.31., or 192.168.. Here, the address is 10.175.44.42 with a subnet of 10.175.44.0/24. Public addresses must not appear in PostgreSQL rules.

Interface names give no clue on bonded hardware, so go by the addresses. Note your private address and subnet before moving on.

Missing private addresses indicate an unattached private network. Contact your hosting provider to attach a private network interface.

#Step 2: Open the firewall

UFW blocks all incoming traffic once enabled, including the SSH session you are using, so add the SSH rule first:

sudo ufw allow OpenSSH

OutputRules updated
Rules updated (v6)

Now allow your application servers to reach PgBouncer on port 6432, using your private subnet from Step 1:

sudo ufw allow from <your_private_subnet> to any port 6432

Rules updated

Port 5432 stays closed. Applications connect through PgBouncer, which runs on this server and reaches PostgreSQL over loopback. The only machine that ever needs 5432 is a standby replica, and you add that rule in the replication section if you build one.

Enable UFW and verify status:

sudo ufw enable
sudo ufw status

OutputCommand may disrupt existing ssh connections. Proceed with operation (y|n)? y
Firewall is active and enabled on system startup

OutputStatus: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
6432                       ALLOW       10.175.44.0/24
OpenSSH (v6)               ALLOW       Anywhere (v6)

#Step 3: Create the database roles

Create two dedicated roles. The saas_owner role owns the database schema and runs migrations. The app_service role handles runtime connections.

Table owners bypass row level security policies by default. Let your application connect as the role owning the tables and every isolation policy you write later is silently ignored.

Generate two passwords:

openssl rand -base64 24
openssl rand -base64 24

Store both passwords securely.

Then open psql as the postgres user:

sudo -u postgres psql

Execute these commands at the postgres=# prompt, replacing the placeholders with generated passwords:

CREATE ROLE saas_owner LOGIN PASSWORD 'first_generated_value';
CREATE ROLE app_service LOGIN PASSWORD 'second_generated_value';
CREATE DATABASE saas_app OWNER saas_owner;

Do not run application workloads using the postgres superuser role.

Verify the password encryption method:

SHOW password_encryption;

Output password_encryption
---------------------
 scram-sha-256
(1 row)

SCRAM-SHA-256 serves as the default authentication method in PostgreSQL 14 and newer versions.

Exit psql using \q.

#Step 4: Generate a TLS certificate

Generate a self-signed TLS certificate to encrypt network traffic:

sudo openssl req -new -x509 -days 365 -nodes \
  -out /etc/postgresql/18/main/server.crt \
  -keyout /etc/postgresql/18/main/server.key \
  -subj "/CN=db.internal" 2>/dev/null

PostgreSQL refuses to start if the key is readable by anyone else, so fix ownership and permissions:

sudo chown postgres:postgres /etc/postgresql/18/main/server.key /etc/postgresql/18/main/server.crt
sudo chmod 600 /etc/postgresql/18/main/server.key
ls -l /etc/postgresql/18/main/server.*

Confirm postgres ownership on both files and 600 (-rw-------) permissions on the key file.

#Step 5: Turn on TLS and bind to the private IP

Open your custom tuning file:

sudo -u postgres nano /etc/postgresql/18/main/conf.d/10-tuning.conf

Add network and TLS configuration settings at the end of the file, substituting your private IP address:

# Network and TLS
listen_addresses = 'localhost,<your_private_ip>'
ssl = on
ssl_cert_file = '/etc/postgresql/18/main/server.crt'
ssl_key_file = '/etc/postgresql/18/main/server.key'

Save and exit.

The listen_addresses setting binds PostgreSQL to loopback and your private IP. Loopback handles PgBouncer connections. The private IP handles replication traffic. PostgreSQL remains unexposed on public interfaces.

File paths for ssl_cert_file and ssl_key_file require absolute paths. Relative file paths resolve against the data directory and cause startup failures.

#Step 6: Restrict user connection permissions

Edit pg_hba.conf to control client access. PostgreSQL evaluates rules top to bottom and applies the first matching rule:

sudo -u postgres nano /etc/postgresql/18/main/pg_hba.conf

Locate the section labeled # IPv4 local connections:. Add rule entries above the default host all all 127.0.0.1/32 entry:

# IPv4 local connections:
hostssl saas_app        app_service     127.0.0.1/32            scram-sha-256
hostssl saas_app        saas_owner      127.0.0.1/32            scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256

The hostssl keyword enforces encrypted connections. Placing hostssl rules above general host rules prevents cleartext connections on loopback interfaces.

Keep the local all postgres peer rule unchanged at the top of the file to preserve administrative psql access. Remove rules containing 0.0.0.0/0 in the address column.

Save and exit, then check file rule evaluation order using this SQL query:

sudo systemctl reload postgresql
sudo -u postgres psql -P pager=off -c \
  "SELECT line_number, type, database, user_name, address, auth_method
   FROM pg_hba_file_rules ORDER BY line_number;"

Output line_number |  type   |   database    |   user_name   |  address  |  auth_method
-------------+---------+---------------+---------------+-----------+---------------
         118 | local   | {all}         | {postgres}    |           | peer
         123 | local   | {all}         | {all}         |           | peer
         125 | hostssl | {saas_app}    | {app_service} | 127.0.0.1 | scram-sha-256
         126 | hostssl | {saas_app}    | {saas_owner}  | 127.0.0.1 | scram-sha-256
         127 | host    | {all}         | {all}         | 127.0.0.1 | scram-sha-256
         129 | host    | {all}         | {all}         | ::1       | scram-sha-256
         132 | local   | {replication} | {all}         |           | peer
         133 | host    | {replication} | {all}         | 127.0.0.1 | scram-sha-256
         134 | host    | {replication} | {all}         | ::1       | scram-sha-256
(9 rows)

Confirm your hostssl rules appear above the host all all entry for 127.0.0.1/32.

#Step 7: Restart and verify TLS configuration

Restart PostgreSQL to apply listen_addresses and ssl changes:

sudo systemctl restart postgresql
pg_lsclusters

Confirm Status reads online.

Now verify encrypted database access using psql:

psql "host=127.0.0.1 dbname=saas_app user=saas_owner sslmode=require" -c "\conninfo"

Enter the password for saas_owner when prompted.

Output            Connection Information
      Parameter       |         Value
----------------------+------------------------
 Database             | saas_app
 Client User          | saas_owner
 Host                 | 127.0.0.1
 Server Port          | 5432
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 51322
 SSL Connection       | true
 SSL Library          | OpenSSL
 SSL Protocol         | TLSv1.3
 SSL Key Bits         | 256
 SSL Cipher           | TLS_AES_256_GCM_SHA384
 SSL Compression      | false
 ALPN                 | postgresql
 Superuser            | off
 Hot Standby          | off
(18 rows)

SSL Connection: true value confirms encryption.

Verify the listening addresses:

sudo ss -tlnp | grep 5432

OutputLISTEN 0      400     10.175.44.42:5432      0.0.0.0:*    users:(("postgres",pid=51240,fd=258))
LISTEN 0      400        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=51240,fd=257))
LISTEN 0      400            [::1]:5432         [::]:*    users:(("postgres",pid=51240,fd=256))

Three listening sockets confirm loopback IPv4, loopback IPv6, and private IP bindings.

If you get two instead of three lines, it means your private address did not bind, usually a typo. PostgreSQL binds what it can and starts anyway, so nothing else in this step would have told you:

sudo -u postgres psql -P pager=off -c "SHOW listen_addresses;"

If that returns an address this machine does not have, correct it in the tuning file and restart.

#How to Choose a Multi-Tenant SaaS Architecture

Your architecture choice dictates your backup strategy, query isolation, and scaling operations.

Database per tenant gives the strongest isolation. A bug in one tenant's queries cannot touch another tenant's rows, and you back up or restore each tenant independently. Operational overhead increases with your tenant count. Managing hundreds of databases requires complex migration scripts and individual connection pools.

Schema per tenant offers a middle ground. You separate data logically without provisioning individual databases.

Shared database with a tenant_id column keeps migrations and pooling simple. Isolation then depends on your application filtering by tenant_id on every query, which is the real risk, and row level security closes that gap at the database layer.

A shared database with row level security suits applications with fewer than a few hundred tenants. The rest of this guide implements this model. Move to separate databases when a tenant needs dedicated resources or a contract requires full separation.

#How to Create the Schema and Enforce Tenant Isolation

This section builds your application schema and enables row level security. Row level security prevents a tenant from reading another tenant's rows.

If you are migrating an existing database from a managed service, run Step 1 and skip to the migration section. Your restore brings its own tables, and creating them here first causes an error. Come back for Steps 4 through 6 once the restore finishes.

#Step 1: Install the extensions

Install extensions using the postgres user. The pg_stat_statements extension requires superuser privileges:

sudo -u postgres psql -d saas_app

The -d saas_app matters. Without it you connect to the postgres database and create the extensions in the wrong place, where your application never sees them.

Run these commands at the prompt:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;

The pg_stat_statements extension records query timings. The pgcrypto extension provides hashing and encryption. The pg_trgm extension handles text search. The citext extension enables case-insensitive text comparisons.

If your product uses embeddings or semantic search, pgvector adds a native vector type. It ships as its own package rather than inside the server package, so install it from another terminal first:

sudo apt install -y postgresql-18-pgvector

Then return to the psql prompt and run:

CREATE EXTENSION IF NOT EXISTS vector;

Run this command to confirm your installations:

\dx

OutputList of installed extensions
        Name        | Version | Default version |   Schema   |                              Description
--------------------+---------+-----------------+------------+------------------------------------------------------------------------
 citext             | 1.8     | 1.8             | public     | data type for case-insensitive character strings
 pg_stat_statements | 1.12    | 1.12            | public     | track planning and execution statistics of all SQL statements executed
 pg_trgm            | 1.6     | 1.6             | public     | text similarity measurement and index searching based on trigrams
 pgcrypto           | 1.4     | 1.4             | public     | cryptographic functions
 plpgsql            | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
 vector             | 0.8.6   | 0.8.6           | public     | vector data type and ivfflat and hnsw access methods
(6 rows)

Exit the prompt with \q.

#Step 2: Connect as the owner role

Everything from here runs as saas_owner, not postgres:

psql "host=127.0.0.1 dbname=saas_app user=saas_owner sslmode=require"

Enter the password from the security section.

This role owns the tables you create next, and your application connects as app_service instead. That separation is what makes row level security work in Step 5. Create the tables as the wrong role and the policies apply to nobody.

#Step 3: Create the tables

Run these commands at the prompt:

CREATE TABLE customers (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id int NOT NULL,
  name text NOT NULL,
  email citext NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email)
);

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  tenant_id int NOT NULL,
  customer_id uuid NOT NULL REFERENCES customers(id),
  total_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX orders_tenant_id_idx ON orders (tenant_id, created_at DESC);

PostgreSQL 18 has a native uuidv7() function, so skip the uuid-ossp extension older guides reach for. Fully random UUIDs scatter inserts across a B-tree index and fragment it over time. uuidv7() puts a millisecond timestamp in the leading bits, so values land roughly in insertion order.

One catch: a uuidv7() value in a public URL leaks the row's creation time. Use a separate opaque ID if that matters.

The unique constraint covers (tenant_id, email) rather than email alone. A global constraint would stop two tenants from ever having a customer at the same address, which is wrong in a shared-schema design.

Run this command to confirm table ownership:

\dt

Output             List of tables
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 public | customers | table | saas_owner
 public | orders    | table | saas_owner
(2 rows)

The Owner column shows saas_owner, not postgres and not app_service. If it says something else, drop the tables and go back to Step 2.

#Step 4: Grant the application role access

app_service owns zero tables. Grant only required data access:

GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers, orders TO app_service;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_service;

The ALTER DEFAULT PRIVILEGES statements cover tables saas_owner creates later, so your next migration doesn't quietly produce a table the application cannot read.

Run this command to confirm your grants:

\dp customers

The Access privileges column shows app_service=arwd/saas_owner, meaning append, read, write and delete, granted by saas_owner.

#Step 5: Enable row level security

Enable and force row level security on both tables, then create a policy for each:

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON customers
  USING (tenant_id = current_setting('app.tenant_id', true)::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', true)::int)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

Three details here separate a working policy from one that does nothing.

FORCE ROW LEVEL SECURITY makes the policy apply to the table owner too. ENABLE alone exempts the owner, so every query you run as saas_owner sees all tenants and you conclude the policy is broken when it is only being bypassed.

WITH CHECK covers writes. USING alone filters what a query reads and leaves INSERT unguarded, so a tenant writes rows under any tenant_id it chooses.

The true second argument to current_setting returns NULL instead of raising an error when the variable is unset. Without it, a connection that forgot to set app.tenant_id throws an exception. You want no rows, not a stack trace.

Repeat these three statements for every tenant-scoped table you add later. A table you forget is a table with no isolation at all.

Confirm the policies registered:

\d customers

OutputTable "public.customers"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+----------
 id         | uuid                     |           | not null | uuidv7()
 tenant_id  | integer                  |           | not null |
 name       | text                     |           | not null |
 email      | citext                   |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_tenant_id_email_key" UNIQUE CONSTRAINT, btree (tenant_id, email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
Policies (forced row security enabled):
    POLICY "tenant_isolation"
      USING ((tenant_id = (current_setting('app.tenant_id'::text, true))::integer))
      WITH CHECK ((tenant_id = (current_setting('app.tenant_id'::text, true))::integer))

forced row security enabled in the parentheses is what confirms FORCE applied.

Then check that both USING and WITH CHECK appear. A block showing only USING means writes are unguarded.

Exit the prompt with \q.

#Step 6: Prove isolation works

Now, verify your policies. This script writes a row as one tenant and then reads as another, which is the only test that catches the ownership mistake.

Install the driver:

sudo apt install -y python3-pip
pip install "psycopg[binary]" --break-system-packages

OutputThe following NEW packages will be installed:
  binutils binutils-common binutils-x86-64-linux-gnu build-essential bzip2 cpp cpp-13
  ...
  libtsan2 libubsan1 lto-disabled-list make python3-dev python3-pip python3-wheel
  python3.12-dev zlib1g-dev
0 upgraded, 59 newly installed, 0 to remove and 0 not upgraded.
Need to get 76.1 MB of archives.
After this operation, 270 MB of additional disk space will be used.

OutputDefaulting to user installation because normal site-packages is not writeable
Collecting psycopg[binary]
  Downloading psycopg-3.3.4-py3-none-any.whl.metadata (4.3 kB)
Collecting typing-extensions>=4.6 (from psycopg[binary])
  Downloading typing_extensions-4.16.0-py3-none-any.whl.metadata (3.3 kB)
Collecting psycopg-binary==3.3.4 (from psycopg[binary])
  Downloading psycopg_binary-3.3.4-cp312-cp312-manylinux2014_x86_64.manylinux_2_17_x86_64.whl.metadata (2.7 kB)
Downloading psycopg_binary-3.3.4-cp312-cp312-manylinux2014_x86_64.manylinux_2_17_x86_64.whl (5.2 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 5.2/5.2 MB 219.0 MB/s eta 0:00:00
Downloading typing_extensions-4.16.0-py3-none-any.whl (45 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 45.6/45.6 kB 33.3 MB/s eta 0:00:00
Downloading psycopg-3.3.4-py3-none-any.whl (213 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 213.0/213.0 kB 138.0 MB/s eta 0:00:00
Installing collected packages: typing-extensions, psycopg-binary, psycopg
Successfully installed psycopg-3.3.4 psycopg-binary-3.3.4 typing-extensions-4.16.0

The [binary] extra matters. Plain psycopg has no bundled libpq and fails at import.

Create the script:

nano verify_isolation.py

Add this code, substituting your app_service password:

import uuid
import psycopg

conn = psycopg.connect(
    host="127.0.0.1",
    port=5432,
    dbname="saas_app",
    user="app_service",
    password="app_service_password",
    sslmode="require",
)

conn.autocommit = True

email = f"billing+{uuid.uuid4().hex[:8]}@acme.example"

with conn.cursor() as cur:
    cur.execute("BEGIN;")
    cur.execute("SET LOCAL app.tenant_id = '1';")
    cur.execute(
        "INSERT INTO customers (tenant_id, name, email) "
        "VALUES (1, 'Acme Corp', %s) RETURNING id;",
        (email,),
    )
    print("inserted:", cur.fetchone()[0])
    cur.execute("COMMIT;")

    cur.execute("BEGIN;")
    cur.execute("SET LOCAL app.tenant_id = '1';")
    cur.execute("SELECT count(*) FROM customers;")
    print("rows visible to tenant 1:", cur.fetchone()[0])
    cur.execute("COMMIT;")

    cur.execute("BEGIN;")
    cur.execute("SET LOCAL app.tenant_id = '2';")
    cur.execute("SELECT count(*) FROM customers;")
    print("rows visible to tenant 2:", cur.fetchone()[0])
    cur.execute("COMMIT;")

conn.close()

conn.autocommit = True stops psycopg opening its own transaction on the first execute. Without it, your explicit BEGIN lands inside one and PostgreSQL warns on every block.

Run the script:

python3 verify_isolation.py

Outputinserted: 01a00e91-9cc1-7414-ae00-755e2c02d375
rows visible to tenant 1: 1
rows visible to tenant 2: 0

The last line confirms your policy. Zero means the policy applies to your application role and tenants are isolated. A one means every tenant reads every other tenant's data, and the cause is almost always table ownership. Go back to Step 3 and check \dt.

The middle line only needs to be non-zero. It climbs by one each time you run the script, since every run inserts another row. A zero there means the policy is filtering out rows the tenant should see, which usually means app.tenant_id is not being set inside the transaction.

Note the pattern the script uses: SET LOCAL inside a transaction, never plain SET. A plain SET persists for the life of the connection, and once PgBouncer starts reusing connections in the next section, that value leaks into the next tenant's queries. SET LOCAL clears on commit.

#How to Pool Connections with PgBouncer

Every PostgreSQL connection is a separate operating system process holding its own memory, so a SaaS application exhausts max_connections quickly if every request opens its own. Raising that limit into the thousands looks like the fix, but a server switching between thousands of mostly idle processes burns CPU doing nothing useful.

PgBouncer keeps a small pool of real backend connections busy while thousands of application connections share them. Your application connects to it on port 6432, and PgBouncer connects to PostgreSQL on 5432 over loopback, so this section configures TLS on both hops.

#Step 1: Install PgBouncer

Install the package:

sudo apt install -y pgbouncer

OutputThe following NEW packages will be installed:
  libcares2 pgbouncer
0 upgraded, 2 newly installed, 0 to remove and 0 not upgraded.
Need to get 308 kB of archives.
...
Setting up pgbouncer (1.25.2-1.pgdg24.04+1) ...

#Step 2: Generate a certificate for PgBouncer

PgBouncer needs its own certificate, since it is a separate service listening on a different port. Generate the certificate and key:

sudo openssl req -new -x509 -days 365 -nodes \
  -out /etc/pgbouncer/server.crt \
  -keyout /etc/pgbouncer/server.key \
  -subj "/CN=pgbouncer.internal" 2>/dev/null

On Ubuntu, PgBouncer runs as the postgres user, so set matching ownership and permissions on the files:

sudo chown postgres:postgres /etc/pgbouncer/server.crt /etc/pgbouncer/server.key
sudo chmod 600 /etc/pgbouncer/server.key
ls -l /etc/pgbouncer/server.*

You want postgres postgres on both files and -rw------- on the key. PgBouncer refuses to start if the key is readable by anyone else.

#Step 3: Build the authentication file

PgBouncer verifies passwords itself before opening a connection to PostgreSQL, so it needs a copy of the SCRAM secrets. Generate the authentication file:

sudo -u postgres psql -tAc \
  "SELECT usename, passwd FROM pg_shadow WHERE usename IN ('app_service','saas_owner');" | \
  awk -F'|' '{print "\"" $1 "\" \"" $2 "\""}' | sudo tee /etc/pgbouncer/userlist.txt

The output displays quoted usernames paired with quoted SCRAM-SHA-256 string hashes:

Output"saas_owner" "SCRAM-SHA-256$4096:CD1CsOKI/7K5MqlxgDYpLg==$Ito/UAmIqjufW7+lz2CUq3jPB+sk8GipZrDGZ4cG3ag=:nK2H9FUPw2X4qBBzxHxuluNLEzVZt4eXO88zBFnAMZE="
"app_service" "SCRAM-SHA-256$4096:CUjMuDnkCZ68g4W2Xwb4mQ==$/zv3i4nXtmIL9/wPPn1/gJzZpHsr4NmjXkAcnET6vx8=:4ndnmVrDiXRROoecSFbicJv9fdRvFDRu7w7xTvdKKgc="

Secure the file:

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

This file holds copies of your password hashes. Rotate a password in PostgreSQL and PgBouncer keeps the old one, rejecting clients that authenticate correctly. Re-run the command above after any password change.

#Step 4: Write the configuration

The packaged config runs to 200 lines and you replace all of it, so empty the file first rather than deleting line by line in the editor:

sudo truncate -s 0 /etc/pgbouncer/pgbouncer.ini
sudo nano /etc/pgbouncer/pgbouncer.ini

Use plain sudo here rather than sudo -u postgres, since /etc/pgbouncer is owned by root.

Paste this configuration block into the file, substituting your private IP from the security section:

[databases]
saas_app = host=127.0.0.1 port=5432 dbname=saas_app

[pgbouncer]
listen_addr = 127.0.0.1,<your_private_ip>
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key

server_tls_sslmode = require

admin_users = saas_owner
stats_users = saas_owner

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

Save and exit, then confirm file ownership:

ls -l /etc/pgbouncer/pgbouncer.ini

Confirm postgres ownership and -rw-r----- permissions. PgBouncer fails to start if it cannot read its configuration.

The [databases] line carries no user= parameter. That's deliberate. SCRAM secrets pass through to PostgreSQL only when the database definition leaves the user unspecified. Pin a user there and PgBouncer cannot complete server-side authentication, failing with an error that points nowhere useful. The PgBouncer configuration reference documents the constraint.

client_tls_sslmode = require is what makes TLS work on the client hop. It defaults to disable, so without these three lines an application using sslmode=require against port 6432 fails with a protocol error even though PostgreSQL itself has TLS on. server_tls_sslmode = require covers the second hop, PgBouncer to PostgreSQL.

pool_mode = transaction returns a connection to the pool when a transaction commits rather than holding it for the whole client session. Most SaaS requests are one or two short transactions, so this multiplies your effective capacity. Session mode is only necessary if your application depends on advisory locks or session-level state.

default_pool_size = 25 against max_client_conn = 1000 is the ratio doing the work: a thousand application connections sharing twenty-five real backends.

If your application uses an ORM, check how it handles prepared statements before assuming transaction mode works. Prisma caches them per connection, and transaction mode hands a client a different physical connection between statements, which breaks the cache in confusing ways. Prisma's fix is ?pgbouncer=true on the connection string. Check your ORM's docs for the equivalent.

#Step 5: Start the service

Port 6432 is already open to your application subnet from the security section, so nothing further is needed on the firewall. Start the service and check the status:

sudo systemctl enable --now pgbouncer
sudo systemctl status pgbouncer --no-pager

OutputSynchronizing state of pgbouncer.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable pgbouncer

Output● pgbouncer.service - connection pooler for PostgreSQL
     Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; enabled; preset: enabled)
     Active: active (running) since Fri 2026-07-31 08:20:53 UTC; 24s ago
       Docs: man:pgbouncer(1)
             https://www.pgbouncer.org/
   Main PID: 56589 (pgbouncer)
      Tasks: 3 (limit: 114442)
     Memory: 3.7M (peak: 3.7M)
        CPU: 24ms
     CGroup: /system.slice/pgbouncer.service
             └─56589 /usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.ini

Jul 31 08:20:53 pg1 systemd[1]: Starting pgbouncer.service - connection pooler for Postg…SQL...
Jul 31 08:20:53 pg1 pgbouncer[56589]: kernel file descriptor limit: 1024 (hard: 524288);…: 1087
Jul 31 08:20:53 pg1 pgbouncer[56589]: listening on 127.0.0.1:6432
Jul 31 08:20:53 pg1 pgbouncer[56589]: listening on 10.175.44.42:6432
Jul 31 08:20:53 pg1 pgbouncer[56589]: listening on unix:/tmp/.s.PGSQL.6432
Jul 31 08:20:53 pg1 pgbouncer[56589]: process up: PgBouncer 1.25.2, libevent 2.1.12-stab…n 2024
Jul 31 08:20:53 pg1 systemd[1]: Started pgbouncer.service - connection pooler for PostgreSQL.
Hint: Some lines were ellipsized, use -l to show in full.

Look for Active: active (running). If the service failed, check the log file for configuration errors:

sudo tail -20 /var/log/postgresql/pgbouncer.log

Now check one line in the startup log, because it reports a limit that silently overrides your config:

sudo journalctl -u pgbouncer -n 20 --no-pager -l | grep "file descriptor"

OutputJul 31 08:20:53 pg1 pgbouncer[56589]: kernel file descriptor limit: 1024 (hard: 524288); max_client_conn: 1000, max expected fd use: 1087

Read the two numbers against each other. PgBouncer needs 1087 file descriptors for the 1000 clients you configured, and the kernel caps the process at 1024. It serves fewer clients than your config claims, silently, and connections start being refused under load.

Raise the limit with a systemd override, so a package update cannot revert it:

sudo systemctl edit pgbouncer

Add this in the editable region at the top of the file:

[Service]
LimitNOFILE=8192

Save the file. Reload systemd and restart PgBouncer:

sudo systemctl daemon-reload
sudo systemctl restart pgbouncer
sudo journalctl -u pgbouncer -n 20 --no-pager -l | grep "file descriptor"

The limit now clears the requirement:

OutputJul 31 08:30:16 pg1 pgbouncer[56897]: kernel file descriptor limit: 8192 (hard: 8192); max_client_conn: 1000, max expected fd use: 1087

The -l flag stops systemd truncating the line at your terminal width, which is where the max expected fd use figure sits. Recheck this entry if you raise max_client_conn.

#Step 6: Verify both hops

First, connect through PgBouncer as the application role:

psql "host=127.0.0.1 port=6432 dbname=saas_app user=app_service sslmode=require" -c "SELECT 1;"

Output ?column?
----------
        1
(1 row)

A single row confirms PgBouncer accepted your TLS connection, authenticated your credentials against userlist.txt, opened a TLS connection to PostgreSQL, and executed your query.

PgBouncer exposes an admin database of its own. Use it to confirm pooling is happening:

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=saas_owner sslmode=require" \
  -P pager=off -c "SHOW POOLS;"

Output database  |    user     | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_canceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us |  pool_mode  | load_balance_hosts
-----------+-------------+-----------+------------+----------------------+-----------------------+-----------+------------------+-------------------+---------+---------+-----------+----------+---------+------------+-------------+--------------------
 pgbouncer | pgbouncer   |         1 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       0 |       0 |         0 |        0 |       0 |          0 | statement   |
 saas_app  | app_service |         0 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       0 |       1 |         0 |        0 |       0 |          0 | transaction |
(2 rows)

SHOW POOLS only shows PgBouncer's view. Confirm the same thing from PostgreSQL's side:

sudo -u postgres psql -P pager=off -c \
  "SELECT count(*) FROM pg_stat_activity WHERE datname = 'saas_app';"

Output count
-------
     0
(1 row)

On an idle server this returns 0, since PgBouncer opens backend connections on demand rather than holding 25 open from the start.

What matters is the ceiling. Under load this count rises to default_pool_size and stops there, however many clients connect through 6432. A number climbing past 25 means connections are bypassing the pooler, and the usual cause is an application still pointed at 5432.

Check your tenant isolation still holds through the pooler. Edit the verification script from the schema section to use port 6432 instead of 5432, then run it again:

sed -i 's/port=5432/port=6432/' verify_isolation.py
python3 verify_isolation.py

Outputinserted: 01a00ee9-7fd9-7423-9f24-323398f2c345
rows visible to tenant 1: 2
rows visible to tenant 2: 0

This is the test that matters most in this section. Transaction pooling reuses physical connections between tenants, and SET LOCAL clearing on commit is what stops one tenant's app.tenant_id leaking into the next tenant's query. A count of 1 on that last line means the leak is real.

#How to Migrate from RDS to Self-Hosted PostgreSQL

Skip to backups if you're building fresh. This section is for moving an existing database off RDS, Cloud SQL or a managed Postgres plan.

Start by taking inventory on the source. Version mismatches and missing extensions cause most failed restores:

SELECT version();
SELECT extname, extversion FROM pg_extension;
SELECT rolname FROM pg_roles WHERE rolcanlogin;

Install any extension the source uses before restoring. Managed providers ship a curated list, and a few of their offerings have no direct equivalent in stock PostgreSQL.

Check your pg_restore version against the source server too. A pg_restore older than the server that produced the dump refuses before processing a single table, which matters when leaving a managed service running an older major version:

pg_restore --version

Roles are the other thing that does not travel. They live at cluster level rather than inside a database, so pg_dump does not carry them. A restore can finish cleanly and leave your application unable to connect because the role its tables belong to never existed on the target.

The approach this guide takes is --no-owner --no-privileges on the dump, which strips ownership so you can assign it to the roles you already created. That suits this setup, where the target has saas_owner and app_service waiting.

For a database with many application roles, dump them instead and load them first:

pg_dumpall -h <source_host> -U <source_user> --globals-only -f /var/tmp/pgmigrate/globals.sql
sudo -u postgres psql -f /var/tmp/pgmigrate/globals.sql

That file contains every CREATE ROLE and CREATE TABLESPACE on the source cluster, so read it before running it. Producing it requires superuser on the source, which some managed providers do not grant.

#Option A: dump and restore

Use this when you can afford a maintenance window. Under 100 GB it usually runs in under an hour on a private link.

Write the dump somewhere the postgres user can reach. Ubuntu 24.04 ships DIR_MODE=0750 in /etc/adduser.conf, so a dump left in /home/dbadmin fails at restore time with a permission error that looks like a pg_restore problem and isn't one. Create a staging directory instead of using /tmp, since a full production dump sitting in a world-readable directory is its own problem:

sudo install -d -o dbadmin -g postgres -m 770 /var/tmp/pgmigrate
ls -ld /var/tmp/pgmigrate

You want drwxrwx---. Group write matters here, because pg_dump runs as your own user against a remote source and as postgres against a local one. At 750 the second case fails with could not open output file ... Permission denied.

Dump the schema and data separately, so you catch schema errors before waiting on a full data load:

pg_dump -h <source_host> -U <source_user> -d <source_db> \
  --schema-only --no-owner --no-privileges -f /var/tmp/pgmigrate/schema.sql

pg_dump -h <source_host> -U <source_user> -d <source_db> \
  --data-only --format=custom -f /var/tmp/pgmigrate/data.dump

Load the schema as the owner role, then restore the data as a superuser over the local socket:

psql "host=127.0.0.1 dbname=saas_app user=saas_owner sslmode=require" \
  -f /var/tmp/pgmigrate/schema.sql

sudo -u postgres pg_restore -d saas_app --jobs=4 --disable-triggers \
  /var/tmp/pgmigrate/data.dump

--jobs=4 restores tables in parallel. Match it to your core count. --disable-triggers stops foreign key checks from firing during the load, which cuts restore time on a large dataset.

Read the output rather than the exit code. pg_restore reports failures as it goes and still exits normally, so a script checking only the exit status treats a failed restore as a success.

The data load runs as postgres for two reasons. --disable-triggers issues ALTER TABLE ... DISABLE TRIGGER ALL, which requires superuser, so the command fails outright as saas_owner. And if you enabled row level security before loading, saas_owner is subject to it under FORCE ROW LEVEL SECURITY: every insert evaluates the WITH CHECK clause against an unset app.tenant_id and gets rejected. Superusers bypass RLS, so the load succeeds. If you'd rather avoid superuser entirely, drop --disable-triggers and add the RLS policies after the restore rather than before.

#Option B: logical replication

Use this when downtime needs to stay under a minute. The source streams changes to your new server while both run, and you cut over when replication lag reaches zero.

On the source, enable logical replication. On RDS this means setting rds.logical_replication = 1 in the parameter group and rebooting. On stock PostgreSQL, set wal_level = logical and restart. Then publish:

CREATE PUBLICATION migration_pub FOR ALL TABLES;

Load the schema onto your new server first, since logical replication copies rows and not DDL:

sudo install -d -o dbadmin -g postgres -m 770 /var/tmp/pgmigrate

pg_dump -h <source_host> -U <source_user> -d <source_db> \
  --schema-only --no-owner --no-privileges -f /var/tmp/pgmigrate/schema.sql

psql "host=127.0.0.1 dbname=saas_app user=saas_owner sslmode=require" \
  -f /var/tmp/pgmigrate/schema.sql

Then subscribe:

CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=<source_host> dbname=<source_db> user=<source_user> password=<secret> sslmode=require'
  PUBLICATION migration_pub;

Watch the lag until it settles:

SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;

#Verify before you cut over

A clean exit code is not verification. Compare row counts on both servers:

SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;

Sequences behave differently depending on which option you took, and this is worth getting right because the failure mode is an insert colliding with an existing primary key on the first write after cutover.

A dump and restore carries them. pg_dump --data-only includes setval calls and pg_restore applies them, so nothing further is needed. Confirm rather than assume:

SELECT last_value FROM <your_sequence_name>;

On a table holding 500 rows that reads 500, and the next insert returns 501. The customers and orders tables built earlier use uuidv7() and own no sequence, so this applies only to tables you brought across with a serial or identity column.

Logical replication does not carry them. It replicates row changes, and a sequence advancing is not a row change. Find every sequence on the target:

SELECT
  s.relname AS sequence_name,
  t.relname AS table_name,
  a.attname AS column_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid AND d.classid = 'pg_class'::regclass
JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE s.relkind = 'S';

Then reset each against its table:

SELECT setval(
  pg_get_serial_sequence('<table_name>', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM <table_name>)
);

Whichever option you took, prove it with a write rather than a query:

INSERT INTO <table_name> (...) VALUES (...) RETURNING id;

An id one higher than your row count means the sequence is correct. A duplicate key error means it is not, and no amount of matching row counts would have told you.

Then stop writes at the source, confirm lag is zero, drop the subscription, point your application at PgBouncer on the new server, and keep the old instance running read-only for a few days as a rollback path.

Delete the staging directory once the cutover holds. /var/tmp survives reboots, and a full copy of your production data has no reason to sit there indefinitely:

sudo rm -rf /var/tmp/pgmigrate

#How to Set Up Backups You Can Restore

A backup nobody has restored is a file you hope works. This section sets up pgBackRest for continuous archiving and point-in-time recovery, then restores from it, because the restore is the only part that proves anything.

pgBackRest handles two things together: periodic base backups of the whole cluster, and continuous archiving of the write-ahead log between them. The pair is what lets you recover to any moment rather than to whenever the last backup ran.

#Step 1: Install pgBackRest and create the repository

sudo apt install -y pgbackrest
sudo mkdir -p /etc/pgbackrest /var/backups/pgbackrest
sudo chown postgres:postgres /var/backups/pgbackrest

OutputThe following NEW packages will be installed:
  libssh2-1t64 pgbackrest
0 upgraded, 2 newly installed, 0 to remove and 0 not upgraded.
Need to get 688 kB of archives.
...
Setting up pgbackrest (2.59.0-1.pgdg24.04+1) ...

The package installs the binary but creates neither directory, so both need making by hand. Confirm the install:

pgbackrest version

OutputpgBackRest 2.59.0

Check your available disk space. The repository holds full backups plus every WAL segment between them:

df -h /var/backups
sudo du -sh /var/lib/postgresql/18/main

The repository holds up to four full backups plus the WAL between them, compressed. Allow several times your data directory size to start with, then watch it for a week and adjust.

#Step 2: Configure the stanza

A stanza is pgBackRest's name for one cluster and its repository. Open the configuration file:

sudo nano /etc/pgbackrest/pgbackrest.conf

Add these configurations:

[saas_app]
pg1-path=/var/lib/postgresql/18/main

[global]
repo1-path=/var/backups/pgbackrest
repo1-retention-full=4
compress-type=zst
start-fast=y
process-max=4
log-level-console=info

[global:archive-push]
compress-level=3

repo1-retention-full=4 keeps four full backups and expires older ones automatically, along with the WAL they needed.

compress-type=zst cuts repository size at lower CPU cost than the gzip default. The lighter compress-level=3 for archive-push matters because WAL archiving runs continuously in the background, and you want it cheap.

start-fast=y triggers an immediate checkpoint instead of waiting for the next scheduled one, so backups begin promptly rather than idling.

process-max=4 runs four parallel workers. Raise it toward your core count for large databases.

Set ownership so pgBackRest can read it as postgres:

sudo chown root:postgres /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf

#Step 3: Enable WAL archiving

Edit your tuning configuration file:

sudo -u postgres nano /etc/postgresql/18/main/conf.d/10-tuning.conf

Append these lines:

# Archiving
archive_mode = on
archive_command = 'pgbackrest --stanza=saas_app archive-push %p'

With archive_mode = on, PostgreSQL retains every WAL segment until archive_command reports success. If that command fails, WAL accumulates in pg_wal and keeps accumulating until the disk fills and the database stops. The check in the next step exists to catch exactly that before it matters.

Restart the service to apply changes:

sudo systemctl restart postgresql
pg_lsclusters

#Step 4: Initialize and verify

Create and verify the stanza:

sudo -u postgres pgbackrest --stanza=saas_app stanza-create
sudo -u postgres pgbackrest --stanza=saas_app check

Output2026-07-31 09:53:20.685 P00   INFO: stanza-create command begin 2.59.0: --exec-id=57973-288da222 --log-level-console=info --pg1-path=/var/lib/postgresql/18/main --repo1-path=/var/backups/pgbackrest --stanza=saas_app
2026-07-31 09:53:20.690 P00   INFO: stanza-create for stanza 'saas_app' on repo1
2026-07-31 09:53:20.691 P00   INFO: stanza-create command end: completed successfully (8ms)

Output2026-07-31 09:53:43.781 P00   INFO: check command begin 2.59.0: --exec-id=57978-1a18a106 --log-level-console=info --pg1-path=/var/lib/postgresql/18/main --repo1-path=/var/backups/pgbackrest --stanza=saas_app
2026-07-31 09:53:43.786 P00   INFO: check repo1 configuration (primary)
2026-07-31 09:53:43.787 P00   INFO: check repo1 archive for WAL (primary)
2026-07-31 09:53:43.887 P00   INFO: WAL segment 000000010000000000000001 successfully archived to '/var/backups/pgbackrest/archive/saas_app/18-1/0000000100000000/000000010000000000000001-f752761b38221e40c6607e2850ada5571f18665e.zst' on repo1
2026-07-31 09:53:43.887 P00   INFO: check command end: completed successfully (107ms)

The check command forces a WAL switch and confirms the segment reaches the repository. A successful output displays an archived path.

That archived path is the proof. It means archive_command ran, pgBackRest received the segment, compressed it and wrote it where a restore will look for it. A failure here means WAL is piling up in pg_wal with nowhere to go, and fixing it now costs nothing.

Run the first backup:

sudo -u postgres pgbackrest --stanza=saas_app --type=full backup

Output2026-07-31 09:58:22.868 P00   INFO: backup command begin 2.59.0: --compress-type=zst --exec-id=58003-0a87be13 --log-level-console=info --pg1-path=/var/lib/postgresql/18/main --process-max=4 --repo1-path=/var/backups/pgbackrest --repo1-retention-full=4 --stanza=saas_app --start-fast --type=full
2026-07-31 09:58:22.873 P00   INFO: execute backup start: backup begins after the requested immediate checkpoint completes
2026-07-31 09:58:22.901 P00   INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028
2026-07-31 09:58:22.901 P00   INFO: check archive for prior segment 000000010000000000000002
2026-07-31 09:58:24.609 P00   INFO: execute backup stop and wait for all WAL segments to archive
2026-07-31 09:58:24.620 P00   INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000158
2026-07-31 09:58:24.621 P00   INFO: check archive for segment(s) 000000010000000000000003:000000010000000000000003
2026-07-31 09:58:24.725 P00   INFO: new backup label = 20260731-095822F
2026-07-31 09:58:24.738 P00   INFO: full backup size = 30.7MB, file total = 1273
2026-07-31 09:58:24.738 P00   INFO: backup command end: completed successfully (1871ms)
2026-07-31 09:58:24.738 P00   INFO: expire command begin 2.59.0: --exec-id=58003-0a87be13 --log-level-console=info --repo1-path=/var/backups/pgbackrest --repo1-retention-full=4 --stanza=saas_app
2026-07-31 09:58:24.738 P00   INFO: expire command end: completed successfully (0ms)

Verify the backup status:

sudo -u postgres pgbackrest --stanza=saas_app info

Outputstanza: saas_app
    status: ok
    cipher: none

    db (current)
        wal archive min/max (18): 000000010000000000000001/000000010000000000000003

        full backup: 20260731-095822F
            timestamp start/stop: 2026-07-31 09:58:22+00 / 2026-07-31 09:58:24+00
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 30.7MB, database backup size: 30.7MB
            repo1: backup set size: 3.6MB, backup size: 3.6MB

Confirm the status reads ok. Note the size difference too: 30.7MB of database occupying 3.6MB in the repository, which is compress-type=zst earning its place.

#Step 5: Schedule backups

Schedule a weekly full backup, with incrementals on the other six days:

sudo nano /etc/cron.d/pgbackrest

Add these lines:

0 2 * * 0 postgres pgbackrest --stanza=saas_app --type=full backup
0 2 * * 1-6 postgres pgbackrest --stanza=saas_app --type=incr backup

Set permissions:

sudo chmod 644 /etc/cron.d/pgbackrest

Files in /etc/cron.d need a user field between the schedule and the command, unlike a personal crontab. Test that yours fires by scheduling an incremental a few minutes ahead of your current time:

date
sudo nano /etc/cron.d/pgbackrest

Add the temporary schedule as a third line:

16 10 * * * postgres pgbackrest --stanza=saas_app --type=incr backup

Wait for the scheduled time and check the status:

sudo -u postgres pgbackrest --stanza=saas_app info

You'll see the incremental alongside the full:

Outputstanza: saas_app
    status: ok
    cipher: none

    db (current)
        wal archive min/max (18): 000000010000000000000001/000000010000000000000005

        full backup: 20260731-095822F
            timestamp start/stop: 2026-07-31 09:58:22+00 / 2026-07-31 09:58:24+00
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 30.7MB, database backup size: 30.7MB
            repo1: backup set size: 3.6MB, backup size: 3.6MB

        incr backup: 20260731-095822F_20260731-101601I
            timestamp start/stop: 2026-07-31 10:16:01+00 / 2026-07-31 10:16:02+00
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 30.7MB, database backup size: 8.3KB
            repo1: backup set size: 3.6MB, backup size: 366B
            backup reference total: 1 full

The size columns show why incrementals run daily without strain: the database is still 30.7MB but only 8.3KB changed, compressing to 366 bytes. And the label 20260731-095822F_20260731-101601I names the full it builds on, with backup reference total stating the dependency outright. Restoring an incremental needs its full still present, which is what repo1-retention-full=4 protects.

Use pgbackrest info for this rather than the cron log. Ubuntu does not record command lines for /etc/cron.d jobs in the journal, so an empty journalctl -u cron | grep pgbackrest tells you nothing either way.

Remove the temporary cron line after the verification.

#Step 6: Copy the repository offsite

A local repository protects you from a dropped table, a bad migration or a corrupted file. It does not protect you from losing the server, since the backups sit on the same disk as the database.

Configure a second repository on S3, Azure or GCS storage using a repo2 block, then run backups against both. The pgBackRest documentation covers the settings for each provider.

#Step 7: Restore to prove it works

Restoring onto a second server is what a real recovery looks like. The repository has to be reachable from another machine, and the restored cluster has to start on hardware that never ran the original.

You need a second Ubuntu 24.04 server with the storage and kernel preparation from earlier in this guide, and PostgreSQL 18 installed and stopped. Do not use a live standby replica, since the restore deletes its data directory and you would be giving up failover to test your backups.

Everything below runs on that server unless marked otherwise.

Give the two servers SSH access as postgres

pgBackRest reaches the repository over SSH as the postgres user, and it needs keys in both directions. This is separate from any SSH access you have as your own account.

On the primary server:

sudo -u postgres ssh-keygen -t ed25519 -N "" -f /var/lib/postgresql/.ssh/id_ed25519
sudo -u postgres cat /var/lib/postgresql/.ssh/id_ed25519.pub

On the second server, create the directory and paste that key in:

sudo -u postgres mkdir -p /var/lib/postgresql/.ssh
sudo -u postgres chmod 700 /var/lib/postgresql/.ssh
sudo -u postgres nano /var/lib/postgresql/.ssh/authorized_keys
sudo -u postgres chmod 600 /var/lib/postgresql/.ssh/authorized_keys

Then do the same in reverse. Generate a key on the second server and authorize it on the primary, since the restore reads from the primary and needs that direction working.

Test both ways. From the primaryserver, run:

sudo -u postgres ssh -o StrictHostKeyChecking=accept-new postgres@<restore_server_ip> hostname

From the second server:

sudo -u postgres ssh -o StrictHostKeyChecking=accept-new postgres@<primary_server_private_ip> hostname

Each should print the other machine's hostname.

OutputWarning: Permanently added '10.175.44.42' (ED25519) to the list of known hosts.
pg1

Point the second server at the repository

Install pgBackRest at the same version as the primary. Mismatched versions between a repository host and its client do not work:

sudo apt install -y pgbackrest
pgbackrest version

Then write a config that points across the network rather than at a local repository:

sudo mkdir -p /etc/pgbackrest
sudo nano /etc/pgbackrest/pgbackrest.conf

Add these lines, substituting the primary server private IP:

[saas_app]
pg1-path=/var/lib/postgresql/18/main

[global]
repo1-host=<primary_private_ip>
repo1-host-user=postgres
log-level-console=info

Then set permissions:

sudo chown root:postgres /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf

Confirm it can read the repository:

sudo -u postgres pgbackrest --stanza=saas_app info

Outputstanza: saas_app
    status: ok
    cipher: none

    db (current)
        wal archive min/max (18): 000000010000000000000001/000000010000000000000005

        full backup: 20260731-095822F
            timestamp start/stop: 2026-07-31 09:58:22+00 / 2026-07-31 09:58:24+00
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 30.7MB, database backup size: 30.7MB
            repo1: backup set size: 3.6MB, backup size: 3.6MB

        incr backup: 20260731-095822F_20260731-101601I
            timestamp start/stop: 2026-07-31 10:16:01+00 / 2026-07-31 10:16:02+00
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 30.7MB, database backup size: 8.3KB
            repo1: backup set size: 3.6MB, backup size: 366B
            backup reference total: 1 full

The backups you took on the primary appear here, read over SSH. If this fails, the problem is SSH or the version, not the restore.

Clear the data directory

The restore requires an empty target directory. Remove and recreate it rather than emptying it in place:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/18/main
sudo -u postgres mkdir -p /var/lib/postgresql/18/main
sudo chmod 700 /var/lib/postgresql/18/main
sudo ls -A /var/lib/postgresql/18/main

Do not use a wildcard deletion. Shell expansion runs as your user, so the glob matches nothing, rm reports success and the directory stays full.

restore --delta is the alternative. It overwrites in place and needs no clearing, which is what you want when recovering onto a server that already holds a damaged copy.

Restore

Run the restore command:

sudo -u postgres pgbackrest --stanza=saas_app restore

Output2026-07-31 11:03:39.415 P00   INFO: restore command begin 2.59.0: --exec-id=56959-6b15ab70 --log-level-console=info --pg1-path=/var/lib/postgresql/18/main --repo1-host=10.175.44.42 --repo1-host-user=postgres --stanza=saas_app
2026-07-31 11:03:39.977 P00   INFO: repo1: restore backup set 20260731-095822F_20260731-101601I, recovery will start at 2026-07-31 10:16:01
2026-07-31 11:03:40.995 P00   INFO: write updated /var/lib/postgresql/18/main/postgresql.auto.conf
2026-07-31 11:03:40.996 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2026-07-31 11:03:40.996 P00   INFO: restore size = 30.7MB, file total = 1273
2026-07-31 11:03:40.997 P00   INFO: restore command end: completed successfully (1583ms)

Transfer the configuration

Do not start the server yet. A pgBackRest restore copies the data directory, but your configuration lives in /etc/postgresql/18/main, outside it. The restored cluster would come up on packaged defaults while its WAL expects the primary's settings.

Copy the tuning file from the primary server:

sudo cp /etc/postgresql/18/main/conf.d/10-tuning.conf /tmp/
sudo chmod 644 /tmp/10-tuning.conf
scp /tmp/10-tuning.conf <your_user>@<restore_server_ip>:/tmp/

On the second server:

sudo cp /tmp/10-tuning.conf /etc/postgresql/18/main/conf.d/
sudo chown postgres:postgres /etc/postgresql/18/main/conf.d/10-tuning.conf
sudo chmod 640 /etc/postgresql/18/main/conf.d/10-tuning.conf
sudo -u postgres nano /etc/postgresql/18/main/conf.d/10-tuning.conf

Update listen_addresses to the private IP of the second server. The primary's address does not exist here, and PostgreSQL binds what it can without complaint, so this one fails quietly.

Delete archive_mode and archive_command to stop this cluster pushing WAL into the primary's repository.

Keep every max_ setting unchanged. Recovery requires shared state allocations equal to or greater than the primary, and a lower value stops the server starting with recovery aborted because of insufficient parameter settings.

Generate a certificate

Your tuning file has ssl = on with paths to a certificate that only exists on the primary server, so the server fails to start without one here. Generate one here:

sudo openssl req -new -x509 -days 365 -nodes \
  -out /etc/postgresql/18/main/server.crt \
  -keyout /etc/postgresql/18/main/server.key \
  -subj "/CN=db-standby.internal" 2>/dev/null

sudo chown postgres:postgres /etc/postgresql/18/main/server.crt /etc/postgresql/18/main/server.key
sudo chmod 600 /etc/postgresql/18/main/server.key

Then start the service and verify data:

sudo systemctl start postgresql
pg_lsclusters

OutputVer Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Status reads online. Confirm the recovery completed:

sudo tail -10 /var/log/postgresql/postgresql-18-main.log
sudo -u postgres psql -P pager=off -c "SELECT pg_is_in_recovery();"

Output2026-07-31 13:13:56.049 P00   INFO: unable to find 00000002.history in the archive
2026-07-31 13:13:56.149 P00   INFO: archive-get command end: completed successfully (299ms)
2026-07-31 13:13:56.150 UTC [59842] LOG:  selected new timeline ID: 2
2026-07-31 13:13:56.166 P00   INFO: archive-get command begin 2.59.0: [00000001.history, pg_wal/RECOVERYHISTORY] --exec-id=59866-8ee9c617 --log-level-console=info --pg1-path=/var/lib/postgresql/18/main --repo1-host=10.175.44.42 --repo1-host-user=postgres --stanza=saas_app
2026-07-31 13:13:56.376 P00   INFO: unable to find 00000001.history in the archive
2026-07-31 13:13:56.476 P00   INFO: archive-get command end: completed successfully (311ms)
2026-07-31 13:13:56.478 UTC [59842] LOG:  archive recovery complete
2026-07-31 13:13:56.478 UTC [59840] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-07-31 13:13:56.500 UTC [59840] LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 1 recycled; write=0.021 s, sync=0.001 s, total=0.023 s; sync files=2, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16384 kB; lsn=0/6000028, redo lsn=0/6000028
2026-07-31 13:13:56.502 UTC [59839] LOG:  database system is ready to accept connections

Output pg_is_in_recovery
-------------------
 f
(1 row)

You want archive recovery complete in the log and f from the query. f means recovery finished and this is a normal read-write cluster. t means it is still waiting for WAL.

Then the test itself:

sudo -u postgres psql -d saas_app -P pager=off -c "SELECT count(*) FROM customers;"

Output count
-------
     2
(1 row)

The count matches what the primary held when the backup ran.

Record the restore time

Time this when you run it against real data. A small database restores in seconds; the duration of a full-sized restore is your actual recovery time, and you want that number before an incident rather than during one.

#How to Add a Standby for High Availability

One server is one point of failure. Streaming replication gives you a second machine holding a continuously updated copy, ready to promote when the primary stops answering.

This section uses physical replication, which copies the entire cluster block by block. That is what a disaster recovery standby needs. Logical replication copies specific tables at row level, which suits cross-version upgrades and migrations rather than a complete standby.

Your second server requires Ubuntu 24.04, the storage and kernel preparation from earlier, and PostgreSQL 18. Do not apply the configuration, security or schema sections to it. The base backup delivers all of that.

#Step 1: Open the primary server to the standby

The standby needs two rules on the primary server. Open the port first:

sudo ufw allow from <standby_private_ip> to any port 5432

Then create the replication role. Generate a secure password and save it, since the base backup prompts for it:

openssl rand -base64 24
sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'your_generated_password';"

REPLICATION is a role attribute rather than a grant. It permits streaming connections only, so this role cannot read your tables.

Add the matching rule in pg_hba.conf alongside the two hostssl lines from the security section:

sudo nano /etc/postgresql/18/main/pg_hba.conf

hostssl replication     replicator      <standby_private_ip>/32 scram-sha-256

Reload the service and confirm the file parsed correctly:

sudo systemctl reload postgresql
sudo -u postgres psql -P pager=off -c \
  "SELECT line_number, type, database, user_name, address, auth_method
   FROM pg_hba_file_rules ORDER BY line_number;"

Output line_number |  type   |   database    |   user_name   |   address    |  auth_method
-------------+---------+---------------+---------------+--------------+---------------
         118 | local   | {all}         | {postgres}    |              | peer
         123 | local   | {all}         | {all}         |              | peer
         125 | hostssl | {saas_app}    | {app_service} | 127.0.0.1    | scram-sha-256
         126 | hostssl | {saas_app}    | {saas_owner}  | 127.0.0.1    | scram-sha-256
         127 | hostssl | {replication} | {replicator}  | 10.175.44.17 | scram-sha-256
         128 | host    | {all}         | {all}         | 127.0.0.1    | scram-sha-256
         130 | host    | {all}         | {all}         | ::1          | scram-sha-256
         133 | local   | {replication} | {all}         |              | peer
         134 | host    | {replication} | {all}         | 127.0.0.1    | scram-sha-256
         135 | host    | {replication} | {all}         | ::1          | scram-sha-256
(10 rows)

Confirm replication is enabled and the primary has spare WAL senders:

sudo -u postgres psql -P pager=off -c "SHOW wal_level;" -c "SHOW max_wal_senders;"

Output wal_level
-----------
 replica
(1 row)

 max_wal_senders
-----------------
 10
(1 row)

wal_level must read replica, which your tuning file set. max_wal_senders defaults to 10, enough for several standbys plus the occasional base backup.

#Step 2: Clear the standby's data directory

The base backup requires an empty target directory, and a fresh installation leaves a cluster in it. Remove and recreate the directory on the standby:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/18/main
sudo -u postgres mkdir -p /var/lib/postgresql/18/main
sudo chmod 700 /var/lib/postgresql/18/main
sudo ls -A /var/lib/postgresql/18/main

Do not use a wildcard deletion. Shell expansion runs as your user, so the glob matches nothing, rm reports success and the directory stays full.

#Step 3: Execute the base backup

Run the backup command:

sudo -u postgres env PGSSLMODE=require pg_basebackup \
  -h <primary_private_ip> -D /var/lib/postgresql/18/main \
  -U replicator -P -R -C -S replica_1

It prompts for the replicator password, then copies the cluster:

Output32052/32052 kB (100%), 1/1 tablespace

PGSSLMODE=require is not optional. Without it pg_basebackup falls back to plaintext and your hostssl rule rejects the connection with a misleading no pg_hba.conf entry error.

-R writes the replication configuration into postgresql.auto.conf and creates standby.signal, which is what makes PostgreSQL start as a standby rather than a normal cluster. -C -S replica_1 creates a named replication slot on the primary at the same time.

Without a slot, the primary recycles WAL segments the standby has not received yet. A standby that drops offline for a stretch comes back to find the WAL it needs gone, and you rebuild it from scratch. The slot tells the primary to hold WAL until the standby confirms it caught up.

#Step 4: Verify the replication configuration

Inspect the configuration file before starting the service:

sudo -u postgres cat /var/lib/postgresql/18/main/postgresql.auto.conf
sudo ls -A /var/lib/postgresql/18/main | grep signal

You'll see a long primary_conninfo line, a primary_slot_name line, and standby.signal in the directory listing:

Output# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
io_method = 'io_uring'
primary_conninfo = 'user=replicator password=''KpsePxJ/aQ+XFRoH8SpvjUk46GL6ovWq'' channel_binding=prefer host=10.175.44.42 port=5432 sslmode=require sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica_1'

Outputstandby.signal

Check that primary_conninfo carries sslmode=require, which it inherits from the environment variable you set on the base backup. The standby uses this line every time it reconnects, so a prefer here would leave it able to fall back to plaintext.

The replicator password sits in that same line in plain text, in a file at mode 600 owned by postgres.

#Step 5: Start the standby

Start the service and check the status:

sudo systemctl start postgresql
pg_lsclusters
sudo -u postgres psql -P pager=off -c "SELECT pg_is_in_recovery();"

OutputVer Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Output pg_is_in_recovery
-------------------
 t
(1 row)

Status reads online and the query returns t. That t is the opposite of what a restored cluster returns, and it is the difference between a standby and an independent copy: a standby stays in recovery permanently, applying WAL as it arrives.

If the server fails to start, the cause is usually configuration living outside the data directory. The base backup copies postgresql.conf from the primary, but on Debian and Ubuntu your tuning file sits in /etc/postgresql/18/main/conf.d, which it does not copy. The standby also needs its own TLS certificate, since the paths in your config point at files that exist only on the primary. Both are covered in the restore step earlier in this guide, and the same fixes apply here.

#Step 6: Verify from both sides

A standby can look connected from the primary while its receiver process has died. Verify the connection on both servers.

Run this query on the primary server:

sudo -u postgres psql -P pager=off -c \
  "SELECT client_addr, state, sync_state, sent_lsn, replay_lsn FROM pg_stat_replication;"

Outputclient_addr  |   state   | sync_state | sent_lsn  | replay_lsn
--------------+-----------+------------+-----------+------------
 10.175.44.17 | streaming | async      | 0/8000168 | 0/8000168
(1 row)

state reads streaming. sent_lsn and replay_lsn matching means the standby has applied everything it received, which is what zero lag looks like on an idle database.

Confirm the slot is in use:

sudo -u postgres psql -P pager=off -c \
  "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Output slot_name | active | restart_lsn
-----------+--------+-------------
 replica_1 | t      | 0/8000168
(1 row)

active = t and a restart_lsn tracking the LSNs above.

Run this query on the standby server:

sudo -u postgres psql -P pager=off -c \
  "SELECT status, sender_host, slot_name FROM pg_stat_wal_receiver;"

Output  status   | sender_host  | slot_name
-----------+--------------+-----------
 streaming | 10.175.44.42 | replica_1
(1 row)

Test the read-only enforcement on the standby:

sudo -u postgres psql -c "CREATE DATABASE test_write;"

OutputERROR:  cannot execute CREATE DATABASE in a read-only transaction

The server rejects the write attempt and returns an error. This rejection proves the standby is functioning correctly.

#Step 7: Test data replication

None of those views show whether a row actually crosses. Insert one on the primary server and check:

python3 verify_isolation.py

Outputinserted: 01a0120d-ee02-74ee-b32b-c44e9a4ae1d1
rows visible to tenant 1: 3
rows visible to tenant 2: 0

Query the table on the standby server:

sudo -u postgres psql -d saas_app -P pager=off -c "SELECT count(*) FROM customers;"

Output count
-------
     3
(1 row)

The count has gone up by one.

Back on the primary server, check the lag while the write is fresh, since that is when the number means something:

sudo -u postgres psql -P pager=off -c \
  "SELECT client_addr, sent_lsn, replay_lsn,
   pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication;"

Output client_addr  | sent_lsn  | replay_lsn | lag_bytes
--------------+-----------+------------+-----------
 10.175.44.17 | 0/8441B98 | 0/8441B98  |         0
(1 row)

lag_bytes is what a monitoring alert thresholds on. An LSN that stops advancing while state still reads streaming is replication that has stalled rather than failed, which no status column will tell you.

#Synchronous replication and automatic failover

This configuration uses asynchronous replication. The primary reports a transaction committed before the standby confirms receipt, which costs you a window of seconds where committed data has not reached the standby if the primary fails at the wrong moment.

Use synchronous_standby_names to force the primary to wait for confirmation:

synchronous_standby_names = 'replica_1'

Apply it selectively. Forcing every write through synchronous confirmation adds latency to traffic that does not need it.

What you have now is a warm standby requiring manual promotion. Patroni automates that, watching the primary through a distributed configuration store, and repmgr does the same with a lighter footprint and no etcd or Consul requirement.

#How to Monitor and Tune PostgreSQL in Production

This section sets up postgres_exporter feeding Prometheus, then shows how to find the queries costing you the most.

#Create a monitoring role

Generate a password and save it, since the exporter's connection string needs it shortly:

openssl rand -base64 24

Create the role with that value and grant it pg_monitor:

sudo -u postgres psql -c "CREATE ROLE exporter LOGIN PASSWORD 'your_generated_password';"
sudo -u postgres psql -c "GRANT pg_monitor TO exporter;"

pg_monitor is a built-in role granting read access to the statistics views and nothing else, so the exporter sees query timings and connection counts without reading a row of your data. PostgreSQL reserves the pg_ prefix for those built-in roles, which is why this one is named exporter.

Open pg_hba.conf and add a rule for it in the # IPv4 local connections: block:

sudo nano /etc/postgresql/18/main/pg_hba.conf

hostssl saas_app        exporter        127.0.0.1/32            scram-sha-256

Save, then reload:

sudo systemctl reload postgresql

#Install postgres_exporter

Release assets carry the version in the filename, so pin it rather than guessing at a latest path. Check the releases page for the current version first:

VERSION=0.20.1
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v${VERSION}/postgres_exporter-${VERSION}.linux-amd64.tar.gz
tar xvf postgres_exporter-${VERSION}.linux-amd64.tar.gz
sudo mv postgres_exporter-${VERSION}.linux-amd64/postgres_exporter /usr/local/bin/
postgres_exporter --version

Outputpostgres_exporter, version 0.20.1 (branch: HEAD, revision: 867fbcac31cd18c143e244190ea9168cca069827)
  build user:       root@f281ca536a03
  build date:       20260708-01:46:52
  go version:       go1.26.4
  platform:         linux/amd64
  tags:             unknown

#Configure the exporter as a service

The exporter reads its connection string from DATA_SOURCE_NAME rather than a config file, so it needs a service definition and somewhere to keep that string. Create a system account with no shell and no home directory:

sudo useradd --system --no-create-home --shell /usr/sbin/nologin postgres_exporter
sudo mkdir -p /etc/postgres_exporter
sudo nano /etc/postgres_exporter/env

Add the connection string. Use the password generated for the exporter role:

DATA_SOURCE_NAME=postgresql://exporter:your_generated_password@127.0.0.1:5432/saas_app?sslmode=require

Restrict file permissions to protect the credential:

sudo chown root:postgres_exporter /etc/postgres_exporter/env
sudo chmod 640 /etc/postgres_exporter/env

Then create the systemd unit file:

sudo nano /etc/systemd/system/postgres_exporter.service

[Unit]
Description=Prometheus PostgreSQL exporter
After=network.target postgresql.service

[Service]
User=postgres_exporter
EnvironmentFile=/etc/postgres_exporter/env
ExecStart=/usr/local/bin/postgres_exporter --web.listen-address=127.0.0.1:9187
Restart=on-failure

[Install]
WantedBy=multi-user.target

--web.listen-address is not optional. Without it the exporter binds every interface including your public IP, and your database metrics become a public endpoint behind nothing but a firewall rule. Prometheus scrapes it over loopback, so loopback is all it needs.

Start the service:

sudo systemctl daemon-reload
sudo systemctl enable --now postgres_exporter
sudo systemctl status postgres_exporter --no-pager

OutputCreated symlink /etc/systemd/system/multi-user.target.wants/postgres_exporter.service → /etc/systemd/system/postgres_exporter.service.

Output● postgres_exporter.service - Prometheus PostgreSQL exporter
     Loaded: loaded (/etc/systemd/system/postgres_exporter.service; enabled; preset: enabled)
     Active: active (running) since Sat 2026-08-01 00:33:30 UTC; 15s ago
   Main PID: 69883 (postgres_export)
      Tasks: 5 (limit: 114442)
     Memory: 3.7M (peak: 3.7M)
        CPU: 3ms
     CGroup: /system.slice/postgres_exporter.service
             └─69883 /usr/local/bin/postgres_exporter --web.listen-address=127.0.0.1:9187

Aug 01 00:33:30 pg1 systemd[1]: Started postgres_exporter.service - Prometheus PostgreSQ…orter.
Aug 01 00:33:30 pg1 postgres_exporter[69883]: time=2026-08-01T00:33:30.918Z level=WARN so…tory"
Aug 01 00:33:30 pg1 postgres_exporter[69883]: time=2026-08-01T00:33:30.918Z level=INFO so…es=[]
Aug 01 00:33:30 pg1 postgres_exporter[69883]: time=2026-08-01T00:33:30.918Z level=INFO so…:9187
Aug 01 00:33:30 pg1 postgres_exporter[69883]: time=2026-08-01T00:33:30.918Z level=INFO so…:9187
Hint: Some lines were ellipsized, use -l to show in full.

Two lines in the startup log look like failures and are not. The Error loading config warning refers to an optional YAML file the exporter proceeds without, and TLS is disabled refers to the metrics endpoint rather than the database connection.

Confirm the exporter is listening on loopback only:

sudo ss -tlnp | grep 9187

OutputLISTEN 0      4096       127.0.0.1:9187      0.0.0.0:*    users:(("postgres_export",pid=69883,fd=3))

Then count the metrics:

curl -s http://127.0.0.1:9187/metrics | grep -c "^pg_"

Output610

A number in the hundreds means it connected and is reading the statistics views. Near zero means the process started but authentication failed, and the journal will say so.

A count alone does not tell you what it is reading. Check three specific metrics:

curl -s http://127.0.0.1:9187/metrics | \
  grep "^pg_up\|^pg_settings_max_connections\|^pg_stat_database_numbackends"

Outputpg_settings_max_connections 200
pg_stat_database_numbackends{datid="1",datname="template1"} 0
pg_stat_database_numbackends{datid="16390",datname="saas_app"} 2
pg_stat_database_numbackends{datid="4",datname="template0"} 0
pg_stat_database_numbackends{datid="5",datname="postgres"} 0
pg_up 1

pg_up 1 is the first metric your alerting should watch, since it going to 0 means the database is unreachable. pg_settings_max_connections reading 200 rather than 100 confirms the exporter sees your tuned configuration. numbackends returns a row per database, so the template databases appear at zero and only saas_app shows connections.

#Scrape it with Prometheus

The exporter publishes metrics but stores nothing. Prometheus collects them on a schedule and keeps the history you query against.

Find the current release and install it:

curl -s https://api.github.com/repos/prometheus/prometheus/releases/latest | grep tag_name

VERSION=3.13.2
wget https://github.com/prometheus/prometheus/releases/download/v${VERSION}/prometheus-${VERSION}.linux-amd64.tar.gz
tar xvf prometheus-${VERSION}.linux-amd64.tar.gz
cd prometheus-${VERSION}.linux-amd64

sudo useradd --system --no-create-home --shell /usr/sbin/nologin prometheus
sudo mv prometheus promtool /usr/local/bin/
sudo mkdir -p /etc/prometheus /var/lib/prometheus
sudo chown prometheus:prometheus /var/lib/prometheus
prometheus --version

Write the scrape configuration file:

sudo nano /etc/prometheus/prometheus.yml

global:
  scrape_interval: 15s

scrape_configs:
  - job_name: postgres
    static_configs:
      - targets: ['127.0.0.1:9187']

Create the systemd unit file:

sudo nano /etc/systemd/system/prometheus.service

[Unit]
Description=Prometheus
After=network.target

[Service]
User=prometheus
ExecStart=/usr/local/bin/prometheus \
  --config.file=/etc/prometheus/prometheus.yml \
  --storage.tsdb.path=/var/lib/prometheus \
  --web.listen-address=127.0.0.1:9090
Restart=on-failure

[Install]
WantedBy=multi-user.target

--web.listen-address binds the web interface to loopback for the same reason the exporter is bound there. Reach it through an SSH tunnel rather than opening the port.

Start the service:

sudo systemctl daemon-reload
sudo systemctl enable --now prometheus
sudo systemctl status prometheus --no-pager

Confirm the target is being scraped and a metric arrives through Prometheus rather than straight from the exporter:

curl -s http://127.0.0.1:9090/api/v1/targets | grep -o '"health":"[^"]*"'
curl -s 'http://127.0.0.1:9090/api/v1/query?query=pg_up'

Output"health":"up"
{"status":"success","data":{"resultType":"vector","result":[{"metric":{"__name__":"pg_up",
"instance":"127.0.0.1:9187","job":"postgres"},"value":[1787014945.324,"1"]}]}}

"health":"up" means Prometheus is reaching the exporter. The value of "1" on pg_up means the metric travelled the whole path, from PostgreSQL through the exporter into Prometheus storage.

To browse the web interface, tunnel to it from your own machine:

ssh -L 9090:127.0.0.1:9090 <your_user>@<server_public_ip>

Then open http://127.0.0.1:9090 locally.

Connect Grafana to Prometheus for connection counts, cache hit ratio, replication lag and transaction throughput on one dashboard, following the same pattern as server monitoring with Prometheus and Grafana.

Run the same exporter on your standby. Replication lag is only visible from both ends, and a standby that has stopped applying WAL looks healthy from the primary for longer than you would like.

#Find your slow queries

Query pg_stat_statements for the worst offenders:

sudo -u postgres psql -d saas_app -P pager=off -c \
  "SELECT query, calls, mean_exec_time, total_exec_time
   FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"

EXPLAIN ANALYZE shows how PostgreSQL executes a query, not only how long it takes. Run it against whichever query is costing you most:

sudo -u postgres psql -d saas_app -P pager=off -c \
  "EXPLAIN ANALYZE SELECT * FROM customers WHERE tenant_id = 1;"

A Seq Scan on a large table where you expected an Index Scan usually means a missing index on the columns you're filtering by.

Alert on pg_up first, since it dropping to 0 means the database is unreachable and everything else is noise until it returns. Add replication lag next if you built a standby, then disk usage on the volume holding your data directory. Connection saturation matters less than most guides suggest once PgBouncer is capping concurrency, and cache hit ratio is a poor signal on its own.

#How to Handle Maintenance and Growth

Autovacuum reclaims space from deleted or updated rows on its own, but the defaults assume a moderate write rate. Your busiest tables need something more aggressive:

sudo -u postgres psql -d saas_app -c \
  "ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);"

Watch for tables falling behind:

sudo -u postgres psql -d saas_app -P pager=off -c \
  "SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
   FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

On a new database every row reads zero with an empty last_autovacuum, which is correct rather than a fault. What to watch for later is n_dead_tup climbing on a busy table while last_autovacuum stays old, meaning autovacuum is falling behind.

If you run a standby, audit its replication slot on the primary. A slot whose standby was decommissioned without dropping it holds WAL for a connection that never returns, growing disk usage until something notices:

sudo -u postgres psql -P pager=off -c \
  "SELECT slot_name, active,
   pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
   FROM pg_replication_slots;"

On a healthy standby this shows active = t with a retained figure near zero. Drop any slot showing active = f that is not tied to a standby you are deliberately keeping offline.

Rebuild bloated indexes without blocking reads or writes:

sudo -u postgres psql -d saas_app -c \
  "REINDEX INDEX CONCURRENTLY orders_tenant_id_idx;"

Schedule REINDEX and manual VACUUM for quiet hours where you can. They add I/O load on top of whatever's already running.

#Scale in the right order

Match the fix to what is actually broken rather than jumping to the most complicated option.

Running out of CPU or RAM means a bigger server. Read queries slowing the primary means sending them to the standby you already built. Tables too large for efficient scans or maintenance means partitioning by date or tenant. Only when a single node is exhausted after all three does Citus and distributed sharding become the answer.

Partitioning by date suits time-series or event data. Create the table as saas_owner, the same role that owns your others, so the ownership rule row level security depends on still holds:

psql "host=127.0.0.1 dbname=saas_app user=saas_owner sslmode=require"

CREATE TABLE events (
  id bigserial,
  tenant_id int,
  created_at timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Partitions inherit the parent's owner, so getting this right once covers every partition you add later.

If you're creating a new date partition every month by hand, TimescaleDB automates this pattern, creating and dropping time-based partitions on a schedule.

Partitioning by tenant_id works better when queries almost always filter to a single tenant, since the planner skips partitions not matching. Watch the partition count either way.

PostgreSQL shows lock management contention past roughly one to two thousand partitions on a single table, so a partition-per-tenant scheme fine at dozens or low hundreds of tenants becomes a liability well before a growing SaaS product reaches four figures. Plan a different key, or a coarser grouping of tenants per partition, before you get there.

A large unpartitioned table hits a different wall if it holds big JSONB or text columns. PostgreSQL stores oversized column values out of line in a mechanism called TOAST, and every TOASTed value gets an object identifier drawn from a 4 billion value space shared across the table.

AWS has documented this failure mode for Aurora and RDS PostgreSQL: as a TOAST table's OID usage climbs toward that ceiling, insert and update latency degrades, sometimes sharply, well before the counter wraps. Partitioning resets that space per partition and avoids the wall.

#Upgrade major versions

Minor releases install like any other package update. Major versions need pg_upgradecluster, which handles the Debian and Ubuntu cluster layout. Substituting your own version numbers, upgrading 18 to 19 looks like this:

sudo apt install -y postgresql-19
sudo pg_dropcluster 19 main --stop
sudo pg_upgradecluster -m upgrade 18 main

Rehearse this on a restored backup before touching production, and read the release notes for behavior changes affecting your queries. Once the new cluster is verified and running, remove the old one:

sudo pg_dropcluster 18 main

#Record a performance baseline

Run pgbench against a scratch database, never your application database, since -i drops and recreates its own tables:

sudo -u postgres createdb benchdb
sudo -u postgres pgbench -i -s 50 benchdb
sudo -u postgres pgbench -c 20 -j 4 -T 60 benchdb

The -i step reports table "pgbench_accounts" does not exist, skipping four times. That is pgbench clearing tables from a previous run and finding none, not an error.

You'll see:

Outputtransaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
number of clients: 20
number of transactions actually processed: 2231843
latency average = 0.538 ms
tps = 37201.608196 (without initial connection time)

Run the read-only variant too, since the two exercise different paths and a SaaS workload is usually read-heavy:

sudo -u postgres pgbench -c 20 -j 4 -T 60 -S benchdb

The same server returned 333,677 tps at 0.060 ms on this one, roughly nine times the write throughput, because WAL flushes leave the picture entirely.

The point isn't the highest figure you can squeeze out. It is a pair of numbers you compare future hardware or configuration changes against, and a record of what this server did when it was healthy.

Note the scale factor. At -s 50 the dataset is about 750MB, which fits inside shared_buffers on any server sized by the proportions earlier in this guide. That measures your CPU and memory rather than your disks. To benchmark storage, raise the scale factor until the dataset exceeds shared_buffers.

Clean up afterwards, since these tables serve no further purpose:

sudo -u postgres dropdb benchdb

Stop overpaying for bandwidth

Deploy high-bandwidth dedicated servers with 100TB included monthly egress, €0.5/TB overage pricing, and 10G ports. Inbound traffic always unlimited. Built for traffic-heavy workloads.

Get Started

#Conclusion

You now have PostgreSQL installed, hardened and tuned for a SaaS workload on hardware you control. SCRAM and TLS are in place on both connection hops, tenant isolation is enforced at the database layer and verified, PgBouncer is pooling connections, a standby is streaming and backups have been restored at least once.

Load test with traffic resembling yours before a real launch, and revisit your capacity plan each quarter as tenant count grows. When one primary and one standby aren't enough, Patroni and Citus are the natural next step. Patroni handles automatic failover, Citus handles distributed sharding.

FAQs

How do you migrate an existing database off a managed service?

Choose between two paths. A `pg_dump` and `pg_restore` cycle is simpler and needs a maintenance window sized to your data volume. Logical replication runs both databases in parallel and cuts downtime to the length of the cutover itself. Either way, install matching extensions first and compare row counts on both servers before switching your connection string. Logical replication also needs you to reset sequence values, since it copies rows rather than sequence state.

How much RAM does PostgreSQL need for a SaaS application?

There's no fixed number. Size it against your working set, the portion of your data accessed often enough to benefit from caching. A common starting point is enough RAM to set `shared_buffers` to roughly 25 percent of total memory while leaving room for the OS page cache and `work_mem` across concurrent connections. Revisit it once real traffic arrives and you can see how much of your data is being read from disk.

Can PostgreSQL handle multi-tenant SaaS at scale?

Yes. Database per tenant, schema per tenant, and shared schema with row-level security all run in production at real scale today. Which one fits depends on your isolation requirements and tenant count, rather than any limitation in PostgreSQL. If you choose row-level security, verify the policies apply to your application role before you trust them.

How do you secure PostgreSQL on a dedicated server?

Restrict network access to a private subnet through your firewall, enforce TLS on every connection with `hostssl` rules, use SCRAM-SHA-256 with a dedicated application role separate from the schema owner, apply least privilege on every grant, and keep the OS and PostgreSQL patched on a schedule.

How do you upgrade PostgreSQL major versions on Ubuntu?

Install the new version's packages, drop the empty cluster it creates, then run `pg_upgradecluster -m upgrade` against your existing cluster. Rehearse the process on a restored backup first and read the release notes for behavior changes affecting your queries. Minor version updates need only a package update and a restart.

#PostgreSQL

Published on Oct 24, 2024 Updated on May 19, 2026

How to install PostgreSQL on Ubuntu 24.04 LTS

Learn how to install PostgreSQL 16 on Ubuntu 24.04 LTS, configure users and databases, manage permissions, and interact with your database using psql, pgAdmin, and Python.

#PostgreSQL

Published on Feb 25, 2024 Updated on Nov 7, 2025

How to Create a Superuser in Postgres?

This tutorial demonstrates how to create a superuser in PostgreSQL and briefly explains what a Postgres superuser is.

#PostgreSQL

Published on Jan 16, 2024 Updated on Nov 7, 2025

How to Create a Database in PostgreSQL [CREATE DATABASE, createdb]

This step-by-step tutorial demonstrates how to create a database in PostgreSQL, using CREATE DATABASE or createdb command.