Micrating from sqlite to postgres

[cellulosa]

Today I migrated from sqlite to postgres to be able to get the meilisearch search working on chandler (discussion). I thought maybe this could be of use to somebody else, so there you go. I am be using docker-compose and env variables to make it obvious where things ought to be personalised.

1. Spin up a postgres instance

  monica-postgres:
    container_name: monica-postgres
    image: postgres:alpine
    volumes:
      - ${DOCKER_CONFIG_DIR}/monica/postgres:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=monica
      - POSTGRES_DB=monica
      - POSTGRES_PASSWORD=${MONICA_POSTGRES_PASS}
    ports:
      - 5432:5432

2. install pgloader (on mac with brew install pgloader) and create the following file (named pgloader-migration.load):

load database
    from sqlite://${DOCKER_CONFIG_DIR}/monica/database.sqlite
    into postgres://monica:${MONICA_POSTGRES_PASS}@localhost:5432/monica

with include drop, create tables, create indexes, reset sequences, concurrency = 1, workers=2

set work_mem to '16MB', maintenance_work_mem to '512 MB';

3. Run Monica with postrgres and let it create the database as if it was a fresh new install:

  monica:
    container_name: monica
    image: ghcr.io/monicahq/monica-next:main
    # volumes: # no need for this anymore
      # - ${DOCKER_CONFIG_DIR}/monica/database.sqlite:/var/www/html/storage/database.sqlite
    environment:
      (...)
      # - DB_DATABASE=/var/www/html/storage/database.sqlite # we're switching to postgres
      - DB_CONNECTION=pgsql
      - DB_HOST=monica-postgres
      - DB_PORT=5432
      - DB_DATABASE=monica
      - DB_USERNAME=monica
      - DB_PASSWORD=${MONICA_POSTGRES_PASS}

5. Now you can run the migration with pgloader pgloader-migration.load. Hopefully you'll see no errors 🤞

6. Now you can remove the ports section of the postgres instance, as monica will be able to communicate with it internally.

If you want to make the search work, you either run docker exec monica php artisan queue:listen --queue=high,low,default as per docs, or spin a redis instance to manage the queue (what I did). Obiously you also need a meilisearch instance.

Here's my full docker-compose in case you're interested:

  monica:
    container_name: monica
    image: ghcr.io/monicahq/monica-next:main
    environment:
      - APP_ENV=production
      - APP_URL=https://monica.${MY_DOMAIN}
      - APP_TRUSTED_PROXIES=*
      - APP_KEY=${MONICA_APP_KEY}
      - DB_CONNECTION=pgsql
      - DB_HOST=monica-postgres
      - DB_PORT=5432
      - DB_DATABASE=monica
      - DB_USERNAME=monica
      - DB_PASSWORD=${MONICA_POSTGRES_PASS}
      - MAIL_MAILER=log
      - LOCATION_IQ_API_KEY=${MONICA_LOCATION_IQ_API_KEY}
      - MAPBOX_API_KEY=${MONICA_MAPBOX_API_KEY}
      - MAPBOX_USERNAME=${MAPBOX_USERNAME}
      - UPLOADCARE_PUBLIC_KEY=${MONICA_UPLOADCARE_PUBLIC_KEY}
      - UPLOADCARE_PRIVATE_KEY=${MONICA_UPLOADCARE_PRIVATE_KEY}
      - MEILISEARCH_HOST=http://monica-meilisearch:7700
      - MEILISEARCH_KEY=${MONICA_MEILI_MASTER_KEY}
      - CACHE_DRIVER=redis
      - SESSION_DRIVER=redis
      - QUEUE_CONNECTION=redis
      - REDIS_HOST=monica-redis
      - REDIS_PASSWORD=${MONICA_REDIS_PASSWORD}
    depends_on:
      - monica-postgres
      - monica-meilisearch
      - monica-redis
    networks:
      - frontend
      - backend

  monica-postgres:
    container_name: monica-postgres
    image: postgres:alpine
    volumes:
      - ${DOCKER_CONFIG_DIR}/monica/postgres:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=monica
      - POSTGRES_DB=monica
      - POSTGRES_PASSWORD=${MONICA_POSTGRES_PASS}
    networks:
      - backend

  monica-meilisearch:
    container_name: monica-meilisearch
    image: getmeili/meilisearch:latest
    volumes:
      - ${DOCKER_CONFIG_DIR}/monica/meilisearch:/meili_data
    environment:
      - MEILI_MASTER_KEY=${MONICA_MEILI_MASTER_KEY} # https://www.meilisearch.com/docs/learn/security/master_api_keys
    networks:
      - backend

  monica-redis:
    container_name: monica-redis
    image: redis:alpine
    volumes:
      - ${DOCKER_CONFIG_DIR}/monica/redis:/data
    command: /bin/sh -c "redis-server --requirepass ${MONICA_REDIS_PASSWORD}"
    networks:
      - backend

networks:
  frontend:
    driver: bridge
  backend:
    driver: bridge

[mathyvds]

Thank you @cellulosa for taking the time to write this out

It took a while to setup properly, but I got it working thanks to your documentation. In my config I had to remove the redis pw, and there were some permission issues with the DB, so after fixing this it was all good. I'm running it on a Synology NAS with Docker.

[cellulosa]

Glad to have been of help and that you got it working!

[mathyvds]

A fresh DB install won't work btw. There is no data, like no modules for the templates.
So I think this will only work by migrating from sqlite at the moment.

Update: see comment below. It was already reported. You need to set QUEUE_CONNECTION to sync and then a fresh install works.

Installing the dummy data gives errors when running php artisan monica:dummy --force -vvv, for example it contradicts the account creation policy (uppercase/lowercase password)

[mathyvds]

I see this issue was already reported here: #6626 (comment)

[cellulosa]

What I did was spin up the postresql and let monica create all tables from scratch as if it was a fresh installation. Then Did the data migration. I added this bit for clarity above - thanks!

[jrpetersjr]

@cellulosa - With this environment variable "MAIL_MAILER=log" how were you able to get your email for login?

[mathyvds]

If you'd like to use mails, use the following variables; example with Gmail:

    environment:
      - MAIL_MAILER=smtp
      - MAIL_HOST=smtp.gmail.com
      - MAIL_PORT=587
      - MAIL_USERNAME=$EMAIL
      - MAIL_PASSWORD=$EMAIL_PW
      - MAIL_ENCRYPTION=tls
      - MAIL_FROM_ADDRESS=$EMAIL
      - MAIL_FROM_NAME=$EMAIL_NAME

[cellulosa]

Having Monica running on a Docker instance via terminal, with the MAIL_MAILER=log environment variable set you should see the information on the terminal itself

[jrpetersjr]

Thank You. I thought it was going to be in a log file.

[hensansi]

@cellulosa, thank you for the guide; it saved me a lot of time. If I could suggest something, it would be to set:
- ASSET_URL=https://monica.${MY_DOMAIN}

By default, the app is requesting assets through HTTP and because of that if you force SSL on the domain, which is what I did, the assets will be rejected.

edit: Actually, the register account link is HTTP by default, too, which causes issues.

[cellulosa]

uhm I am using caddy as reverse proxy and i think I got rid of all CORS errors by setting these two:

- APP_URL=https://monica.${MY_DOMAIN}
- APP_TRUSTED_PROXIES=*

[hensansi]

You are right. - APP_TRUSTED_PROXIES=* solves the issue. It didn't cross my mind that this variable would be so impactful.