Differences

This shows you the differences between two versions of the page.

--- postgresql [2023/08/03 11:33] – admin
+++ postgresql [2026/03/08 18:07] (current) – admin
@@ Line 1: / Line 1: @@
 ====== PostgreSQL ======
-===== Password fix =====
-<code sql>
-SELECT
-    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
-FROM pg_authid
-WHERE rolcanlogin;
-</code>
-<code>
-\password postgres
-</code>
 ===== DEV refresh =====
 ==== Backup fuelwatcher on PROD ====
 <code bash>
-docker exec -it FW_PostgreSQL /bin/bash
+docker exec -it FT_PostgreSQL /bin/bash
 cd /var/lib/postgresql/data/backups/
-pg_dump -d fuelwatcher > fuelwatcher_all_`date +%Y-%m-%dT%H-%M-%S`.backup
+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/
+rsync -av /var/lib/docker/volumes/fueltracker_postgres-data/_data/backups/ /media/usb/backup/postgresql/backups/
 </code>
-Copy backup to STONARD with MobaXterm
-Copy backup to Container with beyond Compare (backups <--> RPi-WiFi backups)
 ==== Restore on DEV ====
+Copy //daily// backup from PROD to DEV using Beyond Compare config **Docker/backups (WSL <--> KARGATH)**
 Login:
 <code bash>
-docker exec -it dev-container_devcontainer_db_1 /bin/bash
+docker exec -it FT_PostgreSQL /bin/bash
 su - postgres
@@ Line 41: / Line 28: @@
 DROP DATABASE fuelwatcher;
-CREATE DATABASE fuelwatcher
+CREATE DATABASE fueltracker
     WITH
     OWNER = postgres
@@ Line 53: / Line 40: @@
 <code>
 cd /var/lib/postgresql/data/backups/
-pg_dump -d fuelwatcher > fuelwatcher_all_`date +%Y-%m-%dT%H-%M-%S`.backup
+pg_dump -d fueltracker > fueltracker_FALKREATH_`date +%Y-%m-%dT%H-%M-%S`.backup
-psql -d fuelwatcher < fuelwatcher_all_2021-08-01T05-23-25.backup
+psql -d fuelwatcher < fueltracker_RPi64_2021-08-01T05-23-25.backup
 </code>
@@ Line 60: / Line 47: @@
 <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/FuelTracker/data/STONARD\ backups/
-cd /workspace/fuelwatcher/data/RPi-WiFi\ backups/
+cd /workspace/FuelTracker/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
+pg_dump -Cs fueltracker > /workspace/FuelTracker/backups/fueltracker_FALKREATH_`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 =====
-How to Upgrade Your PostgreSQL Passwords to SCRAM - https://blog.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram
+[[https://www.postgresqltutorial.com/postgresql-administration/postgresql-restore-database/|PostgreSQL Restore Database]]
-Postgres dump database schema with or without data - https://www.chrisnewland.com/postgres-dump-database-schema-with-or-without-data-61
+[[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]]