search

buggregator / server

Buggregator is a lightweight, standalone server that offers a range of debugging features for PHP applications.
https://buggregator.dev/
Other
648 stars 23 forks source link

[BUG] External databases support with PostgreSQL #133

Closed gander closed 4 months ago

gander commented 4 months ago 
buggregator-1  | [INFO] RoadRunner server started; version: 2023.3.7, buildtime: 2023-11-30T19:15:58+0000
buggregator-1  | [INFO] sdnotify: not notified
buggregator-1  | 2024-04-12T08:42:49+0000       ERROR   app             Cycle\Database\Exception\StatementException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "events" does not exist LINE 2: FROM "events" AS "event"              ^ in /app/vendor/cycle/database/src/Driver/Postgres/PostgresDriver.php at line 246 [] []
buggregator-1  | 
buggregator-1  | 2024-04-12T08:42:49+0000       ERROR   app             Cycle\Database\Exception\StatementException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "events" does not exist LINE 2: FROM "events" AS "event"              ^ in /app/vendor/cycle/database/src/Driver/Postgres/PostgresDriver.php at line 246 [] []
buggregator-1  | 
buggregator-1  | 2024-04-12T08:42:49+0000       ERROR   app             Cycle\Database\Exception\StatementException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "events" does not exist LINE 2: FROM "events" AS "event"              ^ in /app/vendor/cycle/database/src/Driver/Postgres/PostgresDriver.php at line 246 [] []
buggregator-1  | 
buggregator-1  | 2024-04-12T08:42:49+0000       ERROR   app             Cycle\Database\Exception\StatementException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "events" does not exist LINE 2: FROM "events" AS "event"              ^ in /app/vendor/cycle/database/src/Driver/Postgres/PostgresDriver.php at line 246 [] []
services:
    buggregator:
        image: ghcr.io/buggregator/server:latest
        ports:
            - 127.0.0.1:8000:8000
        environment:
            PERSISTENCE_DRIVER: database
            DB_DRIVER: pgsql
            DB_DATABASE: buggregator
            DB_HOST: buggregator-pgsql
            DB_PORT: 5432
            DB_USERNAME: buggregator
            DB_PASSWORD: buggregator

    buggregator-pgsql:
        image: postgres:latest
        ports:
            - 5432:5432
        environment:
            POSTGRES_DB: buggregator
            POSTGRES_USER: buggregator
            POSTGRES_PASSWORD: buggregator
butschster commented 4 months ago

Hi. Could you also try to exec command php app.php migrate --force ?

gander commented 4 months ago 
docker compose exec -it buggregator php app.php migrate --force
Migration 0_default_create_events was successfully executed.

But errors still occurs.

butschster commented 4 months ago

Hello @gander,

I hope this message finds you well. I wanted to let you know that I've recently released a new version which should address the issue we discussed. When you have a moment, could you please check if everything is working as expected?

Thank you so much for your time.

gander commented 4 months ago

Fresh:

buggregator-1  | 2024-04-15T12:39:33+0000       ERROR   server          process wait    {"error": "exit status 1"}
buggregator-1  | [INFO] RoadRunner server started; version: 2023.3.7, buildtime: 2023-11-30T19:15:58+0000
buggregator-1  | [INFO] sdnotify: not notified
buggregator-1  | 2024-04-15T12:41:31+0000       ERROR   app             Cycle\Database\Exception\StatementException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "events" does not exist LINE 2: FROM "events" AS "event"              ^ in /app/vendor/cycle/database/src/Driver/Postgres/PostgresDriver.php at line 246 [] []
buggregator-1  | 
buggregator-1  | 2024-04-15T12:41:31+0000       ERROR   app             Cycle\Database\Exception\StatementException: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "events" does not exist LINE 2: FROM "events" AS "event"              ^ in /app/vendor/cycle/database/src/Driver/Postgres/PostgresDriver.php at line 246 [] []

Migration:

docker compose exec -it buggregator php app.php migrate --force
# Migration 0_default_create_events was successfully executed.

After:

buggregator-1  | [INFO] RoadRunner server started; version: 2023.3.7, buildtime: 2023-11-30T19:15:58+0000
buggregator-1  | [INFO] sdnotify: not notified
gander commented 4 months ago

This migration should be done during initialization. So far, I haven't seen any instructions about this migration anywhere.

butschster commented 4 months ago

This migration should be done during initialization. So far, I haven't seen any instructions about this migration anywhere.

There is no need to run migrations. php app.php migrate --force will be executed when the driver is db.

When RR is started, it runs the on_init command https://github.com/buggregator/server/blob/master/.rr-prod.yaml#L10

Do you use 1.7.3 version?

gander commented 4 months ago

Do you use 1.7.3 version?

yes.

I do docker compose pull before start testing:

services:
    buggregator:
        image: ghcr.io/buggregator/server:latest
        ports:
            - 127.0.0.1:8000:8000
        environment:
            PERSISTENCE_DRIVER: database
            DB_DRIVER: pgsql
            DB_DATABASE: buggregator
            DB_HOST: buggregator-db-pg
            DB_PORT: 5432
            DB_USERNAME: buggregator
            DB_PASSWORD: buggregator

    buggregator-db-pg:
        image: postgres:latest
        ports:
            - 5432:5432
        environment:
            POSTGRES_DB: buggregator
            POSTGRES_USER: buggregator
            POSTGRES_PASSWORD: buggregator
butschster commented 4 months ago

I think there can be a problem thaat buggregator starts before database service and migrations fails. You can check console on buggregator startup and see errors

gander commented 4 months ago

The full solution, is using healthcheck and pg_isready:

services:
    buggregator:
        image: ghcr.io/buggregator/server:latest
        depends_on:
            buggregator-database:
                condition: service_healthy
        ports:
            - 127.0.0.1:8000:8000
        environment:
            PERSISTENCE_DRIVER: database
            DB_DRIVER: pgsql
            DB_DATABASE: buggregator
            DB_HOST: buggregator-database
            DB_PORT: 5432
            DB_USERNAME: buggregator
            DB_PASSWORD: buggregator

    buggregator-database:
        image: postgres:latest
        healthcheck:
            test: [ "CMD-SHELL", "pg_isready --username=buggregator --dbname=buggregator" ]
            interval: 1s
            timeout: 1s
            retries: 15
        environment:
            POSTGRES_DB: buggregator
            POSTGRES_USER: buggregator
            POSTGRES_PASSWORD: buggregator
buggregator-database-1  | The files belonging to this database system will be owned by user "postgres".
buggregator-database-1  | This user must also own the server process.
buggregator-database-1  | 
buggregator-database-1  | The database cluster will be initialized with locale "en_US.utf8".
buggregator-database-1  | The default database encoding has accordingly been set to "UTF8".
buggregator-database-1  | The default text search configuration will be set to "english".
buggregator-database-1  | 
buggregator-database-1  | Data page checksums are disabled.
buggregator-database-1  | 
buggregator-database-1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
buggregator-database-1  | creating subdirectories ... ok
buggregator-database-1  | selecting dynamic shared memory implementation ... posix
buggregator-database-1  | selecting default max_connections ... 100
buggregator-database-1  | selecting default shared_buffers ... 128MB
buggregator-database-1  | selecting default time zone ... Etc/UTC
buggregator-database-1  | creating configuration files ... ok
buggregator-database-1  | running bootstrap script ... ok
buggregator-database-1  | performing post-bootstrap initialization ... ok
buggregator-database-1  | syncing data to disk ... ok
buggregator-database-1  | 
buggregator-database-1  | 
buggregator-database-1  | Success. You can now start the database server using:
buggregator-database-1  | 
buggregator-database-1  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
buggregator-database-1  | 
buggregator-database-1  | initdb: warning: enabling "trust" authentication for local connections
buggregator-database-1  | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
buggregator-database-1  | waiting for server to start....2024-04-15 15:04:18.449 UTC [48] LOG:  starting PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
buggregator-database-1  | 2024-04-15 15:04:18.451 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
buggregator-database-1  | 2024-04-15 15:04:18.459 UTC [51] LOG:  database system was shut down at 2024-04-15 15:04:18 UTC
buggregator-database-1  | 2024-04-15 15:04:18.465 UTC [48] LOG:  database system is ready to accept connections
buggregator-database-1  |  done
buggregator-database-1  | server started
buggregator-database-1  | CREATE DATABASE
buggregator-database-1  | 
buggregator-database-1  | 
buggregator-database-1  | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
buggregator-database-1  | 
buggregator-database-1  | waiting for server to shut down....2024-04-15 15:04:18.622 UTC [48] LOG:  received fast shutdown request
buggregator-database-1  | 2024-04-15 15:04:18.624 UTC [48] LOG:  aborting any active transactions
buggregator-database-1  | 2024-04-15 15:04:18.627 UTC [48] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
buggregator-database-1  | 2024-04-15 15:04:18.628 UTC [49] LOG:  shutting down
buggregator-database-1  | 2024-04-15 15:04:18.630 UTC [49] LOG:  checkpoint starting: shutdown immediate
buggregator-database-1  | 2024-04-15 15:04:18.680 UTC [49] LOG:  checkpoint complete: wrote 923 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.020 s, sync=0.025 s, total=0.053 s; sync files=301, longest=0.008 s, average=0.001 s; distance=4257 kB, estimate=4257 kB; lsn=0/1913078, redo lsn=0/1913078
buggregator-database-1  | 2024-04-15 15:04:18.685 UTC [48] LOG:  database system is shut down
buggregator-database-1  |  done
buggregator-database-1  | server stopped
buggregator-database-1  | 
buggregator-database-1  | PostgreSQL init process complete; ready for start up.
buggregator-database-1  | 
buggregator-database-1  | 2024-04-15 15:04:18.754 UTC [1] LOG:  starting PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
buggregator-database-1  | 2024-04-15 15:04:18.755 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
buggregator-database-1  | 2024-04-15 15:04:18.755 UTC [1] LOG:  listening on IPv6 address "::", port 5432
buggregator-database-1  | 2024-04-15 15:04:18.758 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
buggregator-database-1  | 2024-04-15 15:04:18.764 UTC [64] LOG:  database system was shut down at 2024-04-15 15:04:18 UTC
buggregator-database-1  | 2024-04-15 15:04:18.768 UTC [1] LOG:  database system is ready to accept connections
buggregator-1           | [INFO] RoadRunner server started; version: 2023.3.7, buildtime: 2023-11-30T19:15:58+0000
buggregator-1           | [INFO] sdnotify: not notified
butschster commented 4 months ago

Thanks for help!

gander commented 4 months ago

This works:

services:
    buggregator:
        image: ghcr.io/buggregator/server:latest
        depends_on:
            buggregator-database:
                condition: service_healthy
        ports:
            - 127.0.0.1:8000:8000
        environment:
            PERSISTENCE_DRIVER: database
            DB_DRIVER: pgsql
            DB_DATABASE: buggregator
            DB_HOST: buggregator-database
            DB_PORT: 5432
            DB_USERNAME: buggregator
            DB_PASSWORD: buggregator

    buggregator-database:
        image: postgres:latest
        healthcheck:
            test: [ "CMD-SHELL", "pg_isready --username=buggregator --dbname=buggregator" ]
            interval: 3s
            timeout: 3s
            retries: 1
        environment:
            POSTGRES_DB: buggregator
            POSTGRES_USER: buggregator
            POSTGRES_PASSWORD: buggregator
butschster commented 4 months ago

Thank you!