Snaplet Instant Preview Databases PostgreSQL on Fly Machines

custom-entrypoint.sh

#!/bin/sh

(sleep 30; crond -l 8) &

docker-entrypoint.sh postgres -c "config_file=/etc/postgresql/postgresql.conf"

Dockerfile

FROM postgres:14.4-alpine

COPY postgresql.conf /etc/postgresql/

COPY stop-postgres-on-idle.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/stop-postgres-on-idle.sh
RUN echo '*  *  *  *  *    /usr/local/bin/stop-postgres-on-idle.sh' > /etc/crontabs/root

COPY custom-entrypoint.sh /usr/local/bin/
RUN chmod +x /usr/local/bin/custom-entrypoint.sh

ENTRYPOINT ["custom-entrypoint.sh"]

stop-postgres-on-idle.sh

#!/bin/sh

pg_isready || exit $?

# in minutes
max_idle_time=5

query=$(cat <<SQL
with
non_idling_connections as (select * from pg_stat_activity where state is not null and state != 'idle'),
recent_idling_connections as (select * from pg_stat_activity where state = 'idle' and (now() - query_start) < interval '$max_idle_time minutes')
select count(*) from (
  select * from non_idling_connections
  union
  select * from recent_idling_connections
) t
where pid != pg_backend_pid() and backend_type = 'client backend' and client_addr is not null and client_addr not in (inet '127.0.0.1', inet '::1')
SQL
)

active_connections_count=$(echo "$query" | psql -U postgres -tA)

last_disconnection_time=$(date -d "$(cat "$PGDATA/log/postgresql.log" | grep "disconnection" | grep -vw "host=127.0.0.1\|host=::1\|host=\[local\]" | tail -n 1 | cut -c1-19)" +%s 2>/dev/null || echo 0)
now=$(date +%s)
time_since_last_disconnection="$((now - last_disconnection_time))"

if [ "$active_connections_count" == "0" ] && [ $time_since_last_disconnection -gt "$((max_idle_time * 60))" ]; then
  su postgres -c 'pg_ctl -D "$PGDATA" -m fast stop'
fi