Postgres.app
postgresapp.com
postgresapp.com
This is what I've been using for Postgres:
docker volume create postgres
docker run -d \
-p 127.0.0.1:5432:5432 \
-v postgres:/var/lib/postgresql/data \
--name postgres \
--restart always \
postgres
No one has to give any shits that Fred's workstation runs the latest Ubuntu and Sally likes Arch and John is on macOS and the server is Debian Stable. They'll all run the same versions of your project's service dependencies... and the correct versions of the other five projects you're all working on, which don't need to be updated in lock-step, and Amy the part-time remote contractor you just brought on doesn't have to have her machine polluted with actual installs of any dependencies for your project outside the repo itself, just easily-eradicated containers.
Are you serious?
This isn't just a "i don't want to update" issue. It's a "docker is terrible software and their business model is just as terrible".
They renamed some packages a few times but no big deal, just uninstall the old ones and reinstall the new ones and everything's back up & running with all data kept. The worst thing was when they switched from aufs to overlay2 but that was years ago.
That being said - when you have a handful of clients who are all running in Docker compose… it’s nice to say “down” on one, “up” on another as though I’m switching git branches.
Working on transitioning to kube so I can IaaC a lot of it - but it’s nice to have my local machine freed up.
I tend to lean on straight up Debian linux for most things. One of the R720's is a VMWare ESXi host, the other is a k0s box running on Debian Buster, and the 3770k runs Fedora because I wanted to taste the redhat/dnf fruit but I am diehard Debian.
Pic: https://s3.whalesalad.com/misc/rack.jpg (super messy, in dire need of a cleanup)
Disclaimer: Just a fan.
But is there really any isolation if it's installed on your host os? I always thought Nix was primarily a package management tool. Like brew?
Docker isolation is different.
For example, in project A I use Node.js v12. The project root contains a `default.nix` file that says it needs the package `nodejs-12_x`. When I run `cd /project/root && nix-shell` I'm dropped into a shell that has `nodejs-12_x` along with the rest of my "normal" shell. Once I exit it, `nodejs-12_x` is no longer available. If in project B I use Node.js v14, all I have to do is declare in its `default.nix` file that it uses `nodejs-14_x` and there will be no conflicts whatsoever.
Of course this is different from the isolation Docker provides, but I find that for development it is the perfect middle-ground between "everything is installed globally and conflicts with each other" and "everything is so perfectly isolated I can't get anything done".
Brew has a single shared “brew env” that it executes all its installs in the context of. Which is better than nothing, but it still means that different programs can’t be fixed to rely on different locked+resolved commits of the same symbolic-named ref of a dependent formula. (And Brew is very “naive” in this regard, as formulae can’t even specify a version constraint for their formula dependencies. If a lib updates, and breaks its dependents? Too bad, the Brew maintainers need to go update all the dependents. This creates long-standing update PRs in the homebrew-core repo, as the same PR that introduces an update, is expected to also then fix all the problems that introducing that update created for the rest of the ecosystem.)
Slightly more savvy package managers, like Rubygems, allow version constraints, but only globally; there can only be one resolved version of each package (this is a fundamental limitation — loading multiple versions of the same library into a single Ruby runtime would generate namespace collisions), so Rubygems emits “constraint resolution failures” when different deps want incompatible versions of something.
And then there’s the Node.js approach, where everything can specify its own version constraints, and gets those specified versions installed recursively into its own nested node_modules dir. Which is nice, but 1. still requires all the code to be “source compatible”, as it’s all still being loaded into a single interpreter, and 2. makes it impossible to “share” deps and deduplicate the work of building them, even if you explicitly create two dependent libs that both depend on the same fixed version of an upstream. (I think this latter part is hacked around by tools like yarn, but it’s still part of the “architecture” of the Node.js package ecosystem.)
In Nix, meanwhile, each “package” is a really a build environment, consisting of:
1. specific, locked commits of all upstream build environments;
2. a listing of build artifacts from those upstream build-environments that should be linked into this build environment;
3. a specific, locked commit (or a release tarball with an explicit SHA) of the upstream source of the package.
When you “install” a Nix “package”, you’re really just doing the moral equivalent of a recursive git-submodule checkout — each dep tells Nix to check out explicit refs of its own deps in turn, build those deps, and then link artifacts from those deps into this Nix build env.
But unlike Node.js modules, or git submodules (which form trees of refs), Nix environments form a DAG of references; so if two things in your tree share the exact same “submodule ref”, they can share/reuse the existing build env and its artifacts. (But if they don’t—if they envs they reference are even slightly different—they’ll do separate builds. Though perhaps they’ll share a git-repo cache for separately checked-out worktrees.)
Note that this mechanism isn’t really unique to “packages” per se. It’s less about packages, and more about build environments.
In other words: Nix is a manager for defining reproducible/deterministic chroots, which bootstrap themselves by grabbing other previously-defined reproducible/deterministic chroots and doing things inside them.
Nix “Packages” are just chroots that define build steps, so that other chroots downstream of them can ask the upstream chroot to build itself, and then import/link build artifacts from it. But these chroots don’t have to have build steps. You can totally use Nix to create a leaf-node chroot that doesn’t emit any build artifacts, but rather is just a perfectly-set-up environment to run something in.
Throw an nsenter(2) on top of that chroot(2), and you’ve got yourself a container!
Or take a flattened snapshot of the final chroot, and call it a Docker image. (Nix provides tooling for this: https://nix.dev/tutorials/building-and-running-docker-images)
This is why we use SQLite for all the things. I don't even remember what a database installation process looks like anymore. It's just a nuget dependency and some code for us.
We use it as part of the back-end of a business workflow automation system. Handles 100-1000 concurrent users without any issues.
I would be afraid to use SQLite over Postgres as SQLite is much more... flexible with it's database constraints (or at least, this used to be the case).
Not knocking your engineering choices - if you've been running it in prod for years, then it's working for you - just interested.
Do you lean into DB constraints much or do you do more application level checking/enforcement?
Precisely. We use a tiny ORM on top of Dapper to make sure everything goes in and out of columns as expected.
Edge does not provide safety to every database modifications like manually editing data and you're screwed already.
If someone wants to play around with a SQLite database from production, we just copy the file and hand it to them. That's kinda the whole point IMO.
Either way, it's a way to document exactly how to get your dependencies in order and the project running. That's a big improvement, operationally, at a lot of places. If Docker died tomorrow with no replacement, I'd go back to the Vagrant thing. Installing that stuff directly on my workstation sucks, for a bunch of reasons, and I'll not go back to that if I have any way to avoid it.
A good example is recent mac update to big sur broke pip/python installs for a lot of people. Spending a while reading github issues to make something as basic as python and pip install numpy work is why I like docker for dev environments. There are IDEs that support docker extensions well.
I also do run most of my workloads on clusters so having things dockerized makes it much easier to reproduce a failure locally. Our CI that currently isn't dockerized occasionally has environment issues that are quite annoying to debug as I lack a good way to explore it's environment and see the mismatch.
Obviously everyone’s experience is different because we’re all doing different things but I mostly work with Rust and Node, I use Postgres.app as a local dev database and just run the code natively, sometimes Node via nvm when I care about specific runtime versions.
It works great. It performs better then any Docker-based solution (I’m on a Mac) and doesn’t leave me with a bunch of weird dangling images/containers/whatever taking up resources. I still like the idea of using the same Docker environment in dev that I use in production but in reality I just don’t need it.
However, Kubernetes on Docker (microk8s or whatever it’s called) has always been extremely predictable in its (development-time, single-node) networking behaviour for me. Set up the right Deployment + Service + Ingress resources, ask kubectl(1) for the external IP and port to talk to, curl it—just works. Does the same externally-observable thing on your workstation that it does in prod.
Of course, that requires you to learn Kubernetes… which is a much bigger pain than it should be. But once you've got it, it's pretty simple/lightweight to wield Kubernetes at a problem; and the results are much more widely-applicable to everywhere you'd want to deply than e.g. Docker Compose is.
That's very cool, will definitely give it a whirl!
I haven't tried microk8s, does it do full Service proxying to localhost? I did try Minikube a few years back and the Service proxying wasn't implemented yet.
I'm a big fan of fully replicating the production-like environment (including TLS) in your dev setup, at least for iterating on k8s-layer config changes; taking the cycle time for k8s changes down to seconds makes for a very pleasant development experience.
Over in production, being consistent with dev is really nice, and having a consistent upgrade experience is a good benefit too.
Sure, the Docker route is better in many ways. But perhaps you're not understanding the audience for a packaged Mac application.
For what it's worth, installing Docker on Mac (the audience we're talking about) is as easy as installing Postgres.app (download an installer and open). No administrator privileges necessary, unless you have a weird setup, in which case you'll run into the same issues running Postgres.app.
Huh? Nobody said everyone using a Mac needs it. Where did you get that from? You seem to be putting words into GP's mouth.
But some people certainly could prefer it, which is the whole point of it existing, for those people.
Also, your comparison isn't even close to equivalent. It's not the ease of installing Docker vs Postgres.app... it's the ease of installing Docker and then figuring out how to configure an instance with Postgres vs Postgres.app. Obviously Postgres.app is easier. Some people have no need or desire to figure out Docker, they just want to use tools installed locally.
> Obviously Postgres.app is easier
Not necessarily. I used Postgres.app prior to switching to Docker Compose. It's a great option if you work on one app, don't need to switch between multiple versions, and don't need to work with a lot of different extensions or configs. I personally prefer keeping all of my config in source code, in the context of my application.
I think you are wrong. You are gatekeeping something extremely basic like a database from 1. beginners, 2. hobbyists, 3. even professionals that might have a different background than you (sql server on windows? Back when I used to write software for windows server, while using linux on my personal laptop, the command line was all but necessary), 4. my laptop died, I have to lead a dev workshop in 3 hours and I just got a new laptop but I don't have a full blown system that sets up my dev env because I literally have to do it once every 5+ years when I get a new computer, 5. more..
And I'm saying this as somebody who would probably go for the "command line" solution in most situations.
These are junior level skills.
Read my comment not in isolation, but in context of your comment, particularly, "perhaps you're not understanding the audience for a packaged Mac application". I agree that audience exists, and the Mac makes computing easy for that audience. I just don't feel that audience would be running a database server. If there was a WYSIWYG that let people build React apps via dragging and dropping components and avoiding Javascript, the response would be the same (and completely appropriate).
But looking at my docker list of auxillary services for 8 projects, I see redis, postgis, postgres 10, postgres latest, memcached, mailcatcher (fake smtp), ldap, a custom oauth, kafka, MySQL, piwik(matomo), in various forms and configs.
Sure, abstraction layers and adapters keep many such dependencies out of the way in Dev and testruns. But the inevitable debugging and troubleshooting does require a quick way to run such a service.
But why the snarkily tone? It is really uncalled for.
I found they'd cause more problems than they solved ultimately, as they didn't provide clear upgrade paths, often had opinionated default configs which left newbies wondering why public documentation didn't work, and there was a lot of churn as to which one were currently in vogue and maintained.
The idea of a "system" postgres is kinda wierd, since that single instance has to work with all my projects -- which might have conflicting needs.
How are you meant to apply only security patches on docker containers?
What's the point of "isolating" daemons to avoid "random" files in /etc?
It just makes it harder to git control and back up /etc by splitting it all over the containers.
Hopefully a few other people have had their lives made a tiny bit easier by the redirect I set up.
(Maybe there's not really any attack here - though downloads? - but imagine 'pay.pal' or something. AFAIK servers should be configured only to permit intended hosts, they allow '*' but I don't know when that's what you want?)~~~
Why is this for beginners? Because it is so easy to use? I guess I am a beginner then.
> works pretty nicely
Postgres.app works perfectly and is very minimal in system requirements vs having docker running and the amount of storage it requires.
Others (like myself) use PostgreSQL as a local datastore and analysis engine. All my interaction with the database is via ad hoc SQL commands and locally run scripts and desktop applications. For this usecase, something like this is much easier to use than docker compose.
[1] ie data easily fits on a single hard drive.
And since installing and setting up Postges on windows and mac is just a single download and double-click these days, there really isn't a good reason not to do so.
No, they're not. You both just want a Postgres instance you can access locally. The major difference is that Postgres.app does not seem to do a good job of separating different databases.
> All my interaction with the database is via ad hoc SQL commands and locally run scripts and desktop applications.
You can do this with DBs built by Docker as well, with the added benefit that you can erase and rebuild them whenever you want, and they're separated by project.
(not affiliated, just a fan)
Not affiliated, just an extremely happy customer.
To be fair, I use (and like) TablePlus, but it's no Postico...
There are a couple quality of life things:
1) Postico can infer that I mean NULL if I delete a column value. TablePlus thinks that if I have clicked into a column, I MUST mean that I want an empty string. It happens to me pretty often where I accidentally click the wrong NULL column to update, and when I click away it sets it to "EMPTY", but maybe the column type is a timestamp or something...so I get an invalid type error when I go to add the one I meant to change manually. It's frustrating, but very common. You have to right click and click, the "SET NULL" option.
2) Postico gives me a toggle for ENUM types and Bool types. I can't remember what ENUM values I can choose in one of my tables, so that can be cumbersome.
3) Postico has DDL right at the bottom of the table
4) Table right click options. In Postico I can right click a table name and Open contents/structure/ddl, Copy Name (really useful for those dumb tables we set up in camelCase in the migration from mysql to postgres...), delete, truncate, Analyze, Vacuum, Reindex, Import CSV, and Export In TablePlus I can Import/Export Delete/Truncate. That's it.
Positive on TablePlus is that they did recently add some nice things to the SQL Query like wrapping my camelcase table name in double quotes for me. If only they could do that for the column names too...but it's not their fault we have our setup this way...
Also, I readily admit that Postico isn't as powerful as others. But I cannot use PGAdmin one more second in my life. I'd rather just use the cli.
These days I tend to use "asdf" for my Postgres version management. It's not as friendly, but I kind of like running my services (where n < 5) in the foreground myself so I can see errors in the console with ease, and I like that asdf handles all the tools where I have exacting version requirements consistently (and even a few I don't).
I just wish there was a version of this for mySQL/MariaDB.
https://github.com/supabase/postgres
It’s also bundled as an AWS image with pgbouncer
wsl.exe -d Ubuntu -u root /some/linux/script
Edit: You can also use WSL as a sort of "docker like" setup, using wsl --export / wsl --import to make WSL images with different versions of Postgres, start/stop them, etc. With a small distro like Alpine, it's relatively fast.
Postgres is a client-server system, with full user access controls and multiple client connections.
Postgres has PostGIS (geographic data) and foreign data wrappers.
Postgres is more scalable than Sqlite and can work with larger datasets over multiple instances.
Heroku‘a preference is Postgres over SQLite: https://devcenter.heroku.com/articles/sqlite3
Just get a $5/mo cloud instance and run your stuff as same OS as production and let other people check your environment even while your machine is turned off and continue working on another machine without duplicating the environment.
If you still want it locally, use VMware fusion which became free for personal use lately and run a real Linux like your servers do.
I see no benefit in running stuff on local OS.
A database on localhost is the fastest way to run your tests that need to talk to a DB.