recovery | Recover from a network failure

You didn't describe omitting pg_replslot during your rsync, as the docs recommend. If you didn't omit it, then now your replica has a replication slot which is a clone of the one on the master. But if nothing ever connects to that slot on the replica and advances the cutoff, then the WAL never gets released to recycling. To fix you just need to shutdown the replica, remove that directory, restart it, (and wait for the next restart point to finish).

Do they need to go to wal_archive folder on the disk just like they go to wal_archive folder on the master node?

No, that is optional not necessary. It is set by archive_mode = always if you want it to happen.

S3 buckets are region specific. When you create a new bucket you need to select the target region for that bucket.

For DR reasons, you can keep backups in another region. Should the primary region fail in a way that the entire region is affected, then you could restore in the backup region.

Your DR strategy will depend on your use case, and your needs for returning services back to normal in case of region wide failure.

For example, let's say you rely on ec2/ebs to operate your service and those services suffer region wide outage for 5 hours. In order to recover your service you would need to move to a region where the resources are available. Assuming you need S3 data for operational processing you would want to have that data ready in the Target recovery region.

The problem in this case was a defect SSD, which switched into readonly mode after only 36 TB written, despite having a design of 1200 TBW.

Unfortunately, MacOS did not report this. When looking at System information > Storage > my SSD > SMART status the system still showed "Verified", which is supposed to mean that everything is ok.

It was not.

I determined this by installing smartmontools and running a check:

It's there in your configuration:

You may use case_when with grepl and a regex alternation:

The reason the first query is so much faster is it went parallel. This means the cardinality estimator knew enough about the data it had to handle, and the query was large enough to tip the threshold for parallel execution. Then, the engine passed chunks of data for different processors to handle individually, then report back and repartition the streams.

With the value as a variable, it effectively becomes a scalar function evaluation, and a query cannot go parallel with a scalar function, because the value has to determined before the cardinality estimator can figure out what to do with it. Therefore, it runs in a single thread, and is slower.

Some sort of looping mechanism might help. Create the included indexes to assist the engine in handling this request. You can probably find a better looping mechanism, since you are familiar with the identity ranges you care about, but this should get you in the right direction. Adjust for your needs.

With a loop like this, it commits the changes with each loop, so you aren't locking the table indefinitely.

Couple of things here that I'd like to point out.

First - you don't have to call Get-CimInstance twice. You can call it once and save it as an object and proceed parsing this object :)

Second - you probably need name like 'Dell%Update%' filter, as sometimes Dell Update is named Dell Update for Windows XXXX or in enterprise environments Dell Command | Update for Windows XXXX

So, after little refactor your script might look like:

The JSON sample has some small errors, but using it (with corrections) as input, the relevant jq filter would be:

Shall I kill this process and start over

Yes. The most pressing problem is your join predicate T.RepID = T.RepID. This means the query won't be doing what you hoped.

The join condition between the UPDATE target and #temp table is left completely uncorrelated.

The execution plan image shows that SQL Server treated it as below