Migrating Pangolin from SQLite to PostgreSQL

Guides & Tutorials
,
1

Backup first! i have tested it thoroughly but can’t guarantee successes on all os.

Introduction

This guide provides a walkthrough for migrating your Pangolin application’s database from SQLite to a more robust PostgreSQL instance. While SQLite is excellent for simple, self-contained deployments, migrating to PostgreSQL is highly recommended for production environments or as your user base and data volume grow.

1. Prerequisites

Before you begin the migration, ensure your environment is properly set up. Having these prerequisites in place is not just recommended; it is essential for the script to run successfully and for the migration to complete without issues.

  • SSH/Terminal Access: You will need direct command-line access to the server where your Pangolin instance is hosted. All the commands for this migration, from preparing the environment to running the script and restarting services, will be executed in the server’s terminal.

  • Required Command-Line Tools: The migration script is a wrapper around powerful database client applications. It relies on two key clients to function: sqlite3 for reading and exporting data from your old SQLite database, and psql for connecting to and importing data into your new PostgreSQL instance.

    • To install on Debian/Ubuntu:
sudo apt-get update && sudo apt-get install sqlite3 postgresql-client -y
  • To install on RHEL/CentOS:
sudo yum install sqlite postgresql -y
  • Pangolin Files: You must know the exact location of your Pangolin config.yml file and the SQLite database file (usually db.sqlite ). The script uses common default paths (like ./config/config.yml ), but if your setup places these files elsewhere, you will need to provide the correct, full paths when prompted by the script.

2. The Migration Process

Follow these steps carefully and in the specified order to perform a smooth and successful migration.

Step 1: Start Your PostgreSQL Database

The migration script cannot create the database server for you; it requires a running and accessible PostgreSQL instance to connect to. The best practice is to add PostgreSQL as a dedicated service within your existing docker-compose.yml file.

  1. Edit your docker-compose.yml file: Add the following service definition. If you already have a postgres service, ensure its configuration aligns with these settings, paying close attention to the environment variables, volumes, and network.
services:
  # Add this PostgreSQL service
  postgres:
    image: postgres:17
    container_name: postgres
    restart: unless-stopped
    environment:
      # IMPORTANT: Use a strong, secure password here for production
      POSTGRES_USER: your_user
      POSTGRES_PASSWORD: your_secure_password
      POSTGRES_DB: your_db_name
    volumes:
      # This maps a directory on the host to the container. It is CRITICAL 
      # for ensuring your database data persists even if the container is removed or restarted.
      - ./config/postgres:/var/lib/postgresql/data
    healthcheck:
      # This check confirms the database is ready for new connections before other services start.
      test: ["CMD-SHELL", "pg_isready -U your_user"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - pangolin # Must be the same network as your Pangolin service to allow communication.

  # Your existing Pangolin service...
  pangolin:
    # ...

networks:
  pangolin:
    external: true # Or define the network within this file
  1. Start the PostgreSQL container: Open a terminal in the same directory as your docker-compose.yml file and run the following command to start only the PostgreSQL service.
docker compose up -d postgres

This command starts the database in detached mode (-d ), meaning it runs in the background. Allow a minute or two for the container to initialize itself for the first time. Your database server is now running and ready for the migration script to connect.

Step 2: Save and Prepare the Migration Script

  1. Copy the entire content of the provided migrate_to_postgres.sh script.
  2. raw.githubusercontent.com/hhftechnology/pangolin-database-migration-script/refs/heads/main/migrate_to_postgres.sh
  3. On your server, create a new file named migrate_to_postgres.sh and paste the script’s content into it.
  4. Make the script executable, which grants your system permission to run it as a program:
chmod +x migrate_to_postgres.sh

Step 3: Run the Migration Script

With the database running and the script prepared, you can execute it. It will automate the most complex and error-prone parts of the migration.

./migrate_to_postgres.sh

The script will guide you with interactive prompts for the following information:

  1. SQLite file paths: Provide the location of your db.sqlite and config.yml files. Press Enter to accept the default paths if they are correct.
  2. PostgreSQL connection details: Enter the exact database details (Host, Port, User, Password, DB Name ) that you configured in your docker-compose.yml in Step 1. The password will not be visible as you type, for security.

The script will first test the connection. If successful, it will show a summary of the migration plan and ask for final confirmation. Once you confirm, it will automatically back up your original files, create the necessary table structure, clear any pre-existing data from the tables for a clean import, copy all data, and finally, update your config.yml to use the new database connection string.

Step 4: Finalize Configuration and Restart

The script has prepared the data and configuration, but you must now tell the Pangolin service itself to use the new database.

  1. Edit your docker-compose.yml again: Make two critical changes to your pangolin service definition.
  • Change the image: The standard Pangolin image does not contain the necessary PostgreSQL drivers. You must switch to the specific postgresql-latest tag, which is built to include them.
    • From: image: fosrl/pangolin:latest
    • To: image: fosrl/pangolin:postgresql-latest
  • Add depends_on : This crucial instruction tells Docker Compose to wait until the PostgreSQL container has started and passed its health check before starting the Pangolin container. This prevents a common race condition where the application fails to start because it tries to connect to the database before the database is ready to accept connections.
services:
  postgres:
    # ...
  pangolin:
    image: fosrl/pangolin:postgresql-latest
    # ... other configs
    depends_on:
      postgres:
        condition: service_healthy
  1. Restart the Pangolin service: Apply the configuration changes and restart the container. Using --force-recreate is important as it tells Docker to destroy the old container and create a new one using the updated image and settings.
docker-compose up -d --force-recreate pangolin

3. Verification

Your Pangolin instance should now be running and fully connected to the PostgreSQL database. It is vital to verify that everything is working as expected before considering the migration complete.

  1. Check the container logs for errors: View the logs to confirm a successful startup. Look for lines indicating a successful database connection to your PostgreSQL host and the absence of any connection-refused or authentication errors.
docker-compose logs pangolin
  1. Test the application thoroughly: Open the Pangolin web UI. Log in and navigate through various pages. Specifically check that your users, sites, resources, and settings are all present and correct. Perform write operations, such as adding a new user, editing a site’s configuration, or deleting a temporary resource, to confirm that the application can successfully read from and write to the new database.

Congratulations! Your Pangolin instance is now running on a more powerful and scalable PostgreSQL database.

2 Likes
2

Might be worth stating that you can find the ip address of the postgres container using

docker network inspect pangolin
1 Like