SSD Nodes Learn Hosting plans →
How to do am Matt ConnorBy Matt Connor

Database suppose run for Docker or for host?

Make you no fear to run Postgres, MySQL, or Redis for Docker container. We explain how to handle volumes, backups, and memory limits so your production setup no go crash.

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.

Container actually wetin e change

E no change storage path, as long as you mount one. Na the same kernel, same page cache, and same filesystem.

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

Wetin genuinely 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 inside database container wey go survive the next docker compose pull.
  • Memory accounting. cgroup limit na hard wall wey kernel dey enforce, and most database defaults no dey size themselves to am. MySQL 9.3 or later dey read the limit for some settings. MongoDB dey try do am for its WiredTiger cache, but its own documentation warn say e fit no do am for some cases. PostgreSQL and Redis no dey.
  • The user. Process dey run as numeric user id inside the container, and that user fit own nothing for your host.

Where data dey live na wetin decide everything

Two good options dey, and one common mistake dey.

  • 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 the first time e run. Na this be the default answer.
  • A bind mount: /srv/appname/pg:/var/lib/postgresql/data. You choose the path, so na you go handle the permissions problem.
  • No mount at all. See above. The data dey inside the container.

The full trade-off na topic on its own, 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.

Take care with network storage. No put database data directory for NFS (network file system), or any other network mount whose locking and fsync behaviour you never test. PostgreSQL document one firm requirement for NFS: the mount must use the hard option. E also strongly recommend the sync export option for the NFS server, because without am, an fsync for the client no guarantee say e go reach the server disk. Databases assume say successful fsync mean the bytes don reach stable storage. When that assumption no correct, corruption fit happen and show up 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.

Wetin own the data directory

The official Postgres, MySQL and MongoDB images dey run their server with unprivileged numeric user id: 999 for their default tags, and 70 for the Postgres -alpine tags. When the container start as root, the entrypoint change ownership of the data directory to that user, then drop the extra privileges. Na why empty bind mount dey usually work on the first try.

E go break immediately once you set user: for the compose file, because the entrypoint no longer get privilege 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).

The MongoDB entrypoint dey fix ownership the same way, so this lock file error mean say e no fit do am. Usually, na because user: dey set, or the mount no allow chown from root:

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. For a Postgres -alpine image, use 70:70 instead and expect 70 70. The account wey dem call postgres for your host and the account wey dem call postgres inside the image no relate to each other. The kernel dey compare numbers, while each side dey look up the names separately. how PUID and PGID dey map host users into a container explain that mapping properly. With 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 unnecessary for the first run, because Docker dey create empty directory and the entrypoint dey own am.

Upgrades: package upgrade compared with image tag change

For host, apt upgrade dey move you through minor version. Your distribution no go jump database major version by itself. When you choose to jump, both binary sets fit install together. Na exactly wetin pg_upgrade need.

For container, the tag na the version. So upgrade na to edit one line. This make minor upgrade very easy, but major upgrade 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. This layout 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 dey read PostgreSQL database cluster dump complete. If the file get only few hundred bytes, the dump fail. 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

Plan for one more change at postgres:18. From 18 upward, the official image dey keep data for versioned directory under /var/lib/postgresql. For 18, na /var/lib/postgresql/18/docker. E declare its volume at /var/lib/postgresql. If volume still mount at /var/lib/postgresql/data, the container go exit with error wey start with Error: in 18+, these Docker images are configured to store database data. When you move to 18 or later, change the mount to pgdata:/var/lib/postgresql. The /var/lib/postgresql/data paths for this post na for 17 and earlier.

The other database engines different:

  • MySQL 8 dey upgrade its own data dictionary when e start. So minor tag change 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 the server start on 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 version, mongod no go start and e go log UPGRADE PROBLEM line wey name featureCompatibilityVersion. From MongoDB 7.0 upward, 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.

Why my database container dey exit with code 137?

Because kernel OOM killer kill am. 137 na 128 plus signal 9.

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

docker compose ps --all dey show Exited (137). Without --all, Compose dey list only containers wey dey run, so the dead database simply no dey for the output. The inspect line dey read "OOMKilled": true, and kernel log get matching entry:

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

OOMKilled only tell you say OOM killer end one process inside the container. Na Memory cgroup out of memory prefix talk say na the container own limit cause am. Host-wide shortage dey log Out of memory: Killed process instead, and for that case, na the host you need fix, no be the container.

This na how e work, and e dey surprise people. Inside the container, free and /proc/meminfo still report the host total memory, and cgroup limit no change that number. PostgreSQL no dey size anything from am: shared_buffers default na 128MB, and any value wey pass am na the one you or tuning guide choose. MySQL innodb_buffer_pool_size too default na 128M. Trouble start when settings dey sized from wetin the machine report. PostgreSQL own guidance suggest 25% of system memory as starting shared_buffers, so for 16 GB host with 2 GB limit, that rule give 4 GB, and cgroup go kill the database long before host itself get any pressure. Some engines now dey read the limit for some defaults: MySQL 9.3 and later dey do am, and MongoDB dey use am for its WiredTiger cache when e fit detect am. Neither of dem size everything from am, and MongoDB own documentation talk say detection fit fail. So memory limit alone no enough. You must also tell the database how much e get:

  • PostgreSQL: set shared_buffers, and pay attention to work_mem. work_mem apply to each sort or hash operation, and every connection fit run several at once, so generous value times fifty connections na the usual cause of container wey die under load instead of for startup.
  • MySQL and MariaDB: set innodb_buffer_pool_size, wey default na 128M. For MySQL before 9.3, leave innodb_dedicated_server off inside container, because e dey size buffer pool from machine memory, and for this case na host memory e mean. From 9.3 onward, MySQL dey read the container limits and innodb_dedicated_server dey size from dem, but explicit buffer pool size still na the predictable choice.
  • MongoDB: set the WiredTiger cache size explicitly. MongoDB dey use container limit when e fit detect am (hostInfo report am as memLimitMB), but its own documentation warn say e fit no detect am for some cases and tell you to set --wiredTigerCacheSizeGB or --wiredTigerCacheSizePct below the container memory.
  • Redis: maxmemory default na unlimited, so Redis go grow until cgroup stop am. Set maxmemory well below the container limit and choose a maxmemory-policy.

Postgres too dey report the event from its own side, and na this pair of 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, every other backend must restart, because shared memory fit don become inconsistent. This one go cause connection storm for your application; e no be quiet event. how to set memory limits for Docker Compose cover the syntax and the difference between mem_limit and the deploy.resources form.

None of this disappear for host. E only move. Without cgroup, database dey compete with everything else for the machine, and host OOM killer dey choose victim by score, and sshd fit be the victim. Limit wey kill database predictably 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

E safe to run production database inside Docker?

Yes, for application stack wey dey run for one server. Container na Linux process wey namespaces and cgroups dey around am, so once you mount volume, database go write to the same host filesystem wey e for use if you install am from package. The risks na operational ones, no be about 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 these four things and 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.

I suppose 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 fit silently create fresh empty volume and empty database. Bind mount dey okay if you chown the host directory to the numeric user id wey image dey run as. Na 999 for the default tags of official Postgres, MySQL and MongoDB images, and 70 for the Postgres -alpine tags. Verify am with ls -ldn, because ls -l go show your host's name for that number, and that name no get meaning inside container.

Wetin docker compose down -v delete?

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

How I fit upgrade PostgreSQL to new major version inside 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 DETAIL line wey name both versions, because the new binaries no fit read the old catalog layout. Nothing spoil: put the old tag back and e go start. Take a pg_dumpall with the new version's client against the old container wey dey run. Confirm say the file end with PostgreSQL database cluster dump complete. Then bring up the new tag on empty volume and load the dump. From postgres:18 onward, mount that volume at /var/lib/postgresql instead of /var/lib/postgresql/data. Minor upgrades within 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 completely. Run docker inspect <container> | grep -i oomkilled. If value na true, OOM killer don end process inside container, usually because container reach its own cgroup memory limit. The common cause na memory settings wey dem size for host instead of container. Tools inside container still report the host's total memory, so buffer wey dem size for 16 GB fit end up inside 2 GB. Set shared_buffers and work_mem, or innodb_buffer_pool_size, to match the limit wey you give container. Check journalctl -k for matching Memory cgroup out of memory line to confirm which process kernel choose, and say na container limit cause am, no be host-wide Out of memory kill.

Dis article help you?