SQLite and PostgreSQL

Pick a database, point both containers at it, and move an existing SQLite archive to PostgreSQL. This page also covers migrations and search.

Which database

The archive uses SQLite by default. The database is one file, /data/backups/telegram_backup.db, with -wal and -shm files beside it while it is in use. PostgreSQL is optional. Both backends support live updates and full-text search. They differ only in how updates reach the viewer. On SQLite the backup sends them to the viewer over HTTP. On PostgreSQL it uses the database's LISTEN/NOTIFY.

	SQLite	PostgreSQL
Setup	Nothing to do	One more container, or an external server
Where the data lives	A file under ./data	The PostgreSQL server
Backup	Copy the file with both containers stopped	pg_dump
Viewer's /data mount	Must be read-write	May be :ro

Both containers need the same database settings

The backup and the viewer must resolve to the same database, or the viewer shows no data. The viewer has no env_file. It only receives the variables listed in its environment: block, which covers DATABASE_URL, DB_TYPE, DB_PATH and the POSTGRES_* variables.

Setting DATABASE_DIR or DATABASE_PATH only in .env is the usual cause. Use DB_PATH, which both containers receive. Run with Docker explains why.

How the database location is resolved

The app, Alembic and the backup image's entrypoint all use the same order. The first rule that matches wins.

1. DATABASE_URL, if set.
2. Otherwise, if DB_TYPE is postgresql or postgres, PostgreSQL. Upper and lower case both work. The URL is built from the five POSTGRES_* variables below.
3. Otherwise SQLite, at the first of these that is set: DATABASE_PATH, then DATABASE_DIR/telegram_backup.db, then DB_PATH, then BACKUP_PATH/telegram_backup.db. The path is made absolute.

Variable	Default	Meaning
DATABASE_URL	empty	Full URL. Beats every other database setting.
DB_TYPE	sqlite	sqlite, postgresql or postgres. Used only when DATABASE_URL is empty.
DB_PATH	$BACKUP_PATH/telegram_backup.db	SQLite file. The stock compose file sets /data/backups/telegram_backup.db in both services.
DATABASE_PATH	unset	Older full-path variable for SQLite.
DATABASE_DIR	unset	Older directory variable for SQLite.
POSTGRES_HOST	localhost, postgres in the compose file	Server host.
POSTGRES_PORT	5432	Server port.
POSTGRES_USER	telegram	Database user.
POSTGRES_PASSWORD	empty	Database password.
POSTGRES_DB	telegram_backup	Database name.

The full list with scope per container is in Environment variables.

The app percent-encodes POSTGRES_USER and POSTGRES_PASSWORD when it builds the URL. In a DATABASE_URL you write yourself, percent-encode special characters in the password yourself.

DATABASE_URL accepts these schemes:

• sqlite:/// and sqlite+aiosqlite:///
• postgresql://, postgresql+asyncpg:// and postgres://

An absolute SQLite path needs four slashes. Three slashes make the path relative to /app, which is not the data volume and is read-only under the stock compose file.

DATABASE_URL=sqlite:////data/backups/telegram_backup.db
DATABASE_URL=postgresql://telegram:change-me@postgres:5432/telegram_backup

The backup image refuses to start with ERROR: unrecognised database configuration for any other scheme, such as postgresql+psycopg2://. It also refuses any DB_TYPE that is set to something other than sqlite, postgres or postgresql.

SQLite settings

• The database runs in write-ahead log (WAL) mode with synchronous=NORMAL. On a read-only file the app skips WAL and logs a warning.
• DATABASE_TIMEOUT sets how long a connection waits for a lock, in seconds. The default is 60. Invalid, zero or negative values fall back to 60. It has no effect on PostgreSQL. Raise it on slow disks if the logs show database is locked.
• The viewer needs write access to the /data mount. It writes to the WAL files, the shared .push-secret file for live updates and the thumbnail cache.
• DB_ECHO=true logs every SQL statement. Only the literal true, in any case, turns it on.

DATABASE_TIMEOUT and DB_ECHO are not in the viewer's environment: block. Add them there if the viewer should use them too.

PostgreSQL with the stock compose file

The shipped docker-compose.yml carries a PostgreSQL service in comments. To turn it on:

1. Uncomment the postgres service. It runs postgres:18-alpine as the container telegram-postgres. Its data lives in the named volume postgres_data at /var/lib/postgresql. A pg_isready healthcheck reports when it is ready.
2. Uncomment the top-level volumes: block with postgres_data: at the end of the file.

3. Uncomment depends_on in both telegram-backup and telegram-viewer, so each waits for the database to be healthy:

       depends_on:
         postgres:
           condition: service_healthy
   

4. Set the backend and a password in .env. The postgres service refuses to start without POSTGRES_PASSWORD.

   DB_TYPE=postgresql
   POSTGRES_PASSWORD=change-me
   

5. Start the stack:

   docker compose up -d
   

The compose file defaults POSTGRES_HOST to postgres, the service name. The code default is localhost, which matters only outside the compose file. The database variables are already in the viewer's environment: block, so both containers pick up the values from .env.

On the first start the backup creates every table. Check the schema revision with:

docker compose exec telegram-backup alembic current

An external PostgreSQL server

• Set DATABASE_URL or the POSTGRES_* variables in both services.
• The database user must be allowed to run CREATE EXTENSION pg_trgm. The schema needs it.
• The backup's entrypoint tries to reach the server 30 times, 2 seconds apart. If the server never answers, it exits.
• Each process keeps a fixed connection pool of 5, plus up to 10 overflow connections. It is not configurable.
• The viewer's /data mount may be read-only, ./data:/data:ro, because no database files live there.

Migrations

The backup image's entrypoint runs Alembic on every start, except for the auth command.

• On SQLite, when the database file does not exist yet, it creates it directly at the newest revision.
• Some databases have tables but no alembic_version table. For these, the entrypoint works out the revision from the tables it finds and records it, up to revision 018. Then it upgrades to the newest revision. Every revision after 018 can run safely on a schema that already has its changes.
• On PostgreSQL the entrypoint takes a database lock first, so two containers never migrate at the same time.
• Migration 022, which rebuilds every table, refuses to run while another process holds a non-empty SQLite file. The container exits with a message asking you to stop the viewer. Under the stock compose file the restart policy retries it, and it runs once the file is free.

The viewer never migrates. On PostgreSQL it has no tables until the backup has run once, so it answers with errors until then. During an upgrade it also answers with errors for the seconds a migration takes.

A pip install has no entrypoint. Run telegram-archive migrate yourself, before any other command and after every upgrade. Install from PyPI explains why the order matters.

To check or run migrations by hand:

DockerFrom a checkoutpip

docker compose exec telegram-backup alembic current

alembic -c telegram_archive/alembic.ini current
alembic -c telegram_archive/alembic.ini upgrade head

telegram-archive --data-dir ./data migrate

The newest revision is 033, in 8.17.0. There is no supported downgrade. To go back to an older release, restore the backup you took before upgrading. See Upgrading.

Search

Both backends index message text for word search.

• On SQLite, an FTS5 full-text table that ignores accents, so cafe also finds café.
• On PostgreSQL, a stored tsvector column with a GIN index.

Voice transcripts get the same kind of index. The upgrade that adds the index also indexes every existing message, so it takes longer on a large archive.

If the SQLite build has no FTS5, the migration skips the index. Message search then falls back to plain substring matching, and transcript search stops working.

Move an existing SQLite archive to PostgreSQL

The backup image ships a mover script. The image entrypoint migrates the empty PostgreSQL database to the newest revision first. The script then copies every table except avatar_history and checks the counts.

1. Stop both containers:

   docker compose stop telegram-backup telegram-viewer
   

2. Back up data/. See Backing up the archive.

3. Check that data/backups/telegram_backup.db-wal is missing or empty. SQLite writes that file into the database when the last connection closes. The mover reads only the database file, so anything still in the -wal file would be lost.

4. Turn on PostgreSQL as in PostgreSQL with the stock compose file, but start only the database. It must be empty.

   docker compose up -d postgres
   

5. Find the compose network. By default it is named after the project directory, for example telegram-archive_telegram-network:

   docker network ls
   

6. Run the mover from the backup image. Use the same tag your compose file pins, and your own network name and password:

   docker run --rm -it \
     --network telegram-archive_telegram-network \
     -v ./data/backups/telegram_backup.db:/sqlite.db:ro \
     -e DATABASE_URL=postgresql://telegram:change-me@postgres:5432/telegram_backup \
     drumsergio/telegram-archive:8.17.0 \
     python scripts/migrate-sqlite-to-postgres.py --sqlite /sqlite.db
   

7. Switch both containers to PostgreSQL. With the stock compose file that means DB_TYPE=postgresql and POSTGRES_PASSWORD in .env, and depends_on uncommented. Then start them:

   docker compose up -d
   

   Because the mover runs through the image entrypoint, the empty PostgreSQL database is migrated to the newest revision before the copy starts. alembic_version is already at head when the backup container starts. If you run the mover outside the image, with a pip install or --entrypoint python, the database has no alembic_version. On its first start the backup then works out the revision from the schema, records it and upgrades.

8. Check the result. docker compose exec telegram-backup alembic current shows the newest revision, and the viewer lists your chats and their messages. If the real-time listener is on, a new message also arrives as a live update.

Mover options:

Option	Default	Effect
--sqlite PATH	looked up	SQLite file to read.
--postgres URL	from the environment	PostgreSQL URL to write to.
--batch-size N	1000	Rows per batch.
--dry-run	off	Show what would be copied, write nothing.
--verify-only	off	Only compare row counts between the two databases.

Without --sqlite, the mover takes the first existing file from SQLITE_PATH, DATABASE_PATH, DATABASE_DIR/telegram_backup.db and DB_PATH. Then it tries /data/db/telegram_backup.db, /data/backups/telegram_backup.db and ./telegram_backup.db. Without --postgres, it uses DATABASE_URL when that contains postgresql, otherwise the POSTGRES_* variables, where POSTGRES_HOST is required.

It copies each table in primary-key order and merges rows, so a row that already exists in the target is overwritten. Afterwards it resets the media_transcripts id sequence and compares row counts table by table. The reactions sequence is not reset. The first new reaction after the move triggers the automatic recovery described in Duplicate key errors on reactions.

Before you run the mover

• Point it only at an empty database.
• Keep the backup container stopped for the whole copy. Rows written or deleted during the copy can be missed.
• The mover does not copy avatar history. The avatar files stay on disk and the current avatar carries over, but the viewer no longer lists earlier profile photos.
• There is no way back from PostgreSQL to SQLite. Keep the SQLite file.

To combine two archives, SQLite or PostgreSQL in any mix, use telegram-archive merge. See Merge two archives.

PostgreSQL backups

Back up a PostgreSQL archive with pg_dump, together with the media and session folders. Backing up the archive has the commands and the restore steps.

Duplicate key errors on reactions

On PostgreSQL the reactions id sequence can fall behind the rows in the table, for example after a restore. Inserts then fail with duplicate key value violates unique constraint "reactions_pkey". The backup detects this, resets the sequence and retries. You do not need to do anything.

To fix it by hand, run the same statement, which is also in scripts/fix_reactions_sequence.sql:

docker exec telegram-postgres psql -U telegram -d telegram_backup \
  -c "SELECT setval('reactions_id_seq', COALESCE((SELECT MAX(id) FROM reactions), 0) + 1, false);"