Migrating immich database from Debian to FreeBSD

2025-01-01 freebsd immich postgresql

Install the extensions for vchord:

pkg install postgresql18-vchord postgresql18-pgvector postgresql18-contrib

Enable the extensions in /var/db/postgres/data18/postgresql.auto.conf:

shared_preload_libraries = 'vchord,vector'

Create the database and user:

create database immich;
create user immich;
alter user immich with password 'immich';
alter database immich owner to immich;
grant all privileges on database "immich" to immich;
\c immich
create extension if not exists vchord cascade;
create extension cube;
grant all on schema public to immich;

The encoding on Debian is en_US.utf8 but on FreeBSD it is en_US.UTF-8 so we don’t want to use the --create or --clean options. Also using the custom format avoids the \restrict commands which make it all break:

ssh root@monk 'env PGPASSWORD=postgres pg_dump --username postgres --no-password --host 127.0.0.1 -Fc immich' |pg_restore --username postgres -d immich --no-privileges

But because we disabled permissions everything is owned by postgres user, so fix it:

psql -qat -d immich -c "select 'alter table '||schemaname||'.'||tablename||' owner to immich;' from pg_tables where schemaname = 'public'" > fix-immich-owner.sql

then run it:

psql <fix-immich-owner.sql -d immich