Building Your Portfolio Experience
Loading Tariqul Islam's projects...
Loading Tariqul Islam's projects...
A secure and production-ready setup of PostgreSQL on VPS with optional S3-based offsite backup.
Hostinger VPS (Ubuntu 22.04 or higher)
A domain (optional)
PostgreSQL installed
Optional: AWS S3 and AWS CLI configured
sudo apt update && sudo apt upgrade -y
sudo apt install postgresql postgresql-contrib -ysudo -u postgres psql
\password postgresCreate a dedicated DB user:
CREATE USER dbuser WITH PASSWORD 'securepassword';
CREATE DATABASE mydatabase OWNER dbuser;Exit:
\qEdit postgresql.conf:
sudo nano /etc/postgresql/14/main/postgresql.confSet:
listen_addresses = 'localhost'Edit pg_hba.conf:
sudo nano /etc/postgresql/14/main/pg_hba.confEnsure:
local all all peer
host all all 127.0.0.1/32 md5Restart service:
sudo systemctl restart postgresqlsudo ufw enable
sudo ufw allow 22
sudo ufw allow 80
sudo ufw allow 443
sudo ufw deny 5432mkdir -p ~/backups/postgres~/backups/postgres_backup_mydatabase.sh
#!/bin/bash
BACKUP_DIR=~/backups/postgres
DB_NAME=mydatabase
TIMESTAMP=$(date +%F-%H-%M-%S)
BACKUP_NAME="backup-$DB_NAME-$TIMESTAMP.sql.gz"
pg_dump -U dbuser $DB_NAME | gzip > $BACKUP_DIR/$BACKUP_NAME
ls -t $BACKUP_DIR/backup-$DB_NAME-*.sql.gz | tail -n +6 | xargs -I {} rm {}Make it executable:
chmod +x ~/backups/postgres_backup_mydatabase.shaws s3 cp $BACKUP_DIR/$BACKUP_NAME s3://your-postgres-backup/$BACKUP_NAMEcrontab -e
0 * * * * ~/backups/postgres_backup_mydatabase.shsudo systemctl status postgresqlOnly if you must allow remote connections:
# postgresql.conf
listen_addresses = '*'
# pg_hba.conf
host all all 0.0.0.0/0 md5sudo systemctl restart postgresqlpsql -h 127.0.0.1 -U dbuser -d mydatabaseYou now have a production-ready PostgreSQL server on Hostinger VPS with automated backups (and optional AWS S3 sync).