home.social

#pgadmin — Public Fediverse posts

Live and recent posts from across the Fediverse tagged #pgadmin, aggregated by home.social.

  1. It should not be this hard to run SQL queries on some data

    I’m doing some simple analysis of some data at work in a spreadsheet with about 8,000 rows, essentially to decide which rows I care about and which I can ignore. My first attempt at this involved many iterations of “build a filter filter in LibreOffice Calc to match certain rows I know I can ignore, delete those rows, resetting the filter, repeat.”

    After several hours of this I got myself down to about 4,000 rows remaining before realizing that some rows I’d decided to delete quite a ways earlier in the process, I really should have kept. Oh, well, time to start over.

    This time I decided to be smart about it: instead of doing this analysis in Calc and deleting rows, let’s load the data into something I can run SQL queries on and write a query with lots of where clauses to exclude the rows I don’t want. This way, if I realize part-way in that I made a mistake, I can simply modify the query instead of having to start over.

    My first thought for how to accomplish this was that we’re a Microsoft 365 shop, so I can just load the data into Microsoft Access. Apparently not: it’s not installed on my work laptop, not available in the Jamf self-service software install app, and not available to me for download (as far as I can tell) on the Microsoft 365 website, so either the M365 account my company gave me does not have a license for Access, or Microsoft has cunningly hidden the installer somewhere. I have now run into my first 🚧. Time to change course!

    At this point I remembered that LibreOffice Database exists, i.e., LibreOffice has a free tool kind of like Microsoft Access. So I’m going to try to use that.

    If I just wanted to run read-only SQL queries against the data in the spreadsheet, my problem would be solved here. It’s trivial to connect a LibreOffice Database to an existing ODS file and run SQL queries against it. But I also want to be able to edit the underlying data, and ODS-backed LibreOffice databases are read-only. I’m therefore hoping there’s an easy way to copy a table from one LibreOffice database into another, so I can create a read-write database backed by HSQLDB, which is supported by LibreOffice Database, and do my querying and editing in that.

    I do a bit of web searching, from which I conclude that the recommended way to do this is to create a LibreOffice Database connected to the spreadsheet, create a second Database connected to the HSQLDB backend, copy the table in the first database, and paste it into the second.

    I create the Database connected to the spreadsheet with no trouble, but when I try to create and open the HSQL Database, I get a pop-up telling me I have to go install a JDK. 🚧#2

    I go download and install OpenJDK, which requires some digging around to figure out the correct way to do that on macOS. The first time I do it it doesn’t work, it turns out because I should have installed the “aarch64” version on my silicon Mac rather than the “x64” version. D’oh! 🚧#3. I delete the x64 version I installed and replace it with the aarch64 version, and now I’m able to open the HSQLDB Database.

    Now I attempt the table copy/paste maneuver, and I get errors at the point where LibreOffice is attempting to copy the data into the new table. I waste quite some time digging into what is causing these errors, only to discover that for some inexplicable reason LibreOffice is ignoring the data in the first column of source data and shifting all the data one column to the left, with the result that the data is being imported into the wrong columns, which means the data types of the columns and the data being imported into them don’t match, which is what’s causing the errors. 🚧#4

    I don’t know how to work around this, but it seems like a pretty serious bug, so I should try to construct a simple test case and report it to the LibreOffice maintainers, right? Only when I attempt to do that, I run into a different bug, which I can’t figure out how to work around. I report that one instead. 🚧#5. Since I can’t get past that bug, I can’t create a recipe for reproducing the other one, so I can’t report it. 🙁

    My next thought is that maybe this problem is only in the HSQLDB backend, so I can install MariaDB on my Mac and use that as the backend instead of HSQLDB. I’ve never actually installed MariaDB on a Mac before, but it should be straightforward, right? Alas, the MariaDB install page only says how to install it on Linux and Windows, not Mac. 🚧#6. I’m sure if I had kept digging I could have found a way to install it on my Mac (heck, it’s probably available in Homebrew, which I forgot to check), but I just shrugged and figured what the heck, I’ll use PostgreSQL instead.

    I found, downloaded, and ran the macOS PostgreSQL installer. During the install it said that database initialization failed but didn’t explain why. 🚧#7

    I haven’t actually installed PostgreSQL in a long time, so it took me some digging to refresh my memory about how to initialize the database from the command line, but I figured it out. Then more digging to figure out how to create users and set their passwords, then some more digging to figure out how to give them permissions on the database. Permissions are quite arcane on PostgreSQL, so it took me quite a while to figure it out, and then I subsequently realized in hindsight that all that effort was pointless because initdb enables trust authentication for anybody connecting through localhost. 🚧#8

    While I was in the middle of figuring out how to assign permissions I saw someone on Stack Overflow recommend using pgAdmin for that, so I figured what the heck, I’ll give that a try. My friends, I have a powerful MacBook Pro, and pgAdmin took several minutes to launch. Furthermore, after it launched, the user interface took anywhere from 3 to 10 seconds to respond to every single mouse click. I don’t know what tech pgAdmin is built out of under the hood, but wow, it is absurdly, unusably slow. Also, it turns out you can only use pgAdmin to assign permissions for existing objects; you can’t use it to assign default permissions, apparently. So all that effort was wasted. 🚧#9

    Now that I’ve got the database working, I create a LibreOffice Database connected to it, and then I again attempt to do the table copy/paste from the ODS-connected Database. The entire process of doing that works as expected and seemingly runs to completion, but… the table is not created. LibreOffice is acting like nothing’s wrong but silently failing. I report that bug too. 🚧#10

    “Maybe it will work if I create the table in the target database by hand, and then attempt to do the paste in ‘append the data’ mode, I say to myself. So I try that with a simple test case before I waste time recreating the entire schema of my actual data in the target database. It fails, because the primary key column created by LibreOffice isn’t auto-populating so the INSERT statement generated by LibreOffice fails because it’s not providing a value for that column. I reported that as well. 🚧#11 (EDIT: It turns out this bug was first reported in 2013 and still hasn’t been fixed.)

    You get how absurd all this is, right?

    Next thought: create the table with hand-written SQL with a proper PostgreSQL serial primary key, then append the data from the source table. I try that out with my tiny test table, and lo and behold, it works. So I go ahead and do the same for the actual table schema and data, and, miraculously, that works as well, and I finally have all the data in a LibreOffice Database that I can query and edit. It only took me the majority of a work day. 🤦

    But wait, there’s more. I continue to discover issues as I use LibreOffice Database to query and edit the data:

    • I’m building complex queries. To make the queries readable as I work my way through the data, I’m inserting line breaks, SQL comments, etc. I don’t discover until the first time I close and later reopened one of these queries that when you close a query it strips out all the line breaks and SQL comments. Awesome! Reported as a bug. 😡 🚧#12 (EDIT: This bug was first reported in 2014 and still has not been fixed.)
    • When you edit a row in the query results and then rerun the query before clicking on a different row or clicking the data save button, your changes are silently discarded. This bug cost me about 45 minutes because when I realized it was happening I had to go back and re-review over a thousand rows of data to find the ones that hadn’t been saved properly. Reported as a bug. 🚧#13

    It’s unbelievable how hard this was to do and how many barriers to success I had to get through.

    #LibreOffice #pgAdmin #PostgreSQL
  2. 🚀 Dave Page (the creator of pgAdmin!) came out with a new blog (part 1 of 3): "AI Features in #pgAdmin — Configuration and Reports"

    #AI is making its way into everyday #PostgreSQL tooling. In this first post of a three-part series, you'll learn how to enable and configure AI features in pgAdmin, & how they can generate insights like security reviews, performance analysis, and design recommendations directly from your database environment.

    🔗 pgedge.com/blog/ai-features-in

    #programming #postgres #llm

  3. Как я сделал рабочий pgAdmin4 Desktop для Arch Linux и почему это оказалось сложнее, чем кажется

    pgAdmin4 - де-факто стандартный GUI для PostgreSQL. И при этом на Arch Linux его Desktop-версия годами находится в полурабочем состоянии. Я разобрался, почему pgAdmin4 так плохо ложится на Arch, какие подходы стабильно ломаются, и какой компромиссный, но реально рабочий вариант в итоге получился. Если вам тоже надоело чинить pgAdmin после каждого обновления Python или Electron - это для вас.

    habr.com/ru/articles/996696/

    #pgadmin #pgadmin4 #arch_linux #aur #electron #python #packaging #open_source #postgresql

  4. It really is surprising sometimes how #homelab services grow....and I just want to emphasize that it's silly to think that you'll be the exception.

    Prior to doing my big holiday cleanup of scattered docker containers being moved over to first class, fully automated #proxmox #k3s VM nodes, and #kubernetes services being managed by #ansible and #argocd, I thought for sure I knew exactly how many services I would be running.

    I also felt confident that I didn't need anything more than that.

    Low and behold, the old adage holds true.

    Tonight I was working on my deployment of #netbox (github.com/netbox-community/ne) and I realized that it uses #Postgres under the hood.

    No big deal. I've got a Postgres instance already running in the cluster.....except that I wrongly made the assumption that #paperless_ngx would be the only consumer, and I turned off the superuser.

    Still no big deal....except that I'm deploying everything via GitOps as much as possible, AND I've already got live, important data in Postgres.

    *long sigh*

    So...now I get to go do some manual surgery on users and databases to make it be a proper cluster wide, multi-tenant service, I'll make the gitops side of it look pretty later.

    And that's when I realized that keeping an instance of #pgadmin around would be super useful going forward.

    I was, in no way, planning on running a PgAdmin instance, but here we are.

    Well...guess what? I'm also running Mongo, InfluxDB, and Redis.

    Thankfully, Influx ships with a WebUI...but if I'm already adding pgAdmin, then there's no sense skipping #Mongo Compass and #Redis Insight.

    And that's officially 3 more unexpected services to deploy in a single night.

  5. @gary_alderson @allanjude @ServerNorth @MagnusCatticus I don't use #PgAdmin ... don't really need to.

    The headache was that binary upgrades became a thing in 8.4... and I was on 8.3.

  6. So... most gnome apps are failing to open on my debian sid daily driver. So... pgadmin4 won't open to remotely edit my company database. So... I have learned how to add a column to a table in my remote database using psql locally. Yup, I'm a hacker.

    #postgres #postgresql #pgadmin #pgadmin4 #psql #debian #sid #debiansid #hacker

  7. Earlier this year we started a program ("Developer U") to help colleagues who show promise for #PostgreSQL Development to become contributors. In the first of the series to be published we meet Akshay Joshi, a Principal Engineer and Engineering Manager at EDB, working on #pgAdmin.

    He went in with the assumption that contributions to a mature project with a big user base like PostgreSQL would be difficult to get accepted. Instead, he found a supportive community: enterprisedb.com/blog/postgres

  8. pgAdmin CVE-2025-9636 vulnerability enables OAuth session hijacking, threatening PostgreSQL database security. Database administrators must prioritize pgAdmin 9.8 upgrade immediately. Essential reading for cybersecurity professionals.

    #SecurityLand #CyberWatch #Cybersecurity #PostgreSQL #DatabaseSecurity #CVE #OAuth #pgAdmin

    Read More: security.land/critical-pgadmin

  9. [Free Spatial] Tutorials - Learn The Basics Of QGIS, Postgis, And Geoserver With [Luna Geospatial’s] Growing List Of Free Tutorials
    --
    lunageo.com/tutorials/ <-- shared technical resource
    --
    [this should NOT be considered an endorsement of this vendor, rather an indication of a useful, open set of spatial tutorials - which is appreciated]
    #GIS #spatial #mapping #onlinelearning #elearning #QGIS #PostGIS #geoserver #lunamap #postgresql #remotesensing #pgadmin #tutorials #learning #education #selfeducation #free #opemsource #software
    #lunageospatial #lunageo #technical #resource

  10. Learned this morning that if a #postgres user compromises their password, if you aren't a #superuser, you can't change their password for them....

    ...but they can change their own password in #pgAdmin!!

  11. Just Upgraded To PostgreSQL 15, Lets Have A Look
    --
    postgresql.org/about/news/post <-- release notes, Oct. ‘22
    --
    Just got PostgreSQL 15 installed, I look forward to continuing to grow more into it as part of my applied science work with my team - and with the elephant looking down on me from the bookshelf, (very) quietly encouraging me to learn more and use more (as silly as that of course is!)
    #GIS #spatial #mapping #appliedscience #useage #spatialdata #spatialanalysis #appliedscience #upgrade #postgreSQL #postgis #pgadmin #RDBMS #SQL #enterprisetech #enterprisedata #enterprisesolutions #database #science #team #work #gischat