docker icon indicating copy to clipboard operation
docker copied to clipboard

Published 20 hours ago • verified publisher icon wallabag

Trouble when using an external pgsql database

Open Spiritus44 opened this issue 4 years ago • 12 comments

Hi everyone,

This afternoon I tried using the wallabag docker with an external db (instead of using a local containerized db). My use case is : i rent a professional grade postgresql into a cloud provider and i would use it instead of a local db (mainly for scalability and resilience issues).

I noticed that is an old questions, i read the issue #9 but i can't figure out how make it works.

My db postgresql version 11. My docker compose looks like this :

version: '2'
services:
  wallabag:
    image: wallabag/wallabag
    environment:
      - POSTGRES_PASSWORD=<pgsql password>
      - POSTGRES_USER=<pgsql user>
      - SYMFONY__ENV__DATABASE_DRIVER=pdo_pgsql
      - SYMFONY__ENV__DATABASE_HOST=<remote db's ip>
      - SYMFONY__ENV__DATABASE_PORT=<remote db's port>
      - SYMFONY__ENV__DATABASE_NAME=wallabag
      - SYMFONY__ENV__DATABASE_USER=<pgsql user>
      - SYMFONY__ENV__DATABASE_PASSWORD=<pgsql password>
      - SYMFONY__ENV__DATABASE_CHARSET=utf8
      - SYMFONY__ENV__MAILER_HOST=127.0.0.1
      - SYMFONY__ENV__MAILER_USER=~
      - SYMFONY__ENV__MAILER_PASSWORD=~
      - [email protected]
      - SYMFONY__ENV__DOMAIN_NAME=<my domain name>
    ports:
      - "127.0.0.1:1001:80"

When i make a docker-compose up, i got this :

~/wallabag-docker$ sudo docker-compose up 
Starting wallabagdocker_wallabag_1
Attaching to wallabagdocker_wallabag_1
wallabag_1  | Starting provisioner...
wallabag_1  | [WARNING]: Found both group and host with same name: localhost
wallabag_1  | Provisioner finished.

If i go to the wallabag url, i got these errors message :

wallabag_1  | 2020/04/13 18:54:53 [error] 254#254: *1 FastCGI sent in stderr: "PHP message: PHP Warning:  "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2636PHP message: PHP Warning:  "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2665" while reading response header from upstream, client: 172.18.0.1, server: _, request: "GET / HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "wallabag.xn--delano-uva.fr"
wallabag_1  | 172.18.0.1 - - [13/Apr/2020:18:54:53 +0000] "GET / HTTP/1.1" 302 215 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:73.0) Gecko/20100101 Firefox/73.0"
wallabag_1  | 2020/04/13 18:54:54 [error] 254#254: *1 FastCGI sent in stderr: "PHP message: PHP Warning:  "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2636PHP message: PHP Warning:  "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2665PHP message: PHP Fatal error:  Uncaught PDOException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "wallabag_craue_config_setting" does not exist
wallabag_1  | LINE 1: ...on_1, t0.name AS name_2, t0.value AS value_3 FROM wallabag_c...
wallabag_1  |                                                              ^ in /var/www/wallabag/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105
wallabag_1  | Stack trace:
wallabag_1  | #0 /var/www/wallabag/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOStatement->execute(NULL)
wallabag_1  | #1 /var/www/wallabag/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(847): Doctrine\DBAL\Driver\PDOStatement->execute()
wallabag_1  | #2 /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(712): Doctrine\DBAL\Connection->executeQuery('SELECT t0.secti...', Array, Array)
wallabag_1  | #3 /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php(196): Doctrine\ORM\Persisters\Entity\BasicEntityPersister->load(Array, NULL, NULL, Array, NULL, 1, NULL)
wallabag_1  | #4 /var/w" while reading response header from upstream, client: 172.18.0.1, server: _, request: "GET /login HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "<wallabag domain name>"
wallabag_1  | 172.18.0.1 - - [13/Apr/2020:18:54:54 +0000] "GET /login HTTP/1.1" 500 31 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:73.0) Gecko/20100101 Firefox/73.0"
wallabag_1  | 172.18.0.1 - - [13/Apr/2020:18:54:54 +0000] "GET /favicon.ico HTTP/1.1" 200 34494 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:73.0) Gecko/20100101 Firefox/73.0"

The first problem "Did you mean to use "continue 2"?" looks like the same as #185

The error line 3 : "Undefined table: 7 ERROR:  relation "wallabag_craue_config_setting" does not exist" is a correct error message because my db is really empty :  
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1), server 11.6 (Debian 11.6-1.pgdg90+1))
WARNING: psql major version 10, server major version 11.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

wallabag=> \dt
Did not find any relations.

So, my questions are : Does anyone have the same trouble as me ? Did exist a workaround to simply add an external database to the wallabag container ? How can I force the ansible task to create the default relations ?

Thanks in advance for helping me,

Spiritus44 avatar Apr 13 '20 19:04 Spiritus44

Yes, I just worked through this problem myself:

  • POSTGRES_USER and POSTGRES_PASSWORD must be a SUPERUSER (e.g. postgres)
  • You will need to make sure your pg_hba.conf allows your super user account to login from wherever you are running Wallabag
  • SYMFONY__ENV__DATABASE_USER should not be a SUPERUSER

Hope this helps.

iamwillbar avatar Apr 18 '20 20:04 iamwillbar

I've been trying to accomplish a similar thing with MySQL/MariaDB.

The provisioner requires root database access by default: https://github.com/wallabag/docker/blob/master/root/etc/ansible/entrypoint.yml#L88 However, the environment variable POPULATE_DATABASE=False disables this behaviour and I'm able to run wallabag inside docker and the db on my host (which I provisioned before) without granting db root access to wallabag.

Whisprin avatar Apr 25 '20 11:04 Whisprin

Yes, I just worked through this problem myself:

* `POSTGRES_USER` and `POSTGRES_PASSWORD` must be a `SUPERUSER` (e.g. `postgres`)

* You will need to make sure your `pg_hba.conf` allows your super user account to login from wherever you are running Wallabag

* `SYMFONY__ENV__DATABASE_USER` should **not** be a `SUPERUSER`

Hope this helps.

I have the same problem and I think I've done this like you described but it's still not working. Can somebody please take a look?

My docker-compose.yml:

db: image: postgres container_name: db restart: always networks: - network volumes: - p_db:/var/lib/postgresql/data environment: - POSTGRES_PASSWORD=*superuserpw" - POSTGRES_USER=superuser - POSTGRES_DB=mydb

wallabag: image: wallabag/wallabag container_name: wallabag restart: always networks: - network volumes: - wallabag_images:/var/www/wallabag/web/assets/images environment: - POSTGRES_PASSWORD=superuserpw - POSTGRES_USER=superuser - SYMFONY__ENV__DATABASE_DRIVER=pdo_pgsql - SYMFONY__ENV__DATABASE_DRIVER_CLASS=Wallabag\CoreBundle\Doctrine\DBAL\Driver\CustomPostgreSQLDriver - SYMFONY__ENV__DATABASE_HOST=db - SYMFONY__ENV__DATABASE_PORT=5432 - SYMFONY__ENV__DATABASE_NAME=wallabag - SYMFONY__ENV__DATABASE_USER=walla - SYMFONY__ENV__DATABASE_PASSWORD=pwforwalla - SYMFONY__ENV__LOCALE=de - SYMFONY__ENV__MAILER_HOST=smtp - SYMFONY__ENV__FROM_EMAIL="[email protected]" - SYMFONY__ENV__FOUSER_REGISTRATION=false - SYMFONY__ENV__DOMAIN_NAME="https://wallabag.example.com" - SYMFONY__ENV__REDIS_HOST=redis - VIRTUAL_HOST=wallabag.example.com - LETSENCRYPT_HOST=wallabag.example.com - POPULATE_DATABASE=true depends_on: - db - redis - nginx-proxy - letsencrypt - smtp links: - smtp

I manually added the wallabag database and the walla user to postgres. Maybe that was an error?

If I run docker-compose up -d I get

Starting provisioner... [WARNING]: Found both group and host with same name: localhost Provisioner finished.

When trying to visit wallabag.example.com wallabag outputs:

2020/07/16 09:53:13 [error] 233#233: *1 FastCGI sent in stderr: "PHP message: PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2636PHP message: PHP Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2665PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "wallabag_craue_config_setting" does not exist LINE 1: ...on_1, t0.name AS name_2, t0.value AS value_3 FROM wallabag_c... ^ in /var/www/wallabag/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105 Stack trace: #0 /var/www/wallabag/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOStatement->execute(NULL) #1 /var/www/wallabag/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(847): Doctrine\DBAL\Driver\PDOStatement->execute() #2 /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(712): Doctrine\DBAL\Connection->executeQuery('SELECT t0.secti...', Array, Array) #3 /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php(196): Doctrine\ORM\Persisters\Entity\BasicEntityPersister->load(Array, NULL, NULL, Array, NULL, 1, NULL) #4 /var/w" while reading response header from upstream, client: 172.20.0.4, server: _, request: "GET /login HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "wallabag.wolke29.de" 172.20.0.4 - - [16/Jul/2020:09:53:13 +0000] "GET /login HTTP/1.1" 500 31 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Firefox/78.0"

The postgres container outputs

2020-07-16 09:53:13.926 UTC [373] ERROR: relation "wallabag_craue_config_setting" does not exist at character 77 2020-07-16 09:53:13.926 UTC [373] STATEMENT: SELECT t0.section AS section_1, t0.name AS name_2, t0.value AS value_3 FROM wallabag_craue_config_setting t0 WHERE t0.name = $1 LIMIT 1

I hope somebody can help me out with this. I really don't know what to do next. If you need more information I can provide that too.

Big edit It seems my setup was fine. I needed to manually run docker-compose exec your-wallabag-container /var/www/wallabag/bin/console wallabag:install --env=prod --no-interaction See #182 and this comment

baschi29 avatar Jul 16 '20 10:07 baschi29

I believe creating the database shouldn't be the concern of the wallabag image/container, but instead handled by the postgres container using the provided POSTGRES_DB, POSTGRES_USER and POSTGRES_PASSWORD environment variables ( https://hub.docker.com/_/postgres )

ptman avatar Jul 29 '20 10:07 ptman

Wow this is very weird behavior. I've never seen docker container requesting root password for postgres.

matejdro avatar Jul 30 '20 13:07 matejdro

I've been trying to accomplish a similar thing with MySQL/MariaDB.

The provisioner requires root database access by default: https://github.com/wallabag/docker/blob/master/root/etc/ansible/entrypoint.yml#L88 However, the environment variable POPULATE_DATABASE=False disables this behaviour and I'm able to run wallabag inside docker and the db on my host (which I provisioned before) without granting db root access to wallabag.

@Whisprin : I'm trying to do same setup. Running docker wallabag and use MariaDB 10 on my Synology without success. I've set env.variable POPULATE_DATABASE=False and created an empty MariaDB=wallabag and user=wallabag with phpMyAdmin on Synololgy. What am I doing wrong ?

2020-08-11_105803 2020-08-11_110043

emo1509 avatar Aug 11 '20 09:08 emo1509

POPULATE_DATABASE=false seems to do the trick. But I don't think populating the database should also create the database and user.

ptman avatar Aug 14 '20 08:08 ptman

@emo1509 it looks like you can't connecting the database with the root user (wrong password ?). You have this error message in your log : error

Thanks to all the contributors, I just made a new try with this docker-compose file :

version: '2'
services:
  wallabag:
    build:
      context: .
    image: wallabag:postgresql
    container_name: wallabag
    environment:
      - POSTGRES_USER=<wallabag_admin>
      - POSTGRES_PASSWORD=<<allabag_admin's password>
      - SYMFONY__ENV__DATABASE_DRIVER=pdo_pgsql
      - SYMFONY__ENV__DATABASE_HOST=<pgsql ip>
      - SYMFONY__ENV__DATABASE_PORT=<pgsql port>
      - SYMFONY__ENV__DATABASE_NAME=wallabag
      - SYMFONY__ENV__DATABASE_USER=wallabag
      - SYMFONY__ENV__DATABASE_PASSWORD=<wallabag password>
      - SYMFONY__ENV__DATABASE_CHARSET=utf8
      - SYMFONY__ENV__MAILER_HOST=127.0.0.1
      - SYMFONY__ENV__MAILER_USER=~
      - SYMFONY__ENV__MAILER_PASSWORD=~
      - [email protected]
      - SYMFONY__ENV__DOMAIN_NAME=https://wallabag.mydomain.com
    ports:
      - "127.0.0.1:80:80"```

Before typing a "docker-compose up" i created the <wallabag_admin> user with admin right on the pgsql db, neither the wallabag or the wallabag database was manually created.

Next i run a docker-compose up and i get that error :

$ sudo docker-compose up
Recreating wallabag ... 
Recreating wallabag ... done
Attaching to wallabag
wallabag    | Starting provisioner...
wallabag    | [WARNING]: Found both group and host with same name: localhost
wallabag    | Provisioner finished.

*******I CONNECT ON THE WALLABAG DOMAIN NAME AND GET THIS********

wallabag    | 172.18.0.1 - - [23/Aug/2020:19:15:57 +0000] "GET / HTTP/1.1" 500 31 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:79.0) Gecko/20100101 Firefox/79.0"
wallabag    | 2020/08/23 19:15:57 [error] 265#265: *1 FastCGI sent in stderr: "PHP message: PHP Warning:  "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2636PHP message: PHP Warning:  "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /var/www/wallabag/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php on line 2665PHP message: PHP Fatal error:  Uncaught Symfony\Component\Filesystem\Exception\IOException: Unable to write to the "/var/www/wallabag/var/cache/prod" directory. in /var/www/wallabag/vendor/symfony/symfony/src/Symfony/Component/Filesystem/Filesystem.php:677
wallabag    | Stack trace:
wallabag    | #0 /var/www/wallabag/vendor/symfony/symfony/src/Symfony/Component/Config/ResourceCheckerConfigCache.php(138): Symfony\Component\Filesystem\Filesystem->dumpFile('/var/www/wallab...', '<?php\n\nuse Symf...')
wallabag    | #1 /var/www/wallabag/vendor/symfony/symfony/src/Symfony/Component/Routing/Router.php(301): Symfony\Component\Config\ResourceCheckerConfigCache->write('<?php\n\nuse Symf...', Array)
wallabag    | #2 [internal function]: Symfony\Component\Routing\Router->Symfony\Component\Routing\{closure}(Object(Symfony\Component\Config\ResourceCheckerConfigCache))
wallabag    | #3 /var/www/wallabag/vendor/symfony/symfony/src/Symfony/Component/Config/ResourceCheckerConfigCacheFactory.php(43): call_user_func(Object(Closure), Object(Symfony\Component\Config\ResourceCheckerConfi" while reading response header from upstream, client: 172.18.0.1, server: _, request: "GET / HTTP/1.1", upstream: "fastcgi://127.0.0.1:9000", host: "127.0.0.1"
wallabag    | 172.18.0.1 - - [23/Aug/2020:19:15:58 +0000] "GET /favicon.ico HTTP/1.1" 200 34494 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:79.0) Gecko/20100101 Firefox/79.0"

^CGracefully stopping... (press Ctrl+C again to force)
Stopping wallabag ... done

It's populate a wallabag database and an wallabag user in postgresql. @ptman I confirm that using the default value of POPULATE_DATABASE (True) populate the database and the user.

Unfortunately i'm not able to understand this problem Unable to write to the "/var/www/wallabag/var/cache/prod" directory. in /var/www/wallabag/vendor/symfony/symfony/src/Symfony/Component/Filesystem/Filesystem.php:677. Did I make something wrong ?

Spiritus44 avatar Aug 23 '20 19:08 Spiritus44

I've been trying to accomplish a similar thing with MySQL/MariaDB. The provisioner requires root database access by default: https://github.com/wallabag/docker/blob/master/root/etc/ansible/entrypoint.yml#L88 However, the environment variable POPULATE_DATABASE=False disables this behaviour and I'm able to run wallabag inside docker and the db on my host (which I provisioned before) without granting db root access to wallabag.

@Whisprin : I'm trying to do same setup. Running docker wallabag and use MariaDB 10 on my Synology without success. I've set env.variable POPULATE_DATABASE=False and created an empty MariaDB=wallabag and user=wallabag with phpMyAdmin on Synololgy. What am I doing wrong ?

2020-08-11_105803 2020-08-11_110043

@emo1509 I have the same issue, connecting remote mysql. Did you fix the issue? i have tried POPULATE_DATABASE=False but still getting issue. Thanks

ghost avatar Dec 23 '20 06:12 ghost

I've been trying to accomplish a similar thing with MySQL/MariaDB. The provisioner requires root database access by default: https://github.com/wallabag/docker/blob/master/root/etc/ansible/entrypoint.yml#L88 However, the environment variable POPULATE_DATABASE=False disables this behaviour and I'm able to run wallabag inside docker and the db on my host (which I provisioned before) without granting db root access to wallabag.

@Whisprin : I'm trying to do same setup. Running docker wallabag and use MariaDB 10 on my Synology without success. I've set env.variable POPULATE_DATABASE=False and created an empty MariaDB=wallabag and user=wallabag with phpMyAdmin on Synololgy. What am I doing wrong ? 2020-08-11_105803 2020-08-11_110043

@emo1509 I have the same issue, connecting remote mysql. Did you fix the issue? i have tried POPULATE_DATABASE=False but still getting issue. Thanks

No, I could not fix the problem. Still have the same error. I'm currently running the docker version with SQLite. Waiting for someone who can find the solution to connect to MariaDB 10 on Synology NAS.

emo1509 avatar Dec 23 '20 10:12 emo1509

@emo1509 i have fixed it. you need to try 2 things in order to use remote mariadb.

  1. POPULATE_DATABASE=False (set as environment property)
  2. sudo docker exec -t wallabag /var/www/wallabag/bin/console wallabag:install --env=prod --no-interaction (run this command in terminal : this command basically will create tables as we have set dont do it in 1st step.)

I hope it helps. Cheers. I know its a work around. but until this issue with remote db fixed we need to use this work around.

ghost avatar Dec 23 '20 11:12 ghost

2. sudo docker exec -t wallabag /var/www/wallabag/bin/console wallabag:install --env=prod --no-interaction

Thank you very much !! This solved my problem and it created the wallabag tables in MariaDB10 on my Synology. So 2020 still ends in a climax for me :-) wallabag10

emo1509 avatar Dec 23 '20 14:12 emo1509