---
title: Migrate to Managed PostgreSQL
url: https://www.tines.com/stories/docs/self-hosted/upgrade-tines/migrate-to-managed-postgresql/
updated: 2026-11-17T16:27:24+00:00
---

*[tines.com](https://www.tines.com/llms.txt) › [Docs](https://www.tines.com/stories/docs/llms.txt) › [Self-Hosted](https://www.tines.com/llm/stories/docs/self-hosted.md) › [Upgrading Tines](https://www.tines.com/llm/stories/docs/self-hosted/upgrade-tines.md)*

# Migrate to Managed PostgreSQL

*[View on tines.com](https://www.tines.com/stories/docs/self-hosted/upgrade-tines/migrate-to-managed-postgresql/)*

This guide explains how to migrate your Tines database from the PostgreSQL container included with Docker Compose to an external PostgreSQL server.

You'll prepare the external database, transfer your existing data using PostgreSQL's dump and restore tools, and update your Tines connection settings. The procedure uses a dedicated, non-superuser database account and transfers only the Tines database.

The migration requires downtime while you export the database, restore it, and update the connection. We recommend completing a trial restore before scheduling your maintenance window. This helps you estimate the time required for your database size, server capacity, and network connection.

## Before you begin

Before you begin, make sure you have:

- Access to the Tines host and permission to run Docker Compose.
- A recent, recoverable backup of your deployment, including its configuration and encryption keys. See [Data backup recommendations](https://www.tines.com/stories/docs/self-hosted/best-practices/data-backup-recommendations/).
- An external PostgreSQL server, its connection details, and a dedicated account for Tines.
- A database administrator who can prepare the target database, permissions, and required extensions.
- Network access from the Tines containers to the target server, including any firewall and PostgreSQL access rules.
- Enough free storage for the dump on the Tines host and inside the database container, and enough capacity on the target for the restored database, indexes, and restore overhead.

This procedure applies to deployments using the `public` schema and the same supported PostgreSQL major version on both servers. Keep your Tines version unchanged during the migration. If you also need to upgrade PostgreSQL, refer to [Upgrading from PostgreSQL 14 to PostgreSQL 18](https://www.tines.com/stories/docs/self-hosted/upgrade-tines/upgrading-from-postgresql-14-to-postgresql-18/) for the required Tines and PostgreSQL client versions.

Use a supported Tines release that supports your PostgreSQL version and non-superuser database accounts. The 37.3 and 38.5 release lines received relevant database permissions fixes in 37.3.8 and 38.5.4, respectively. If you run an older release, confirm compatibility with Tines Support before proceeding.

Run the commands from the directory containing your Compose configuration on the Tines host. Use a terminal session that will remain connected throughout the migration. The examples use the PostgreSQL tools already available in your database container.

In this guide, the **source** is your existing database and the **target** is the external database. Replace the following example values with those for your deployment:

- `db`: Source database service in Docker Compose.
- `tines`: Source database username.
- `tines_production`: Source and target database name.
- `TARGET_HOST`: Target server hostname.
- `TARGET_PORT`: Target server port, usually `5432`.
- `APP_USER`: Dedicated database account Tines will use on the target.

Your source service may be named `db-pg18`, and your database or username may differ. If the target database has a different name, use that name in commands that connect to the target and in the updated Tines configuration.

Commands that connect to the target will prompt for the database account's password.

### Configure TLS if required

The examples leave `PGSSLMODE` unset. Unless your connection settings specify otherwise, PostgreSQL uses `prefer`: it attempts a TLS connection first and can fall back to a connection without TLS. You do not need to set `PGSSLMODE=prefer` explicitly.

If your database requires TLS, apply the settings provided by your database administrator to both the migration commands and your Tines configuration:

- Use `PGSSLMODE=require` to require a TLS connection. This setting does not verify the server's hostname.
- Use `PGSSLMODE=verify-full` with `PGSSLROOTCERT` to verify the server's certificate and hostname.

For example, to require TLS:

```
docker compose exec -e PGSSLMODE=require db \
  psql -X -W -v ON_ERROR_STOP=1 \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -d tines_production
```

To verify the server's certificate and hostname, use the following command with a CA certificate mounted at `/certs/postgresql-ca.crt`:

```
docker compose exec \
  -e PGSSLMODE=verify-full \
  -e PGSSLROOTCERT=/certs/postgresql-ca.crt \
  db psql -X -W -v ON_ERROR_STOP=1 \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -d tines_production
```

Apply your chosen TLS options to every migration command that connects to the target, and add the same settings to the application and worker configuration in step eight. If you use `verify-full`, the certificate must be available at the specified path inside the database, application, and worker containers. Updating the host's `.env` file does not change the environment of an already running database container.

## 1. Review your current configuration

Start by identifying the services in your deployment:

```
docker compose config --services
docker compose ps
```

Review `.env`, `docker-compose.yml`, and any Compose override files. Use `DATABASE_HOST`, `DATABASE_NAME`, and `DATABASE_USERNAME` to identify the database your deployment uses. If both `db` and `db-pg18` are present, make sure you select the active database service for the export.

If your deployment uses a database URL, connection proxy, or separate read endpoint, include those settings in your migration plan. They may override the connection values shown in this guide.

Save a copy of your configuration in a private directory:

```
umask 077
mkdir migration-backup
cp .env migration-backup/env.before-migration
cp docker-compose.yml migration-backup/compose.before-migration.yml
```

Use your actual Compose filename if it differs, and copy any override files or separate environment files you use. If `migration-backup` already exists, choose a new directory and use it throughout this guide.

Record the database version, size, locale, and installed extensions:

```
docker compose exec db psql -X -v ON_ERROR_STOP=1 \
  -U tines -d tines_production \
  -c "SHOW server_version;" \
  -c "SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;" \
  -c "SELECT pg_encoding_to_char(encoding) AS encoding, datcollate, datctype FROM pg_database WHERE datname = current_database();" \
  -c "SELECT extname, extversion, extnamespace::regnamespace AS schema FROM pg_extension ORDER BY extname;"
```

On PostgreSQL 18, also record the locale provider and provider-specific locale:

```
docker compose exec db psql -X -v ON_ERROR_STOP=1 \
  -U tines -d tines_production \
  -c "SELECT datlocprovider, datlocale FROM pg_database WHERE datname = current_database();"
```

For `datlocprovider`, `b` means built-in, `c` means libc, and `i` means ICU. This query does not apply to PostgreSQL 14, which uses libc for the database locale.

Share these results with your database administrator so they can prepare a compatible target. The source encoding should be `UTF8`. Available locales and extensions can differ between PostgreSQL installations.

Check the available storage on the host and in the container:

```
df -h .
docker compose exec db df -h /tmp
```

Allow enough space for a copy of the dump in both locations. If the host directory and container storage share a filesystem, that filesystem needs room for both copies. Although the dump is compressed, its size depends on the data.

## 2. Prepare the external database

Your database administrator should complete this section before the maintenance window. The target must be a new database with no existing application objects or data.

Make `APP_USER` the database owner and use that account to restore the application objects. This account will also need to retain permission to create objects in the `public` schema for normal operation and future upgrades.

For a source database using a libc locale, the administrator can use the following example, replacing `SOURCE_COLLATION` and `SOURCE_CTYPE` with the recorded source values:

```
CREATE DATABASE tines_production
  OWNER APP_USER
  ENCODING 'UTF8'
  TEMPLATE template0
  LC_COLLATE 'SOURCE_COLLATION'
  LC_CTYPE 'SOURCE_CTYPE';
```

On PostgreSQL 18, add `LOCALE_PROVIDER libc` to that command when the source uses libc, so the new database does not inherit a different provider from its template.

For a PostgreSQL 18 source using the built-in provider, use the following instead, replacing `SOURCE_LOCALE` with the recorded `datlocale` value:

```
CREATE DATABASE tines_production
  OWNER APP_USER
  ENCODING 'UTF8'
  TEMPLATE template0
  LOCALE_PROVIDER builtin
  LOCALE 'SOURCE_LOCALE';
```

For an ICU source, have the administrator configure the matching ICU locale and any custom collation rules.

While connected to the target database, the administrator should assign the `public` schema to the Tines account and create the required extensions:

```
ALTER SCHEMA public OWNER TO APP_USER;
GRANT USAGE, CREATE ON SCHEMA public TO APP_USER;

CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS btree_gin WITH SCHEMA public;
```

Assigning schema ownership allows the restore to apply the source schema's comment. Before proceeding, confirm that any additional extensions on the source are available on the target, with compatible versions and schemas.

Although this procedure has an administrator prepare the extensions, `pg_trgm` and `btree_gin` are trusted PostgreSQL extensions. An account with `CREATE` permission on the database can also install them where the server's policies allow it.

Apply the following connection settings to the Tines account. The `IN DATABASE` clause limits these defaults to connections to the target database:

```
ALTER ROLE APP_USER IN DATABASE tines_production SET standard_conforming_strings = 'on';
ALTER ROLE APP_USER IN DATABASE tines_production SET intervalstyle = 'iso_8601';
ALTER ROLE APP_USER IN DATABASE tines_production SET client_min_messages = 'warning';
ALTER ROLE APP_USER IN DATABASE tines_production SET lock_timeout = '5000';
ALTER ROLE APP_USER IN DATABASE tines_production SET idle_in_transaction_session_timeout = '65000';
```

The server and account connection limits must accommodate all Tines application and worker connections, alongside any other workloads on the server.

For background, see [Using a non-superuser PostgreSQL account](https://www.tines.com/stories/docs/self-hosted/configuring-tines/using-a-non-superuser-postgres-user/).

## 3. Verify access to the external database

Before stopping Tines, verify that the application account can connect to the target:

```
docker compose exec db \
  psql -X -W -v ON_ERROR_STOP=1 \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -d tines_production
```

In the PostgreSQL session, run the following checks:

```
SELECT current_user AS username,
       current_database() AS database,
       has_database_privilege(current_database(), 'CONNECT') AS can_connect,
       has_database_privilege(current_database(), 'TEMP') AS can_use_temp_tables,
       has_schema_privilege('public', 'USAGE') AS can_use_public,
       has_schema_privilege('public', 'CREATE') AS can_create_objects;

SHOW server_version;
SHOW search_path;
SHOW default_transaction_read_only;
SHOW standard_conforming_strings;
SHOW intervalstyle;
SHOW client_min_messages;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;

SELECT extname, extversion, extnamespace::regnamespace AS schema
FROM pg_extension ORDER BY extname;
```

Confirm:

- The username and database match the intended target.
- Every permission check is `t`.
- The PostgreSQL major version matches the source.
- The search path resolves to `public`.
- `default_transaction_read_only` is `off`.
- `standard_conforming_strings` is `on`.
- `intervalstyle` is `iso_8601`.
- `client_min_messages` is `warning`.
- `lock_timeout` is `5s`.
- `idle_in_transaction_session_timeout` is `65s`.
- The required extensions are present, with `pg_trgm` and `btree_gin` in `public`.

Before continuing, have your administrator confirm that the target contains no existing application tables, sequences, views, or functions. Restoring into a database used by another application can cause conflicts.

Close the PostgreSQL session:

```
\q
```

If the connection fails, resolve the error before starting the maintenance window:

- **Timeout or no response:** Check the hostname, port, routing, firewall rules, and whether PostgreSQL is listening.
- `**no pg_hba.conf entry**`**:** Check PostgreSQL access rules for the host, account, database, and TLS mode.
- `**password authentication failed**`**:** Check the account name and password.
- **TLS or certificate error:** Check the required TLS mode, certificate hostname, and certificate paths inside the containers.

## 4. Stop Tines and record a baseline

Start this section at the beginning of your maintenance window.

Follow the shutdown sequence in [Infrastructure maintenance](https://www.tines.com/stories/docs/self-hosted/upgrade-tines/infrastructure-maintenance/). Stop incoming traffic and the application first, allow the worker queues to drain, then stop workers and the remaining services. Leave the source PostgreSQL service running.

For a deployment using the standard service names, stop the proxy and application:

```
docker compose stop nginx
docker compose stop tines-app
```

While the workers are still running, check their queues:

```
docker compose exec tines-sidekiq bundle exec rails runner \
  'puts JSON.pretty_generate(Sidekiq::Queue.all.map { |q| { name: q.name, size: q.size, latency: q.latency.round(2), paused: q.paused? } })'
```

Repeat the check over two to five minutes. Follow the maintenance guide if the queues are not draining. Once they are near zero and are not growing, stop workers, the command runner, and Redis in order:

```
docker compose stop tines-sidekiq
docker compose stop tines-command-runner
docker compose stop redis
```

Use the service names in your deployment and skip services you do not have. Stop any additional workers or schedulers before stopping Redis. Also stop auxiliary services such as `pypi-server` and `npm-registry`, and any unused database service:

```
docker compose stop pypi-server
docker compose stop npm-registry
```

Keep the source database running. Do not remove containers or volumes during this step.

Confirm which services are still running:

```
docker compose ps
```

At this point, only the source database should remain running. Confirm that no other deployment or external process is writing to it.

Save a baseline for comparison after the restore:

```
docker compose exec -T db psql -X -A -t -v ON_ERROR_STOP=1 \
  -U tines -d tines_production \
  -c "SELECT 'migrations', count(*) FROM schema_migrations;" \
  -c "SELECT 'public_tables', count(*) FROM information_schema.tables WHERE table_schema = 'public';" \
  -c "SELECT 'stories', count(*) FROM stories;" \
  > migration-backup/source-counts.txt
```

```
cat migration-backup/source-counts.txt
```

You'll compare these counts with the restored database in step seven. They provide a basic consistency check; add checks for other data important to your deployment. All application and worker services must remain stopped during the export, even if their queues are empty.

## 5. Export your existing database

Create a custom-format archive inside the source container:

```
docker compose exec db sh -c 'umask 077
exec pg_dump \
  -U tines -d tines_production \
  --format=custom --no-privileges \
  --file=/tmp/tines_production.dump'
```

Allow the export to finish before continuing. If it reports an error, resolve the issue and repeat the export.

Copy the archive to the host and save its table of contents:

```
docker compose cp db:/tmp/tines_production.dump \
  migration-backup/tines_production.dump

docker compose exec -T db pg_restore --list /tmp/tines_production.dump \
  > migration-backup/archive.list

ls -lh migration-backup/tines_production.dump
```

Confirm that the archive is not empty and that `pg_restore --list` completes successfully. Keep the host copy so the archive remains available if the container is removed or recreated.

## 6. Restore the database

Because the target extensions were created by an administrator, the application account may not own them. Exclude extension comments from the restore to avoid ownership errors:

```
awk '!/ COMMENT - EXTENSION /' migration-backup/archive.list \
  > migration-backup/restore.list

docker compose cp migration-backup/restore.list \
  db:/tmp/tines-restore.list
```

Compare the two lists to confirm that only extension comment entries were removed. Keep the extension creation entries and all application objects.

Restore as `APP_USER`:

```
docker compose exec db pg_restore \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -W \
  -d tines_production \
  --no-owner --no-privileges --exit-on-error --verbose \
  --use-list=/tmp/tines-restore.list \
  /tmp/tines_production.dump
```

The restore uses the following options:

- `--no-owner`: Assigns ownership of newly restored application objects to the restoring account.
- `--no-privileges`: Omits access grants that refer to accounts on the source.
- `--exit-on-error`: Stops the restore if a command fails.

Wait for the restore to complete successfully before proceeding. If it fails, keep Tines stopped and have your administrator prepare a fresh target database before retrying. Stopping at an error does not remove objects that were already restored.

After the restore, refresh the statistics PostgreSQL uses to plan queries:

```
docker compose exec db \
  psql -X -W -v ON_ERROR_STOP=1 \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -d tines_production \
  -c "ANALYZE;"
```

A non-superuser account may receive warnings about PostgreSQL system catalogs it cannot analyze. Review any errors or warnings involving Tines application tables before proceeding.

## 7. Verify the restored data

Run the same counts on the target:

```
docker compose exec db \
  psql -X -W -v ON_ERROR_STOP=1 \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -d tines_production \
  -c "SELECT 'migrations', count(*) FROM schema_migrations;" \
  -c "SELECT 'public_tables', count(*) FROM information_schema.tables WHERE table_schema = 'public';" \
  -c "SELECT 'stories', count(*) FROM stories;"
```

Compare the results with `migration-backup/source-counts.txt`. Each count must match. Resolve any differences before starting Tines.

Confirm that the restored application tables belong to `APP_USER`:

```
docker compose exec db \
  psql -X -W -v ON_ERROR_STOP=1 \
  -h TARGET_HOST -p TARGET_PORT -U APP_USER -d tines_production \
  -c "SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname = 'public' AND tableowner <> current_user;"
```

For a standard deployment, the query should return no rows. If it returns any tables, review their ownership with your administrator. The extensions themselves can remain owned by the administrator.

## 8. Update your connection settings

Edit the configuration files identified in step one:

```
DATABASE_HOST=TARGET_HOST
DATABASE_PORT=TARGET_PORT
DATABASE_USERNAME=APP_USER
DATABASE_PASSWORD='replace-with-the-target-account-password'
DATABASE_NAME=tines_production
```

If your database requires TLS, also add `PGSSLMODE` and any certificate settings used in your earlier connection checks. Preserve any existing TLS requirements. Follow Docker Compose's [environment file syntax](https://docs.docker.com/compose/how-tos/environment-variables/variable-interpolation/#env-file-syntax) when entering passwords with special characters.

Apply the new values to the application and all worker services. Check for values in each service's `environment` configuration, which can override its environment file. Also update any database proxy or read endpoint configuration identified in step one.

Keep your other settings unchanged, including encryption keys and Redis configuration. Retain the source database service, its original image, and its data volume until the migration is accepted. Changing `POSTGRES_USER` or `POSTGRES_PASSWORD` does not update the credentials stored in an already initialized source database.

Validate the Compose configuration without printing its contents:

```
docker compose config --quiet
```

Start Redis if it was stopped, then run Tines' database preparation task using the existing application image:

```
docker compose up -d redis

docker compose run --rm --no-deps tines-app \
  bundle exec rake tines:prepare_db --trace
```

This task prepares the target database and can modify its contents. It should finish with `Done preparing Tines database`.

When using a non-superuser account, you may see `Skipping privileged statement` warnings for settings and extensions prepared in step two. Confirm that each skipped statement has already been handled. Resolve any failures or unexpected warnings before starting Tines.

## 9. Restart Tines and verify the migration

Start the services:

```
docker compose up -d
docker compose ps
```

Check the database connection from the running application:

```
docker compose exec tines-app bundle exec rails runner \
  'puts ActiveRecord::Base.connection.select_one("SELECT current_database() AS database, current_user AS username, inet_server_addr()::text AS server_address, inet_server_port() AS server_port").to_json'
```

Confirm that the returned database, username, server address, and port match the target. Repeat this check in `tines-sidekiq` and any separately configured worker services.

Complete the following checks:

- Sign in to Tines and confirm that your stories and other expected data are present.
- Run a controlled test story.
- Review the application and worker logs for connection or permission errors.

The source database container may start because it is still included in your Compose configuration. Use the application and worker connection checks above to confirm which database Tines is using.

## Roll back the migration

If you need to return to the source database before restarting Tines, restore the configuration saved in step one.

After Tines starts on the target, new data and background changes may exist only in that database. Returning to the source will omit those changes. If Tines has already written to the target, stop the services and work with your database administrator or Tines Support to agree on a recovery plan before rolling back.

To return to the source:

1. Stop Tines using the maintenance sequence, including application and worker services.
2. Preserve the target database for recovery.
3. Restore the saved configuration and any override files:
  
  `cp migration-backup/env.before-migration .env
  cp migration-backup/compose.before-migration.yml docker-compose.yml`
4. Start Tines with the original database image and volume:
  
  `docker compose up -d`
5. Repeat the connection and application checks to confirm Tines is using the source database.

Do not delete either database volume during recovery.

## After the migration

Once you've verified the migration:

- Configure backups, monitoring, and capacity management for the external database.
- Keep the migration archive, saved configuration, and source volume for your agreed recovery period.
- Restrict access to the archive and configuration, which contain sensitive data.
- After accepting the migration and verifying a new backup, remove any temporary dump files remaining in the container.
- Retire the source database service and remove references to it from Compose dependencies as a separate cleanup step.

The Tines account must retain ownership of its application objects and permission to create objects in the schema. Before future upgrades, review the release notes with your administrator for any new extensions or settings that need to be prepared.
