Łukasz Herok

Run PostgreSql in container

Install podman (Fedora's docker) and prepare the volume to store the database data persistently. If you use docker then just replace all the podman commands with docker.

To make the container a system wide service run the commands as root (sudo):

$ dnf install podman

# Create a Pod to link more containers in the future
$ sudo podman pod create --name $PODNAME -p 5432:5432 -p 9306:9306 

$ podman pull postgres:12
$ mkdir -p /home/$USER/podman/volumes/postgres12
$ sudo podman run --name postgres12 --pod $PODNAME -e POSTGRES_PASSWORD=$PASSWORD -d  -v /home/$USER/podman/volumes/postgres12:/var/lib/postgresql/data:z postgres:12
$ sudo podman ps

$ sudo dnf install postgresql
$ psql -h localhost -W -U postgres

The :z suffix solves the permission denied problem for the volume.

Create user and database:

psql -h localhost -U postgrespsql -h localhost -U postgres

create user $DBUSER;
\password $DBUSER
create database $DBNAME;
alter database $DBNAME owner to $DBUSER;

Configure the Flask project

Change the database URL in the Flask project to: 'postgresql+psycopg2://$DBUSER:$DBPASS@localhost/$DBNAME'

sudo dnf install postgresql-devel
source venv/bin/activate
pip install psycopg2

# Restart flask application eg.

# Create schema in PostgreSql database
flask db upgrade

Migrate the database

sqlite3 app.db .schema > schema.sql
sqlite3 app.db .dump > dump.sql
grep -vx -f schema.sql dump.sql > data.sql

Edit dump file, and replace:

PRAGMA foreign_keys=OFF; > ''
INSERT INTO alembic_version .... > ''
replace(replace( > ' E'
,'\r',char(13)),'\n',char(10)) > ''
,'\r',char(13)),'\012',char(10)) > ''

You will also need to set value for the sequences, or replace ids in sql file. You can prepare the sql statements according to the template: alter sequence seq_name restart with max_id+1.

Load the data into database:

psql -h localhost -U $DBUSER $DBNAME < data.sql

Systemctl service

Configure systemd service to start container during the system startup:

$ sudo setsebool -P container_manage_cgroup on
$ sudo podman generate systemd --name $PODNAME
# Paste the generated output in proper files:
$ sudo vim /etc/systemd/system/pod-$PODNAME.service
$ sudo vim /etc/systemd/system/container-postgres12.service

 $sudo systemctl enable pod-$PODNAME.service
 $sudo systemctl enable container-postgres12.service

Backup

Do that as normal user, that will make backups

$ sudo dnf install cronie
$ sudo systemctl enable crond
$ sudo systemctl start crond
$ mkdir ~/backup
$ echo "localhost:5432:$DBUSER:$DBNAME:$DBPASS" > .pgpass
$ chmod 600 ~/.pgpass
$ crontab -e
* 3 * * * backup.sh

backup.sh

#!/bin/bash

find $BACKUP_PATH -type f  -name "*.db.gz" | xargs rm

pg_dump -h localhost -U $DBUSER $DBNAME | gzip > $BACKUP_PATH/$DBNAME-$(date +%Y-%m-%d).db.gz  2> $BACKUP_PATH/error.log

Don't forget to download backup to different host, eg:

rsync -avze ssh user@backup-host:backup backupdir

Tracking logs