← All posts

September 12, 2026

PostgreSQL for ASP.NET Core in production: setup, Npgsql, backups

PostgreSQL is the easier of the two big relational databases to run on a small Linux box next to an ASP.NET Core app — Ubuntu’s own repos carry a current version, the defaults are already reasonably safe, and Npgsql (the .NET driver) is a first-class EF Core provider. The steps below were run end to end on a real Ubuntu 24.04 box, including a restore drill, because a backup nobody has ever restored isn’t verified.

1. Install

apt update
apt install -y postgresql postgresql-contrib

Ubuntu 24.04’s own repo carries PostgreSQL 16 directly — no third-party repo, no signing key to add, unlike SQL Server. pg_lsclusters confirms a running cluster on port 5432, owned by the postgres system user.

2. Create a role and database for your app

Don’t run your app as the postgres superuser. Create a role scoped to its own database:

sudo -u postgres psql -c "CREATE ROLE myapp_user WITH LOGIN PASSWORD '<a strong password>';"
sudo -u postgres psql -c "CREATE DATABASE myapp_db OWNER myapp_user;"

\du in psql lists roles; myapp_user should show no special attributes (no Superuser, no Create DB) — exactly what an app role needs.

3. Connections are localhost-only by default — keep it that way

Check listen_addresses in /etc/postgresql/16/main/postgresql.conf and you’ll find Ubuntu’s package already sets PostgreSQL to bind 127.0.0.1 only (ss -tln confirms: no 0.0.0.0:5432). That’s the opposite default from SQL Server, and it means the same “never open the database port” rule needs zero extra configuration here — there’s nothing listening on the public interface to accidentally expose in the first place.

pg_hba.conf controls who’s allowed to authenticate once a connection reaches the socket:

local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

peer on the local Unix socket means “your Linux username has to match the Postgres role name” — that’s why sudo -u postgres psql works without a password. The host lines are what your .NET app actually uses, over TCP to 127.0.0.1, with a real password (scram-sha-256, Postgres’s modern hashed-password auth).

4. Connect with Npgsql

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

Connection string, in appsettings.Production.json or (better, so it never touches source control) an environment variable read at startup:

Host=127.0.0.1;Port=5432;Database=myapp_db;Username=myapp_user;Password=<password>

Host=127.0.0.1, not a public IP — the app and the database live on the same box in this setup, so there’s no reason for that connection to ever leave loopback.

Npgsql pools connections by default — you don’t add a separate pooler for a small app. Each NpgsqlDataSource/DbContext pulls from an internal pool keyed by connection string, so repeated requests reuse existing connections instead of opening a new TCP connection to Postgres per request. Maximum Pool Size=100 in the connection string is the default ceiling; a small droplet running Postgres and the app together will hit memory pressure from Postgres’s own per-connection overhead long before 100 connections becomes the bottleneck, so it’s rarely worth touching unless you’ve actually measured a problem.

5. Migrations at deploy

context.Database.Migrate() called once at startup (guarded so it only runs where you want it to, not on every worker instance if you ever scale past one) is the simplest option for a small app. For anything with more than one instance ever hitting the same database, run migrations as an explicit deploy step instead — dotnet ef database update against the connection string, before the new release’s symlink gets switched in — so schema and code change together, not whenever the app happens to start.

6. Backups: pg_dump, and a restore you’ve actually tried

mkdir -p /var/backups/postgres
chown postgres:postgres /var/backups/postgres
sudo -u postgres pg_dump -Fc myapp_db -f /var/backups/postgres/myapp_db_$(date +%Y%m%d%H%M%S).dmp

-Fc (custom format) is what lets you restore a single table later instead of replaying an entire SQL script. Ship it to S3 the same way as any other file:

aws s3 cp /var/backups/postgres/myapp_db_20260101120000.dmp s3://my-backups-bucket/

The part almost nobody actually does is prove the dump is good:

sudo -u postgres psql -c "CREATE DATABASE myapp_db_restore_test OWNER myapp_user;"
sudo -u postgres pg_restore -d myapp_db_restore_test /var/backups/postgres/myapp_db_20260101120000.dmp
sudo -u postgres psql -d myapp_db_restore_test -c "SELECT count(*) FROM widgets;"
sudo -u postgres psql -c "DROP DATABASE myapp_db_restore_test;"

Restoring into a scratch database and querying a table you know should have rows is a two-minute check that turns “we have backups” into “we have backups that work.” Run it once when you set the job up, then again any time you change the backup script.

7. On a schedule, with retention

A systemd timer keeps the backup job in the same place as everything else you’re already managing with systemd, instead of a separate crontab. /etc/systemd/system/pg-backup.service:

[Unit]
Description=PostgreSQL backup to S3

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg-backup.sh

/etc/systemd/system/pg-backup.timer:

[Unit]
Description=Run pg-backup daily

[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true

[Install]
WantedBy=timers.target

systemctl enable --now pg-backup.timer
systemctl list-timers | grep pg-backup

pg-backup.sh runs the pg_dump and aws s3 cp pair from above, then deletes local dumps older than a few days so /var/backups doesn’t grow forever while S3 keeps the full history (a bucket lifecycle rule handles pruning there, the same as the SQL Server article):

find /var/backups/postgres -name '*.dmp' -mtime +3 -delete

Persistent=true on the timer means a backup that was missed because the droplet happened to be rebooting at 3 AM still runs once the box comes back, instead of silently waiting for the next scheduled day.

Reaching it from your own machine

The same SSH-tunnel model from the SQL Server article applies here unchanged, because listen_addresses already refuses anything but loopback:

ssh -L 15432:localhost:5432 deploy@<server-ip>

Point pgAdmin or DBeaver at localhost:15432 with the myapp_user role — the tunnel does the work of making a loopback-only service reachable from your laptop without ever touching the server’s firewall rules.

What breaks (and why)

peer authentication failed for user "myapp_user". That’s the local Unix-socket rule — psql with no -h connects over the socket and tries to match your Linux username to the Postgres role. Either run the command as the matching OS user (sudo -u postgres psql) or connect with -h 127.0.0.1 so pg_hba.conf’s host line (password auth) applies instead.

App can connect locally but a migration from your own machine can’t. Expected — listen_addresses = 'localhost' means only the box itself can reach port 5432 at all. Run migrations from the server (or through an SSH tunnel, the same model as the SQL Server article), never by opening 5432 to the internet.

pg_restore: [archiver] could not open input file. The dump path is wrong, or pg_dump didn’t actually run (check its exit code) — -Fc dumps are binary; don’t try to psql < them directly, that only works for plain-SQL (-Fp) dumps.