DEV Community: Wedson Lima

Quick SQLite to PostgreSQL Migration with pgloader

Wedson Lima — Thu, 01 May 2025 17:18:27 +0000

pgloader is an open-source data loading tool that efficiently streams data into PostgreSQL using the COPY protocol. It handles schema discovery, data transformations, and error management automatically. For SQLite migrations, it can handle both schema and data in a single command, making it perfect for automated database migrations.

1. Restore SQLite from Litestream

# Set your AWS credentials and restore the backup
export LITESTREAM_ACCESS_KEY_ID="your_access_key" \
       LITESTREAM_SECRET_ACCESS_KEY="your_secret_key" && \
litestream restore -o storage/production.sqlite3 \
  s3://your-bucket/storage/production.sqlite3

2. Migrate to PostgreSQL

For cloud PostgreSQL (e.g., Supabase):

docker run --rm -it \
  -v /path/to/your/sqlite:/data/production.sqlite3 \
  dimitri/pgloader:latest \
  pgloader --with "DATA ONLY" --verbose \
    sqlite:///data/production.sqlite3 \
    postgresql://user:password@your-postgres-host:6543/postgres

For local PostgreSQL:

docker run --rm -it \
  -v /path/to/your/sqlite:/data/production.sqlite3 \
  dimitri/pgloader:latest \
  pgloader --with "DATA ONLY" --verbose \
    sqlite:///data/production.sqlite3 \
    postgresql://postgres:postgres@host.docker.internal:5432/your_db

Key points:

Use --with "DATA ONLY" if tables are already created in PostgreSQL
Use host.docker.internal to connect to local PostgreSQL from Docker
Ensure your SQLite file path in the volume mount matches the path in the pgloader command

Important Notes

Always backup your data before migration
Test the migration process in a staging environment first
Plan for downtime during the migration
Update your application's database connection strings after migration

Active Storage: Retaining and removing uploaded files on form submission

Wedson Lima — Mon, 12 Feb 2024 22:21:54 +0000

Working with active storage, you may encounter a common problem when submitting the form; Rails will replace or remove all previously uploaded files.

Here, I show a simple approach to:

not replace or delete the previously uploaded files on form submission;
remove a specific file while editing a form;

We will use the signed_id from active record.

# your model
has_many_attached :files

// _form.html
= f.input :files, input_html: { class: 'form-control', multiple: true }

- if f.object.files.attached?
  - f.object.files.each do |file|
    = f.input :files, input_html: { value: file.signed_id, multiple: true }, as: :hidden

    li
      = link_to file.filename, rails_blob_path(file, disposition: 'attachment')
      '
      button.btn.btn-danger.remove-uploaded-file[data-uploaded-file-signed-id=file.signed_id] X

jQuery(function ($) {
  $('.remove-uploaded-file').on('click', function (e) {
    e.preventDefault();

    const $parent = $(this).parent();
    $parent.remove();

    const signedId = $(this).data('uploaded-file-signed-id');
    const $fileInput = $('input[value="' + signedId + '"]');

    $fileInput.remove();
  });
});

This will give you something like this

How to work with multiple PostgreSQL versions at the same time without losing your mind

Wedson Lima — Mon, 12 Feb 2024 20:55:45 +0000

Using Docker to work with multiple PostgreSQL versions

How to work with multiple PostgreSQL versions at the same time without losing my mind?

That's what we are trying to answer here. With Docker.

Docker PG Volumes

Data saved in a docker container are lost when that container is destroyed. We can save that data using docker volumes.

That's how we create a volume to use with a pg version 9.6:

docker volume create pgdata96

And that's how we create a volume for pg 13:

docker volume create pgdata13

Docker PG Containers

I like to map each pg version with a similar port. I follow this simple pattern:

version	port
postgres96	5496
postgres12	5412
postgres13	5413

This command will download, run and setup for us a container with a postgres 9.6:

docker run --name postgres96 -e POSTGRES_PASSWORD=123456 -d -p 5496:5432 -v pgdata96:/var/lib/postgresql/data postgres:9.6

And the same for a postgres 13:

docker run --name postgres13 -e POSTGRES_PASSWORD=123456 -d -p 5413:5432 -v pgdata13:/var/lib/postgresql/data postgres:13

Look how I mapped the ports.

Create a DB

Let's create a database called todolist in the pg version 13:

docker exec -i postgres13 psql -U postgres -c "CREATE DATABASE todolist WITH OWNER=postgres;"

Or you can log in to the container e create it using psql:

docker exec -it postgres13 psql -U postgres

and then

$ CREATE DATABASE todolist;

Start the container

When you restart your machine, or for some other reasons unknown, you can start the containers like this:

docker start postgres13

If you need to run other containers you can run like this:

docker start postgres13 redis memcached

That is no need to run the command docker run again.

Restore a dump

That's how you can restore a local dump file into a database:

docker exec -i postgres13 psql -U postgres -d todolist -f dump.sql

Configuring a PostgreSQL Database with Docker for a Rails App

Wedson Lima — Mon, 12 Feb 2024 20:53:11 +0000

Introduction

Docker and Docker Compose are indispensable tools in modern development workflows. They simplify the setup and management of development environments, ensuring consistency across various setups. There are a plethora of resources available for understanding Docker and Docker Compose, and their installation guides can be found here and here respectively. Since these topics are well explored, we’ll dive straight into configuring a PostgreSQL database for your Rails project using Docker Compose, emphasizing PostgreSQL version specification and managing separate volumes for different versions.

Prerequisites

Before we dive in, ensure you have the following set up:

Docker and Docker Compose installed on your machine.
A Rails project set up and ready to be configured with a PostgreSQL database.
A basic understanding of Rails and PostgreSQL.

Step 1: Installing Docker and Docker Compose

If you haven't installed Docker and Docker Compose yet, follow the official guides linked in the introduction. Once installed, verify the installations by running the following commands in your terminal:

docker --version
docker-compose --version

Step 2: Defining Volumes

In Docker, volumes are utilized for data persistence across container restarts and sharing data among containers. They are paramount for databases to ensure that data remains intact even when containers are stopped or removed. For our PostgreSQL setup, having separate volumes for different versions of PostgreSQL is beneficial to avoid any data conflicts or corruption when switching between different versions.

volumes:
  postgres_data_16:
  postgres_data_15:

In the snippet above, we define separate volumes for PostgreSQL versions 16, 13, and 12.

Step 3: Creating a Docker Compose File

Docker Compose facilitates the management of multi-container Docker applications. We'll create a docker-compose.yml file to define our PostgreSQL service, specifying the version and linking the appropriate volume for data storage.

version: '3'
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp_development
      POSTGRES_USER: myappuser
      POSTGRES_PASSWORD: myapppassword
    volumes:
      - postgres_data_16:/var/lib/postgresql/data
    ports:
      - "5416:5432"
volumes:
  postgres_data_16:
  postgres_data_15:

In this configuration, we've specified the PostgreSQL version as 16, defined the database user and password, mounted the postgres_data_16 volume to the /var/lib/postgresql/data directory inside the container, and mapped the container's port 5432 to the host's port 5160.

Step 4: Setting up the Procfile.dev

The Procfile.dev is a manifest used in Rails projects to define the services required for the development environment. Create a Procfile.dev file in the root of your project with the following content:

web: bundle exec rails s
db: docker-compose up db

This configuration specifies three services: the Rails server, Sidekiq for background jobs, and our Docker Compose-managed PostgreSQL database.

Step 5: Starting Your Development Environment

With all configurations in place, use Foreman to start your development environment:

foreman start -f Procfile.dev

Foreman will launch the services defined in Procfile.dev, getting your development environment up and running.

Conclusion

We've streamlined the process of configuring a PostgreSQL database for a Rails project using Docker Compose. This setup not only simplifies the database management but also ensures consistency across different development environments. Feel free to adapt this setup to your projects, and explore further customizations to tailor the environment to your needs.