Cron in postgresql:alpine docker container

Question

I am using the "plain" postgresql:alpine docker image, but have to schedule a database backup daily. I think this is a pretty common task.

I created a script backupand stored in the container in /etc/periodic/15min, and made it executable:

bash-4.4# ls -l /etc/periodic/15min/
total 4
-rwxr-xr-x    1 root     root            95 Mar  2 15:44 backup

I tried executing it manually, that works fine.

My problem is getting crond to run automatically.

If I exec docker exec my-postgresql-container crond, the deamon is started and cron works, but I would like to embed this into my Dockerfile

FROM postgres:alpine

# my backup script, MUST NOT have .sh extension
COPY backup.sh /etc/periodic/15min/backup 
RUN chmod a+x /etc/periodic/15min/backup

RUN crond # <- doesn't work

I have no idea how to rewrite or overwrite the commands in the official image. For update reasons I also would like to stay on these images, if possible.

Chico

I was facing the same problem but did not want to go down the road of multiple services. In my case i built a separate container: github.com/piccaso/docker-postgres-backup – Lancet

Answer 1

Note: This option if you would like to use the same container with multiple service

Install Supervisord which will makes you able to run crond and postgresql. The Dockerfile will be as the following:

FROM postgres:alpine
RUN apk add --no-cache supervisor
RUN mkdir /etc/supervisor.d
COPY postgres_cron.ini /etc/supervisor.d/postgres_cron.ini
ENTRYPOINT ["/usr/bin/supervisord", "-c", "/etc/supervisord.conf"]

And postgres_cron.ini will be as the following:

[supervisord]
logfile=/var/log/supervisord.log ; (main log file;default $CWD/supervisord.log)
loglevel=info                ; (log level;default info; others: debug,warn,trace)
nodaemon=true              ; (start in foreground if true;default false)

[program:postgres]
command=/usr/local/bin/docker-entrypoint.sh postgres
autostart=true
autorestart=true

[program:cron]
command =/usr/sbin/crond -f
autostart=true
autorestart=true

Then you can start the docker build process and run a container from your new image. Feel free to modify the Dockerfile or postgres_cron.ini as needed

Matronage

Dear Mostafa, thank you very much for this complete answer. You have given me the solution to my problem. Thanks! – Chico

didn't work for me – Lingulate

Answer 2

I had the exact same problem a few month ago. The key aspect is that a container can have only one main process defined by the ENTRYPOINT and/or CMD in your Dockerfile.

You cannot just swap out postgres with crond otherwise your database isn't running. It is generally recommended to separate areas of concern by using one service per container.

---

With that in mind either use a separate container which runs nothing but crond and thus Docker can both track its lifecycle, and restart it when/if it fails, the machine restarts, etc.

Or run the jobs via cron on your host using docker exec.

The third and in my opinion best (but also advanced) solution is pg_cron. It is an postgres extension and therefore runs the jobs in the same database container. Your challenge would be to adapt the configuration and installation of it.

The easy part should be the postgresql.conf:

# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

Next, you need to add the pg_cron extension to your image by adjusting the Dockerfile, which you can derive from the official alpine postgres image. The installation of it is described here.

Idempotent

Thanks Yannic! But why is it not possible to "swap out postgres with crond"? If I exec a bash and run crond in background, it works... It seem I have not understood the docker concept in this aspect... – Chico

@OlliD-Metz the general goal of docker is to deploy a single piece of functionality. In your case it's PG. While you can use bash against it, that's a secondary effect of the VM containing an OS at its base. Wha Yannic is that you should have 2 containers: PG and a cron job container that does what ever you need to do. So the 2nd container would get run every 15 minutes to pack up the DB. – Cantal

Answer 3

Ok this is 2024 and after quietly cursing the maintainers of the postgres docker for a couple of hours for making my life hell by stripping me of my root privileges in favor of a 'slightly more secure' yet 'overly complex' postgres user, here is my solution:

In my scenario, i wanted to run certbot to renew my certs every day or so.

Grab a copy of the docker-entrypoint.sh in /usr/local/bin/docker-entrypoint.sh inside the docker image

i.e. docker cp <container>:/usr/local/bin/docker-entrypoint.sh .

You will have to modify this file by adding one line to it and copying back into the image. The line to add will be shown shortly. Update the docker file like this:

FROM postgres:latest

#do your installs etc (i'm installing certbot and cron here)

# ---below is important---  
USER root
COPY ./crontab /etc/cron.d/certbot-renew
RUN chmod 0644 /etc/cron.d/certbot-renew
RUN crontab /etc/cron.d/certbot-renew
USER postgres
COPY ./docker-entrypoint.sh /usr/local/bin/docker-entrypoint.sh

clarification : drop into root mode, copy your crontab file into the container, set its permissions and register it with cron, then drop back into postgres user.

The crontab file looks like this:

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
0 5 * * * certbot renew -n >> /var/log/cron.log 2>&1

Notice that i'm sending the output to /var/log/cron.log, and not /proc/1/fd/1 - I explain why at the bottom of this answer if you are interested.

Now, start the container with docker compose however include in the docker-compose.yml file the user:0:0 option like so:

services:

  postgres:
    container_name: ${CONTAINER_NAME}
    image: ${IMAGE_NAME}
    build:
      dockerfile: Dockerfile
      context: .
    restart: unless-stopped
    user: 0:0

Here's the trick - by executing the docker compose with user root it will step into the if statement defined in 'docker-entrypoint.sh' roughly line 314 which demotes you to postgres user- this section here: modify the docker-entrypoint.sh like so:

_main() {

...
        if [ "$(id -u)" = '0' ]; then
            # then restart script as postgres user
            # !!! -HERE IS YOUR ONLY CHANCE BEFORE LOSING ROOT PRIVILEGES
            # insert this line below:
            service cron start
            # now you lose your root privileges
            exec gosu postgres "$BASH_SOURCE" "$@"
        fi

So the logic is as follows - you start the container as root, the entrypoint starts cron just before dropping you to postgres, and the container starts as normal.

You will notice that I don't log the output (in the crontab) to /proc/1/fd/1 - because yet again - the paranoid maintainers even don't allow root to publish to /proc/1/fd/1 - so to verify your cronjob is running, tail the /var/log/cron.log inside the container - yet another terribly annoying thing so you can't actually see what cron is doing via docker compose logs, You need to log into the container as root. Attempting to log the output of your job to /proc/1/fd/1 will result in 'permission denied' error and it will appear as if cron is not working, as, indeed, the job will fail with error and no output.

Lingulate