Here I share my experience deploying a self-managed PostgreSQL instance on Digital Ocean.
Note that I'm ignoring some best practices (like using Infrastructure as Code and automating deployments) because this is the first time that I have used Digital Ocean, I intend to explore the services and building blocks that DO provide.
Create the droplet for the DB
I haven't used Terraform with DigitalOcean yet. To iterate more quickly I just created the droplet using the web UI. Since it's my first time with Digital Ocean, I considered that better make some quick progress to be able to evaluate the platform and leave automation for a second iteration.
I already have my ssh public key in Digital Ocean, so bringing the droplet up was quick and easy.
Update Ubuntu
ubuntu-pg16$ apt-get update
ubuntu-pg16$ apt-get upgrade
Setup basic firewall
In the first iteration, just enable ssh (later we'll have to enable access to our PostgreSQL from our own VPC).
ubuntu-pg16$ ufw status
Status: inactive
ubuntu-pg16$ ufw allow ssh
Rules updated
Rules updated (v6)
ubuntu-pg16$ ufw enable
Command may disrupt existing ssh connections. Proceed with operation (y|n)? y
Firewall is active and enabled on system startup
ubuntu-pg16$ ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
To Action From
-- ------ ----
22/tcp ALLOW IN Anywhere
22/tcp (v6) ALLOW IN Anywhere (v6)
Let's reboot and make sure the firewall is enabled:
ubuntu-pg16$ reboot
ubuntu-pg16$ dmesg | grep 'UFW BLOCK'
[ 6.531028] [UFW BLOCK] IN=eth0 OUT= MAC=e2:3c:75:ad:05:2e:fe:00:00:00:01:01:08:00 SRC=85.227.85.186 DST=165.227.146.221 LEN=40 TOS=0x00 PREC=0x00 TTL=55 ID=10342 PROTO=TCP SPT=39469 DPT=5555 WINDOW=48150 RES=0x00 SYN URGP=0
[ 25.538523] [UFW BLOCK] IN=eth0 OUT= MAC=e2:3c:75:ad:05:2e:fe:00:00:00:01:01:08:00 SRC=159.223.205.160 DST=165.227.146.221 LEN=40 TOS=0x00 PREC=0x00 TTL=246 ID=54321 PROTO=TCP SPT=50029 DPT=59352 WINDOW=65535 RES=0x00 SYN URGP=0
[ 28.253638] [UFW BLOCK] IN=eth0 OUT= MAC=e2:3c:75:ad:05:2e:fe:00:00:00:01:01:08:00 SRC=131.108.89.137 DST=165.227.146.221 LEN=40 TOS=0x00 PREC=0x00 TTL=54 ID=25717 PROTO=TCP SPT=11610 DPT=23 WINDOW=50090 RES=0x00 SYN URGP=0
[ 28.634874] [UFW BLOCK] IN=eth0 OUT= MAC=e2:3c:75:ad:05:2e:fe:00:00:00:01:01:08:00 SRC=198.235.24.59 DST=165.227.146.221 LEN=44 TOS=0x00 PREC=0x60 TTL=250 ID=54321 PROTO=TCP SPT=52843 DPT=17778 WINDOW=65535 RES=0x00 SYN URGP=0
[ 29.139858] [UFW BLOCK] IN=eth0 OUT= MAC=e2:3c:75:ad:05:2e:fe:00:00:00:01:01:08:00 SRC=64.226.115.74 DST=165.227.146.221 LEN=40 TOS=0x00 PREC=0x00 TTL=253 ID=54321 PROTO=TCP SPT=54075 DPT=10240 WINDOW=65535 RES=0x00 SYN URGP=0
[ 34.146572] [UFW BLOCK] IN=eth0 OUT= MAC=e2:3c:75:ad:05:2e:fe:00:00:00:01:01:08:00 SRC=109.205.213.26 DST=165.227.146.221 LEN=40 TOS=0x00 PREC=0x00 TTL=243 ID=62145 PROTO=TCP SPT=52784 DPT=8888 WINDOW=1024 RES=0x00 SYN URGP=0
Install PostgreSQL
Now that we have our node updated and with the firewall enabled, let's install the database. Let's install PostgreSQL 16 following official documentation PostgreSQL.org/download/linux/ubuntu
ubuntu-pg16$ sudo sh -c 'echo "deb https://apt.PostgreSQL.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
ubuntu-pg16$ wget --quiet -O - https://www.PostgreSQL.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).
OK
ubuntu-pg16$ sudo apt-get update
Fetched 414 kB in 13s (32.3 kB/s)
Reading package lists... Done
And let's install it:
ubuntu-pg16$ sudo apt-get -y install PostgreSQL-16
Need to get 45.2 MB of archives.
After this operation, 182 MB of additional disk space will be used.
Fetched 45.2 MB in 7s (6,587 kB/s)
Preconfiguring packages ...
Creating config file /etc/PostgreSQL-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
'/etc/apt/trusted.gpg.d/apt.PostgreSQL.org.gpg' -> '/usr/share/PostgreSQL-common/pgdg/apt.PostgreSQL.org.gpg'
Created symlink /etc/systemd/system/multi-user.target.wants/PostgreSQL.service → /lib/systemd/system/PostgreSQL.service.
Setting up PostgreSQL-16 (16.0-1.pgdg22.04+1) ...
Creating new PostgreSQL cluster 16/main ...
/usr/lib/PostgreSQL/16/bin/initdb -D /var/lib/PostgreSQL/16/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/PostgreSQL/16/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Let's reboot and check database is up:
ubuntu-pg16$ reboot
ubuntu-pg16$ systemctl status PostgreSQL
● PostgreSQL.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/PostgreSQL.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2023-10-06 10:12:09 UTC; 6min ago
Process: 792 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 792 (code=exited, status=0/SUCCESS)
CPU: 1ms
Oct 06 10:12:09 ubuntu-pg16 systemd[1]: Starting PostgreSQL RDBMS...
Oct 06 10:12:09 ubuntu-pg16 systemd[1]: Finished PostgreSQL RDBMS.
We need to allow connections from the VPC:
ubuntu-pg16$ ufw allow proto tcp from 10.114.0.0/20 to any port 5432 comment 'pgsql from vpc'
And bind to all IPs. For that, add listen_addresses = '*' to /etc/PostgreSQL/16/main/PostgreSQL.conf and restart PostgreSQL:
ubuntu-pg16$ vim /etc/PostgreSQL/16/main/PostgreSQL.conf
ubuntu-pg16$ systemctl restart PostgreSQL
ubuntu-pg16$ ss -nltp | grep postgres
LISTEN 0 200 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=1558,fd=6))
LISTEN 0 200 [::]:5432 [::]:* users:(("postgres",pid=1558,fd=7))
Create a user and allow authenticated access from VPC
Create user and database:
ubuntu-pg16$ sudo -u postgres createuser bench --createdb --pwprompt
Enter password for new role: ****
Enter it again: ****
ubuntu-pg16$ sudo -u postgres createdb --owner=bench bench
Add host all all 10.114.0.0/20 scram-sha-256 to pg_hba.conf:
ubuntu-pg16$ vim /etc/PostgreSQL/16/main/pg_hba.conf
ubuntu-pg16$ egrep -v ^# /etc/PostgreSQL/16/main/pg_hba.conf
local all postgres peer
host all all 10.114.0.0/20 scram-sha-256
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
We need to reload the DB to pick up changes in pg_hba.conf:
ubuntu-pg16$ systemctl reload PostgreSQL
Let's run some benchmark
First, we need to initialize the database:
root@ubuntu-bench:~# pgbench --host=10.114.0.4 --username=bench --initialize --foreign-keys --scale=100 bench
Password:
dropping old tables...
creating tables...
generating data (client-side)...
10000000 of 10000000 tuples (100%) done (elapsed 11.55 s, remaining 0.00 s)
vacuuming...
creating primary keys...
creating foreign keys...
done in 24.03 s (drop tables 0.01 s, create tables 0.01 s, client-side generate 12.67 s, vacuum 0.95 s, primary keys 6.55 s, foreign keys 3.84 s).
Now we can run the benchmarks:
root@ubuntu-bench:~# pgbench --host=10.114.0.4 --username=bench --client=5 --jobs=2 --time=300 --progress=60 bench
pgbench (16.0 (Ubuntu 16.0-1.pgdg22.04+1))
starting vacuum...end.
progress: 60.0 s, 556.9 tps, lat 8.941 ms stddev 3.362, 0 failed
progress: 120.0 s, 532.9 tps, lat 9.377 ms stddev 3.418, 0 failed
progress: 180.0 s, 575.7 tps, lat 8.681 ms stddev 2.932, 0 failed
progress: 240.0 s, 561.6 tps, lat 8.897 ms stddev 3.519, 0 failed
progress: 300.0 s, 572.2 tps, lat 8.733 ms stddev 3.232, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 5
number of threads: 2
maximum number of tries: 1
duration: 300 s
number of transactions actually processed: 167959
number of failed transactions: 0 (0.000%)
latency average = 8.919 ms
latency stddev = 3.304 ms
initial connection time = 198.553 ms
tps = 560.188356 (without initial connection time)
The benchmark caused this resource utilization:
CPU:
Disk IO:
Bandwidth:
💡
Try Digital Ocean for free. 🤑 Sign up and get a $200, 60-day credit to try droplets, managed PostgreSQL, and more...