Sonarr Configuring PostgreSQL Database | Servarr Wiki (https://use.fontawesome.com/releases/v5.10.0/css/all.css) (/_assets/css/app.cd94ee14df75f4c2a9da.css) (/_assets/css/tags.9d379e682b01062fe3e8.css) (/_assets/css/vendor.45a5dcfa0ba005b18f08.css) (/_assets/css/theme0.2d5fc139e64d75a2ac94.css) (/_assets/css/mdi.c5234666a42449d3f1e3.css) (/_assets/js/login.js?1643500888) (/_assets/js/ui-extra.js?1643500888) (/_assets/js/editor.js?1643500888) (/_assets/css/comments.a57cbfd8541ec9647407.css) (/_assets/css/ui-extra.aa824cc7416f7a5a8b5c.css) (/_assets/css/admin.3fc27c433e103b2adef0.css)      Servarr Wiki        Search...      (/t)              (/login)                   Browse    (/)   Home  Applications (/en/radarr)   Radarr  (/en/sonarr)   Sonarr  (/en/lidarr)   Lidarr  (/en/readarr)   Readarr  (/en/whisparr)   Whisparr  (/en/prowlarr)   Prowlarr  Contribute (/en/donate)   Donate  (/en/bug-report)   Report a Bug    Suggest a Feature  (https://github.com/Servarr/Wiki)   Github  Support (https://radarr.video/discord)   Discord             / (/en/sonarr) sonarr  / (/en/sonarr/postgres-setup) postgres-setup     Sonarr Configuring PostgreSQL Database Configuring Sonarr with a Postgres Database    Page Contents  Sonarr and Postgres   Setting up Postgres   Creation of database   Migrating data    Last edited by   Administrator 02/05/2024                      ¶ Sonarr and Postgres This document will go over the key points of migrating and setting up Postgres support in Sonarr. Sonarr v4.0.0.615 or newer required  This guide was been created by the amazing (https://github.com/Roxedus) Roxedus . Postgres databases are NOT backed up by Sonarr, any backups must be implemented and maintained by the user  ¶ Setting up Postgres First, we need a Postgres instance. This guide is written for usage of the postgres:14 Docker image. Do not even think about using the latest tag!  docker create --name= postgres14 \ -e POSTGRES_PASSWORD = qstick \ -e POSTGRES_USER = qstick \ -e POSTGRES_DB = sonarr-main \ -p 5432 :5432/tcp \ -v /path/to/appdata/postgres14:/var/lib/postgresql/data \ postgres:14          Copy    ¶ Creation of database Sonarr needs two databases, the default names of these are: sonarr-main This is used to store all configuration and history sonarr-log This is used to store events that produce a logentry  Sonarr will not create the databases for you. Make sure you create them ahead of time  Create the databases mentioned above using your favorite method - for example (https://www.pgadmin.org/) pgAdmin or (https://www.adminer.org/) Adminer . You can give the databases any name you want but make sure config.xml file has the correct names. For further information see (/sonarr/postgres-setup#schema-creation) schema creation . ¶ Schema creation We need to tell Sonarr to use Postgres. The config.xml should already be populated with the entries we need: < PostgresUser >  qstick</ PostgresUser >  < PostgresPassword >  qstick</ PostgresPassword >  < PostgresPort >  5432</ PostgresPort >  < PostgresHost >  postgres14</ PostgresHost >         Copy    If you want to specify a database name then should also include the following configuration: < PostgresMainDb >  MainDbName</ PostgresMainDb >  < PostgresLogDb >  LogDbName</ PostgresLogDb >       Copy    Only after creating both databases you can start the Sonarr migration from SQLite to Postgres. ¶ Migrating data If you do not want to migrate a existing SQLite database to Postgres then you are already finished with this guide!  Migrating an existing sqlite3 database is unsupported, and this script may not work without modifications which we cannot assist you with. We support only new installs using postgres.  To migrate data we can use (https://github.com/dimitri/pgloader) PGLoader . It does, however, have some gotchas: By default transactions are case-insensitive, we use --with "quote identifiers" to make them sensitive. The version packaged in Debian and Ubuntu's apt repo are too old for newer versions of Postgres (Roxedus has not tested packages in other distros).Roxedus (https://github.com/Roxedus/Pgloader-bin) built a binary to enable this support (no code modification was needed, simply had to be built with updated dependencies).  Do not drop any tables in the Postgres instance  Before starting a migration please ensure that you have run Sonarr against the created Postgres databases at least once successfully. Begin the migration by doing the following: Stop Sonarr Open your preferred database management tool and connect to the Postgres database instance Run the following commands:  DELETE FROM "QualityProfiles" ; DELETE FROM "QualityDefinitions" ; DELETE FROM "DelayProfiles" ; DELETE FROM "Metadata" ; DELETE FROM "Config" ; DELETE FROM "VersionInfo" ; DELETE FROM "ScheduledTasks" ;           Copy    Start the migration by using either of these options: pgloader --with "quote identifiers" --with "data only" sonarr.db 'postgresql://qstick:qstick@localhost/sonarr-main'     Copy     docker run --rm -v /absolute/path/to/sonarr.db:/sonarr.db:ro --network= host ghcr.io/roxedus/pgloader --with "quote identifiers" --with "data only" /sonarr.db "postgresql://qstick:qstick@localhost/sonarr-main"     Copy      If you experience an error using pgloader it could be due to your DB being too large, to resolve this try adding --with "prefetch rows = 100" --with "batch size = 1MB" to the above command  With these handled, it is pretty straightforward after telling it to not mess with the scheme using --with "data only"    For those having the issues POST-MIGRATION from SQLite run the following: select setval('public."AutoTagging_Id_seq"',(SELECT MAX("Id")+1 FROM "AutoTagging"));
select setval('public."Blacklist_Id_seq"',(SELECT MAX("Id")+1 FROM "Blocklist"));
select setval('public."Commands_Id_seq"',(SELECT MAX("Id")+1 FROM "Commands"));
select setval('public."Config_Id_seq"',(SELECT MAX("Id")+1 FROM "Config"));
select setval('public."CustomFilters_Id_seq"',(SELECT MAX("Id")+1 FROM "CustomFilters"));
select setval('public."CustomFormats_Id_seq"',(SELECT MAX("Id")+1 FROM "CustomFormats"));
select setval('public."DelayProfiles_Id_seq"',(SELECT MAX("Id")+1 FROM "DelayProfiles"));
select setval('public."DownloadClientStatus_Id_seq"',(SELECT MAX("Id")+1 FROM "DownloadClientStatus"));
select setval('public."DownloadClients_Id_seq"',(SELECT MAX("Id")+1 FROM "DownloadClients"));
select setval('public."DownloadHistory_Id_seq"',(SELECT MAX("Id")+1 FROM "DownloadHistory"));
select setval('public."EpisodeFiles_Id_seq"',(SELECT MAX("Id")+1 FROM "EpisodeFiles"));
select setval('public."Episodes_Id_seq"',(SELECT MAX("Id")+1 FROM "Episodes"));
select setval('public."ExtraFiles_Id_seq"',(SELECT MAX("Id")+1 FROM "ExtraFiles"));
select setval('public."History_Id_seq"',(SELECT MAX("Id")+1 FROM "History"));
select setval('public."ImportListExclusions_Id_seq"',(SELECT MAX("Id")+1 FROM "ImportListExclusions"));
select setval('public."ImportListStatus_Id_seq"',(SELECT MAX("Id")+1 FROM "ImportListStatus"));
select setval('public."ImportLists_Id_seq"',(SELECT MAX("Id")+1 FROM "ImportLists"));
select setval('public."IndexerStatus_Id_seq"',(SELECT MAX("Id")+1 FROM "IndexerStatus"));
select setval('public."Indexers_Id_seq"',(SELECT MAX("Id")+1 FROM "Indexers"));
select setval('public."MetadataFiles_Id_seq"',(SELECT MAX("Id")+1 FROM "MetadataFiles"));
select setval('public."Metadata_Id_seq"',(SELECT MAX("Id")+1 FROM "Metadata"));
select setval('public."NamingConfig_Id_seq"',(SELECT MAX("Id")+1 FROM "NamingConfig"));
select setval('public."NotificationStatus_Id_seq"',(SELECT MAX("Id")+1 FROM "NotificationStatus"));
select setval('public."Notifications_Id_seq"',(SELECT MAX("Id")+1 FROM "Notifications"));
select setval('public."PendingReleases_Id_seq"',(SELECT MAX("Id")+1 FROM "PendingReleases"));
select setval('public."QualityDefinitions_Id_seq"',(SELECT MAX("Id")+1 FROM "QualityDefinitions"));
select setval('public."QualityProfiles_Id_seq"',(SELECT MAX("Id")+1 FROM "QualityProfiles"));
select setval('public."RemotePathMappings_Id_seq"',(SELECT MAX("Id")+1 FROM "RemotePathMappings"));
select setval('public."RootFolders_Id_seq"',(SELECT MAX("Id")+1 FROM "RootFolders"));
select setval('public."SceneMappings_Id_seq"',(SELECT MAX("Id")+1 FROM "SceneMappings"));
select setval('public."ScheduledTasks_Id_seq"',(SELECT MAX("Id")+1 FROM "ScheduledTasks"));
select setval('public."Series_Id_seq"',(SELECT MAX("Id")+1 FROM "Series"));
select setval('public."SubtitleFiles_Id_seq"',(SELECT MAX("Id")+1 FROM "SubtitleFiles"));
select setval('public."Tags_Id_seq"',(SELECT MAX("Id")+1 FROM "Tags"));
select setval('public."Users_Id_seq"',(SELECT MAX("Id")+1 FROM "Users"));                                      Copy     Start Sonarr          Powered by (https://wiki.js.org) Wiki.js