Computer hung, now database "is in recovery mode"
NorthOf45
Posts: 5,437
So my machine froze without warning (I think I need a new power supply), now CMS will not start because "the database system is in recovery mode".
Does it ever recover on its own? How long do I wait, or is it toast? There are several postgres.exe processes running, but none seem to be doing anything.
Daz 3D is part of 
Connect
DAZ Productions, Inc.
224 S 200 W, Suite #250
Salt Lake City, UT 84101
Licensing Agreement | Terms of Service | Privacy Policy | EULA
© 2024 Daz Productions Inc. All Rights Reserved.
Comments
I would reboot your PC then restart CMS
Tried that twice. No change.
Reinstalling Studio should also reinstall PostgreSQL without touching the underlying database. Maybe that might help. (For that matter, PostgreSQL has its own separate installer in the product library, but I don't have any idea if that's kept up to date separately, since the listing is old enough that it has a Valentina conversion program included in the downloads.)
If it stays in recovery mode after that, you may have a corrupted/damaged database from the freeze.I don't know how/if that can be repaired.
I decided to re-import the metadata. I have long been telling users to export your User Data to back it up, which I have been doing regularly. Time to put it to the test, myself. If it works properly, I will have only lost about 10 days of categorization (<100 packages installed, or about 0.36% of the total) since the last export, so I'm not really complaining. Just need to remember to do it more often.
All done, but a lot of stuff missing. Not happy...
This is a disaster. The custom categories are there, but half are empty. The files are installed, according to DIM, but the Product in Studio is empty. Re-importing metadata for a specific product does nothing, and re-installing never seems to succeed in un-installing it first. Looking at the file list in DIM, tehre are certain shared files that never get removed from the product, so it seems to never finish the uninstall part. Eventually it completes, after maybe 5 minutes. Should take 2 seconds. There used to be a command to re-build that cross-index of shared files (InstallManagerFileRegister.json), but I can't find it. The file gets re-built somehow. Maybe it is all automated now.
Okay, going to go slowly and not try to import 27000 metadata files and 14 User Data files all at once.
Uninstalling is painful. I get the following error on every product (a very small, ancient product). It eventually succeeds, but it takes 5 minutes. New or old, always the same error.
2023-05-03 04:24:45.156 [INFO] :: Uninstalling: 70-1 : Treasure Chest & Anchor
2023-05-03 04:29:45.373 [WARNING] :: ..\..\..\..\..\src\dzpostgresqlobjectdatabase.cpp(37): db delete error: ERROR: canceling statement due to statement timeout
CONTEXT: SQL statement "UPDATE ONLY "dzcontent"."content" SET "description_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "description_id""
2023-05-03 04:29:45.373 [INFO] :: db delete error: ERROR: canceling statement due to statement timeout
CONTEXT: SQL statement "UPDATE ONLY "dzcontent"."content" SET "description_id" = NULL WHERE $1 OPERATOR(pg_catalog.=) "description_id""
2023-05-03 04:29:45.392 [INFO] :: Uninstall Successful: Treasure Chest & Anchor
--
Any product that did not have DAZ-supplied metadata ends up in LOCAL USER, and categorized as Lost and Found even after re-importing User Data, which restores the Custom Categories, but leaves them in Lost and Found. Re-installing does not remove them from LOCAL USER, and re-importing metadata a couple of times does not correct it. Total fail.
I will start over tomorrow, slowly.
Okay, the CMS error was due to an obsolete connection, but somehow managed to get through. Uninstall is back to 2 seconds...
Re-importing metadata with DIM, a couple hundred at a time. All Products get fully populated, unlike that first attempt, but no UserData import yet. That will be done at the end, after I back up the cms folder before trying it. Also taking the opportunity to clear problems with metadata that I had not noticed before. The DIM log shows every little discrepancy between what is installed and what the metadata thinks it should be.
All right, re-imported all vendor metadata. Found another hundred or so metadata files with "difficulties", mostly defunct entries, but I couldn't just leave them. A few conflicts that originally went unnoticed, a few early attempts at modifying them that I botched; almost second nature now.
Started importing User Data. So far so good, but slow (14 files to do). I'll take a few hours restoring healthy data over potentially taking literally years to reproduce it from scratch.
I do not know if it was the sheer volume of information that tripped up DAZ Studio when using the Content DB Maintenance -> Re-Import Metadata to do it all at once. Doing it in smaller steps definitely worked flawlessly. Using DIM, it is easy to select ranges of SKUs, instead of clicking hundreds or thousands of tiny boxes. I can't say I would recommend Re-Importing everything in one pass if you have a lot of stuff. I will be backing up the CMS data cluster from now on, also. I hope to never have to do this again.
No it won't. Reinstalling software is pretty much always a complete waste of time because it's not so much the software itself that acts up: it's the configuration and/or settings files that cause it to act up. And reinstalling will never touch those, as you said yourself.
Sorry to see that I'm too late for this thread :/ I'm trying to provide help but I keep getting blocked because I mention SQL commands? :/
So... what you need to do is to shutdown the database manually, using the progress command tools which you can find in the Daz Studio program folder. You need to know the credentials and such for it, basically... the port number is 17237, the username "dzcms" and the database is called "Content". You also need to know the database folder: "%appdata%\Daz 3D\cms\ContentCluster", you use that together with pg_ctl.
ok, edit time. so far, so good:
Now, the command which you want to use => pg_ctl stop -D "%appdata%\Daz 3D\cms\ContentCluster" -m fast
You will find these in the sub-folder in your Daz 3D folder, in my example: C:\Program Files\Daz 3D\PostgreSQL CMS\bin.
THAT is the proper way to do a clean shutdown of postgres outside of Daz Studio.
Edit2:
So when you have issues with postgress and it mentioning recovery mode.. well: time to clean it up! It does this when it finds a file called "recovery.conf" in its data folder (which I mentioned above). When in recovery mode the server refuses all client commands, BUT you can still administrate the services using the pg_ctl command I mentioned above.
Don't just remove that file though, it does this with a reason. Possible database corruption or database problems, hence the need for a clean shutdown. Once all is ready and done: vacuumdb.exe. This can clean up and analyze your database.
"vacuumdb -? | more" will tell you more about the parameters, I've already mentioned the ones you need above.
Final edit:
Sorry for a very brief and short post but the moment I mention the wrong names then I get blocked from posting because of the whole sql thingamagick. But this should help out a bit.
Thanks for the information. It could come in handy "the next time" (knock on wood). I couldn't deal with researching the commands, my eyes just wouldn't focus, and I didn't have the patience.
Almost done copying the newly imported User Data's Custom Categories to an editable version. I think the database needed a good cleaning, anyway. Now if there was just a way to re-import User Data that did not make it Read-Only.
For the record... I got a little frustrated yesterday (local time) that I was forced to trim down my response in such drastic ways. Today, time of writing, I was still a little frustrated about it. Please keep in mind: I fully understand and respect those security measures, it was just annoying that they got in the way of my intentions to help.
SO... I grabbed all the info I gathered and posted a full guide for CMS maintenance on Reddit ;) => If you check the r/Daz3D subreddit you may come across my just posted tutorial called:"[Guide] If you're having cms / PSQL related problems you might want to read this.". I'm not sure about linking, also because it's unofficial and sometimes also contains some "adult renders", so I'll just leave it here.
Still hoping this can help out a bit... thing is: I've been there myself once, and like you I also heavily customized my CMS. I know how demotivating this can be, but hang in there!
All done. Got everything back, with a few modifications along the way. Missed a block of products' vendor data, so they all ended up in LOCAL USER when I imported the user data. Had to re-do those manually, since the user data is basically one big blob of entries with no way to limit or restrict what it uses, and you don't know what is where. You could search for it, but you still couldn't isolate it without making an edit and creating a subset to import. Maybe I'll export per product. A lot of files, but once done, the categorization is not likely to change. That way, I can select any product(s) to restore. Keep the exported data in a separate folder (you can choose where to send it), and copy the ones you want to the Runtime/Support folder for the DB Maintenance-Re-Import metadata to find. Seeing as it will be Read-Only, I just have to change the path for the Category to some throwaway destination in the dsx file before re-importing. Then I just have to copy that small subset back into the main tree, not the whole thing. Still going to do a full bcakup of the database, and the full user data export, just to cover all angles.