How does it work?

We connect into the docker container (exec -i) with the name (e.g. postgres_database) or container ID and then run inside the psql command to interact with the database

1) End all active connections to database

Connect to database via psql

sudo docker exec -it container_name psql -U user_name

Select all processes in the database

SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE datname = 'directus';

Terminate Active Sessions:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'directus';

Alternative (this works better then connection to database):

docker stop container_name_of_directus_instance

2) Install Backup

Delete the Database first:

sudo docker exec -it postgres_database psql -U directus template1 -c 'drop database directus;'

Create Database

sudo docker exec -it postgres_database psql -U directus template1 -c 'create database directus with owner directus;'

Fill database with dumpfile

sudo docker exec -i postgres_database psql -U directus directus < /home/nino/directus/backups/20240831_123901_directus_dump.sql

*) Create Cronjob to setup periodic backup files

0 2 * * * cd /home/nino/directus/backups && docker exec postgres_database pg_dump -U directus directus > $(date +\%Y\%m\%d_\%H\%M\%S)_directus_dump.sql 2> /home/nino/directus/backups/cron_error.log

Use iconv to convert the file from UTF-16 to UTF-8:

iconv -f UTF-16 -t UTF-8 directus_dump.sql -o directus_dump_utf8.sql

Ensure the converted file is in UTF-8:

file directus_dump_utf8.sql