> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need. # Metadata Database The metadata database is the PostgreSQL database where Amp keeps its state: the datasets you registered and their versions, the jobs and their progress, the workers, and the list of every Parquet file in [storage](/running-amp/storage) with its metadata. Every service connects to it, and it's how they coordinate: the controller assigns a job by recording it there, and the database notifies the worker. Datasets' data stays in storage, not in the database. But Amp only reads the files the database lists, so treat the database as part of your data: back it up, and keep it as available as your query servers need to be. [Solo mode](/running-amp/solo-mode) runs its own PostgreSQL server for it, unless you give it one. Every other command needs a database you provide. ## Requirements * **PostgreSQL 16 or newer.** * **A database for Amp, and a user that owns it.** When Amp [migrates](/running-amp/metadata-database#migrations) the database, it creates and changes tables, indexes, and triggers. * **Connections for every service.** Each one opens up to its pool's [`pool_size`](/running-amp/metadata-database#pool_size) connections, 10 by default: keep their total, across every service, below the server's `max_connections`. * **A private network** between the services and the database: `ampd` [doesn't use TLS](/running-amp/metadata-database#limitations) to connect. ## Connect to the database Give Amp the database's URL in the `[metadb]` section: ```toml [config.toml] [metadb] url = "postgresql://amp:amp@db.internal:5432/amp" ``` The URL is `postgresql://user:password@host:port/database`. Percent-encode special characters in the password, like `%40` for `@`. To keep the password out of the file, set the URL in an environment variable instead: ```bash export AMP_CONFIG_METADB__URL="postgresql://amp:$AMP_DB_PASSWORD@db.internal:5432/amp" ``` To connect through a Unix socket, put the socket's directory in `host`: `postgresql:///amp?host=/var/run/postgresql`. ## Settings The `[metadb]` section's settings. Each service can change the connection pool settings for itself, in its own section: see [Per-service pools](/running-amp/metadata-database#per-service-pools). ### `url` The database's URL. Required, except in solo mode, which runs its own database without it. * **Type**: string * **Environment variable**: `AMP_CONFIG_METADB__URL` ```toml [metadb] url = "postgresql://amp:amp@localhost:5432/amp" ``` ### `pool_size` The most connections the service opens to the database at once. * **Type**: integer * **Default**: `10` * **Environment variable**: `AMP_CONFIG_METADB__POOL_SIZE` ```toml [metadb] pool_size = 20 ``` ### `min_connections` The connections the service keeps open, even when they're idle, so a request rarely waits for a new one. When it needs more, it opens them, up to `pool_size`. * **Type**: integer * **Default**: a quarter of `pool_size`, rounded up, and at least 1: `3` for the default `pool_size` * **Environment variable**: `AMP_CONFIG_METADB__MIN_CONNECTIONS` ```toml [metadb] min_connections = 5 ``` ### `max_lifetime_secs` How long a connection lives before the service replaces it, in seconds. This keeps connections from reaching the server's or a proxy's own time limits. * **Type**: integer * **Default**: `1800` (30 minutes) * **Environment variable**: `AMP_CONFIG_METADB__MAX_LIFETIME_SECS` ```toml [metadb] max_lifetime_secs = 900 ``` ### `idle_timeout_secs` How long a connection can stay unused before the service closes it, in seconds, down to `min_connections`. * **Type**: integer * **Default**: `600` (10 minutes) * **Environment variable**: `AMP_CONFIG_METADB__IDLE_TIMEOUT_SECS` ```toml [metadb] idle_timeout_secs = 300 ``` ### `acquire_timeout_secs` How long a request waits for a connection, in seconds, including the time to open a new one. Past it, the request fails, rather than waiting on a database that's overloaded or unreachable. * **Type**: integer * **Default**: `5` * **Environment variable**: `AMP_CONFIG_METADB__ACQUIRE_TIMEOUT_SECS` ```toml [metadb] acquire_timeout_secs = 10 ``` ### `auto_migrate` Whether a service [migrates](/running-amp/metadata-database#migrations) the database when it starts. With `false`, it checks that the database is up to date instead, and doesn't start if it isn't. * **Type**: boolean * **Default**: `true` * **Environment variable**: `AMP_CONFIG_METADB__AUTO_MIGRATE` ```toml [metadb] auto_migrate = false ``` ## Per-service pools Every service connects to the same database, but they don't use it as much: workers record every file they write, while the controller handles occasional requests. To size each service's connection pool, add a section for it, with the pool settings to change. The others, `url` included, come from `[metadb]`: ```toml [config.toml] [metadb] url = "postgresql://amp:amp@db.internal:5432/amp" pool_size = 10 [worker.metadb] pool_size = 50 [controller.metadb] pool_size = 5 ``` The sections are `[server.metadb]`, `[worker.metadb]`, and `[controller.metadb]`, and their environment variables are like `AMP_CONFIG_WORKER__METADB__POOL_SIZE`. For each setting, a service uses the first of: 1. Its own section's environment variable, like `AMP_CONFIG_WORKER__METADB__POOL_SIZE`. 2. `[metadb]`'s environment variable, like `AMP_CONFIG_METADB__POOL_SIZE`. 3. Its own section in the file, like `[worker.metadb]`. 4. `[metadb]` in the file. 5. The default. Solo mode and `ampd migrate` only use `[metadb]`. To size pools, watch the [metrics](/running-amp/metrics#metadata-database-pool) on how many connections are in use, and how long requests wait for one. ## Migrations Each version of Amp expects its own version of the database's tables. Migrations bring the database up to date: they create the tables when the database is new, and change them when you [upgrade](/running-amp/upgrading). PostgreSQL records the ones it applied in the `_sqlx_migrations` table. There are two ways to apply them: * **When services start**, with [`auto_migrate`](/running-amp/metadata-database#auto_migrate) on, the default. Each service applies the migrations its version has and the database lacks. Services that start at the same time take turns, so each migration runs once. * **With `ampd migrate`**, which applies them, then exits. Pair it with `auto_migrate = false`, so you decide when the database changes, like in a deployment step before the services restart. Services then only check the database, and don't start until it's migrated. ```bash ampd --config config.toml migrate ``` Running it again does nothing, since every migration is applied once. It exits with status `0` on success, and `1` on an error. Migrations only go forward: no version of `ampd` undoes them, and an older `ampd` refuses to start on a database that a newer one migrated. To go back to an older version, restore a backup from before the migration. Take one before every upgrade, in a way that keeps the files it lists in storage: [Upgrading](/running-amp/upgrading#what-a-rollback-needs) explains how, and how to [roll back](/running-amp/upgrading#roll-back). ## Limitations `ampd` connects to PostgreSQL without TLS. A URL that requires it fails: ``` Exiting with error: Migrate command failed | Caused by: Failed to run migrations -> Error connecting to metadata db: error occurred while attempting to establish a TLS connection: TLS upgrade required by connect options but SQLx was built without TLS support enabled ``` Keep the database on a private network with the services. When your database only accepts TLS, like some managed databases, connect through a proxy that adds it, like your cloud provider's database proxy, running next to each service. ## Troubleshooting ### `Missing required metadata database configuration` The service has no `url`, or a setting in `[metadb]` has the wrong type, like a `pool_size` in quotes. Set [`url`](/running-amp/metadata-database#url), and check the section's values. ### `is defined in the binary but not yet applied` ``` Exiting with error: Controller command failed | Caused by: Failed to connect to metadata database -> Migration consistency check failed: Migration consistency check failed: version 20241021142330 "initial" is defined in the binary but not yet applied — run `amp migrate` or set `auto_migrate = true` ``` `auto_migrate` is off, and the database is behind this version of `ampd`. Run `ampd migrate`, the command the message calls `amp migrate`. ### `is applied in the database but not present in the binary` ``` Exiting with error: Server command failed | Caused by: Failed to connect to metadata database -> Migration consistency check failed: Migration consistency check failed: version 20260923120000 "notify location change from membership triggers" is applied in the database but not present in the binary (latest version in binary: Some(20260910120000)); the binary is older than the database or was built from a different migration set ``` A newer `ampd` migrated the database. With `auto_migrate` on, the same problem reads `migration 20260923120000 was previously applied but is missing in the resolved migrations`. Run the newer version, or [roll back](/running-amp/upgrading#roll-back) to a backup from before its migration. ### `migrations table is dirty` A migration failed partway, so the database may be half changed. Look at the `_sqlx_migrations` row the message names and at the error that failed it, in the logs of the service that ran it. [Restoring the backup](/running-amp/upgrading#roll-back) you took before the upgrade is the safest way out. Then fix the cause, like a missing privilege, and migrate again. ### `pool timed out while waiting for an open connection` A request waited longer than [`acquire_timeout_secs`](/running-amp/metadata-database#acquire_timeout_secs) for a connection. The pool is too small for the load, or the database is slow or unreachable. Check the database, then raise the service's [`pool_size`](/running-amp/metadata-database#pool_size) within the server's `max_connections`.