search

pgadmin-org / pgadmin4

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
https://www.pgadmin.org
Other
2.41k stars 632 forks source link

Cannot start PGAdmin docker with PostgreSQL metadata back-end. #5941

Closed maudrid closed 1 year ago

maudrid commented 1 year ago

Describe the bug

When I configure PGAdmin to use PostgreSQL instead of SQLite, it cannot start up.

To Reproduce

Steps to reproduce the behavior:

  1. Use this compose file:

    
services:

pgadmin:
image: dpage/pgadmin4:6.21
container_name: pgadmin
depends_on:
  pgadmin-postgres:
    condition: service_healthy
environment:
  PGADMIN_CONFIG_CONFIG_DATABASE_URI: '''postgresql://pgadmin:pgadmin@pgadmin-postgres:5432/pgadmin'''
  PGADMIN_DEFAULT_EMAIL: qa@qa.com
  PGADMIN_DEFAULT_PASSWORD: qa.qa
restart: unless-stopped

pgadmin-postgres:
container_name: pgadmin-postgres
environment:
  PGDATA: /var/lib/postgresql/data/
  PGUSER: pgadmin
  POSTGRES_DB: pgadmin
  POSTGRES_INITDB_ARGS: --data-checksums
  POSTGRES_PASSWORD: pgadmin
  POSTGRES_PORT: "5432"
  POSTGRES_USER: pgadmin
healthcheck:
  test:
  - CMD-SHELL
  - sh -c 'pg_isready -U pgadmin'
  timeout: 30s
  interval: 10s
  retries: 3
  start_period: 15s
image: postgres:14.6
restart: unless-stopped
2. run `docker-compose up`
3. See the errors

**Expected behavior**

PGAdmin should successfully create a new schema in the empty database and then start.

**Error message**

From the database:

2023-03-13 16:02:21.441 UTC [1] LOG: database system is ready to accept connections 2023-03-13 16:02:37.686 UTC [80] ERROR: relation "version" does not exist at character 75 2023-03-13 16:02:37.686 UTC [80] STATEMENT: SELECT version.name AS version_name, version.value AS version_value FROM version WHERE version.name = 'ConfigDB' LIMIT 1 2023-03-13 16:02:38.487 UTC [81] ERROR: syntax error at or near "user" at character 13 2023-03-13 16:02:38.487 UTC [81] STATEMENT: ALTER TABLE user RENAME TO user_old 2023-03-13 16:02:38.494 UTC [81] ERROR: current transaction is aborted, commands ignored until end of transaction block 2023-03-13 16:02:38.494 UTC [81] STATEMENT: SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relkind in ('r', 'p') 2023-03-13 16:02:38.499 UTC [80] ERROR: relation "keys" does not exist at character 63 2023-03-13 16:02:38.499 UTC [80] STATEMENT: SELECT keys.name AS keys_name, keys.value AS keys_value FROM keys WHERE keys.name = 'CSRF_SESSION_KEY' LIMIT 1

From PGAdmin:

2023-03-13 16:02:38,495: ERROR pgadmin: (psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block

[SQL: SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = %(schema)s AND c.relkind in ('r', 'p')] [parameters: {'schema': 'public'}] (Background on this error at: https://sqlalche.me/e/14/2j85) Traceback (most recent call last): File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context NOTE: Configuring authentication for SERVER mode.

self.dialect.do_execute(

File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) psycopg2.errors.UndefinedTable: relation "keys" does not exist LINE 2: FROM keys ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/pgadmin4/run_pgadmin.py", line 4, in from pgAdmin4 import app File "/pgadmin4/pgAdmin4.py", line 93, in app = create_app() File "/pgadmin4/pgadmin/init.py", line 498, in create_app name='CSRF_SESSION_KEY').first().value File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2824, in first return self.limit(1)._iter().first() File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2916, in _iter result = self.session.execute( File "/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1714, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection return connection._execute_clauseelement( File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapiexception util.raise( File "/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_ raise exception File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "keys" does not exist LINE 2: FROM keys 



**Desktop:**
 - OS: Ubuntu 22.04
 - Version: 4:6.21
 - Mode: Server
 - Browser N/A
 - Package type: Container
maudrid commented 1 year ago

When using image dpage/pgadmin4:6.20, there is no error.

MHenn1g commented 1 year ago

We encountered the same error when utilizing an empty database...furthermore, if the database specified in
CONFIG_DATABASE_URI already contained data and a migration was performed, this process exits with the following error

2023-03-14 11:19:17.441 CET [31372] user=<username>,db=<database>,app=[unknown],client=10.197.93.82 ERROR:  syntax error at or near "user" at character 13
2023-03-14 11:19:17.441 CET [31372] user=<username>,db=<database>,app=[unknown],client=10.197.93.82 STATEMENT:  ALTER TABLE user RENAME TO user_old
akshay-joshi commented 1 year ago

@maudrid

In accordance with the design, the database, and schema must exist before using it. pgAdmin does not parse the URL and check if the schema exists or not. Can you please try creating the schema and then check whether it is working or not?

maudrid commented 1 year ago

I can do that, but why would pgadmin4:6.20 not have the same issue, if it is according to design? The schema used by pgadmin4:6.20 is "public" and this schema already exists on a new database.

akshay-joshi commented 1 year ago

@maudrid

I am currently working on the issue and analyzing it.

akshay-joshi commented 1 year ago

Hi @maudrid and @MHenn1g

I have fixed the issue as a part of commit, fix will be available in next major release 7.0 which will be released on 6th April 2023.

maudrid commented 1 year ago

Thanks for the update. Great news.

On Mon, Mar 20, 2023, 12:26 Akshay Joshi @.***> wrote:

Hi @maudrid https://github.com/maudrid and @MHenn1g https://github.com/MHenn1g

I have fixed the issue as a part of commit https://github.com/pgadmin-org/pgadmin4/commit/a144815201444c47ec6ba3e9e3b3f6dbd5464d70, fix will be available in next major release 7.0 which will be released on 6th April 2023.

— Reply to this email directly, view it on GitHub https://github.com/pgadmin-org/pgadmin4/issues/5941#issuecomment-1476055753, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACJQ647CJOGRQ2ROG6KENK3W5A5GNANCNFSM6AAAAAAVZJLU7U . You are receiving this because you were mentioned.Message ID: @.***>

yogeshmahajan-1903 commented 1 year ago

This is working on snapshot build docker pull dpage/pgadmin4:snapshot.

maudrid commented 3 months ago

@akshay-joshi I'm running into this issue again on pgadmin4 v8.7. Postrgresql log:

2024-06-10 15:07:46.658 UTC [2233] ERROR:  relation "version" does not exist at character 75
2024-06-10 15:07:46.658 UTC [2233] STATEMENT:  SELECT version.name AS version_name, version.value AS version_value 
    FROM version 
    WHERE version.name = $1::VARCHAR 
     LIMIT $2::INTEGER
2024-06-10 15:07:48.882 UTC [2233] ERROR:  relation "preferences" does not exist at character 8
2024-06-10 15:07:48.882 UTC [2233] STATEMENT:  UPDATE preferences SET name=$1::VARCHAR WHERE preferences.name = $2::VARCHAR

PGadmin log:

NOTE: Configuring authentication for SERVER mode.
Traceback (most recent call last):
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context

    self.dialect.do_execute(
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 737, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "preferences" does not exist
LINE 1: UPDATE preferences SET name=$1::VARCHAR WHERE preferences.na...
               ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/pgadmin4/run_pgadmin.py", line 4, in <module>
    from pgAdmin4 import app
  File "/pgadmin4/pgAdmin4.py", line 103, in <module>
    app = create_app()
          ^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/__init__.py", line 471, in create_app
    run_migration_for_others()
  File "/pgadmin4/pgadmin/__init__.py", line 456, in run_migration_for_others
    db_upgrade(app)
  File "/pgadmin4/pgadmin/setup/db_upgrade.py", line 25, in db_upgrade
    flask_migrate.upgrade(migration_folder)
  File "/venv/lib/python3.12/site-packages/flask_migrate/__init__.py", line 111, in wrapped
    f(*args, **kwargs)
  File "/venv/lib/python3.12/site-packages/flask_migrate/__init__.py", line 200, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/venv/lib/python3.12/site-packages/alembic/command.py", line 403, in upgrade
    script.run_env()
  File "/venv/lib/python3.12/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/pgadmin4/pgadmin/setup/../../migrations/env.py", line 95, in <module>
    run_migrations_online()
  File "/pgadmin4/pgadmin/setup/../../migrations/env.py", line 87, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/venv/lib/python3.12/site-packages/alembic/runtime/environment.py", line 948, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/venv/lib/python3.12/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "/pgadmin4/migrations/versions/ac2c2e27dc2d_.py", line 23, in upgrade
    Preferences.name == 'execute_query').update({'name': 'execute_script'})
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/venv/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 3251, in update
    result: CursorResult[Any] = self.session.execute(
                                ^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2236, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1624, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 737, in execute
    raise ex.with_traceback(None)
maudrid commented 3 months ago

Pgadmin 8.7 and up has this issue, V8.6 works as expected.

maximemoreillon commented 3 months ago

I am also encountering this issue with pgAdmin 8.8

vivekpd15 commented 3 months ago

Facing the same. 8.6 works well.