Immich•23h ago

Issues while restoring PostgreSQL backup from Immich volume

Hey peeps, trying to restore the PostgreSQL database of my Immich installation after I messed it up I'm running the gunzip | sed | psql command, but facing an error while doing so:

ERROR:  syntax error at or near "3038832"
LINE 1: 3038832 Vila 1.56654 42.53176 AD 03  2024-11-04 Encamp

ERROR:  syntax error at or near "3038832"
LINE 1: 3038832 Vila 1.56654 42.53176 AD 03  2024-11-04 Encamp

Any ideas why the syntax error? 👀

10 Replies

Immich•23h ago

:wave: Hey @ar3s3ru, Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:. References - Container Logs: docker compose logs docs - Container Status: docker ps -a docs - Reverse Proxy: https://immich.app/docs/administration/reverse-proxy - Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA Checklist I have... 1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time). 2. :ballot_box_with_check: read applicable release notes. 3. :ballot_box_with_check: reviewed the FAQs for known issues. 4. :ballot_box_with_check: reviewed Github for known issues. 5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy). 6. :ballot_box_with_check: uploaded the relevant information (see below). 7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable (an item can be marked as "complete" by reacting with the appropriate number) Information In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider: - Your docker-compose.yml and .env files. - Logs from all the containers and their status (see above). - All the troubleshooting steps you've tried so far. - Any recent changes you've made to Immich or your system. - Details about your system (both software/OS and hardware). - Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h). - The version of the Immich server, mobile app, and other relevant pieces. - Any other information that you think might be relevant. Please paste files and logs with proper code formatting, and especially avoid blurry screenshots. Without the right information we can't work out what the problem is. Help us help you ;) If this ticket can be closed you can use the /close command, and re-open it later if needed.

bo0tzz•23h ago

No idea without more context from the file. But that line is from the reverse geocoding data, you're fine to just delete it from the sql file to keep moving

ar3s3ruOP•23h ago

FYI I'm using the cloudnative-pg cluster now, before I was on the old postgres deployment provided by the chart

# yaml-language-server: $schema=https://raw.githubusercontent.com/cloudnative-pg/charts/refs/heads/main/charts/cluster/values.schema.json
---
type: postgresql
version:
  postgresql: "16"

mode: standalone

cluster:
  instances: 1

  storage:
    size: 5Gi
    storageClass: longhorn-nvme-3-replicas

  imageName: ghcr.io/tensorchord/cloudnative-vectorchord:16.9-0.4.3

  # Enable superuser access with the secret you created
  enableSuperuserAccess: true
  superuserSecret: immich-postgresql-postgres

  # Initialize the database for Immich
  initdb:
    database: immich
    owner: immich
    secret:
      name: immich-postgresql-immich
    # TODO: Use managed extensions (pg 18)
    postInitApplicationSQL:
      # Commands based on: https://immich.app/docs/administration/postgres-standalone/#without-superuser-permission
      - CREATE EXTENSION vchord CASCADE;
      - CREATE EXTENSION earthdistance CASCADE;

  # PostgreSQL configuration
  postgresql:
    # TODO(ar3s3ru): from Claude - check if correct
    # parameters:
    #   max_connections: "300"
    #   shared_buffers: "256MB"
    #   effective_cache_size: "1GB"
    #   work_mem: "16MB"

    shared_preload_libraries:
      - "vchord.so"

  # TODO(ar3s3ru): from Claude - check if correct
  # resources:
  #   requests:
  #     cpu: 500m
  #     memory: 1Gi
  #   limits:
  #     cpu: 2000m
  #     memory: 2Gi

  # Service configuration
  services:
    disabledDefaultServices: ["ro", "r"]
    additional:
      - selectorType: rw
        serviceTemplate:
          metadata:
            name: immich-postgresql-cluster
          spec:
            type: LoadBalancer
            loadBalancerClass: tailscale

monitoring:
  enabled: true
  podMonitor:
    enabled: true
  prometheusRule:
    enabled: true

# TODO(ar3s3ru): configure backups
backups:
  enabled: false

# yaml-language-server: $schema=https://raw.githubusercontent.com/cloudnative-pg/charts/refs/heads/main/charts/cluster/values.schema.json
---
type: postgresql
version:
  postgresql: "16"

mode: standalone

cluster:
  instances: 1

  storage:
    size: 5Gi
    storageClass: longhorn-nvme-3-replicas

  imageName: ghcr.io/tensorchord/cloudnative-vectorchord:16.9-0.4.3

  # Enable superuser access with the secret you created
  enableSuperuserAccess: true
  superuserSecret: immich-postgresql-postgres

  # Initialize the database for Immich
  initdb:
    database: immich
    owner: immich
    secret:
      name: immich-postgresql-immich
    # TODO: Use managed extensions (pg 18)
    postInitApplicationSQL:
      # Commands based on: https://immich.app/docs/administration/postgres-standalone/#without-superuser-permission
      - CREATE EXTENSION vchord CASCADE;
      - CREATE EXTENSION earthdistance CASCADE;

  # PostgreSQL configuration
  postgresql:
    # TODO(ar3s3ru): from Claude - check if correct
    # parameters:
    #   max_connections: "300"
    #   shared_buffers: "256MB"
    #   effective_cache_size: "1GB"
    #   work_mem: "16MB"

    shared_preload_libraries:
      - "vchord.so"

  # TODO(ar3s3ru): from Claude - check if correct
  # resources:
  #   requests:
  #     cpu: 500m
  #     memory: 1Gi
  #   limits:
  #     cpu: 2000m
  #     memory: 2Gi

  # Service configuration
  services:
    disabledDefaultServices: ["ro", "r"]
    additional:
      - selectorType: rw
        serviceTemplate:
          metadata:
            name: immich-postgresql-cluster
          spec:
            type: LoadBalancer
            loadBalancerClass: tailscale

monitoring:
  enabled: true
  podMonitor:
    enabled: true
  prometheusRule:
    enabled: true

# TODO(ar3s3ru): configure backups
backups:
  enabled: false

I also see a bunch of

invalid command \N
invalid command \.

invalid command \N
invalid command \.

may be related to the formatting of the sql restore file

bo0tzz•23h ago

Sounds like bad formatting yeah Maybe a line endings thing? idk

ar3s3ruOP•23h ago

This seems to be the relevant part:

--
-- Data for Name: geodata_places; Type: TABLE DATA; Schema: public; Owner: immich
--

COPY public.geodata_places (id, name, longitude, latitude, "countryCode", "admin1Code", "admin2Code", "modificationDate", "admin1Name", "admin2Name", "alternateNames") FROM stdin;
3038832    Vila    1.56654    42.53176    AD    03        2024-11-04    Encamp    \N    Casas Vila,Vila

--
-- Data for Name: geodata_places; Type: TABLE DATA; Schema: public; Owner: immich
--

COPY public.geodata_places (id, name, longitude, latitude, "countryCode", "admin1Code", "admin2Code", "modificationDate", "admin1Name", "admin2Name", "alternateNames") FROM stdin;
3038832    Vila    1.56654    42.53176    AD    03        2024-11-04    Encamp    \N    Casas Vila,Vila

Any ideas? I would fully remove this block if not necessary, but I'm not sure if I were to mess up geocoding when I start up the server again Looks like a sort of reverse geocoding cache

bo0tzz•23h ago

It's the lookup table It'll get recreated when the data changes (shipped in the container image) So it's fine to delete

ar3s3ruOP•18h ago

ok boss, lemme try 🫡 Backup worked in the end, but it turns out it was quite an old one... Is there a way to rebuild the assets database, assuming I have all the files still in the upload folder?

Immich•18h ago

https://immich.app/docs/features/command-line-interface

ar3s3ruOP•18h ago

So the idea is to do immich upload --recursive upload/{my-user-id}? Is the hashing of the files during upload consistent, or would that cause duplicates/double space taken?

bo0tzz•18h ago

It's consistent

Gaming

Programming

Issues while restoring PostgreSQL backup from Immich volume

Did you find this page helpful?