4
votes
Fortnightly Programming Q&A Thread
General Programming Q&A thread! Ask any questions about programming, answer the questions of other users, or post suggestions for future threads.
Don't forget to format your code using the triple backticks or tildes:
Here is my schema:
```sql
CREATE TABLE article_to_warehouse (
article_id INTEGER
, warehouse_id INTEGER
)
;
```
How do I add a `UNIQUE` constraint?
Posting an answer to a question no one asked, but one I wish I had already known a few weeks ago: what’s the best developer tool for mission-critical, production-grade data archival and backup?
May seem like a high-context or subjective question… but for damn near every practical case I can think of, it’s rsync.net. They give you a massive ZFS storage pool (from 680 GB up to exabyte) that can be accessed using standard CLI tools (e.g. …rsync), and take scheduled snapshots of the data in case of ransomware or accidental deletion. No worrying about physical disk layout, bitrot/checksumming, encryption tools, compatibility, etc.
I’d known of it by reputation in the past, but after actually getting my hands dirty, it’s the best devtool service I’ve ever tried by a mile, and I never want to live without it again.
TLDR - Rsync.net, apply directly to the forehead.
How would you compare this to tarsnap?
I personally have been using tarsnap for my personal off-site backup for about 10 years and it just works once set up.
Rsync.net looks similar in philosophy, but offloads the encryption/compression part to the user, right?
Tarsnap is in my "aware of but haven't tried" bucket. I've shied away from it because of the user-managed-keys thing - I actually find comfort in the fact that with rsync there's a human who can help if something crazy happens and all backups of my* private key go up in flames at the same time. Yet if I wanted to store sensitive data requiring encryption, I'd still be able to do that 100% securely by piping data through any old command-line encryption utility, and similarly, I can just pipe data through tar + gz to get fast and efficient compression.
And unless I'm reading something wrong, it's $0.016/GB/month vs. $0.25/GB/month lol.
Tarsnap has no minimums, so it's still cheaper up to 40-something GB/month.
Personally, I use Backblaze B2 for my offsite storage, which is both extremely cheap ($0.005/GB/month, though there are transfer fees to consider) and no-minimum, so my monthly bill with them is on the order of 70¢. However, it is emphatically batteries-excluded, and definitely not suitable for someone not comfortable with writing their own backup solution.
Yeah, I like Backblaze too - but correct me if I'm wrong: I don't think they offer too much in the way of checksumming/error correction?
A lot of my large files are database dumps, meaning that a single bit flip in the wrong place can render a backup useless. ZFS is a big selling point for me.
Their upload protocol includes checksumming to detect transmission errors, and of course there is a great deal of redundancy in storage (backups being their stock in trade). And they support arbitrary file metadata, so you can store checksums alongside on your own if you have them.
(Also you could use something like par2 to create your own ECC and store it alongside or out-of-band.)
I'm not sure what reliability guarantees they make about data retention, but I'd assume they're at least as good as S3 (which claims something idiotic like ten 9s). I've never heard a credible report of a major cloud storage provider experiencing loss or corruption of stored data.
Duplicati works pretty well for me, though I don't have any extreme needs! It's storage-agnostic and feature-rich (including data integrity/encryption). Usable with CLI or web UI.