3 ms·
You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous da
by eropple 4y ago
You can set up Postgres with three lines of bash and a Unix socket. It is more work; I wouldn't say it's appreciably more work, and if you need more rigorous data modeling (I frequently do) then it's worth considering if this is the mode in which you'd like to run your data stores (I wouldn't, but latency isn't the most critical thing for stuff I do).
- riku_iki 4y agoI think it is more than that. Steps will be: - sudo apt install postgres (more steps are needed if you are not satisfied with OS default version) - sudo su postgres - psql -> create db, user, grant permissions - modify hba.conf file to change connection permissions - modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware Another options would be to build your docker image, but I am not good with this.
- remram 4y agoWhereas with SQLite it's: - sudo apt install sqlite3 # you're going to want the client - sudo chmod myapp db.sqlite3 - sudo chmod myapp . # make sure you have directory write permissions for WAL/journal - sudo -u myapp sqlite3 db.sqlite3 'PRAGMA journal_mode=WAL;' Nothing is ever single-command easy in all cases.
- riku_iki 4y agoI am not familiar with SQLite, and not familiar how engineers embed it to apps, but I believe for many apps with SQLite in distribution you don't need all these steps, it is included as some kind of library and not as standalone server. I am familiar with Java and H2, you just say in config somewhere: store db in this directory, and you are all set. I think there is no such route currently for PGSQL.
- eropple 4y agoSorry, but it's more complex than this. As an example, NPM's `sqlite3` package does not contain the actual library. Neither does Ruby's. You need the platform library for quite a few runtimes and now your single-command future has...multiple commands. Which isn't bad. This is a pretty low bar to clear. (All that said, I am of the opinion that separating data storage from application logic is better for operations, for security, and for disaster recovery unless you desperately need to prioritize avoiding network latency above everything else, so this is all pretty moot to me.)
- riku_iki 4y ago> but it's more complex than this. As an example, NPM's `sqlite3` package does not contain the actual library. Neither does Ruby's. You need the platform library for quite a few runtimes and now your single-command future has...multiple commands. I think usual way to do it is say having shared library file like sqlite.so in some directory or installed through OS package manager, and thin API binding for specific runtime in form of runtime package (e.g. npm). > you desperately need to prioritize avoiding network latency above everything else, so this is all pretty moot to me Goal here is easy distribution of embedded DB. Many apps are doing this with SQLite, for example Dropbox client. Many other examples: https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html
- eropple 4y agoI see what you're saying now. But unless I'm very much missing the plot, and everyone else is talking about a web application. The post to which you originally replied even constrained it as such: > you could conceivably run postgres on your app server So what you've been saying has been taken in the context of packing sqlite or Postgres next to your web application, which has pretty limited use cases, and reads really weirdly in that light.
- riku_iki 4y agoDiscussion was about "app server" and ops maintenance. We just got used to that if you want to bootstrap/install web/api stack, you need to change tons of knobs and configs and install many libs and software pieces. But there is another approach: one self contained binary/artifact which can include embedded DB too, and be launched by one command.
- remram 4y agoAs my comment says, you are going to want the stand-alone client though, for a variety of reasons. The psql client usually comes with the postgresql-server, but apps embedding SQLite3 definitely don't come with the stand-alone command-line sqlite3 client.
- riku_iki 4y ago> you are going to want the stand-alone client though I don't want stand-alone client for app which customer downloaded from my site.
- jayd16 4y agoThe official docker image seems quite good and handles a lot of the bootstrapping needs.
- riku_iki 4y agoCould you be more specific what exactly it handles from the steps above?.. Also, docker adds complexity itself.
- physicsguy 4y agoI can’t remember the exact command off top of my head but it’s something like: docker run -it -e POSTGRES_PASSWORD -p 5432:5432 postgres:12 And bam, you have a server
- sixbrx 4y agoAnother cool thing is you can initialize the Postgres db when it starts the container with your own SQL (or sh) scripts to create your schemas, tables, views etc by just copying the scripts into /docker-entrypoint-initdb.d/. Like: FROM postgres:14.3 COPY pg/pg-config.sql /docker-entrypoint-initdb.d/000-pg-config.sql COPY pg/schemas.sql /docker-entrypoint-initdb.d/010-schemas.sql COPY pg/meddra-tables.sql /docker-entrypoint-initdb.d/020-meddra-tables.sql # ... RUN chmod a+r /docker-entrypoint-initdb.d/* ENV POSTGRES_USER=myapp ENV POSTGRES_PASSWORD=xxx ENV POSTGRES_DB=mydb
- physicsguy 4y agoI tend to do things like (after launching container): docker exec postgres psql -h localhost -p 5432 -U ${DBADMINUSER} -c "some SQL statement"
- riku_iki 4y agoThere is config change in steps above (pgsql defaults suck), and having docker now, changing them becomes harder. Also, I usually change hba.conf file too, to disallow outside connections.