Migrating a SQLite3 Database to PostgreSQL in Dokku
Tags: dokku, sqlite3, postgres, rails
Published: 2024-05-30 17:35:36

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:

```bash
dokku storage:report <your_app_name>
```

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

```bash
=====> 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:

```bash
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:

```bash
dokku postgres:info <your_postgres_service_name>
```

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

```swift
=====> 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.

```bash
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**:
    
    ```bash
    dokku postgres:connect <your_postgres_service_name>
    ```
    
2. **List Tables**:
    
    ```sql
    \dt
    ```
    
3. **Check Data in Specific Tables**:
    
    To inspect the data in the **`users`** table, for example:
    
    ```sql
    SELECT * FROM users LIMIT 10;
    ```
    
4. **Check Data in the rails console.**
    
    ```bash
    dokku run webapp
    bin/rails console
    ```