Sunday, June 23, 2024

Migrating a SQLite3 Database to PostgreSQL in Dokku

Programming LanguageMigrating 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>
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
    

Check out our other content

Check out other tags:

Most Popular Articles