pgAdmin: sqlite3 OperationalError: no such column: server

Question

I'm working with Postgres and pgAdmin. After updating pgAdmin to the newest version (6.21), I get this error:

sqlite3 OperationalError: no such column: server...

At the bottom of the error there is a link (here).

This error occurs after entering the master password when clicking on the "server"-button (basically, when I try to open the list of the servers).

I've tried to reinstall pgAdmin, but nothing works. I'm new to databases, Postgres and pgAdmin. Any suggestions on what I could try?

Answer 1

I´ve ran into the same problem, but the cause was maybe different. I have used two versions of pgAdmins, the newest one v7 and the v5.7. I got no problem with logging to local and remote servers into the v7, but v5.7 logs the error you described. In the end the reseting master password did not work either. My guess, but sadly not confirmed, is the newest version of pgadmin edited some configuration files that was not aplicable for the old one...

At the end what helped me was reinstalling pgadmin, removing all data linked with postgres (all folder of PQ in Program Files) and removing cache data in user folder. Specially db data saved in pgadmin4.db, just delete the specific folder (in W11 is located in AppData\Roaming\pgadmin\pgadmin4.db). The pgadmin will automatically generate the new one.

The original log error:

[SQL: SELECT server.id AS server_id, server.user_id AS server_user_id, server.servergroup_id AS server_servergroup_id, server.name AS server_name, server.host AS server_host, server.hostaddr AS server_hostaddr, server.port AS server_port, server.maintenance_db AS server_maintenance_db, server.username AS server_username, server.password AS server_password, server.save_password AS server_save_password, server.role AS server_role, server.ssl_mode AS server_ssl_mode, server.comment AS server_comment, server.discovery_id AS server_discovery_id, server.db_res AS server_db_res, server.passfile AS server_passfile, server.sslcert AS server_sslcert, server.sslkey AS server_sslkey, server.sslrootcert AS server_sslrootcert, server.sslcrl AS server_sslcrl, server.sslcompression AS server_sslcompression, server.bgcolor AS server_bgcolor, server.fgcolor AS server_fgcolor, server.service AS server_service, server.connect_timeout AS server_connect_timeout, server.use_ssh_tunnel AS server_use_ssh_tunnel, server.tunnel_host AS server_tunnel_host, server.tunnel_port AS server_tunnel_port, server.tunnel_username AS server_tunnel_username, server.tunnel_authentication AS server_tunnel_authentication, server.tunnel_identity_file AS server_tunnel_identity_file, server.tunnel_password AS server_tunnel_password, server.shared AS server_shared, server.kerberos_conn AS server_kerberos_conn, servergroup_1.id AS servergroup_1_id, servergroup_1.user_id AS servergroup_1_user_id, servergroup_1.name AS servergroup_1_name 
    FROM server LEFT OUTER JOIN servergroup AS servergroup_1 ON servergroup_1.id = server.servergroup_id 
    WHERE (server.user_id = ? OR server.shared = 1) AND server.servergroup_id = ?]
    [parameters: (1, 1)]
    (Background on this error at: http://sqlalche.me/e/13/e3q8)
    Traceback (most recent call last):
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
        self.dialect.do_execute(
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
        cursor.execute(statement, parameters)
    sqlite3.OperationalError: no such column: server.hostaddr
    
    The above exception was the direct cause of the following exception:
    
    Traceback (most recent call last):
      File "C:\~\python\lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
        rv = self.dispatch_request()
      File "C:\~\python\lib\site-packages\flask\app.py", line 1936, in dispatch_request
        return self.view_functions[rule.endpoint](**req.view_args)
      File "C:\~\python\lib\site-packages\flask\views.py", line 89, in view
        return self.dispatch_request(*args, **kwargs)
      File "C:\~\web\pgadmin\browser\utils.py", line 315, in dispatch_request
        return method(*args, **kwargs)
      File "C:\~\web\pgadmin\browser\utils.py", line 350, in children
        children = self.get_children_nodes(*args, **kwargs)
      File "C:\~\web\pgadmin\browser\utils.py", line 371, in get_children_nodes
        children.extend(module.get_nodes(*args, **kwargs))
      File "C:\~\web\pgadmin\browser\server_groups\servers\__init__.py", line 208, in get_nodes
        servers = self.get_servers(servers, hide_shared_server, gid)
      File "C:\~\web\pgadmin\browser\server_groups\servers\__init__.py", line 174, in get_servers
        for server in all_servers:
      File "C:\~\python\lib\site-packages\sqlalchemy\orm\query.py", line 3535, in __iter__
        return self._execute_and_instances(context)
      File "C:\~\python\lib\site-packages\sqlalchemy\orm\query.py", line 3560, in _execute_and_instances
        result = conn.execute(querycontext.statement, self._params)
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
        return meth(self, multiparams, params)
      File "C:\~\python\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
        return connection._execute_clauseelement(self, multiparams, params)
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
        ret = self._execute_context(
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
        self._handle_dbapi_exception(
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapi_exception
        util.raise_(
      File "C:\~\python\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
        raise exception
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
        self.dialect.do_execute(
      File "C:\~\python\lib\site-packages\sqlalchemy\engine\default.py", line 608, in do_execute
        cursor.execute(statement, parameters)

Answer 2

I've got the same mistake. You can handle the problem with a trick (no usage of masterpassword), but it's not a solution of the cause.

Please be sure about the consequences and if you really want to avoid the masterpassword! You can find the documentation is here: https://www.pgadmin.org/docs/pgadmin4/latest/master_password.html

To disable the master password these are the steps:

1. locate your config.py file (e.g. C:\Program Files\pgAdmin 4\v7\web)
2. In this directory create a new file named "Config_local.py" and add the line MASTER_PASSWORD_REQUIRED = False. If the "Config_local.py" already exist, edit it, and add the line at the bottom of the file. Save the file.
3. Close PGAdmin4 and reopen it. You should now be able to access PGAdmin without the master password.

But maybe someone can REALLY solve the problem, I've made a screeshot of the message (I accures for a few seconds and disappears): pgadmin_sqlite3_OperationalError