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.
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 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, usually5432.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=requireto require a TLS connection. This setting does not verify the server's hostname.Use
PGSSLMODE=verify-fullwithPGSSLROOTCERTto 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_productionTo 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_productionApply 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 psReview .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.ymlUse 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 /tmpAllow 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.
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_productionIn 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_onlyisoff.standard_conforming_stringsison.intervalstyleisiso_8601.client_min_messagesiswarning.lock_timeoutis5s.idle_in_transaction_session_timeoutis65s.The required extensions are present, with
pg_trgmandbtree_gininpublic.
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:
\qIf 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. 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-appWhile 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 redisUse 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-registryKeep the source database running. Do not remove containers or volumes during this step.
Confirm which services are still running:
docker compose psAt 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.txtcat migration-backup/source-counts.txtYou'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.dumpConfirm 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.listCompare 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.dumpThe 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_productionIf 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↗ 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 --quietStart 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 --traceThis 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 psCheck 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:
Stop Tines using the maintenance sequence, including application and worker services.
Preserve the target database for recovery.
Restore the saved configuration and any override files:
cp migration-backup/env.before-migration .env
cp migration-backup/compose.before-migration.yml docker-compose.ymlStart Tines with the original database image and volume:
docker compose up -dRepeat 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.