PostSur PicGres

I was looking for a quick hostable image storing solution, specifically I needed easily accessible POST / GET options.

Definitely needed to be more on the simple side, and I had no interest in setting up the myriad of options that come with something like Immich, but I do recommend if you're looking for a Google Photos alternative. Nothing but good things to say about it.

Admittedly, I didn't look very hard, and came across Picsur, which is definitely not an Imgur clone. Perused the documentation, saw that it had API endpoints. Perfect.

One of the requirements for the container was PostGres, which I have used on my test / backup machine a few times, but never put it into production. I've also used it extensively to read data from for various dashboards / queries at work.

I set both containers up, they were talking fine, not problem at all. I was trying to drag images in and was having issues -

Initially, I assumed it was my setup, and I was looking at the logs, and variables of the Picsur container as PostGres seemed fine.

I noticed there was no volumes mounted, and assumed the issue was it could not store images. I went back and forth mounting a place to store images, looking through the documentation, trying to mimic what apparently wasn't even there. I just assumed it was correct. Mind you was a Friday evening, after work. I doubt the brain was working at 100%.

I resorted to asking GPT, gave it the docker compose yaml, it assumed I was correct in asking about does it need a volume mapped. GPT did give some insightful commands that I could have ran, but it was mostly related to permissions and memory. Which at a surface level, did not seem like I had issues with either of those.

I spent far too long trying to figure this out, and having GPT explain the portions I didn't understand, nevertheless it didn't go anywhere.

Then I just gave it another go with a different image.

Annnnd... It worked?

Turns out, dragging an image from 'Everything', a fantastic search engine you can use to quickly search everything on your computer, or in my case, all the mapped networked drives I have, would just fail when dragging and dropping into Picsur.

The more you know...

Regardless, this wasn't actually what I found impressive, I never actually got a volume mounted in Picsur, and this is what baffled me.

That initial image that failed had thrown me down a completely unnecessary rabbit hole, because PostGres can store images?!

This is understandably old news for anyone who actually uses PostGres. From my quick browse, it seems like it was more commonplace post-2010, but it seems like LOB and binary have been stored in SQL databases for far longer, just illadvised.

I think I'm for it, but only for my current use case. These are arguably images that only need to be stored temporarily as a holding place before being added to a production server that stores them on a more traditional file server.

It's kind of unique to the industry I work in, 9.9/10 we aren't shooting photos of existing products. We're reusing images from a supplier, manufucturer etc, but we do need them all to conform to a standard. In our cases that's a 1:1 ratio, and not all of the suppliers and manufacturers are going to adhere to that standard for their own eCommerce / CMS platforms.

So, it would be something like the following -
Get bulk images from the provider, or rip them from the manufacturers website. Process them into a 1:1 ratio with a required minimum resolution. Then we need to store them before we can push them bulk into our production CMS.

We could have stowed them on the production CMS, but it seemed wasteful, to POST, GET all the the URLs, collate for them for a particular SKU and then re-POST once we're ready to update the products.

There's probably a way to link the images via the API to the product, rather than having to POST them, but we found a workaround in the end, and it's reasonably efficient. On top of that, if Picsur ends up being reliable, technically we have a backup of all those images outside of AWS, not that I'm questioning AWS's resillience.

I think the moral of this story is that I was kind of stuck using MariaDB / MySQL, because it seemed correct. I'm no expert, in fact pretty new to using SQL, my last post mentioned using GSheets as a stopgap, as that is what I'm most familiar with.

Seeing that PostGres can store binary data without issue (so far), has made it a little more inviting to look outside of what I seemed was the ideal, or model way to approach databases. I'm sure there's a million and one posts out there that swing one way or another, but I'm glad I stumbled upon this issue to see something I was completely unaware of, and I'll definitely be utilising PostGres more often for own work.