PostgreSQL

docker exec -it FT_PostgreSQL /bin/bash
cd /var/lib/postgresql/data/backups/
pg_dump -d fuelwatcher > fuelwatcher_RPi64_`date +%Y-%m-%dT%H-%M-%S`.backup

Login to root:

sudo su -
rsync -av /var/lib/docker/volumes/fueltracker_postgres-data/_data/backups/ /media/usb/backup/postgresql/backups/

Copy daily backup from PROD to DEV using Beyond Compare config Docker/backups (WSL ←→ KARGATH) Login:

docker exec -it FT_PostgreSQL /bin/bash
su - postgres
 
psql

DROP and CREATE DATABASE

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='fuelwatcher';
 
DROP DATABASE fuelwatcher;
 
CREATE DATABASE fueltracker
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Backup DEV then Restore PROD to DEV:

cd /var/lib/postgresql/data/backups/
pg_dump -d fueltracker > fueltracker_FALKREATH_`date +%Y-%m-%dT%H-%M-%S`.backup
psql -d fuelwatcher < fueltracker_RPi64_2021-08-01T05-23-25.backup

TMP

pg_dumpall > dev-container_devcontainer_db_1_all_`date +%Y-%m-%dT%H-%M-%S`.backup
cd /workspace/FuelTracker/data/STONARD\ backups/
cd /workspace/FuelTracker/data/RPi-WiFi\ backups/

For comparison between PROD and DEV. Perform on both:

pg_dump -Cs fueltracker > /workspace/FuelTracker/backups/fueltracker_FALKREATH_`date +%Y-%m-%dT%H-%M-%S`.bak

Test (gunzipped) restore of daily PROD backup to test PostgreSQL server (new version).

docker exec -it TEST_PostgreSQL /bin/bash

cd /var/lib/postgresql/data/backups/
psql -U postgres -f FW_PostgreSQL_all_2023-11-28T00-00-01.bak

Stop both PostgreSQL servers.

Move PROD directory aside and clone test data files into place (perms are important).

mv _data data_bak
rsync -av ../postgresql-test_test-postgres-data/_data .

Move backups back to proper location

mv data_bak/backups/* _data/backups/

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

\password postgres

PostgreSQL Restore Database

How to Upgrade Your PostgreSQL Passwords to SCRAM

Postgres dump database schema with or without data

PostgreSQL: Basic psql Commands

How to upgrade PostgreSQL from 14 to 15