# Prepare PostgreSQL

Before installing the collector, configure two things on every PostgreSQL server: a read-only login
with the required grants and the `pg_stat_statements` extension.

## Database grants

Create a dedicated read-only login for the collector.  Grant it the following privileges:

```sql
CREATE ROLE dbg_readonly LOGIN PASSWORD 'replace-me';
GRANT pg_monitor TO dbg_readonly;                          -- cluster-wide stats
GRANT USAGE ON SCHEMA public TO dbg_readonly;              -- per schema you want captured
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbg_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO dbg_readonly;                  -- tables created later
```

Replace `app_owner` with the role that creates your tables, and repeat the statement for each one.
**`ALTER DEFAULT PRIVILEGES` without `FOR ROLE` covers only tables created by the role running it.**
If your application creates tables as its own role and you ran the command as `postgres`, new
tables are not covered, and you land in the failure below the first time your application migrates.
List the roles that own tables with:

```sql
SELECT DISTINCT tableowner FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema');
```

`CREATE ROLE` and `GRANT pg_monitor` are cluster-wide, so run those commands once.  The `USAGE` and
`SELECT` grants apply only to the database you are connected to and the schema you name.  Run them in every
database you list in `databases`, once per schema.  If you grant them in only one database, the collector
fails as shown below.

`pg_monitor` alone is not enough, and the failure is quiet: the collector connects and reports
metrics normally, then fails every schema capture with `permission denied for table …`, logged as
a warning that retries forever.
Schema capture runs `pg_dump`, which must `SELECT` every table it reads.

## pg_stat_statements

`pg_stat_statements` is required for full functionality.  The extension records how long each statement takes
and how often it runs.  Everything DBGorilla tells you about query performance comes from it.

Without it, the collector connects and reports host metrics, database size, table statistics and cluster
topology.  You lose every query-level feature: slow query analysis, query-level recommendations, and
anything that ranks statements by cost.  The collector does not report the missing extension, so check for it
before you install:

```sql
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
SHOW shared_preload_libraries;
```

Loading it takes a **full server restart**, not a reload, because it hooks into the executor at
startup.
One caution before you run it: **`ALTER SYSTEM SET` replaces the whole value, it does not append.**
Read the current setting first and include everything already there.  Otherwise the next restart
unloads that extension.

```sql
SHOW shared_preload_libraries;
-- if it is empty:
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- if it already lists something, repeat every existing entry, for example:
ALTER SYSTEM SET shared_preload_libraries = 'pg_cron,pg_stat_statements';

-- restart PostgreSQL, then:
CREATE EXTENSION pg_stat_statements;
```

On managed platforms, set it through the provider: a parameter group on AWS RDS and Aurora, server
parameters on Azure Database for PostgreSQL, or database flags on Cloud SQL.  Each still needs a restart.

:::note[Restart before you install]

The restart is the only collector installation step that interrupts your database.  Do it first.
Nothing after it interrupts the database.
:::

## Grant the right permissions

The statements are in [Database grants](#database-grants). `pg_monitor` alone is not
enough: with only that grant the collector connects and reports metrics, but captures no schema,
and nothing surfaces an error.

`pg_monitor` covers the stats views the metrics pipeline reads, but grants nothing on your tables,
and schema capture shells out to `pg_dump`, which has to `SELECT` every table it dumps.  A role with
only `pg_monitor` connects and reports metrics fine, then fails every schema pass with
`permission denied for table …`, logged as a warning that retries forever, so the collector looks
healthy while capturing no schema at all.

Repeat the last three statements in [Database grants](#database-grants) for every schema and database you want captured.