Until now Birdfeeder was powered by a self-hosted Postgres instance which is used by various other side projects. It was running out of space and decided to migrate to a managed Postgres instance on DigitalOcean (Affiliate link, giving you $100 in credits).

The reason for going managed was that I didn't want to deal with backups and have the option to scale up with the click of a button.

I couldn't find a simple guide on how to move from a self-hosted instance to a managed instance on DigitalOcean and decided to write a short summary.

Goal: Move database from source instance to target instance with little read/write downtime.

Out of scope: No read / write downtime

Create new instance

Start by creating a new Postgres instance on DigitalOcean. This will take a couple of minutes. After this is done verify that the IP you are accessing the database from is added to "Trusted sources" in the control panel.

Create database

Once the database is running log in with the doadmin user that DigitalOcean is displaying in the interface. I prefer to use a GUI client like Postico to query the database.

Run the following command to create the database and the role you'll be using to access the database. The naming doesn't matter, it's a personal preference.

create role birdfeederdb_prod_write WITH createdb password 'some-very-secure-password';
create database birdfeederdb_prod;

Set permissions for role and schema

Create the schema under which the tables will be created. The new role will get the permissions to use the schema and create tables.

create schema birdfeederdb;
alter role birdfeederdb_prod_write SET search_path = 'birdfeederdb';
grant usage on schema birdfeederdb to birdfeederdb_prod_write;
grant create on schema birdfeederdb to birdfeederdb_prod_write;
alter database birdfeederdb_prod owner to birdfeederdb_prod_write;
alter schema birdfeederdb owner to birdfeederdb_prod_write;

Create backup from source instance

Stop all writes to the old database. In my case I shut down my app and everything accessing the database.

Replace the value behind the -h flag with the IP or hostname of the source database. You have to provide the username and the database name. It's likely that this is the same information that your app is currently using to access the database.

pg_dump -h 10.0.0.1 -U birdfeederdb_prod_write -p 5432 -Fc birdfeederdb_prod > birdfeederdb_prod.pgsql

Restore backup to target instance

Now it's time to import the backup into the new managed Postgres running on DigitalOcean.

pg_restore -d 'postgresql://birdfeederdb_prod_write:some-very-secure-password@your.instance.hostname.db.ondigitalocean.com:25060/birdfeederdb_prod?sslmode=require' --no-owner --role=birdfeederdb_prod_write --clean --jobs 4 --if-exists birdfeederdb_prod_.pgsql

Not using the --if-exists flag will result in seeing non-critical errors. More about that can be read in the Postgres documentation.

Restart your services

Now you can update the hostname/port in your app and it will start talking to the new database.