DEV Community: Steve

Migrating a SQLite3 Database to PostgreSQL in Dokku

Steve — Thu, 30 May 2024 17:35:36 +0000

Migrating a SQLite3 database to PostgreSQL in a Dokku-managed environment involves several steps. This guide will walk you through the entire process, ensuring a smooth transition from SQLite3 to PostgreSQL.

Prerequisites

Dokku installed and running
PostgreSQL service set up in Dokku
pgloader installed on your main server
Access to your SQLite3 database file

Step-by-Step Guide

1. Find the Location of the SQLite3 Database File

First, identify where the SQLite3 database file is stored. Use the following Dokku command to find the storage location:

dokku storage:report <your_app_name>

This command will display information about the persistent storage used by your application. For example:

=====> webapp storage information
       Storage build mounts:
       Storage deploy mounts:         -v /var/lib/dokku/data/storage/webapp:/rails/storage
       Storage run mounts:            -v /var/lib/dokku/data/storage/webapp:/rails/storage

In this example, the SQLite3 database file is located in /var/lib/dokku/data/storage/webapp.

2. Install `pgloader` on Your Main Server

If pgloader is not already installed, you can install it using the following command:

sudo apt-get update
sudo apt-get install pgloader

3. Retrieve PostgreSQL Connection Information

Get the connection details for your PostgreSQL service. Run the following command to get the necessary information:

dokku postgres:info <your_postgres_service_name>

This command will provide details including the DSN, internal IP, and port. For example:

=====> production postgres service information
       Config dir:          /var/lib/dokku/services/postgres/production/data
       Data dir:            /var/lib/dokku/services/postgres/production/data
       Dsn:                 postgres://postgres:<password>@dokku-postgres-production:5432/production
       Internal ip:         172.17.0.6
       Status:              running
       Version:             postgres:16.2

4. Run `pgloader`

Use pgloader to migrate the SQLite3 database to PostgreSQL. Use the internal IP address instead of the hostname for the connection string.

pgloader sqlite:///var/lib/dokku/data/storage/webapp/production.sqlite3 postgres://postgres:<password>@172.17.0.6:5432/production

Replace /var/lib/dokku/data/storage/webapp/production.sqlite3 with the path to your SQLite3 database file and <password> with the actual password.

5. Verify the Migration

Connect to your PostgreSQL database and verify that the data was imported correctly.

Connect to PostgreSQL:

dokku postgres:connect <your_postgres_service_name>

List Tables:
```
\dt
```
Check Data in Specific Tables:

To inspect the data in the users table, for example:
```
SELECT * FROM users LIMIT 10;
```
Check Data in the rails console.
```
dokku run webapp
bin/rails console
```

A couple things to think about when changing domains

Steve — Wed, 15 Dec 2021 15:45:24 +0000

My business is B2B and I was using the domain overdue.io for a while but wanted a .com. overdue.com is for sale but the owner wants like $100k, I'd rather donate that to charity when I'm at the place where I can afford to drop that much on a domain name. Regardless here are a couple things to think about when changing names.

Are you keeping the old domain name and redirecting to the new one? You're going to want to set that up to report a permanent redirect which I ended up doing in nginx.

But even before that think about the services that send information to your site. Do you host an API? Do you receive webhooks from Stripe, Postmark, or somewhere else? Update those before you start redirecting as it will cause errors. Stripe and many others will re-send the webhooks if they're unsuccessful.

Well that was embarrassing.

Steve — Sun, 08 Mar 2020 13:55:11 +0000

The last few days I had been receiving errors when a new user would sign up to my site. It was a deserialization error that wasn't actually causing any problems. Someone would sign up and after_create they would receive a welcome message. I decided to take a look at Google to see if I could find a fix for it.

What was happening was sidekiq was trying to complete the welcome message job so fast that it was running into errors as all of the information wasn't available in the database just yet. The fix I saw basically had the same explanation that made sense but I didn't look at the context of the original poster. They weren't sending welcome messages. Their fix was to change after_create to after_commit. As you may know, after_create happens a total of once per user, after_commit has no limits, it fires every time that user record is committed into the database. DOH!

The issue was found quickly and my users were apologized to and notified that this didn't affect any of their customers. It was definitely a reminder to slow down and take an extra few minutes to think about what you're about to deploy. Especially when you're the only check and balance and don't have a team to give you a quick "dummy check" as we called it in the Air Force.

It's never fun messing something up, even if it was something that resulted in a customer needing to delete a few emails. Regardless it was a learning experience I thought I would share.

Have an awesome week.

Rails 6 and ngrok

Steve — Sat, 29 Feb 2020 15:18:15 +0000

Long time lurker, first time caller.

I just recently updated my failed payment application to Rails 6. Went to use Ngrok to test some webhoooks and got an interesting little error. Now, unless you specify a host in rails 6 everything except for localhost gets blocked. Great new security feature. Buuuut, what if I use ngrok regularly and don't have the paid version so I get a new address every time I run it? It's regex to the rescue.

Simply open config/environments/development.rb and then add config.hosts << /[a-z0-9]+\.ngrok\.io/super simple and awesome.

Thought I'd keep it short.