Database recovery

[jekeam]

Hello, I was repairing the mail server and restarted the editor with the command

# docker-compose down && docker-compose up -d

after which I was pleased with the start screen and realized that my entire database had been deleted, can I somehow restore it?

# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
89ce6151a019 redash/nginx:latest "nginx -g 'daemon of…" 29 minutes ago Up 29 minutes 0.0.0.0:80->80/tcp, :::80->80/tcp, 443/tcp redash_nginx_1
40f1cf857872 redash/redash:10.0.0.b50363 "/app/bin/docker-ent…" 29 minutes ago Up 29 minutes 5000/tcp redash_scheduler_1
ea2a1724daff redash/redash:10.0.0.b50363 "/app/bin/docker-ent…" 29 minutes ago Up 29 minutes 5000/tcp redash_adhoc_worker_1
5385d77ea52a redash/redash:10.0.0.b50363 "/app/bin/docker-ent…" 29 minutes ago Up 29 minutes 0.0.0.0:5000->5000/tcp, :::5000->5000/tcp redash_server_1
d76452b61c05 redash/redash:10.0.0.b50363 "/app/bin/docker-ent…" 29 minutes ago Up 29 minutes 5000/tcp redash_scheduled_worker_1
bdb4abf6add8 postgres:9.6-alpine "docker-entrypoint.s…" 29 minutes ago Up 29 minutes 5432/tcp redash_postgres_1
e7c5d5c728f0 redis:5.0-alpine "docker-entrypoint.s…" 29 minutes ago Up 29 minutes 6379/tcp redash_redis_1

# docker volume ls

DRIVER VOLUME NAME
local 1a263c126a3fa108709d8b0060046e6c023996cecdbc10349675fede07de075a
local 4b3a5dd945067085e2c1d077a34b6653f816ae79238f223de1a5f250ff2550f4
local 7b2710744518290454309f281077ef55e5612a907587b4f44a1bfff90495a8ab
local 9daadd66a89e5a52b9db4d214c95a40a1dd1e400707a5f0f5f4a761c1f856b77
local 11ac7dc5cbf71b6b6e63de5de2a844447905bd4e9ac08b8b12598166e00edb5a
local 60a794b2391635a05e70ca7266fdf13f4d3f1d3df3d6afb3c6a8c7fc1d97425e
local 60dfde021fc83147a095566c0ff188bf2d8ba49b4cac2f6f7298ab7651808f40
local 89bf515874f75b59aca593bfd5a343cce2c591fb0dc737e4686740bb55607ee3
local 749b2b5821869475d95333ba97d8c433d2113c0087449e091bf6092ce181bb81
local a0ac1ac5ed72ec6a83a2f8e6bcbfa5220b01e686b0d231b06e2eff95e7837909
local ade4bb7d65e35373410d561ab887eed3adc731efe70d8b4343b4acc0c279f790
local b48a37670ed2cb24d62a83bc53495438aa170f62f1b335cba2bd934f1aac2fe8
local dd9844f721be7b6b71f81b3d89771bcf58a4a94123f5b41e9b5d0f8afddf1be4
local df6706f85090009cd8662760f63d9de7f1a344afb89277f8b35f5eb26b95b8b4
local e0c6b5a287b328dd79b1d187a3f0252b765a1647f813cf522410b03cc2b940d3
local f3b8b8c0aa30a4df5e8ca4187b7be10bfa1f713721b12602af2f57ea6b4ccfa1
local f5e95e3c34833f1f905bb48a7450a0d4b74f386ab2603ac107a018bbe3145732
local fc51981092de0d7972e028a9b92092effa53cfc092798d06058be391bd0de297
local fdd4758737119595cfb5840bb397d35f144ae5591be4e668d9945716a2b3b823
local my_data

Is data stored in containers or in separate volumes?

[jekeam]

I found a volume, which is probably my old database, because... it is the largest in size, can I probably remount it?

# du -sh /var/lib/docker/volumes/*

576K    /var/lib/docker/volumes/11ac7dc5cbf71b6b6e63de5de2a844447905bd4e9ac08b8b12598166e00edb5a
576K    /var/lib/docker/volumes/1a263c126a3fa108709d8b0060046e6c023996cecdbc10349675fede07de075a
576K    /var/lib/docker/volumes/4b3a5dd945067085e2c1d077a34b6653f816ae79238f223de1a5f250ff2550f4
576K    /var/lib/docker/volumes/60a794b2391635a05e70ca7266fdf13f4d3f1d3df3d6afb3c6a8c7fc1d97425e
16K     /var/lib/docker/volumes/60dfde021fc83147a095566c0ff188bf2d8ba49b4cac2f6f7298ab7651808f40
576K    /var/lib/docker/volumes/749b2b5821869475d95333ba97d8c433d2113c0087449e091bf6092ce181bb81
128M    /var/lib/docker/volumes/7b2710744518290454309f281077ef55e5612a907587b4f44a1bfff90495a8ab
576K    /var/lib/docker/volumes/89bf515874f75b59aca593bfd5a343cce2c591fb0dc737e4686740bb55607ee3
576K    /var/lib/docker/volumes/9daadd66a89e5a52b9db4d214c95a40a1dd1e400707a5f0f5f4a761c1f856b77
576K    /var/lib/docker/volumes/a0ac1ac5ed72ec6a83a2f8e6bcbfa5220b01e686b0d231b06e2eff95e7837909
39M     /var/lib/docker/volumes/ade4bb7d65e35373410d561ab887eed3adc731efe70d8b4343b4acc0c279f790
28K     /var/lib/docker/volumes/b48a37670ed2cb24d62a83bc53495438aa170f62f1b335cba2bd934f1aac2fe8
0       /var/lib/docker/volumes/backingFsBlockDev
576K    /var/lib/docker/volumes/dd9844f721be7b6b71f81b3d89771bcf58a4a94123f5b41e9b5d0f8afddf1be4
576K    /var/lib/docker/volumes/df6706f85090009cd8662760f63d9de7f1a344afb89277f8b35f5eb26b95b8b4
620K    /var/lib/docker/volumes/e0c6b5a287b328dd79b1d187a3f0252b765a1647f813cf522410b03cc2b940d3
72K     /var/lib/docker/volumes/f3b8b8c0aa30a4df5e8ca4187b7be10bfa1f713721b12602af2f57ea6b4ccfa1
576K    /var/lib/docker/volumes/f5e95e3c34833f1f905bb48a7450a0d4b74f386ab2603ac107a018bbe3145732
576K    /var/lib/docker/volumes/fc51981092de0d7972e028a9b92092effa53cfc092798d06058be391bd0de297
576K    /var/lib/docker/volumes/fdd4758737119595cfb5840bb397d35f144ae5591be4e668d9945716a2b3b823
32K     /var/lib/docker/volumes/metadata.db
4.8M    /var/lib/docker/volumes/my_data

# docker volume inspect 7b2710744518290454309f281077ef55e5612a907587b4f44a1bfff90495a8ab

[
    {
        "CreatedAt": "2023-12-10T06:45:21Z",
        "Driver": "local",
        "Labels": null,
        "Mountpoint": "/var/lib/docker/volumes/7b2710744518290454309f281077ef55e5612a907587b4f44a1bfff90495a8ab/_data",
        "Name": "7b2710744518290454309f281077ef55e5612a907587b4f44a1bfff90495a8ab",
        "Options": null,
        "Scope": "local"
    }
]

# ls /var/lib/docker/volumes/7b2710744518290454309f281077ef55e5612a907587b4f44a1bfff90495a8ab/_data

base    pg_clog       pg_dynshmem  pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  postgresql.auto.conf  postmaster.opts
global  pg_commit_ts  pg_hba.conf  pg_logical     pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xlog     postgresql.conf

[justinclift]

Uh oh. Are you not backing up your database and environment file?

Also, if Redash is installed in the standard /opt/redash directory... what does an ls -lah of that directory show?

[jekeam]

Redash is not installed in the standard directory:

root@178-21-10-23:~/redash-stepik-course-master/redash-stepik-course-master/redash# ls -lah

total 28K
drwxr-xr-x 2 root root 4.0K Dec 10 06:40 .
drwxr-xr-x 3 root root 4.0K Oct  5  2022 ..
-rw-r--r-- 1 root root 1.5K Nov 13  2022 docker-compose.yml
-rw-r--r-- 1 root root 1.3K Nov 13  2022 docker-compose.yml_back
-rw-r--r-- 1 root root  552 Dec 10 06:40 redash.env
-rw-r--r-- 1 root root  520 Nov 13  2022 redash.env_back
-rw-r--r-- 1 root root   21 Oct  5  2022 start_redash.bat

[justinclift]

k. It looks like that's not set up in a production oriented way, as there's no PostgreSQL data directory showing up in that output:

https://github.com/getredash/setup/blob/e611a6af966124eb974d1d953f64a10c5e9767e6/data/docker-compose.yml#L41-L42

---

That being said, it shouldn't hurt to try using the data from the old database.

[jekeam]

How can I remount a volume from ade4bb7d65e35373410d561ab887eed3adc731efe70d8b4343b4acc0c279f790 for a container bdb4abf6add8 to 7b2710744518290454309f281077ef55e5612a907587b 4f44a1bfff90495a8ab?

[justinclift]

That's a good question. I've never needed to try mounting stuff out of an old docker volume like that before. First things first though, make a complete copy of that old volume somehow, just in case whatever is tried next goes badly. 😄

[jekeam]

Yes, I already made a backup of it 😅

[justinclift]

What I'd probably do is this:

1. Make a backup of that old volume (somehow), such that you can restore it if things goes badly
2. Also make a copy of those two environment files (redash.env, redash.env_back)
3. Add a persistent volume option to your docker-compose.yml (like this one), so the database doesn't get lost when you restart Redash in future.
4. Run the command to create a fresh new database in that new volume location: docker compose run server create_db <-- this is just to create the directory and initial files
5. Shut down Redash again (docker compose down)
6. Manually copy the files from that old volume into the new persistent storage volume. Don't do this blindly, make sure the structure matches up first

In theory, that should all work ok and be the manual equivalent of restoring the database. If that works, then starting Redash should have all of your stuff in it... 😅

Er... good luck? 😁

[jekeam]

Thank you very much, you helped me a lot, I restored the database.

[justinclift]

Awesome. Well done. 😄

[justinclift]

Oh, be aware that the environment file (should) contain a value in it for encrypting some of the important data in the database (an encryption key). That's why it's important to back up the environment file.

If you correctly restore the database, but are using an environment file with a mismatching encryption key... then the Redash database still won't be loaded fully. It'll only load the unencrypted parts of it.