1"use strict";(globalThis.webpackChunkdawarich_app=globalThis.webpackChunkdawarich_app||[]).push([[3795],{70100(e,n,a){a.r(n),a.d(n,{assets:()=>o,contentTitle:()=>d,default:()=>h,frontMatter:()=>t,metadata:()=>r,toc:()=>c});const r=JSON.parse('{"id":"self-hosting/maintenance/update-postgresql","title":"Updating to PostgreSQL 17 with PostGIS","description":"Step-by-step guide to upgrade your Dawarich database from an older PostgreSQL version to PostgreSQL 17 with PostGIS.","source":"@site/docs/self-hosting/maintenance/update-postgresql.md","sourceDirName":"self-hosting/maintenance","slug":"/self-hosting/maintenance/update-postgresql","permalink":"/docs/self-hosting/maintenance/update-postgresql","draft":false,"unlisted":false,"tags":[],"version":"current","sidebarPosition":10,"frontMatter":{"sidebar_position":10,"title":"Updating to PostgreSQL 17 with PostGIS","description":"Step-by-step guide to upgrade your Dawarich database from an older PostgreSQL version to PostgreSQL 17 with PostGIS."},"sidebar":"tutorialSidebar","previous":{"title":"Moving to PostGIS","permalink":"/docs/self-hosting/maintenance/moving-to-postgis"},"next":{"title":"Monitoring","permalink":"/docs/category/monitoring"}}');var s=a(74848),i=a(28453);const t={sidebar_position:10,title:"Updating to PostgreSQL 17 with PostGIS",description:"Step-by-step guide to upgrade your Dawarich database from an older PostgreSQL version to PostgreSQL 17 with PostGIS."},d="Updating to PostgreSQL 17 with Postgis",o={},c=[{value:"What we'll do",id:"what-well-do",level:2},{value:"0. Preparing local backup",id:"0-preparing-local-backup",level:2},{value:"1. Preparing the <code>docker-compose.yml</code> file",id:"1-preparing-the-docker-composeyml-file",level:2},{value:"2. Creating a database dump",id:"2-creating-a-database-dump",level:2},{value:"3. Creating a new database",id:"3-creating-a-new-database",level:2},{value:"4. Restore the dump into the new database",id:"4-restore-the-dump-into-the-new-database",level:2},{value:"5. Renaming the database container",id:"5-renaming-the-database-container",level:2},{value:"6. Validating the app is working",id:"6-validating-the-app-is-working",level:2},{value:"In case of failure: the rollback plan",id:"in-case-of-failure-the-rollback-plan",level:2},{value:"7. Clean up",id:"7-clean-up",level:2},{value:"8. Well done!",id:"8-well-done",level:2}];function l(e){const n={a:"a",admonition:"admonition",code:"code",h1:"h1",h2:"h2",header:"header",li:"li",ol:"ol",p:"p",pre:"pre",...(0,i.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsx)(n.header,{children:(0,s.jsx)(n.h1,{id:"updating-to-postgresql-17-with-postgis",children:"Updating to PostgreSQL 17 with Postgis"})}),"\n",(0,s.jsx)(n.admonition,{type:"info",children:(0,s.jsx)(n.p,{children:"This guide is for users upgrading from PostgreSQL 14 to PostgreSQL 17. New installations of Dawarich already use PostgreSQL 17 with PostGIS \u2014 no migration needed."})}),"\n",(0,s.jsx)(n.p,{children:"As of January 2025, Dawarich uses kinda obsolete version of PostgreSQL, so to make sure we'll be able to improve and expand the project, we need to update it to the most recent version."}),"\n",(0,s.jsx)(n.p,{children:"This post will serve you as an instruction on how to do that."}),"\n",(0,s.jsxs)(n.admonition,{type:"danger",children:[(0,s.jsx)(n.p,{children:"Be careful with this process. Make sure you're aware of the risks and have a local backup of the database."}),(0,s.jsxs)(n.p,{children:["Also, make sure your Dawarich app is not running. All four containers (",(0,s.jsx)(n.code,{children:"dawarich_app"}),", ",(0,s.jsx)(n.code,{children:"dawarich_sidekiq"}),", ",(0,s.jsx)(n.code,{children:"dawarich_db"}),", ",(0,s.jsx)(n.code,{children:"dawarich_redis"}),") should be stopped after performing the local backup in step 0."]})]}),"\n",(0,s.jsx)(n.h2,{id:"what-well-do",children:"What we'll do"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsx)(n.li,{children:"Dump the entire database to a file so we could have a local backup"}),"\n",(0,s.jsxs)(n.li,{children:["Add a new PostgreSQL container to the ",(0,s.jsx)(n.code,{children:"docker-compose.yml"})," file"]}),"\n",(0,s.jsx)(n.li,{children:"Create a PostgreSQL 14 database dump in the container"}),"\n",(0,s.jsx)(n.li,{children:"Restore the dump into the new PostgreSQL 17 database"}),"\n",(0,s.jsx)(n.li,{children:"Validate the data presence in the new database"}),"\n"]}),"\n",(0,s.jsx)(n.p,{children:"We'll also be able to roll back to the previous version if something goes wrong."}),"\n",(0,s.jsx)(n.admonition,{type:"info",children:(0,s.jsxs)(n.p,{children:["If you're looking for a compatible PostgreSQL image with PostGIS, you can find list of images for different architectures ",(0,s.jsx)(n.a,{href:"/docs/self-hosting/maintenance/moving-to-postgis",children:"here"}),"."]})}),"\n",(0,s.jsx)(n.h2,{id:"0-preparing-local-backup",children:"0. Preparing local backup"}),"\n",(0,s.jsxs)(n.p,{children:["First, we need to create a local backup of the existing database. The instructions on that you can find on the ",(0,s.jsx)(n.a,{href:"/docs/self-hosting/maintenance/backup-and-restore",children:"Backup and restore"})," page."]}),"\n",(0,s.jsxs)(n.h2,{id:"1-preparing-the-docker-composeyml-file",children:["1. Preparing the ",(0,s.jsx)(n.code,{children:"docker-compose.yml"})," file"]}),"\n",(0,s.jsxs)(n.p,{children:["Let's update our ",(0,s.jsx)(n.code,{children:"docker-compose.yml"})," to add an updated version of PostgreSQL container. Note that we're not removing the old container yet."]}),"\n",(0,s.jsxs)(n.p,{children:["Add a new service to the ",(0,s.jsx)(n.code,{children:"services"})," section:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-yaml",children:'...\n\ndawarich_db_pg_17:\n image: postgis/postgis:17-3.5-alpine\n shm_size: 1G\n container_name: dawarich_db_pg_17\n volumes:\n - dawarich_db_data_pg_17:/var/lib/postgresql/data\n - dawarich_shared:/var/shared\n networks:\n - dawarich\n environment:\n POSTGRES_USER: postgres\n POSTGRES_PASSWORD: password\n restart: always\n healthcheck:\n test: [ "CMD-SHELL", "pg_isready -U postgres -d dawarich_development" ]\n interval: 10s\n retries: 5\n start_period: 30s\n timeout: 10s\n...\n'})}),"\n",(0,s.jsxs)(n.p,{children:["Add new ",(0,s.jsx)(n.code,{children:"dawarich_db_data_pg_17"})," volume:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-yaml",children:"...\nvolumes:\n dawarich_db_data:\n dawarich_shared:\n dawarich_public:\n dawarich_watched:\n dawarich_db_data_pg_17:\n"})}),"\n",(0,s.jsx)(n.h2,{id:"2-creating-a-database-dump",children:"2. Creating a database dump"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsx)(n.li,{children:"Start the database container:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker
1compose -f docker/docker-compose.yml up dawarich_db -d\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"2",children:["\n",(0,s.jsx)(n.li,{children:"Create a database dump. It may take some time, so be patient:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker exec dawarich_db pg_dump -U postgres -Fc -f /var/shared/backup.pg14.dump dawarich_development\n"})}),"\n",(0,s.jsx)(n.h2,{id:"3-creating-a-new-database",children:"3. Creating a new database"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsx)(n.li,{children:"Start the new database container:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker compose -f docker/docker-compose.yml up -d dawarich_db_pg_17\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"2",children:["\n",(0,s.jsx)(n.li,{children:"Create a new database in the new container:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:'docker exec dawarich_db_pg_17 psql -U postgres -c "CREATE DATABASE dawarich_development;"\n'})}),"\n",(0,s.jsx)(n.h2,{id:"4-restore-the-dump-into-the-new-database",children:"4. Restore the dump into the new database"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsx)(n.li,{children:"Restore the dump into the new database. It may take some time, so be patient:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker exec dawarich_db_pg_17 pg_restore -U postgres -d dawarich_development /var/shared/backup.pg14.dump\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"2",children:["\n",(0,s.jsx)(n.li,{children:"Validate the data presence in the new database:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:'docker exec dawarich_db_pg_17 psql -U postgres -d dawarich_development -c "SELECT COUNT(*) FROM points;"\n'})}),"\n",(0,s.jsx)(n.p,{children:"If the data is present, we're good to go."}),"\n",(0,s.jsx)(n.h2,{id:"5-renaming-the-database-container",children:"5. Renaming the database container"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsx)(n.li,{children:"Stop both database containers:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker compose -f docker/docker-compose.yml stop dawarich_db_pg_17 dawarich_db\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"2",children:["\n",(0,s.jsx)(n.li,{children:"Rename both database containers:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker rename dawarich_db dawarich_db_old\ndocker rename dawarich_db_pg_17 dawarich_db\n"})}),"\n",(0,s.jsx)(n.h2,{id:"6-validating-the-app-is-working",children:"6. Validating the app is working"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsxs)(n.li,{children:["\n",(0,s.jsxs)(n.p,{children:["Remove the ",(0,s.jsx)(n.code,{children:"dawarich_db"})," service from the ",(0,s.jsx)(n.code,{children:"docker-compose.yml"})," file"]}),"\n"]}),"\n",(0,s.jsxs)(n.li,{children:["\n",(0,s.jsxs)(n.p,{children:["Everywhere in the ",(0,s.jsx)(n.code,{children:"docker-compose.yml"})," file where you see ",(0,s.jsx)(n.code,{children:"dawarich_db_pg_17"})," replace it with ",(0,s.jsx)(n.code,{children:"dawarich_db"}),". The resulting ",(0,s.jsx)(n.code,{children:"docker-compose.yml"})," file should look something like this (considering you have no custom changes):"]}),"\n"]}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-yaml",children:'\nnetworks:\n dawarich:\nservices:\n dawarich_redis:\n image: redis:7.0-alpine\n container_name: dawarich_redis\n command: redis-server\n networks:\n - dawarich\n volumes:\n - dawarich_shared:/data\n restart: always\n healthcheck:\n test: [ "CMD", "redis-cli", "--raw", "incr", "ping" ]\n interval: 10s\n retries: 5\n start_period: 30s\n timeout: 10s\n dawarich_db:\n image: postgis/postgis:17-3.5-alpine\n shm_size: 1G\n container_name: dawarich_db\n volumes:\n - dawarich_db_data_pg_17:/var/lib/postgresql/data\n - dawarich_shared:/var/shared\n networks:\n - dawarich\n environment:\n POSTGRES_USER: postgres\n POSTGRES_PASSWORD: password\n restart: always\n healthcheck:\n test: [ "CMD-SHELL", "pg_isready -U postgres -d dawarich_development" ]\n interval: 10s\n retries: 5\n start_period: 30s\n timeout: 10s\n dawarich_app:\n image: freikin/dawarich:latest\n container_name: dawarich_app\n volumes:\n - dawarich_public:/var/app/public\n - dawarich_watched:/var/app/tmp/imports/watched\n networks:\n - dawarich\n ports:\n - 3000:3000\n # - 9394:9394 # Prometheus exporter, uncomment if needed\n stdin_open: true\n tty: true\n entrypoint: web-entrypoint.sh\n command: [\'bin/rails\', \'server\', \'-p\', \'3000\', \'-b\', \'::\']\n restart: on-failure\n environment:\n RAILS_ENV: production\n REDIS_URL: redis://dawarich_redis:6379/0\n DATABASE_HOST: dawarich_db\n DATABASE_USERNAME: postgres\n DATABASE_PASSWORD: password\n DATABASE_NAME: dawarich_development\n MIN_MINUTES_SPENT_IN_CITY: 60\n APPLICATION_HOSTS: localhost\n TIME_ZONE: Europe/London\n APPLICATION_PROTOCOL: http\n DISTANCE_UNIT: km\n PROMETHEUS_EXPORTER_ENABLED: false\n PROMETHEUS_EXPORTER_HOST: 0.0.0.0\n PROMETHEUS_EXPORTER_PORT: 9394\n logging:\n driver: "json-file"\n options:\n max-size: "100m"\n max-file: "5"\
1n healthcheck:\n test: [ "CMD-SHELL", "wget -qO - http://127.0.0.1:3000/api/v1/health | grep -q \'\\"status\\"\\\\s*:\\\\s*\\"ok\\"\'" ]\n interval: 10s\n retries: 30\n start_period: 30s\n timeout: 10s\n depends_on:\n dawarich_db:\n condition: service_healthy\n restart: true\n dawarich_redis:\n condition: service_healthy\n restart: true\n deploy:\n resources:\n limits:\n cpus: \'0.50\' # Limit CPU usage to 50% of one core\n memory: \'4G\' # Limit memory usage to 4GB\n dawarich_sidekiq:\n image: freikin/dawarich:latest\n container_name: dawarich_sidekiq\n volumes:\n - dawarich_public:/var/app/public\n - dawarich_watched:/var/app/tmp/imports/watched\n networks:\n - dawarich\n stdin_open: true\n tty: true\n entrypoint: sidekiq-entrypoint.sh\n command: [\'sidekiq\']\n restart: on-failure\n environment:\n RAILS_ENV: production\n REDIS_URL: redis://dawarich_redis:6379/0\n DATABASE_HOST: dawarich_db\n DATABASE_USERNAME: postgres\n DATABASE_PASSWORD: password\n DATABASE_NAME: dawarich_development\n APPLICATION_HOSTS: localhost\n BACKGROUND_PROCESSING_CONCURRENCY: 10\n APPLICATION_PROTOCOL: http\n DISTANCE_UNIT: km\n PROMETHEUS_EXPORTER_ENABLED: false\n PROMETHEUS_EXPORTER_HOST: dawarich_app\n PROMETHEUS_EXPORTER_PORT: 9394\n logging:\n driver: "json-file"\n options:\n max-size: "100m"\n max-file: "5"\n healthcheck:\n test: [ "CMD-SHELL", "bundle exec sidekiqmon processes | grep $${HOSTNAME}" ]\n interval: 10s\n retries: 30\n start_period: 30s\n timeout: 10s\n depends_on:\n dawarich_db:\n condition: service_healthy\n restart: true\n dawarich_redis:\n condition: service_healthy\n restart: true\n dawarich_app:\n condition: service_healthy\n restart: true\n deploy:\n resources:\n limits:\n cpus: \'0.50\' # Limit CPU usage to 50% of one core\n memory: \'4G\' # Limit memory usage to 4GB\n\nvolumes:\n dawarich_db_data:\n dawarich_shared:\n dawarich_public:\n dawarich_watched:\n dawarich_db_data_pg_17:\n'})}),"\n",(0,s.jsxs)(n.ol,{start:"3",children:["\n",(0,s.jsx)(n.li,{children:"Start Dawarich as usual:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker compose -f docker/docker-compose.yml up -d\n"})}),"\n",(0,s.jsx)(n.h2,{id:"in-case-of-failure-the-rollback-plan",children:"In case of failure: the rollback plan"}),"\n",(0,s.jsxs)(n.ol,{children:["\n",(0,s.jsx)(n.li,{children:"Stop the application:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker compose -f docker/docker-compose.yml stop\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"2",children:["\n",(0,s.jsx)(n.li,{children:"Rename the database containers back:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker rename dawarich_db dawarich_db_failed\ndocker rename dawarich_db_old dawarich_db\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"3",children:["\n",(0,s.jsx)(n.li,{children:"Restart the application:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker compose -f docker/docker-compose.yml up -d\n"})}),"\n",(0,s.jsxs)(n.ol,{start:"4",children:["\n",(0,s.jsx)(n.li,{children:"Verify that the data is present in the old database :"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:'docker exec -it dawarich_db psql -U postgres -d dawarich_development -c "SELECT COUNT(*) FROM points;"\n'})}),"\n",(0,s.jsxs)(n.ol,{start:"5",children:["\n",(0,s.jsx)(n.li,{children:"Optional: remove the failed database container:"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker stop dawarich_db_failed\ndocker rm dawarich_db_failed\ndocker volume rm dawarich_db_data_pg_17\n"})}
1),"\n",(0,s.jsx)(n.h2,{id:"7-clean-up",children:"7. Clean up"}),"\n",(0,s.jsx)(n.p,{children:"Once you're sure Dawarich is working with the new database, you can remove the old database container:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker stop dawarich_db_old\ndocker rm dawarich_db_old\n"})}),"\n",(0,s.jsx)(n.p,{children:"And remove the old volume:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-bash",children:"docker volume rm dawarich_db_data\n"})}),"\n",(0,s.jsx)(n.h2,{id:"8-well-done",children:"8. Well done!"}),"\n",(0,s.jsx)(n.p,{children:"You can celebrate now! \ud83c\udf89"})]})}function h(e={}){const{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,s.jsx)(n,{...e,children:(0,s.jsx)(l,{...e})}):l(e)}},28453(e,n,a){a.d(n,{R:()=>t,x:()=>d});var r=a(96540);const s={},i=r.createContext(s);function t(e){const n=r.useContext(i);return r.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function d(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(s):e.components||s:t(e.components),r.createElement(i.Provider,{value:n},e.children)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.