Skip to content

Instantly share code, notes, and snippets.

@tobz
Last active May 18, 2024 10:49
Show Gist options
  • Save tobz/929fd4ad8da80ac2ce524af73d4ea615 to your computer and use it in GitHub Desktop.
Save tobz/929fd4ad8da80ac2ce524af73d4ea615 to your computer and use it in GitHub Desktop.
Migrate Radarr from SQLite to Postgres

Migration steps

  1. Upgrade Radarr to at least v4.1.0.6133 or newer. This brings in support for Postgres. This will also ensure that all of your SQLite tables have the latest schema migrations applied before we migrate to Postgres. If you want to upgrade further, that's fine too, but make sure you've completed all upgrades first before continuing.

  2. Create your Postgres databases (one for the "main" database and one for the "logs" database) and configure Radarr with the relevant Postgres credentials. Both databases need to be owned by/accessible from the same Postgres user.

  3. Restart Radarr, and ensure it connects to Postgres and runs all schema migrations.

  4. Once all schema migrations have been applied, and no other activity is occurring, stop Radarr.

  5. Copy the SQLite databases from the Radarr instance/pod, both the main database and logs database. For this guide, we'll assume these files are called radarr.db and logs.db.

  6. Using pg_dump -s, dump the schema for the main Radarr database . Pipe the output from pg_dump to radarr-main.sql.

  7. In order to reset all tables to a clean starting point before importing our SQLite data, we need to drop and recreate the main database on the Postgres side. Using psql with the default superuser login (generally postgres), run the following (may need to be tweaked):

    DROP DATABASE "radarr-main";
    CREATE DATABASE "radarr-main";
    ALTER SCHEMA public OWNER TO <Postgres user created for Radarr>;
    ALTER DATABASE "radarr-main" OWNER TO <Postgres user created for Radarr>;
    
  8. With our database recreated, we'll now re-import the schema we dumped. You can use psql -f radarr-main.sql, with the appropriate flags/arguments for username, password, etc.

  9. Now with the schema reimported and all tables clean, we can finally import our SQLite databases. We'll use pgloader for this. The following is an example of the command to run to do, but be aware it may need tweaks for your specific situation:

    docker run --rm -v /absolute/path/to/radarr.db:/radarr.db:ro --network=host ghcr.io/roxedus/pgloader --with "quote identifiers" --with "data only" /radarr.db "postgresql://user:password@localhost/radarr-main"
    

    Run this for both radarr-main and radarr-logs, specifying the appropriate SQLite database file.

  10. Running pgloader successfully should result in a table of statistics and information about what was imported. If you see this, you should now be able to start Radarr again and observe that everything was imported successfully.

  11. Weee, you're now running on Postgres! 🐘

Links

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment