DEV Community

Daniel Blackwell
Daniel Blackwell

Posted on

Run a cheap Postgres / PostGIS server on a Digital Ocean droplet for USD$6 per month

The following shouldn't be used for anything where data security is a concern. This is a proof of concept only.

Before you go down this route, just know Digital Ocean offers managed database solutions for about $15 per month which offers far better availability, data resiliency, security and pretty much everything else.

Sure, having a database run on a small droplet means the performance isn't great, but sometimes you need to run something on the cheap. This is a way to do just that.

Create a droplet

https://cloud.digitalocean.com/droplets

  • Image <- Ubuntu (I used the 20.04 LTS x64)
  • Shared CPU <- Basic Plan
  • Block Storage <- 10GB (larger volumes cost more)
  • Data center <- somewhere close to your server
  • Host name <- something useful
  • Cert / Password <- Password is easier for most people

You can leave everything else as it is.

SSH to the droplet

  • Username: root
  • Password: [created in previous step]
  • Host/IP: Should be showing in the droplet overview.

Install Postgres / Postgis 

apt-get update -y && apt-get upgrade -y && \
apt-get install postgis # or postgres if that is all you need
Enter fullscreen mode Exit fullscreen mode

CHANGE THE POSTGRES PASSWORD! 

sudo -u postgres psql # Run psql with the postgres user
# In psql, issue the change password command \password
 \password postgres
# Enter new password:
# Enter it again:
Enter fullscreen mode Exit fullscreen mode

Edit the configs

Stop Postgres 

systemctl stop postgresql
Enter fullscreen mode Exit fullscreen mode

Find the version of postgres you are running 

ls /etc/postgresql  # lists the version folders
# "12"
Enter fullscreen mode Exit fullscreen mode

Find the /mnt/ point of your block volume 

ls /mnt
# volume_lon1_01 or similar
Enter fullscreen mode Exit fullscreen mode

Move the postgres data

This post was useful for pointers on moving the data.

rsync -av /var/lib/postgresql /mnt/[BLOCK VOLUME]/
Enter fullscreen mode Exit fullscreen mode

Edit /etc/postgresql/[VERSION]/main/pg_hba.conf

Change:

# IPv4 local connections:
host    all             all             127.0.0.1/32               md5
Enter fullscreen mode Exit fullscreen mode

to:

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
Enter fullscreen mode Exit fullscreen mode

Edit /etc/postgresql/[VERSION]/main/postgresql.conf

Change:

listen_addresses = 'localhost' # listening address
data_directory = '/var/lib/postgresql/12/main' # data
Enter fullscreen mode Exit fullscreen mode

to:

listen_addresses = '*' # listen to all 
data_directory = '/mnt/[BLOCK VOLUME]/postgresql/[VERSION]/main' # use block storage
Enter fullscreen mode Exit fullscreen mode

Start Postgres 

 systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Finally, configure the droplet firewall

Never open a database to the open web without securing it first!

https://cloud.digitalocean.com/networking/firewalls

  1. Create a firewall
  2. Add the Postgres port to the firewall
    • Incoming TCP Port 5432 from All Sources

Top comments (0)

Read next

codetradeindia profile image

Myth vs. Fact: A Common Misconceptions About AI

CodeTrade India Pvt. Ltd. -

sp90 profile image

Journey towards self hosted AI code completion

Simon -

devopscurry profile image

Exploring Distributed Databases in 2024 !

DevopsCurry -

karleb profile image

#2000. Reverse Prefix of Word

Karleb -