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;
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
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
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
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
podman logs postgres12