How to use an external database for pgAdmin user settings

November 18, 2022

The user settings used by pgAdmin are stored in a SQLite database. In this database, many settings are stored, such as preferences, user accounts, auto-discovered servers, and many more.

As SQLite is a file-based database and it can be anywhere in the file system, so it is not designed to take care of failures (no HA support). Example: In environments such as Kubernetes it may be useful to use an alternate backend to avoid using SQLite on non-ephemeral storage and to allow HA of the settings database.

In order to prevent this, pgAdmin now supports storing user settings in an external database using the new 'CONFIG_DATABASE_URI' parameter in the config.py file.

Another reason to implement this feature is users wanted to be able to store their settings in PostgreSQL for the convenience.

How to use SQLite Database

In order to use SQLite Database, make sure CONFIG_DATABASE_URI parameter is set to an empty string like ''. By default it is set to an empty string in the config.py so if you would like to use SQLite database then no need to change anything.

How to use External Database

In order to use an external database, make sure the CONFIG_DATABASE_URI parameter is set in the specified format "dialect+driver://username:password@host:port/database". Note at this time we only support the use of PostgreSQL and EDB Advanced Server as external databases, both of which use the "postgresql" dialect. Other databases may work, but are not supported.

Note: It is required to create the database in advance.

How to use PostgreSQL as external database

Following are the formats to use PostgreSQL as an external database.

Basic syntax

postgresql://username:password@host:port/database

Using a specific schema

postgresql://username:password@host:port/database?options=-csearch_path=<schema name>

Note: It is required to create the database and schema in advance.

In PostgreSQL, using pgpass file is a common way to store connection information instead of specifying passwords in URIs.

As soon as you have set the CONFIG_DATABASE_URI, run setup.py in server mode or start pgAdmin in desktop mode to automatically create the database schema and start using it.

Conclusion

Use the external database server to save the pgAdmin 4 user settings in situations where SQLite is not preferred. It is useful when adding HA support for the configuration database, or to avoid using SQLite on non-ephemeral storage.
 

Share this