Error when using different databases for `--sources` and `--metrics`

[0xgouda]

Describe the bug
While working on #1287 I tried to use a different database for --sources and --metrics and got the following error:

$ go run ./cmd/pgwatch --sources="postgres://postgres:postgres@localhost:5437/pgwatch" --metrics="postgres://postgres:postgres@localhost:5437/postgres" --sink="postgres://postgres:postgres@localhost:5437/postgres"                       
2026-03-10 16:13:48.568 [INFO] [sink:postgres] [db:postgres] initialising measurements database...
2026-03-10 16:13:48.576 [INFO] [sink:postgres] [db:postgres] measurements sink is activated
2026-03-10 16:13:48.577 [ERROR] [sql:select /* pgwatch_generated */
        name, 
        "group", 
        dbtype, 
        connstr,
        coalesce(config, '{}'::jsonb) as config, 
        coalesce(config_standby, '{}'::jsonb) as config_standby,
        coalesce(preset_config, '') as preset_config,
        coalesce(preset_config_standby, '') as preset_config_standby,
        coalesce(include_pattern, '') as include_pattern, 
        coalesce(exclude_pattern, '') as exclude_pattern,
        coalesce(custom_tags, '{}'::jsonb) as custom_tags, 
        only_if_master,
        is_enabled
from
        pgwatch.source] [err:ERROR: relation "pgwatch.source" does not exist (SQLSTATE 42P01)] [name:stmtcache_37fa59bb0cd8aa1f372ee1720a0dac7caff08787cceb9e71] [pid:196912] [time:230.895µs] Prepare
2026-03-10 16:13:48.577 [ERROR] [sql:select /* pgwatch_generated */
        name, 
        "group", 
        dbtype, 
        connstr,
        coalesce(config, '{}'::jsonb) as config, 
        coalesce(config_standby, '{}'::jsonb) as config_standby,
        coalesce(preset_config, '') as preset_config,
        coalesce(preset_config_standby, '') as preset_config_standby,
        coalesce(include_pattern, '') as include_pattern, 
        coalesce(exclude_pattern, '') as exclude_pattern,
        coalesce(custom_tags, '{}'::jsonb) as custom_tags, 
        only_if_master,
        is_enabled
from
        pgwatch.source] [args:[]] [err:ERROR: relation "pgwatch.source" does not exist (SQLSTATE 42P01)] [pid:196912] [time:267.787µs] Query
2026-03-10 16:13:48.577 [ERROR] [error:ERROR: relation "pgwatch.source" does not exist (SQLSTATE 42P01)] could not refresh active sources, using last valid cache
2026-03-10 16:13:48.582 [INFO] [metrics:74] [presets:14] metrics and presets refreshed

This also happens when supplying a database conn str for --sources but using yaml files for --metrics.

To Reproduce

1. run pgwatch with 2 different new databases for --sources and --metrics

Expected behavior
The docs state that we should be able to use any combination of storage options for metrics and sources

[Mazen050]

Hello, looks like this happens because pgwatch does not automatically create the necessary tables and indexes in the database when it starts. (contrary to what the docs says)

running:

go run ./cmd/pgwatch --sources="postgres://postgres:postgres@localhost:5437/pgwatch" config init

first then running pgwatch normally works. If you are not working on this can I take?

[0xgouda]

Running config init won't change anything.

No, I will work on it.

[Mazen050]

Running config init won't change anything.

weird running config init fixed it for me. maybe the bug is deeper than what I thought

[sahilkirad]

should i help to solve this

[0xgouda]

No, thanks.