Storage Backend Postgresql

[makuartur]

Good afternoon, could you please help me understand what I’m doing wrong?
I’m trying to run Teleport with PostgreSQL as the storage backend — here is my config:

root@bastion:/var/lib/teleport/proc# cat /etc/teleport.yaml
version: v3
teleport:
  storage:
    type: postgresql
    conn_string: postgresql://teleport_backend:password@127.0.0.1:7432/teleport_backend?sslmode=disable&pool_max_conns=20
    region: eu-central-1
    audit_sessions_uri: "s3://some-s3-bucket/records"
    audit_events_uri:
      - postgresql://teleport_audit:password@127.0.0.1:7432/teleport_audit?sslmode=disable
  nodename: bastion.test.net
  data_dir: /var/lib/teleport
  log:
    output: stderr
    severity: DEBUG
    format:
      output: text
  ca_pin: ""
  diag_addr: 127.0.0.1:3000
auth_service:
  enabled: "yes"
  listen_addr: 0.0.0.0:3025
  cluster_name: teleport.test.net
  proxy_listener_mode: multiplex
ssh_service:
  enabled: "yes"
  labels:
    role: bastion
    fqdn: bastion.test.net

Teleport starts up and works, but when I create a bot, it still writes to SQLite (example log):

root@bastion:/home/test/teleport# cat terraform-bot.yaml
kind: bot
version: v1
metadata:
  name: terraform
spec:
  # The terraform-provider is a default role shipped in Teleport granting access
  # to every resource supported by the terraform provider.
  roles: ["terraform-provider"]
root@bastion:/home/test/teleport# tctl create terraform-bot.yaml
2025-05-16T13:19:56.215Z DEBU             'default' automatic update channel not specified, teleport will serve its version by default. automaticupgrades/channel.go:65
2025-05-16T13:19:56.230Z DEBU [SQLITE]    Connected to database database:file:/var/lib/teleport/proc/sqlite.db?_busy_timeout=10000&_sync=FULL&_txlock=immediate poll_stream_period:1s lite/lite.go:259
2025-05-16T13:19:56.231Z DEBU [SQLITE]    retrieved pragma values journal_mode:delete synchronous:2 busy_timeout:10000 lite/lite.go:310
2025-05-16T13:19:56.232Z DEBU  Auth client connecting auth_servers:[{Addr:0.0.0.0:3025 AddrNetwork:tcp Path:}] authclient/authclient.go:63
bot "terraform" has been created
root@bastion:/home/test/teleport# tctl bot ls
2025-05-16T13:20:09.060Z DEBU             'default' automatic update channel not specified, teleport will serve its version by default. automaticupgrades/channel.go:65
2025-05-16T13:20:09.074Z DEBU [SQLITE]    Connected to database database:file:/var/lib/teleport/proc/sqlite.db?_busy_timeout=10000&_sync=FULL&_txlock=immediate poll_stream_period:1s lite/lite.go:259
2025-05-16T13:20:09.076Z DEBU [SQLITE]    retrieved pragma values journal_mode:delete synchronous:2 busy_timeout:10000 lite/lite.go:310
2025-05-16T13:20:09.077Z DEBU  Auth client connecting auth_servers:[{Addr:0.0.0.0:3025 AddrNetwork:tcp Path:}] authclient/authclient.go:63
Bot       User          Roles
--------- ------------- ------------------
terraform bot-terraform terraform-provider


To view active instances of a bot, run:

> tctl bots instances list [name]
root@bastion:/home/test/teleport#

At the same time, the databases and tables in PostgreSQL were created, and entries also appear in teleport_audit.events indicating that the bot was created.

teleport_audit=# select event_data,creation_time from events order by event_time DESC limit 1;
                                                                                                                                                event_data                                                                                                                                                 |         creation_time
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------
 {"ei":0,"event":"bot.create","uid":"some_id","code":"TB001I","time":"2025-05-16T13:19:56.251Z","cluster_name":"teleport.test.net","name":"terraform","expires":"0001-01-01T00:00:00Z","user":"some_id.teleport.test.net","user_kind":1} | 2025-05-16 13:19:56.253522+00
(1 row)

teleport_audit=#

What am I doing wrong?
Is it possible to completely get rid of SQLite?

[stevenGravy]

You can't have a existing backend and then change the storage in place for a auth service. It should not have a existing storage in place. You can use the teleport backend clone to copy from a existing backend to another storage. You should start the auth service with no existing backend in the Teleport data dir that points to the storage you've migrated to.

[makuartur]

Understood, thank you.
When I try to perform a clone, I get an error.
Here is the contents of clone.yaml:

  # src is the configuration for the backend where data is cloned from.
  src:
    type: sqlite
    path: /var/lib/teleport
  # dst is the configuration for the backend where data is cloned to.
  dst:
    type: postgres
    conn_string: postgresql://teleport_backend:test@127.0.0.1:7432/teleport_backend?sslmode=disable&pool_max_conns=20
    audit_events_uri:
      - postgresql://teleport_audit:test@127.0.0.1:7432/teleport_audit?sslmode=disable
  # parallel is the amount of backend data cloned in parallel.
  # If a clone operation is taking too long consider increasing this value.
  parallel: 100
  # force, if set to true, will continue cloning data to a destination
  # regardless of whether data is already present. By default this is false
  # to protect against overwriting the data of an existing Teleport cluster.
  force: false

Here is the contents of the /var/lib/teleport directory:

root@bastion:/home/test/teleport# ls -la /var/lib/teleport/ /var/lib/teleport/backend/* /var/lib/teleport/proc/*
-rw------- 1 root root 7213056 May 17 17:59 /var/lib/teleport/backend/sqlite.db
-rw------- 1 root root  114688 May 17 17:57 /var/lib/teleport/proc/sqlite.db

/var/lib/teleport/:
total 32
drwx------  7 root root 4096 May 17 18:24 .
drwxr-xr-x 48 root root 4096 May 17 18:00 ..
drwx------  2 root root 4096 May 17 17:59 backend
srwxr-xr-x  1 root root    0 May 16 14:47 debug.sock
drwx------  2 root root 4096 May 16 13:43 handover
-r--------  1 root root   36 May 16 13:43 host_uuid
drwxr-x---  6 root root 4096 May 17 00:19 log
drwx------  2 root root 4096 May 17 17:57 proc
drwxr-xr-x  2 root root 4096 May  8 00:10 versions
root@bastion:/home/test/teleport#

Here is the error:

root@bastion:/home/test/teleport# teleport backend clone --config clone.yaml
2025-05-17T18:31:08.858Z ERRO [PGBK]      Failed to delete expired items. error:[
ERROR REPORT:
Original Error: *errors.errorString context canceled
Stack Trace:
	github.com/gravitational/teleport/lib/backend/pgbk/common/utils.go:127 github.com/gravitational/teleport/lib/backend/pgbk/common.retry[...]
	github.com/gravitational/teleport/lib/backend/pgbk/common/utils.go:113 github.com/gravitational/teleport/lib/backend/pgbk/common.RetryIdempotent[...]
	github.com/gravitational/teleport/lib/backend/pgbk/background.go:57 github.com/gravitational/teleport/lib/backend/pgbk.(*Backend).backgroundExpiry
	github.com/gravitational/teleport/lib/backend/pgbk/pgbk.go:195 github.com/gravitational/teleport/lib/backend/pgbk.NewWithConfig.func1
	runtime/asm_amd64.s:1700 runtime.goexit
User Message: context canceled] pgbk/background.go:74

At the same time, the teleport_backend database is created with the following tables and records:

postgres=# \c teleport_backend
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "teleport_backend" as user "postgres".
teleport_backend=# \dt+
                                              List of relations
 Schema |      Name       | Type  |      Owner       | Persistence | Access method |    Size    | Description
--------+-----------------+-------+------------------+-------------+---------------+------------+-------------
 public | backend_version | table | teleport_backend | permanent   | heap          | 8192 bytes |
 public | kv              | table | teleport_backend | permanent   | heap          | 8192 bytes |
(2 rows)

teleport_backend=# select * from backend_version;
 version |            created
---------+-------------------------------
       2 | 2025-05-17 18:31:08.838226+00
(1 row)

teleport_backend=# select * from kv;
 key | value | expires | revision
-----+-------+---------+----------
(0 rows)

teleport_backend=#

Should I specify the /var/lib/teleport/backend directory in the src path field to ensure that only the SQLite database from there is used?
If so, do I need to do anything with the SQLite file in /var/lib/teleport/proc, and if yes, what exactly?
Could you please tell me what I am doing wrong?

[webvictim]

You don't need to do anything with the SQLite file in /var/lib/teleport/proc, this holds Teleport's local identity/state and isn't relevant here. Your Postgres backend was working correctly before, you don't need to run teleport backend clone.

[zmb3]

Is it possible to completely get rid of SQLite?

No. The sqlite entires you see in the logs are for process-local storage, not the cluster state backend. This is expected behavior.

[webvictim]

Further to this: everything here is working as expected according to the original logs.