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
Top comments (0)