Postgres database has error pq: relation "tasks" does not exist

[lgfqmljk7]

So, I have a postgres server running already in a docker container. And I’ve created dll for vikunja to connect to with the following:

create schema if not exists vikunja_ddl authorization vikunja;
GRANT CONNECT ON DATABASE prod TO vikunja;

ALTER SCHEMA vikunja_ddl owner to vikunja;
GRANT USAGE ON SCHEMA vikunja_ddl TO vikunja;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA vikunja_ddl TO vikunja;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA vikunja_ddl TO vikunja; 
ALTER ROLE vikunja SET search_path TO vikunja_ddl;

Then I have the following docker container for vikunja to connect to the database:

version: '3'
services:
  api:
    image: vikunja/api
    container_name: vikunja
    environment:
      VIKUNJA_SERVICE_JWTSECRET: le_secret
      VIKUNJA_SERVICE_FRONTENDURL: http://my.domain.com/
      VIKUNJA_DATABASE_TYPE: postgres
      VIKUNJA_DATABASE_HOST: postgres
      VIKUNJA_DATABASE_PASSWORD: DB_PW
      VIKUNJA_DATABASE_DATABASE: prod
      VIKUNJA_DATABASE_USER: vikunja   
      PUID: $USRID 
      PGID: $GRPID
    ports:
      - 3456:3456
    volumes:
      - $BASE_DIR/config/files:/app/vikunja/files
    restart: unless-stopped
    networks:
      - vikunja-ntwk
      - nginx-ntwk
      - postgres-ntwk

  frontend:
    image: vikunja/frontend
    container_name: vikunja-frontend
    ports:
      - 80:80
    restart: unless-stopped
    networks:
      - vikunja-ntwk
      - nginx-ntwk

networks:
  vikunja-ntwk:
    driver: bridge
    name: vikunja-ntwk
  nginx-ntwk:
    external: true
  postgres-ntwk:
    external: true

(I had to split the above up else it wouldn’t format correctly, but it’s one docker-compose.yaml file)

So far, so good. when I run docker compose up I get the following errors:

vikunja-frontend  | /docker-entrypoint.sh: /docker-entrypoint.d/ is not empty, will attempt to perform configuration
vikunja-frontend  | /docker-entrypoint.sh: Looking for shell scripts in /docker-entrypoint.d/
vikunja-frontend  | /docker-entrypoint.sh: Launching /docker-entrypoint.d/20-envsubst-on-templates.sh
vikunja-frontend  | 20-envsubst-on-templates.sh: Running envsubst on /etc/nginx/templates/default.conf.template to /etc/nginx/conf.d/default.conf
vikunja-frontend  | /docker-entrypoint.sh: Launching /docker-entrypoint.d/30-tune-worker-processes.sh
vikunja-frontend  | /docker-entrypoint.sh: Launching /docker-entrypoint.d/50-injector.sh
vikunja-frontend  | info: API URL is /api/v1
vikunja-frontend  | info: Sentry enabled: false
vikunja-frontend  | info: started at 2023-04-30T00:17:39+00:00
vikunja-frontend  | /docker-entrypoint.sh: Launching /docker-entrypoint.d/60-ipv6-disable.sh
vikunja-frontend  | /docker-entrypoint.sh: Configuration complete; ready for start up
vikunja           | info: creating the new user vikunja with 1010:1010
vikunja           | 2023/04/30 00:17:40 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:40.312245194Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 0
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:17:41 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:42.044766229Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 0
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:17:43 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:43.817891028Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 0
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:17:45 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:45.577791509Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 0
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:17:47 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:47.360838984Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 1
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:17:49 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:49.88167938Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 1
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:17:53 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:17:54.045653715Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 1
vikunja           | usermod: no changes
vikunja           | 2023/04/30 00:18:01 No config file found, using default or config from environment variables.
vikunja           | 2023-04-30T00:18:01.352389231Z: CRITICAL	▶ migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation "tasks" does not exist
vikunja exited with code 1

So, what the heck is going on? Can vikunja only connect to the publich schema in postgres instead of another schema? Is this a bug? Did I fat finger something, what’s going? Some help will be greatly appreciated.

[kolaente]

This looks like the initial creation of tables did not work. Can you enable db debug logging to see if any queries were executed in the db?

[lgfqmljk7]

adding the following fields to my docker-compose.yaml

      VIKUNJA_LOG_DATABASE: stdout
      VIKUNJA_LOG_DATABASELEVEL: INFO

and got the following output:

vikunja | 2023/05/02 12:32:31 No config file found, using default or config from environment variables.
vikunja | 2023-05-02T12:32:31.604166381Z: INFO [DATABASE] 001 [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 [public] - 12.849061ms
vikunja | 2023-05-02T12:32:31.622905495Z: INFO [DATABASE] 002 [SQL] SELECT column_name, column_default, is_nullable, data_type, character_maximum_length, description,
vikunja | CASE WHEN p.contype = ‘p’ THEN true ELSE false END AS primarykey,
vikunja | CASE WHEN p.contype = ‘u’ THEN true ELSE false END AS uniquekey
vikunja | FROM pg_attribute f
vikunja | JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
vikunja | LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
vikunja | LEFT JOIN pg_description de ON f.attrelid=de.objoid AND f.attnum=de.objsubid
vikunja | LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
vikunja | LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
vikunja | LEFT JOIN pg_class AS g ON p.confrelid = g.oid
vikunja | LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
vikunja | WHERE n.nspname= s.table_schema AND c.relkind = ‘r’::char AND c.relname = $1 AND s.table_schema = $2 AND f.attnum > 0 ORDER BY f.attnum; [migration public] - 18.685907ms
vikunja | 2023-05-02T12:32:31.626046354Z: INFO [DATABASE] 003 [SQL] SELECT indexname, indexdef FROM pg_indexes WHERE tablename=$1 AND schemaname=$2 [migration public] - 3.088657ms
vikunja | 2023-05-02T12:32:31.626305117Z: INFO [DATABASE] 004 [SQL] SELECT count() FROM “migration” WHERE “id” IN ($1) [SCHEMA_INIT] - 199.944µs
vikunja | 2023-05-02T12:32:31.626504987Z: INFO [DATABASE] 005 [SQL] SELECT count() FROM “migration” - 162.812µs
vikunja | 2023-05-02T12:32:31.626699098Z: INFO [DATABASE] 006 [SQL] SELECT count(*) FROM “migration” WHERE “id” IN ($1) [20190324205606] - 165.278µs
vikunja | 2023-05-02T12:32:31.626713549Z: INFO [DATABASE] 007 Remove reminders_unix from tasks
vikunja | 2023-05-02T12:32:31.62692064Z: INFO [DATABASE] 008 [SQL] ALTER TABLE tasks DROP COLUMN reminders_unix - 176.23µs
vikunja | 2023-05-02T12:32:31.626935294Z: CRITICAL migration/Migrate 009 Migration failed: migration 20190324205606 failed: pq: relation “tasks” does not exist

It looks like it’s still querying the public schema, but it’s not doing any intilization in the vikunja_ddl

[kolaente]

Looks like the schema is indeed not changeable. Can you try setting the database to prod?currentSchema=vikunja_ddl?

[lgfqmljk7]

Yea, no dice:

vikunja | 2023/05/02 17:10:18 No config file found, using default or config from environment variables.
vikunja | 2023-05-02T17:10:18.025596005Z: INFO [DATABASE] 001 [SQL] SELECT tablename FROM pg_tables WHERE schemaname = $1 [public] - 6.283006ms
vikunja | 2023-05-02T17:10:18.025616985Z: CRITICAL migration/Migrate 002 Migration failed: pq: unrecognized configuration parameter “currentSchema”

I think, within vikunja the public schema is hard coded, which is fine if the db is standalone and only for vikunja. However, I’ve got a postgres centralized for all my apps, so that I can run jobs across the data the generate.

[kolaente]

It looks like this is a little more complicated.

Gitea does it like this: gitea/sql_postgres_with_schema.go at bf999e406994ab34420fb62e0de7948c8c2116c1 · go-gitea/gitea · GitHub

Do you want to send a PR?

[winston0410]

Checking the sourcecode of Vikunja, it uses xorm, even thought xorm uses github.com/lib/pq for Postgres under the hood, setting search_path in the connection string is not enough. I notice xorm also expect schema to be set programmatically, based on this Gitea PR

Add support for database schema in PostgreSQL

It will just be a one line change: gitea/models/models.go at 2cdda3ddc76339084fb01c114300a63a6d1b23a8 · guillep2k/gitea · GitHub

So to conclude, I think we just need to accept a new property from config, say database.schema, and add that one line into codebase.

@kolaente if you are happy with the above solution, I can create a PR for it

[kolaente]

Sure, I’d happily take a PR!

[winston0410]

@winston0410

Vikunja

Hi, notice permission issue, I guess my account has to be manually enabled?

How to register for Vikunja gitea?

I have been working on a feature and I’d like to submit a pull request. But when I register for an account at vikunja/vikunja: The to-do app to organize your life. Public API Roadmap here: - vikunja - Gitea it tells me to contact the site admin.

My account name on gitea is winston0410

[kolaente]

Your account is now enabled.