Currently I'm migrating ASP.net MVC 4 web application Certified Mixtapez to .NET core 2.2 from Windows Server 2012 & Microsoft Sql Server 2014 to Ubuntu 16.04 and PostgreSQL 11. Since Digital Ocean released Managed Postgres Instances, I decided to try it out.
Getting Started with Managed PostgreSql DB
I created and migrated to the DO Managed Database in 3 steps!
- Create 15GB/mo 1GB Postgres Database
- Import Microsoft SQL Server Existing Data to PostgreSQL with DBeaver
- Connect DB to Application
Here are the screenshots from Import with DBeaver
Getting Started with Postgres Droplet
After creating a managed database, I had several slow running queries after running several webservices. I upgraded the managed database to 8GB ($120) and I was still receiving 18 second responses. I decided to create a postgres droplet.
Installing Postgres & Creating DB - Digital Ocean
sudo apt-get install wget ca-certificates wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ lsb_release -cs-pgdg main" >> /etc/apt/sources.list.d/pgdg.list' sudo apt-get update sudo apt-get install postgresql postgresql-contribsudo su - postgres sudo ufw allow 5432 # allow 5432 connections sudo ufw status createuser --interactive --pwprompt # create user for external use sudo -u postgres createdb cm-web # create database
I tuned the PostgreSQL instance using PGTune. I added the configuration below to
max_connections = 200 shared_buffers = 1GB effective_cache_size = 3GB maintenance_work_mem = 256MB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 5242kB min_wal_size = 1GB max_wal_size = 2GB max_worker_processes = 2 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 listen_addresses = '*'
I opened up the
5432 port by modifying the
/etc/postgresql/11/main/pg_hba.conf to match the configuration below:
local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::0/0 md5
service postgresql restart
Managed Database - Postgres
|No Infrastructure Maintenance||No Postgres Extensions|
|Dashboard for Memory Usage, Queries, and Connections||Customization|
|Upgrade Easily||No exporting log file|
- Simplicity - PostgresSQL is easy to get up and running without installing any dependencies
- No Infrastructure Maintenance - Database Admin is not needed. Developers don't have to patch servers. This means there shouldnt be any downtime from maintenance unless DO has planned it.
- Upgrade Easily - Developers can upgrade RAM and storages in seconds
- Performance - The server has moderate performance for production databases with millions of rows. If you're developing a new product, it will work just fine.
- Customization - Developers can not install PostgreSQL Extensions to help tune databases. Developer also can not modify
postgres.conffile for performance or any special configurations
- No exporting log file - I didnt see a way to export database logs to run analysis.
- Expensive $$ - Databases cost alot for RAM
|1 GB / 1 vCPU/ 10 GB Disk||$15/mo|
|2 GB / 1 vCPU/ 25 GB Disk||$30/mo|
|4 GB / 2 vCPU/ 38 GB Disk||$60/mo|
|8 GB / 4 vCPU/ 115 GB Disk||$120/mo|
|16 GB / 6 vCPU/ 270 GB Disk||$240/mo|
Droplet - Postgres
|Cost Efficient||Ease of Use|
- Customization - Developers can install any extensions, utilities, and monitoring tools on the Droplet.
- Performance - DO Spaces performance was better with the same RAM and tuning from PGTune.
- Cost Efficient - DO Spaces prices are 3x cheaper than Managed Databases.
|1 GB / 1 CPU/ 25 GB SSD Disk||$5/mo|
|2 GB / 1 CPU/ 50 GB SSD Disk||$10/mo|
|4 GB / 2 CPU/ 80 GB SSD Disk||$20/mo|
|8 GB / 4 CPU/ 160 GB SSD Disk||$40/mo|
|16 GB / 6 CPU/ 320 SSD GB Disk||$80/mo|
- Infrastructure Maintenance - Scheduled Patches will need to be install for PostgreSQL and Ubuntu to avail security vulnerabilities.
- Installion - Developers have to install PostgreSQL to get up and started.
- Ease of Use - There are not any DB monitoring tools out the box so if you want dashboards, developers will need to install them
Performance is very important to me so I decided to use Spaces. I'm currently running my application on Windows Server 2012 128 GB RAM and 64GB is just for SQL Server. I didn't want to regress in performance plus I'm comfortable with Linux so the installation wasn't a big deal. The price was a major factor since the reason i migrated to Digital Ocean was to get rid of a 1200/mo bare metal server and SQL Server License Fees. I still believe the managed database is a very good option for applications in the early stages of launching or development.