Arrrrgh! R3 won't restart and I need to do a restore on the prod server :O

Umb

Hi - something weird happened on my production server - I had a symbolic link to c:\rei3 in my onedrive folder so I would have a neat and up to date copy of everything to restore quickly but the permissions were changed on the symbolic link and it removed the files from the local filesystem. This caused postgres to panic & shutdown but when I copied everything back into c:\rei3 it seems to have caused some corruption somewhere :O and upshot is that I now need to restore from the last backup ... the trouble is that it's the installed version (on a windows base) and I don't know what the correct commands are in that configuration so that it restores everything to the correct folder (assuming c:\rei3\database). I ran postgres.exe in the command line and got this:

C:\Program Files\REI3\pgsql16\bin>postgres -D c:\rei3\database\
2025-11-01 21:39:37.176 CET [10640] LOG: starting PostgreSQL 16.1, compiled by Visual C++ build 1937, 64-bit
2025-11-01 21:39:37.180 CET [10640] LOG: listening on IPv6 address "::1", port 5432
2025-11-01 21:39:37.180 CET [10640] LOG: listening on IPv4 address "127.0.0.1", port 5432
2025-11-01 21:39:37.235 CET [5264] LOG: database system was interrupted while in recovery at 2025-11-01 21:39:13 CET
2025-11-01 21:39:37.235 CET [5264] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
2025-11-01 21:39:38.740 CET [5264] LOG: database system was not properly shut down; automatic recovery in progress
2025-11-01 21:39:38.754 CET [5264] LOG: redo starts at 1A8/AA7C8BD8
2025-11-01 21:39:38.757 CET [5264] PANIC: invalid lp
2025-11-01 21:39:38.757 CET [5264] CONTEXT: WAL redo at 1A8/AA7C8E20 for Heap/LOCK: xmax: 255730, off: 33, infobits: [IS_MULTI, EXCL_LOCK], flags: 0x00; blkref #0: rel 1663/16388/17513, blk 0
2025-11-01 21:39:40.372 CET [10640] LOG: startup process (PID 5264) was terminated by exception 0xC0000409
2025-11-01 21:39:40.372 CET [10640] HINT: See C include file "ntstatus.h" for a description of the hexadecimal value.
2025-11-01 21:39:40.373 CET [10640] LOG: terminating any other active server processes
2025-11-01 21:39:40.388 CET [10640] LOG: shutting down due to startup process failure
2025-11-01 21:39:40.391 CET [10640] LOG: database system is shut down

Can anyone let me know the correct command & options needed to restore from the last backup!

Any help is greatly appreciated ;)

gabriel

To recover the DB, you use the Postgres utilities. The commands are listed here:
https://rei3.de/en/docs/admin#database

Running on Windows, the utilities are in the r3 directory under pgsql\bin.

Please do not combine a database system with any kind of file sync solution like OneDrive or Nextcloud. You can safely sync backups, but do not run a database system from a managed folder.

Umb

Thankyou - and yes ... I will definitely be removing that symlink asap!

Umb

Hey Gabriel

I tried following the instructions in the link but they're quite vague and I'm not that experienced with Postgre especially on a CLI level so, as you can imagine ... it's not going so well! :D

I tried using initdb to create a new database in an alternate folder which did work but I keep getting a Access Denied error when I try to start it - I just don't know enough about Postgre to follow the instructions on the link - Is there any chance you could take me through the entire process with step by step instructions like I'm a 5 year old! I know this is a bit of an ask but I'm sure it would also be a valuable tutorial resource for others who are also not experienced enough with Postgre to follow the instructions successfully ... like me ;)

In the end though, I spun up a new portable instance, created an app2 db in Postbird and restored to that, then removed the app db from postgres and renamed app2 to app. Then I reinstalled my application via r3 and it all worked. Finally I zipped up the database folder, transferred it to my prod server and updated the password in the config.json ... finally it all worked - after all this I'm thinking I might make an update the R3 Toolshop to handle the restore process for both normal restores and completely broken instances where postgres won't even start! I suppose I could also make it perform both full & incremental backups too at a user set time with user defined intervals in minutes ... but the main aim would be to facilitate 1 click recoveries without days of pulling my hair out :D

gabriel

Umb I know this is a bit of an ask but I'm sure it would also be a valuable tutorial resource for others who are also not experienced enough with Postgre to follow the instructions successfully ... like me ;)

I have explained the procedure in the forum multiple times in the past. Since you have not found it, I don´t think that others would benefit too much from doing it yet one more time - unless you have not looked for it yet 😉

Since you were able to restore your system to working order, I assume it is not urgent anymore.

I think it would be more useful to update our admin docs instead. We have not put the step-to-step explanation there yet, because it depends on multiple factors (which deployment model, which OS, what tools are used, etc.). But I´ll take another crack at it when I find some free time. Maybe I can add more detail, without making things too complicated.