dar-alatta / docs/prod/deploy_backend/init.md

Initializing the production servers

Dar Al Atta's application and website

Last updated: 4/16/2026GitHubDar Al Atta

Initializing the production servers

This document go into detail on how the production servers were initialized in ODP. This is already done, so the instructions here are for reference only and can be followed if the prod servers need to be set again from scratch.

1. Installing Postgres and configuring db backup

This section contains the steps for installing Postgres in an ubuntu VM for production use.

1.1. SSH to Postgres VM

Make sure that you are connected to FortiClient VPN, then run:

ssh root@10.8.122.54

1.2. Install Postgres 17

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
sudo apt update
apt install postgresql-17

Modify /lib/systemd/system/postgresql.service to be like this (make sure user is postgres):

# postgresql.service is the meta unit for managing all PostgreSQL clusters on
# the system at once. Conceptually, this unit is more like a systemd target,
# but we are using a service since targets cannot be reloaded.
#
# The unit actually managing PostgreSQL clusters is postgresql@.service,
# instantiated as postgresql@15-main.service for individual clusters.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on
User=postgres

[Install]
WantedBy=multi-user.target

Then, change the password for postgres user in db:

sudo -u postgres -i
psql -c "ALTER USER postgres WITH PASSWORD '$PG_USER_POSTGRES';" # replace $PG_USER_POSTGRES with actual password stored in vaultwarden

1.3. Configure db

This section add custom configs for the postgres database, adds support for pgbackrest for backups, and modifies pg_hba.conf to the API and Backup VMs can access the database.

Modify /etc/postgresql/17/main/postgresql.conf:

max_connections = 300
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 6990kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
archive_mode = on
archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
max_wal_senders = 3
wal_level = replica
listen_addresses = '*'

And modify /etc/postgresql/17/main/pg_hba.conf by adding the following to accept connections from both API and Backup VMs:

host    all             all             10.8.122.38/32           md5
host    all             all             10.8.122.39/32           md5

Then run:

sudo systemctl restart postgresql

1.4. Configure backup

The backups will be stored in the Backup VM so that we don't have a single point of failure. Follow the below steps to configure pgbackrest in both Postgres and Backup VMs.

1.4.1. In Backup VM

We will use the backup VM to store the backups, so first, ssh to it:

ssh root@10.8.122.38

Then, install pgbackrest:

sudo apt-get install pgbackrest

Then create backup directory:

sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest

The pgbackrest tool created a user called postgres which we will connect to via ssh below, so reset the user's password.

Make sure you are root then run the following:

sudo passwd postgres

And enter this password: <$PG_USER_BACKUP> # replace $PG_USER_BACKUP with actual password stored in vaultwarden

1.4.2. In Postgres VM

1.4.2.1 Configure

Install pgbackrest:

sudo apt-get install pgbackrest

Configure /etc/pgbackrest.conf:

[global]
repo1-type=sftp
repo1-path=/var/lib/pgbackrest
repo1-sftp-host=10.8.122.38
repo1-sftp-host-key-hash-type=sha256
repo1-sftp-host-user=postgres
repo1-sftp-private-key-file=/var/lib/postgresql/.ssh/id_rsa
repo1-retention-full=2
repo1-retention-diff=5
compress-level=3

[dar-alatta]
pg1-path=/var/lib/postgresql/17/main
pg1-port=5433

Then create backup directory:

sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
1.4.2.2. Setup SSH with Backup VM

Now we need to make sure the Postgres VM can access the Backup VM via ssh without a password, hence, we need create public/private key pair. Do the following:

sudo -u postgres ssh-keygen -t rsa -b 4096 -f /var/lib/postgresql/.ssh/id_rsa
sudo chmod 600 /var/lib/postgresql/.ssh/id_rsa
sudo chown postgres:postgres /var/lib/postgresql/.ssh/id_rsa

Make sure that you do NOT enter any passphrase, leave it empty.

Then copy the public key to the Backup VM by running:

sudo -u postgres ssh-copy-id -i /var/lib/postgresql/.ssh/id_rsa.pub postgres@10.8.122.38

You will be notified to enter postgres user password in Backup VM. Use the password you created earlier.

Now, you should ssh to Backup VM from Postgres VM with no password. Try it out to double beck:

sudo -u postgres ssh postgres@10.8.122.38
1.4.2.3. Initialize pgBackRest stanza
sudo -u postgres pgbackrest --stanza=dar-alatta stanza-create
1.4.2.4 Cronjobs

Create the following cronjobs by running crontab -e:

# daily backup
0 * * * * sudo -u postgres pgbackrest --stanza=dar-alatta backup --type=incr

# weekly backup
0 0 * * * sudo -u postgres pgbackrest --stanza=dar-alatta backup --type=full

2. Configuring Postgres users

This section lays out the excat privileges given to postgres users.

First make sure you are in Postgres VM and accessed the database using the psql command, then follow:

2.1. Strapi db

First switch to strapi db by running /c strapi, then:

BEGIN;
CREATE USER strapi_user WITH PASSWORD 't3tFCopRmv2nLyccjoDn';

GRANT ALL PRIVILEGES ON DATABASE strapi TO strapi_user;
GRANT USAGE, CREATE ON SCHEMA public TO strapi_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO strapi_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO strapi_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO strapi_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO strapi_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO strapi_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON FUNCTIONS TO strapi_user;

COMMIT;

2.2. API db

First switch to api db by running /c api, then:

BEGIN;

CREATE USER api_user WITH PASSWORD 'oDVDX7n2jUfjsxEAtkm8pJmUMV';

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO api_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA s_donations TO api_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA s_support TO api_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA s_notifications TO api_user;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA s_donations TO api_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA s_support TO api_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA s_notifications TO api_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA s_donations GRANT SELECT, INSERT, UPDATE ON TABLES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA s_support GRANT SELECT, INSERT, UPDATE ON TABLES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA s_notifications GRANT SELECT, INSERT, UPDATE ON TABLES TO api_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA s_donations GRANT USAGE, SELECT ON SEQUENCES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA s_support GRANT USAGE, SELECT ON SEQUENCES TO api_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA s_notifications GRANT USAGE, SELECT ON SEQUENCES TO api_user;

GRANT USAGE ON SCHEMA public TO api_user;
GRANT USAGE ON SCHEMA s_donations TO api_user;
GRANT USAGE ON SCHEMA s_support TO api_user;
GRANT USAGE ON SCHEMA s_notifications TO api_user;

ALTER MATERIALIZED VIEW s_donations.v_donations_sums OWNER TO api_user;

-- Explicit grants for the new tables
GRANT SELECT, INSERT, UPDATE, DELETE ON s_donations.project_donation_totals TO api_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON s_donations.donation_updates TO api_user;

-- Explicitly grant TRUNCATE permission (needed for full_donation_refresh)
GRANT TRUNCATE ON s_donations.project_donation_totals TO api_user;

-- Grant view permissions
GRANT SELECT ON s_donations.v_donations_with_users TO api_user;

-- Grant function execution permissions
GRANT EXECUTE ON FUNCTION s_donations.update_donation_totals() TO api_user;
GRANT EXECUTE ON FUNCTION s_donations.full_donation_refresh() TO api_user;

COMMIT;

3. Strapi backup

We are using rsync to backup strapi's media folder to the Backup VM. Follow the below steps to configure strapi backup for the first time.

3.1. Install rsync

Run the following command to install rsync in API and Backup VMs:

sudo apt update
sudo apt install rsync

3.2. Create ssh keys for passwordless login

In API VM (ensure empty passphrase):

ssh-keygen -t rsa -b 2048

Then copy the public key to Backup VM:

ssh-copy-id root@10.8.122.38

3.3. Try sync

Run this in API VM to sync strapi-data folder:

rsync -avz /root/strapi-data/ root@10.8.122.38:/var/lib/strapi-data/

It should work, but we need to add it as a cronjob.

3.4. Cronjob

Add this:

*/5 * * * * rsync -avz /root/strapi-data/ root@10.8.122.38:/var/lib/strapi-data/