Migrating a SQLite3 Database to PostgreSQL in Dokku

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.

1. Connect to PostgreSQL:
   

   dokku postgres:connect <your_postgres_service_name>
   

2. List Tables:
   

   \dt
   

3. Check Data in Specific Tables:

   To inspect the data in the users table, for example:
   

   SELECT * FROM users LIMIT 10;
   

4. Check Data in the rails console.
   

   dokku run webapp
   bin/rails console