Docker Database or Host: Which One Better?

Database suppose e run for Docker or for host?

Run the database for Docker. For one application stack on one VPS, containerised PostgreSQL, MySQL, MongoDB or Redis na normal production choice. The argument wey people dey make about am usually no be the correct one. Container na Linux process wey namespaces and cgroups dey surround, no be virtual machine. So hypervisor no dey between the database and the disk. With bind mount or local named volume, reads and writes go enter host filesystem, na the same filesystem wey package install go use.

The real cost na operational work. Four things go decide whether this setup dey okay or go turn slow disaster: where the data dey, who own that directory, how major version upgrade go work, and whether you don ever restore backup before. If you get these things right, container na just small detail. If you get dem wrong, na container you go blame.

Na the same decision for every server database. The examples below use PostgreSQL, MySQL, MongoDB and Redis. We go point out the product-specific differences where dem matter.

Wetin container really dey change

No be storage path, as long as you mount one. Na the same kernel, same page cache, and same filesystem.

One real performance wahala dey, and na when you mount nothing. If volume no dey, data directory go enter the container writable layer, wey be overlay filesystem stacked on top the image. Writes for there dey slower, and the whole layer go delete when you remove the container. Na from there "my database was empty this morning" dey happen.

Wetin genuinely dey change:

• The lifecycle. docker compose down dey destroy the container. Anything wey no dey inside volume go follow am.
• The version. Image tag na the version. No apt upgrade dey inside database container wey go survive the next docker compose pull.
• Memory accounting. cgroup limit na hard wall wey kernel dey enforce, and database no know say e dey there.
• The user. Process dey run as numeric user id inside the container, and this user fit no own anything for your host.

Wia data dey decide everything

Two good options dey, plus one common mistake.

• A named volume: pgdata:/var/lib/postgresql/data. Docker go create the directory for /var/lib/docker/volumes/<project>_pgdata/_data, and the image entrypoint go set ownership for first run. Na this be the default answer.
• A bind mount: /srv/appname/pg:/var/lib/postgresql/data. You choose the path, so na you own the permissions problem.
• No mount at all. See above. The data dey inside the container.

The complete trade-off na topic for itself, and bind mounts against named volumes explain am. For database, the short version be: use a named volume unless you get specific reason to know the host path. If you use bind mount, put am for stable place like /srv/appname/pg instead of inside the project directory where a git clean fit reach am.

One hard limit: no put database data directory for NFS (network file system) or any network mount wey you never test the locking and fsync behaviour for. Databases assume say successful fsync mean say the bytes dey for stable storage. When that assumption no true, corruption fit happen and only show weeks later.

Pin volume name make volume no disappear

Compose dey name volume <project>_<volume>, and project name dey default to directory name. So volume identity depend on directory name, and na something people fit change without thinking.

Move /srv/app go /srv/app-old, or rename pgdata key for compose file, and the next docker compose up -d go create brand-new empty volume. Postgres go initialise fresh cluster inside am. Container go healthy, application go start, and every table go disappear. Old volume still dey for disk under old name, and na the good news be that.

docker volume ls
docker volume inspect app_pgdata

Pin the names so this kind thing no fit happen. Set project name and volume name explicitly:

name: myapp

services:
  db:
    image: postgres:17
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:
    name: myapp_pgdata

If stray volume already hold your data, copy am across while database stop:

docker compose stop db
docker run --rm -v app_pgdata:/from -v myapp_pgdata:/to alpine sh -c 'cp -a /from/. /to/'
docker compose start db

If you copy am while database dey run, you go get incomplete copy of files wey database dey write at that time. Stop am first.

Na who own the data directory

The official Postgres, MySQL and MongoDB images dey run their server with unprivileged user id, usually 999. When container start as root, the entrypoint dey change ownership of the data directory give that user, then e drop privileges. Na why empty bind mount usually dey work for first try.

E go break immediately once you set user: for the compose file, because entrypoint no get privilege again to fix anything. Postgres talk am directly:

initdb: error: could not change permissions of directory "/var/lib/postgresql/data": Operation not permitted

If data directory dey exist with wrong mode, e go show different message. You need recognise this one because the fix na chmod, no be chown:

FATAL:  data directory "/var/lib/postgresql/data" has invalid permissions
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

For MongoDB, bind mount wey root own go fail for the lock file:

Unable to create/open the lock file: /data/db/mongod.lock (Permission denied). Ensure the user executing mongod is the owner of the lock file and has the appropriate permissions.

The fix na to chown the host directory to the numeric id, no be to a name:

sudo chown -R 999:999 /srv/appname/pg
sudo chmod 700 /srv/appname/pg
ls -ldn /srv/appname/pg

ls -ldn dey print numbers instead of names, and e suppose show 999 999. The account wey dem call postgres for your host and the account wey dem call postgres inside the image no relate to each other. Kernel dey compare the numbers, while each side dey look up the names separately. how PUID and PGID map host users into a container explain that mapping properly. Under rootless Docker or user namespace remapping, the numbers fit shift again. So read the ids from the running container instead of assuming 999.

Named volumes make this whole section no longer necessary for first run, because Docker dey create empty directory and entrypoint dey own am.

Upgrades: package upgrade against image tag change

For host, apt upgrade go move you within minor version. Your distribution no go jump database major version by itself. When you choose to jump, both sets of binaries fit install together, and na exactly wetin pg_upgrade need.

For container, tag na the version, so upgrade na to edit one line. This one make minor upgrades very easy, but major upgrades still need proper procedure.

Change postgres:16 to postgres:17, run docker compose up -d, and the container go stop immediately:

PostgreSQL Database directory appears to contain a database; Skipping initialization
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 16, which is not compatible with this version 17.

Nothing spoil. The new binaries no fit read the old on-disk catalog layout because e dey change between major versions. Put the tag back to postgres:16 and e go start again. This rollback na the one real upgrade advantage wey containers give you.

The supported method na dump and restore. PostgreSQL prefer make newer client take the dump. So run am from the new image against the old server wey still dey run for the compose network:

docker run --rm --network myapp_default -e PGPASSWORD="$POSTGRES_PASSWORD" \
  postgres:17 pg_dumpall -h db -U postgres > /srv/backups/all.sql
ls -lh /srv/backups/all.sql
tail -n 2 /srv/backups/all.sql

The file suppose get at least tens of kilobytes and end with line wey read PostgreSQL database cluster dump complete. If the file get only few hundred bytes, the dump fail, and you dey about to delete volume for nothing. Only after you check that:

docker compose down
docker volume rm myapp_pgdata
# edit the compose file: image: postgres:17
docker compose up -d db
docker compose exec -T db psql -U postgres -f /dev/stdin < /srv/backups/all.sql

The other engines different:

• MySQL 8 dey upgrade its own data dictionary when e start. So minor tag bump usually na just restart. Read the release notes before you jump between release series, and take dump first either way.
• MariaDB expect make you run mariadb-upgrade after server don come up for the new version.
• MongoDB must upgrade one major version at a time. After every step, set the feature compatibility version before you continue. If you skip one version, mongod no go start and e go log an UPGRADE PROBLEM line wey name featureCompatibilityVersion. From MongoDB 7.0 onward, the command need explicit confirmation flag: db.adminCommand({ setFeatureCompatibilityVersion: "8.0", confirm: true }).
• Redis dey load older snapshot files without problem, but e no fit load newer ones. So upgrade na restart, while downgrade fit fail to load the data.

The general rule be say: container make downgrade easy, but e no make upgrade easier.

Wetin make my database container exit with code 137?

Na because the kernel's out of memory (OOM) killer kill am. 137 na 128 plus signal 9.

docker compose ps
docker inspect myapp-db-1 | grep -i oomkilled
journalctl -k | tail -n 20

docker compose ps show Exited (137), the inspect line read "OOMKilled": true, and the kernel log get matching entry:

Memory cgroup out of memory: Killed process 4711 (postgres) total-vm:2170416kB

Na so e happen, and plenty people dey surprised by am. PostgreSQL and MySQL dey size their buffers based on the total memory wey the host report. cgroup limit no change that number for dem. For host wey get 16 GB and limit wey be 2 GB, the database plan as if e get 16 GB, then the cgroup kill am long before the host itself get memory pressure. So memory limit alone no enough. You must also tell the database the memory wey e get:

• PostgreSQL: set shared_buffers, and pay attention to work_mem. work_mem dey allocate for each sort operation and each connection, so generous value multiplied by fifty connections na the usual reason why container dey die under load instead of for startup.
• MySQL and MariaDB: set innodb_buffer_pool_size, wey default to 128M. Leave innodb_dedicated_server off inside container, because the whole work wey e dey do na to size itself from detected machine memory.
• MongoDB: set the WiredTiger cache size explicitly instead of allowing am guess from host memory.
• Redis: maxmemory default to unlimited, so Redis go grow until the cgroup stop am. Set maxmemory well below the container limit and choose a maxmemory-policy.

Postgres still report the event from its own side, and na these two lines you go find for the log:

LOG:  server process (PID 123) was terminated by signal 9: Killed
LOG:  terminating any other active sessions due to crash of another server process

If dem kill one backend, e force every other backend to restart, because shared memory fit don become inconsistent. This one go cause connection storm for your application; e no be quiet event. setting memory limits for Docker Compose explain the syntax and the difference between mem_limit and the deploy.resources form.

None of this disappear for the host. E just move. Without cgroup, the database go compete with everything else for the machine, and the host OOM killer go choose victim based on score; that victim fit be sshd. Limit wey kill the database in predictable way easier to operate than host OOM wey lock you out.

Backups: dump inside, back up outside

No copy data directory of database wey still dey run. File-level copy wey you take while server dey write na torn copy, and you go discover am when you wan restore.

Two correct methods dey: use database own tool to create dump while e dey run, then back up the dump; or stop the container and copy the volume cold.

docker compose exec -T db pg_dump -U postgres -Fc appdb > /srv/backups/appdb.dump
docker compose exec -T db mysqldump -u root -p"$MYSQL_ROOT_PASSWORD" --single-transaction --all-databases > /srv/backups/mysql.sql
docker compose exec -T db mongodump --archive --gzip --db appdb > /srv/backups/appdb.archive.gz
docker compose exec -T redis redis-cli BGSAVE

The -T important. If e no dey, docker compose exec fit attach terminal to the command, and terminal layer go add carriage returns to the output stream. Text dump go then restore with strange errors, while binary dump go simply corrupt. E fit fail quietly during backup and show the error loudly one month later.

--single-transaction give mysqldump consistent snapshot of InnoDB tables without locking the whole server.

Those commands write one file each. Dem no be backup system: retention no dey, off-box copy no dey, and verification no dey. Give the dump directory to a tool wey handle all three, na wetin restic backups from a VPS dey do. Back up /srv/backups, no be /var/lib/docker/volumes.

Then run the restore, because backup wey you never restore no be backup:

docker compose exec -T db createdb -U postgres restore_test
docker compose exec -T db pg_restore -U postgres -d restore_test < /srv/backups/appdb.dump
docker compose exec -T db psql -U postgres -d restore_test -c '\dt'

\dt suppose list your application's tables. Empty result, or Did not find any relations., mean say the dump no be wetin you think. Drop restore_test when you don finish.

Command wey dey delete everything

docker compose down -v.

Plain down go remove the containers and the network. The -v go also remove every named volume wey dem declare for that compose file, plus every anonymous volume wey dey attach to those containers. E no go ask for confirmation, and you no fit undo am. Na one of the commonest ways self-hosted database dey get destroyed. E usually happen while person dey troubleshoot another problem, because one forum answer tell am to run the command.

Four things fit reduce the damage:

• Declare the database volume external: true. Compose no go remove volume wey no be e own, so -v no fit reach am. You go create am once with docker volume create myapp_pgdata.
• Use docker compose stop and docker compose start for normal restarts. down versus stop for Compose explain wetin each one dey remove.
• Keep dumps for host path outside every volume wey compose dey manage.
• Never paste -v from troubleshooting answer inside stack wey get data wey you care about.

No publish database port

Dis line dey put your database for public internet:

    ports:
      - "5432:5432"

E bind to every interface. Docker publish port by changing packet destination before your firewall input rules fit see am, and ufw rules dey for input chain, so ufw deny 5432 no dey do anything at all. why Docker published ports bypass ufw show how packet dey pass through the chain.

Application wey dey inside the same compose project fit reach database with service name for compose network, so e no need published port. Delete the block. If you want client for the host, bind am to loopback only:

    ports:
      - "127.0.0.1:5432:5432"

Check wetin dey listen:

sudo ss -ltnp | grep 5432

127.0.0.1:5432 na wetin you want. 0.0.0.0:5432 mean say anybody fit try your password.

Where to run wetin

One application on one VPS. Use container. Use one named volume with fixed name, no published port, memory limit wey match the database settings, and nightly dump go one host path wey restic dey collect. Start from clean Docker install for one VPS and keep the stack inside one compose file wey you commit. The benefit dey clear: database version become one line for git wey people fit review.

Host wey dey run several services. Use containers, with one database for each application, no be one shared server for all of dem. Shared server dey tie every application to one upgrade schedule, and one query wey dey consume too much resources fit make everybody outage. Give each container im own memory limit so bad query remain inside the app wey generate am. Several small Postgres instances go cost small extra disk, but coordination go reduce well-well.

The database na the product. Run am for the host from the vendor package repository, or pay for managed service. pg_upgrade need both major versions of the binaries installed at the same time. Package management fit provide this, but single-version image no fit. Replication and point in time recovery with WAL (write ahead log) archiving dey easier when database own the machine and im disks. Choose the boring option for the system wey go page you at 03:00.

The application small. Think about no running server database at all. Single-writer web application for one VPS often dey work better with SQLite for production on one VPS, where backup na one file and upgrade path na library version.

FAQ

Database wey dey run for production inside Docker safe?

Yes, for single-server application stack. Container na Linux process wey namespaces and cgroups surround, so once you mount volume, database go write for the same host filesystem wey e for use if you install am from package. The risks dey operational side, no be speed: volume wey no get pinned name, bind mount wey wrong user id own, restore wey you never test, and docker compose down -v. Fix those four things, container go dey okay. Move go host install when database na the main workload and you need pg_upgrade, replication or point in time recovery.

Make I use bind mount or named volume for database data?

Use named volume unless you get specific reason to know the host path. Docker go create the directory, and image entrypoint go set ownership when e start for the first time, so permission problem no go show. Pin the volume with explicit name: or mark am external: true; otherwise, if you rename project directory, e go silently create fresh empty volume and empty database. Bind mount dey okay if you chown the host directory to the numeric user id wey the image run as. For official Postgres, MySQL and MongoDB images, na 999. Verify am with ls -ldn, because ls -l dey show the name wey your host use for that number, and that name no get meaning inside container.

Wetin docker compose down -v delete?

E remove the containers and network like plain down. The -v additionally remove every named volume wey that compose file declare, together with every anonymous volume attached to those containers. Database dey included. No confirmation prompt dey, and no recovery dey. Volumes wey get external: true marking no go remove, and na the main reason to mark database volume external. For normal restart, use docker compose stop and docker compose start instead.

How I fit upgrade PostgreSQL to new major version for Docker?

Dump and restore. If you change postgres:16 to postgres:17 and restart, e go give FATAL: database files are incompatible with server with a DETAIL line wey name both versions, because the new binaries no fit read the old catalog layout. Nothing spoil: return the old tag, and e go start. Use the new version client to take pg_dumpall against the old container wey dey run. Confirm say the file end with PostgreSQL database cluster dump complete. Then start the new tag on empty volume and load the dump. Minor upgrades inside one major version only need pull and restart.

Why my database container dey exit with code 137?

137 na 128 plus signal 9, so something kill the process directly. Run docker inspect <container> | grep -i oomkilled. Value of true mean say container reach its cgroup memory limit. The usual cause na say PostgreSQL and MySQL dey read total memory from host, and dem no dey see the container limit. So dem plan for 16 GB while dem dey live inside 2 GB. Set shared_buffers and work_mem, or innodb_buffer_pool_size, to match the limit wey you give the container. Check journalctl -k for the matching Memory cgroup out of memory line to confirm which process the kernel choose.