DEV Community

Steve
Steve

Posted on

Migrating a SQLite3 Database to PostgreSQL in Dokku

#dokku #sqlite3 #postgres #rails

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>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)