✎ postgresql [It's in The Wiki]

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== PostgreSQL ======
===== DEV refresh =====
==== Backup fuelwatcher on PROD ====
<code bash>
docker exec -it FW_PostgreSQL /bin/bash
cd /var/lib/postgresql/data/backups/
pg_dump -d fuelwatcher > fuelwatcher_RPi64_`date +%Y-%m-%dT%H-%M-%S`.backup
</code>
Login to root:
<code bash>
sudo su -
rsync -av /var/lib/docker/volumes/fw_stack_postgres-data/_data/backups/ /media/usb/backup/postgresql/backups/
</code>

==== Restore on DEV ====
Copy //daily// backup from PROD to DEV using Beyond Compare config **Docker/backups (WSL <--> KARGATH)**
Login:
<code bash>
docker exec -it FW_PostgreSQL /bin/bash
su - postgres

psql
</code>
DROP and CREATE DATABASE
<code sql>
select pg_terminate_backend(pid) from pg_stat_activity where datname='fuelwatcher';

DROP DATABASE fuelwatcher;

CREATE DATABASE fuelwatcher
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
</code>
Backup DEV then Restore PROD to DEV:
<code>
cd /var/lib/postgresql/data/backups/
pg_dump -d fuelwatcher > fuelwatcher_STONARD_`date +%Y-%m-%dT%H-%M-%S`.backup
psql -d fuelwatcher < fuelwatcher_RPi64_2021-08-01T05-23-25.backup
</code>

TMP
<code bash>
pg_dumpall > dev-container_devcontainer_db_1_all_`date +%Y-%m-%dT%H-%M-%S`.backup
cd /workspace/fuelwatcher/data/STONARD\ backups/
cd /workspace/fuelwatcher/data/RPi-WiFi\ backups/
</code>
===== Data Dictionary export =====
For comparison between PROD and DEV. Perform on both:
<code bash>
pg_dump -Cs fuelwatcher > /workspace/FuelWatcher/backups/fuelwatcher_STONARD_`date +%Y-%m-%dT%H-%M-%S`.bak
</code>
===== Version upgrade =====
Test (gunzipped) restore of daily PROD backup to test PostgreSQL server (new version).
<code>
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
</code>

Stop both PostgreSQL servers.

Move PROD directory aside and clone test data files into place (perms are important).
<code>
mv _data data_bak
rsync -av ../postgresql-test_test-postgres-data/_data .
</code>

Move backups back to proper location
<code>
mv data_bak/backups/* _data/backups/
</code>

===== Password fix =====
<code sql>
SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;
</code>
<code>
\password postgres
</code>

===== References =====
[[https://www.postgresqltutorial.com/postgresql-administration/postgresql-restore-database/|PostgreSQL Restore Database]]

[[https://blog.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram|How to Upgrade Your PostgreSQL Passwords to SCRAM ]]

[[https://www.chrisnewland.com/postgres-dump-database-schema-with-or-without-data-61|Postgres dump database schema with or without data]]

[[https://www.commandprompt.com/education/postgresql-basic-psql-commands/|PostgreSQL: Basic psql Commands]]

[[https://www.kostolansky.sk/posts/upgrading-to-postgresql-15/|How to upgrade PostgreSQL from 14 to 15]]

Edit summary