I have some general questions about how best to build and manage DB servers (Postgres and MySQL mostly) when using Docker builds with docker-compose.yml. In the past, I've sometimes bound the MySQL and Postgres DB's to a bound folder on the host FS. There are pluses and minuses to that it seems, but it seems like it might be better to use docker volumes instead. Those are not quite a 'visible' on the host because different setups and OS's might put the docker volumes in different places ( /var/lib/docker/volumes for LINUX), and I often develop on MacOS or Windows, but deploy on LINUX.
A simple example for Postgres is:
- ./Postgres:/var/lib/postgresql/data vs. Postgres:/var/lib/postgresql/data
command: docker-entrypoint.sh -c 'autovacuum=on' -c 'track_counts=on'
where I have a host folder "./Postgres" in binding to host and create / use a docker volume in the 2nd case.
I'm just trying various configurations to optimize performance and portability, taking into account that some servers might have the system on an SSD and other mounted parts of the file system on a RAID, a NFS or in the Cloud even.
Just looking for some suggestions as to have to optimize DB performance in that kind of setup. My MySQL DB's are not that large, i.e. maybe 2GB, and the postgres DB's are even smaller, although both could grow to maybe 10 GB over the next few years. I've read that running ACUUM, ANALYZE, and REINDEX in postgres can be helpful.
Also, if I wanted to migrate it seems like some options would be to copy my host folder contents to the volume using something like this:
docker run --rm \
-v ./MySQL_DB:/from \
-v MySQL_DB:/to \
alpine ash -c "cd /from ; cp -av . /to"
vs. just creating a new DB on the new volumes and then importing dumped DB's into the volumes using the CLI in the Containers ?