I figured out the answer while creating the thread, but I will post it since it might be useful for others:
What I was missing was the -n option:
-n, --disable-stdin Disable stdin (reads from /dev/null)
It works fine now:
root@asuna ~# lxc exec -n mysql -- mysqldump archive | restic backup --verbose --stdin --stdin-filename archive.sql
open repository
repository 9e543735 opened successfully, password is correct
lock repository
load index files
using parent snapshot 98577368
read data from stdin
start scan on [archive.sql]
start backup on [archive.sql]
scan finished in 2.090s: 1 files, 0 B
Files: 0 new, 1 changed, 0 unmodified
Dirs: 0 new, 0 changed, 0 unmodified
Data Blobs: 1 new
Tree Blobs: 1 new
Added to the repo: 609.105 KiB
processed 1 files, 0 B in 0:03
snapshot a869ff74 saved
incus exec with parameter --disable-stdin will disable the STDIN so that you can pipe the output of the command that runs in the instance, to some target on the host. Depending on the command for the piping, you may or may not need --disable-stdin.
In your case, you are just running this big process in the instance /usr/bin/docker exec -t immich_postgres pg_dumpall --clean --if-exists --username=postgres | gzip > /opt/stacks/immich/dbbackup/test. If that is the intention, then create some script in the instance, like