Migrate Radarr from SQLite to Postgres

steps.md

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

• Main migration article from Servarr
• Reddit post detailing an issues with the Servarr wiki article instructions

Thanks for this!

• I decided to also export the schema for logs, drop and recreate, and import the schema for that as well so that there would be no extra entries after using pgloader on logs - other people mentioned dropping and recreating logs but nobody seems to mention restoring the schema before using pgloader on the logs db, so doing both just made sense. Not sure if it makes a difference .
• I was tripped up at first doing pg_dump because I forgot to pass the database name (ie. pg_dump radarr-main -s) and that resulted in the schema missing when I got to pgloader - I was confused because leaving out radarr-main still exports some stuff so I thought I did it properly but reimporting the schema took only seconds vs many minutes when done properly.
• I got lots of cast warnings from pgloader for things like bigint vs int, but no fatal errors so I assume that's expected due to differing schema types. Everything seems to work fine and without any warnings or errors when running and now I'm no longer blocked from performing operations due to DB locks while the scanning task is running 🎉