Database

Below are the steps to install PostgreSQL from the PGDG repository.

Install the PGDG helper

This installs the apt.postgresql.org.sh script.

sudo apt install -y postgresql-common

Add the PostgreSQL Apt repository

This script adds the PGDG repo and its signing key for the current Ubuntu release.

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Install PostgreSQL

This pulls the server packages from the PGDG repo.

sudo apt -y install postgresql

Sanity check

Check that the service is running and the local connection works.

This is a simple, no‑frills way to take daily PostgreSQL backups with pg_dump and cron. The script writes timestamped dumps and keeps only the last 7 days.

1. Create a backup script

Create the script file:

vi /path/to/backup_script.sh

Add the content below. Swap the database credentials and backup directory.

#!/bin/bash

# Database credentials
DB_NAME="your_database_name"
DB_USER="your_database_user"
DB_PASSWORD="your_database_password"

# Backup directory
BACKUP_DIR="/path/to/backup/directory"
mkdir -p $BACKUP_DIR

# Date format for backup file
DATE=$(date +%Y-%m-%d_%H-%M-%S)

# Backup file name
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql"

# Export password to avoid prompt
export PGPASSWORD=$DB_PASSWORD

# Perform the backup
pg_dump -U $DB_USER -d $DB_NAME -F c -f $BACKUP_FILE

# Unset the password
unset PGPASSWORD

# Optional: Delete backups older than 7 days
find $BACKUP_DIR -type f -name "*.sql" -mtime +7 -exec rm {} \;

echo "Backup completed successfully at $DATE"

Make it executable:

Quick list of free (or free‑tier) cloud PostgreSQL options. Limits change over time, so double‑check before committing to anything long‑term.

1. Neon

Serverless Postgres. Free plan limits are per project.

• 100 CU-hours per month per project
• 0.5 GB storage per project
• Compute sizes up to 2 CU (8 GB RAM)
• Features: branching, autoscaling, read replicas

2. Supabase

Free plan limits are per project.

• 500 MB database size
• 5 GB egress
• 1 GB file storage
• 50,000 monthly active users

3. Aiven

Managed Postgres with a free plan and clear resource limits.