How to SSH into CapRover and dump a postgres database?!
reddit / selfhosted

----------------------------------------------------------------------
oh_jaimito · 2023-10-28 22:10

I felt it better to post here, as a quick search for `CapRover` showed
more posts here, than on /r/webdev.

I am a web developer and recently just installed CapRover on a
DigitalOcean droplet. It also has a Directus installation, it's a
headless CMS with postgres.

I'm not super familiar with Docker/containers, as I just don't use
them often enough, but wanted to ask here:

I'm sure I can SSH into the server and run some command to make a
backup of the postgres database instance?

My goal is to make/dump a 'Initial-Database.sql' and then
occasionally/incrementally perform backups of the database.

I'm just not familiar enough with CapRover to do so. I didn't read
anything in the docs regarding this.

----------------------------------------------------------------------
thekrautboy · 2023-10-28 23:15

You simply SSH into the Docker host machine, just like you would SSH
into any other machine if it has a SSH server running.

https://www.digitalocean.com/community/tutorials/how-to-use-ssh-to-
connect-to-a-remote-server

Once logged in, you can exec into the specific Docker container
(postgres) and run a dump command.

Example `docker exec -it postgres-containername sh` if the container
image has `sh` as shell in their `PATH`, many do. If it fails, try
`bash` or `\bin\bash\` etc. There is no general rule, it depend on the
exact image that is used.

https://docs.docker.com/engine/reference/commandline/exec/

Once logged into the container you can run a tool like `pgdump` if it
exists in the image to produce a dump of the database. You could
either save the output as a file inside the container, ideally to a
path that is already mapped to a folder on the Docker host. Or
redirect the output directly to a file on the host. Again, it all
depends.

https://www.postgresql.org/docs/current/app-pgdump.html


Why not simply look at the Docker documentation, and the Postgres
documentation? And if you dont know how to SSH into a host, maybe
start with subs like /r/LinuxQuestions and /r/Linux4Noobs. This sub
here is not a general techsupport-for-everything sub.

----------------------------------------------------------------------
oh_jaimito · 2023-10-29 05:51

THANK YOU, tremendously, for providing all those links. They proved
very helpful.

Initially I asked here, instead of /r/webdev, or the linux-related
subs, as I didn't know if there was a `CapRover` way of doing this -
or maybe an app that would make this simpler (one-click).

But it turns out, basic bash is all I needed 👍

---

> This sub here is not a general techsupport-for-everything sub.

I understand that.

Here is what I came up with.

```sh
#!/bin/bash

# dump_remote.sh

timestamp=$(TZ=":America/Denver" date +"%Y%m%d_%H%M%S")

ssh USER@www.xxxx.yyyy.zzzz "sleep 10 && docker exec -i srv-captain--
qwerty-postgres.xxxxx /bin/bash -c 'PGPASSWORD=xxxxxx pg_dump
--username user1 database1' > ~/dumps/dump_${timestamp}.sql && cp
~/dumps/dump_${timestamp}.sql ~/dumps/dump.sql"
```

It took me a while to find the name of the container.

Experiment with different flags.

I wanted to run the `cp` command, so I could run a `git diff` and see
what changed.

---

Thanks again for the guidance in the right direction!