#PostgreSQL
Seems like #postgresql does have a "create inedx if not exists", nice!
Sadly this project uses MySQL 😭
@Anachron Don't know their motivations, but I've often found that people who make decisions don't understand tech. They think free/open source means amateur.
Unfortunately, they're often right, so the amateur free/open source taints the good fee/open source by association.
オンプレのPostgreSQLからAzure Database for PostgreSQLにDBを移行したい
https://qiita.com/reo_01/items/1eb631596e867aebf3a5?utm_campaign=popular_items&utm_medium=feed&utm_source=popular_items
#qiita #Azure #PostgreSQL
Powszechnie wiadomo, że #PostgreSQL jest zwykle nieco trudniejszy (głównie bardziej restrykcyjny) niż #MySQL. Nie jest to duża różnica, choć istnieje, natomiast na poziomie podstawowym nie ma to dużego znaczenia.
Ale gdy zejdziemy głębiej, to robi się ciekawie... Ten artykuł warto polecić nie tylko ze względu na PSQL, ale także ogólnie na mechanikę działania relacyjnych baz danych, jeśli chcecie odświeżyć wiadomości z uczelni.
Luca Ferrari (@fluca1978): psql \watch improvements https://fluca1978.github.io/2023/09/22/PostgreSQL16psqlwatch.html
#postgres #postgresql
@fosdem I submitted a stand request for the #PostgreSQL project, but the website threw a 500 error. Upon trying it again, it says there is already an entry in the database. But I did not get the usual confirmation email.
Can you please check if the application is valid? Thanks.
osm2pgsql moved to it's own GitHub organization, no longer using the OpenStreetMap org
#osm2pgsql #OSM #OpenStreetMap #GitHub #PostGIS #PostgreSQL
https://osm2pgsql.org/news/2023/09/20/new-github-organisation.html
Effective #PostgreSQL Monitoring: Utilizing the views pg_stat_all_tables and pg_stat_all_indexes in PostgreSQL 16 https://www.enterprisedb.com/blog/effective-postgresql-monitoring-utilizing-pg-stat-all-tables-and-indexes-postgresql-16
Hope you're enjoying #Ruby #Rails #Euruko2023
@euroku. I'm interested in the talk from Chikahiro Tokoro
@kibitan on "Generate Anonymised Database With Masking." Curious how the approaches compare to the #pgrailsbook techniques to scrub sensitive row data. #postgresql
Implementing Central Server cluster using Patroni
Friday, September 22, 2023
by Jan Raik
https://x-road.global/programme-2
X-Road Community Event 2023
#OpenSource #distributed #database #postgresql #patroni #citus #event
From this week's ADMIN Update newsletter: @madkiss looks at @openproject for project management in medium-sized enterprises https://www.admin-magazine.com/Archive/2023/75/Manage-projects-in-SMEs-with-OpenProject #SME #OpenProject #tools #ProjectManagement #IT #FOSS #OpenSource #Docker #PostgreSQL
Understanding the CREATEROLE Privilege in #PostgreSQL - Stormatics https://stormatics.tech/blogs/understanding-the-createrole-privilege-in-postgresql
Greg Sutcliffe (Red Hat), at the #Ansible Day in #Berlin:
"Mailing lists are very discrete. You start moving this to forums, and you see people exchanging ideas.
Peak in mailing lists was in 2014."
Now this reminds me about another community which also heavily relies on mailing lists ... looking at you, #PostgreSQL
Hell called and it's freezing 🥶 down there. #Oracle #cloud sells #Postgresql
https://blogs.oracle.com/cloud-infrastructure/post/oci-database-postgres
New entry in #PostgreSQL Event Calendar:
👫 Meetup PostgreSQL à Lausanne
Date: 2023-09-27T16:00:00Z - 2023-09-27T18:00:00Z
Location: Powerhouse, Pl. de la Gare 10, 1003 Lausanne, Switzerland
ICS file: https://ics.postgresql.life/4vvkfctburlfef6b4bevk26tj7.ics
Oracle is thrilled to announce the [limited] availability of #PostgreSQL in their selling options. $$$Money$$$ quotes:
* Lower cost
* Higher performance
* Simple user experience
https://blogs.oracle.com/cloud-infrastructure/post/oci-database-postgres
Yet another example of why open source software is amazing:
PostgreSQL 16 added support for using underscores as thousand separators to improve readability of large numbers, eg. `1_500_000`. It's something I really appreciate in #Elixir, #Rust, and now it's coming to #SQL too.
The amazing part, as usual, is behind the scenes.
@petereisentraut shared how the feature came to be and why it took 4 years from idea to release.
An incredible amount of effort goes into bringing (deceptively) "simple" features to any project as complex as Postgres - including the detour of getting it into the SQL standard first!
Thanks to Peter (and the team) for not only putting in the work, but also sharing the story, I really enjoyed it! 🐘🩶
https://peter.eisentraut.org/blog/2023/09/20/grouping-digits-in-sql
#PostgreSQL 16: Playing with pg_stat_io (1) – extends
https://www.dbi-services.com/blog/postgresql-16-playing-with-pg_stat_io-1-extends/
Hans-Juergen Schoenig: Citus: 7 commonly used advanced SQL tools https://www.cybertec-postgresql.com/en/citus-running-advanced-sql-7-common-tools/
#postgres #postgresql
New cocktail party opening line:
>> "Do you listen to podcasts?"
Is this happening to you too @asw?
Timed wait events in PostgreSQL
{ by @fritshoogland } from @hashnode databaseperformance.hashnode.dev/timed-wait-eve… #postgresql #performance
A PL/PgSQL Simple Roman Number Translator https://fluca1978.github.io/2023/07/24/PostgreSQLRomanCalculator.html #postgresql
Useful slides -- because they come with examples -- on the recent PostgreSQL 16.0.0 release : https://momjian.us/main/writings/pgsql/features.pdf
Having some #postgresql issues - if a condition uses an explicit value (eg. now()) planner decides to use an index, but if I convert that to a subquery (eg. select now()) it does a seq scan.
Penalty for using a subquery is 10x.
Is that a bug in the planner or am I doing something wrong?
Published release v0.7.0 of our #PostgreSQL query builder for Go: https://github.com/networkteam/qrb
It now supports operator precedences to smartly wrap expressions in parentheses where needed.
I really enjoy working with it so far. Better than Squirrel or custom SQL string building.
Umair Shahid: Incremental Sort in PostgreSQL: A Developer’s Guide https://stormatics.tech/umairs-planet-postgresql/incremental-sort-in-postgresql-a-developers-guide
#postgres #postgresql
I guess it must be possible to alter the column type of a PostgreSQL table to the same type but just bigger. In the dark times 7 years ago I assumed that 100 characters would be enough for a "character varying" field. In hindsight it was not and insertions/updates sometimes fail. There's a lot of user data in the table though and I obviously don't want to mess with that, it should not be changed at all.
Reproducible builds: a #PostgreSQL query optimization example https://www.cybertec-postgresql.com/en/reproducible-builds-postgresql-query-optimization/
#PostgreSQL Event Calendar, Reminder:
👫 Boston Meetup: Vacuum Through Pictures 2nd Edition
Date: 2023-09-26T22:30:00Z - 2023-09-27T00:30:00Z
Location: 355 Main St US-CAM-5CC, Cambridge, MA 02142, USA
ICS file: https://ics.postgresql.life/7fno54m369alqeurgoh6nu3tc2.ics
@lukaseder That's true, haven't seen it used so far.
(You forgot the #PostgreSQL hashtag 😉)
Exemplo de como suporte corporativo 💸️ pode deixar mais a desejar do que uma comunidade engajada.
Uma pena, pois aparentemente o legado da Sun na Oracle é preservado por esta ao manter o #OpenJDK, base do meu ganha-pão por muitos anos, e estamos estudando implantar algumas VMs com OracleLinux no lugar do descontinuado CentOS, para sustentarem o proprietário banco de dados dessa #BigTech usado por alguns sistemas terceiros.
Ainda bem que nossos principais são 🐘️ #PostgreSQL com as 4 liberdades.
I love PostgreSQL, sometimes I thought about pivoting to be DBA. Then I look at question on the #PostgreSQL 's Slack and it seems that pivoting may not be a good idea. 😄
I think I love the query part and less the admin part.
I think replica issues and configuration stress me. 😅
Peter Eisentraut (@petereisentraut): Grouping digits in SQL http://peter.eisentraut.org/blog/2023/09/20/grouping-digits-in-sql
#postgres #postgresql
PGConf NYC is 2 weeks from today! Do you have your ticket?
Have you booked your hotel room?
Is your company sponsoring? They should!
Go! Go! Go!
@PostgreSQL
#PostgreSQL #postgres #pgconfnyc
My #Oracle to #PostgreSQL Migration: The 7 Tools That Made It Possible | EverSQL https://www.eversql.com/my-oracle-to-postgresql-migration-the-7-tools-that-made-it-possible/
🐘 Buffer analysis when using EXPLAIN ANALYSE in Postgres
➥ Will Jessop
https://willj.net/posts/buffer-analysis-when-using-explain-analyse-in-postgres/
Claire Giordano: Why giving talks at Postgres conferences matters? Highlights from a podcast https://www.citusdata.com/blog/2023/09/19/why-giving-talks-at-postgres-conferences-matter-highlights-from-a-podcast/
#postgres #postgresql
welp, if you need (or knows someone who needs) help with #PHP, #Docker, #RabbitMQ, #Redis, #PostgreSQL, #SoftwareArchitecture let me know, I'm looking for #freelance work or a #job :) I work with PHP since 2000 and have plenty of experience in Team #Leadership, Tech #Recruiting and #Startups
Payfit is hiring Senior Software Engineer - Self-Acquisition
🔧 #typescript #react #node #graphql #aws #cicd #docker #kubernetes #postgresql #terraform #seniorengineer
🌎 Remote; Paris, France
⏰ Full-time
🏢 Payfit
Job details https://jobsfordevelopers.com/jobs/senior-software-engineer-self-acquisition-at-payfit-com-jul-31-2023-dbba6d?utm_source=mastodon.world&ref=mastodon.world
#jobalert #jobsearch #hiring
New entry in #PostgreSQL Event Calendar:
👫 Boston Meetup: Vacuum Through Pictures 2nd Edition
Date: 2023-09-26T22:30:00Z - 2023-09-27T00:30:00Z
Location: 355 Main St US-CAM-5CC, Cambridge, MA 02142, USA
ICS file: https://ics.postgresql.life/7fno54m369alqeurgoh6nu3tc2.ics
@postgresuk I'm happy that this #PostgreSQL conference was a success, as member of the program committee and as a sponsor.
I'm sorry I couldn't attend because of personal reasons.
The #Rel8 #Haskell library lets you construct #Postgresql #SQL queries using a monadic interface.
Interesting bit: "Rel8 has a fairly unique feature in that it’s able to return not just lists of rows, but can also return trees."
https://rel8.readthedocs.io/en/latest/cookbook.html#tree-like-queries
@hbuchel I'm happy we kept our focus on SSR. We use #SpringBoot #PostgreSQL and #Thymeleaf. We recently started to use #HTMX https://htmx.org The biggest challenge is having a stable&fast&integrated tool to process/bundle the JS and SCSS code where we use http://www.asset-pipeline.com
#postgresql is my love language
In #postgresql, how do you analyze a query that won't complete to see where the problems are? Can you time-limit EXPLAIN ANALYZE? Is there some other strategy? Can you answer this question? https://software.codidact.com/posts/289737
🚀 How Instagram scaled to 14 million users with only 3 engineers
https://engineercodex.substack.com/p/how-instagram-scaled-to-14-million
Monday morning, the peak time for requests and issues. One of the things I try to avoid on #Linux servers is using external repositories for installing specific package versions. Just a while ago, a developer asked me to install the php module for #PostgreSQL on an (old) #Ubuntu server with an external php repository. Even though the server is being decommissioned, this is a temporary operation for migration. Unfortunately, I had to tell them that the packages are no longer available because that repository no longer provides them. Tech debt always comes knocking sooner or later.
PostgreSQL brings more performance, security to open-source database
💾 https://venturebeat.com/ai/postgresql-brings-more-performance-security-to-open-source-database/
--
#postgresql #pgsql #database #oss #opensource #performance #security
PgOSM Flex is ready to go with Postgres 16 and PostGIS 3.4! 🤓 🐘 🗺️
#pgosm #postgis #postgres #postgresql #openstreetmap #gischat
https://github.com/rustprooflabs/pgosm-flex/releases/tag/0.10.2
And... merged! Have fun with #postgresql 16, #nix users! 🐘🤝❄️
PostgreSQL 16 Released with Notable Improvements
https://linuxiac.com/postgresql-16-released-with-notable-improvements/
PostgreSQL 16 open-source RDBMS improves performance, logical replication, monitoring, and access control. Here's what's new!
The advanced open source database #PostgreSQL has released version 16 with enhanced performance, refined query parallelism, optimized bulk data loading, and enhanced logical replication.
https://alternativeto.net/news/2023/9/postgresql-16-released-with-performance-enhancements-and-many-new-features/
Now that #PostgreSQL 16 has been released, Roji has also released the #Npgsql hotfixes 5.0.17, 6.0.10 and 7.0.6. They contain quite a few bugfixes and one replication-related compatibility fix for #PostgreSQL16, so make sure, you upgrade your #dotNET projects to the latest compatible version.
Recently at work, I had to build a real-time notification system for a #Django project. I opted for Server-Sent Events( #SSE ) . Luckily for me, around that time #Django 4.2 introduced async support for StreamingHttpResponse, which seemed ideal. However, as I delved deeper into the project, I decided instead to build a relay service using Starlette https://github.com/Tobi-De/sse_server_postgres_listen_notify which transmit messages received through the #PostgreSQL LISTEN/NOTIFY protocol to client browsers connected via SSE.
1/2
Postgres 16 is OUT THERE! 🐘🎉
https://www.postgresql.org/about/news/postgresql-16-released-2715/
#postgresql 16 has been released 🎉
https://www.postgresql.org/about/news/postgresql-16-released-2715/
New entry in #PostgreSQL Event Calendar:
👫 PostgreSQL® Session 16
Date: 2024-02-06 - 2024-02-07
Location: Paris, France
ICS file: https://ics.postgresql.life/5d7kejie5fgj4i1p5c60jn2v3c.ics
Payfit is hiring Senior Software Engineer - Self-Acquisition
🔧 #typescript #react #node #graphql #aws #cicd #docker #kubernetes #postgresql #terraform #seniorengineer
🌎 Remote; Paris, France
⏰ Full-time
🏢 Payfit
Job details https://jobsfordevelopers.com/jobs/senior-software-engineer-self-acquisition-at-payfit-com-jul-31-2023-dbba6d?utm_source=mastodon.world&ref=mastodon.world
#jobalert #jobsearch #hiring
Can anyone recommend a #postgresql book? Audience is a small business owner with software development experience who will be managing his own instance until there’s enough revenue to hire someone to help with that. On version 14 if that matters
@edward It's not only table names, it's also column names, etc.
It can be changed by compiling PostgreSQL.
Other RDBMS have other maximum length for identifiers:
- SQL Server 124
- MySQL 64
- Oracle 30
The #PostgreSQL limits are documented here https://www.postgresql.org/docs/current/limits.html
Had an interesting bug involving #postgresql today.
Identifiers (table names, column names, other named things) can only be 63 characters long. We had some materialized views that were longer, some of which shared the same prefix. So like: "calculations_for_xxxx" and "calculations_for_yyyy" but much longer. Still unique though.
We didn't know this and changed the name of the view. "What could go wrong?" we thought.
I only have one constant when I build anything new: #PostgreSQL.
Now, I will have another constant: Prisma over PostgreSQL.
#selfhosting #docker #podman #containers #databases #database #postgresql #SQL
self-hosting newbie here. Is it worth centralizing all my databases onto one container, or is having a container for each service the best approach?
We are currently hosting a booth at #ContainerDays in Hamburg and are excited to discuss #Postgres, Kubernetes and #CloudNativePG with our visitors. After a successful first day yesterday, it's great to see the interest from everyone here today!
We look forward to chatting with you some more - so come join us to say “hi”, 👋 and learn more about how to run #PostgreSQL the #Kubernetes way on this final day of ContainerDays 2023.
insightful podcast about the ecosystem and the applications of #postgis with @pwramsey and Regina Obe
#postgresql #foss4g #osgeo #gischat #qgis
https://pathtocituscon.transistor.fm/episodes/why-people-care-about-postgis-and-postgres-with-paul-ramsey-regina-obe
@tuckerteague PostgreSQL is the go-to datastore on almost ALL projects I've been on for the last 10 years... or more.
It's awesome. Frankly, for *almost* all purposes where people are using these hip NoSQL, timeseries, geospatial, etc things and whatnot, at the scale 99% of us need, pg will handle it JUST fine. Seriously.
And as others have said, very solid but also VERY powerful.
Loved chatting with Brittany and Pat on the @therubyonrails podcast. Brittany asked me about what lead up to the book, and it was fun to share the back story. Give it a listen and let us know what you think! 🎙️#postgresql #rails
High Performance PostgreSQL for Rails with @andatki 🤖
Any of you familiar with or actively using #PostgreSQL ??
I'm doing some training on it and enjoying it thus far. I like the PGadmin4 client as well. But I have very, very little experience with #SQL in general, so I'm curious what people think about PostgreSQL.
Personally, I love how many #bugs can be solved by just going “Have you turned on verbose logging on [x] and checked the output in [log]?”
Btw, thanks to #postgresql for having the ability to log all statements, and even logging when it was silently failing binds.
If #PostGIS &/or #PostgreSQL is your jam 👋 join us for the live recording of the Path To Citus #podcast on Wed Sep 6th @ 10am PDT
There is a live text chat that happens in parallel to the live podcast recording
Guests @pwramsey & Regina Obe are renowned in the geospatial field
Topic = Why people care about PostGIS & #Postgres
This cal invite has instructions on how to join.
(you can listen to podcast after it is published, but participating live is fun)
Pls boost
PostgreSQL and cURL aren't the only ones. Someone is faking security alerts for numerous open-source projects. https://opensourcewatch.beehiiv.com/p/now-postgresqls-turn-bogus-cve by @sjvn
But, it’s not just cURL and
#PostgreSQL being abused. The #CVE #security system itself is badly flawed.
Any #postgresql / #gis / #gischat folks here should join me in following @winslett from CrunchyData, to encourage him to post his cool PostgreSQL content and industry thoughts here in the fediverse too.
Apparently, Postgres is getting multi-master support soon.
That is a big deal.
Amazing and massive explanation of #postgresql topics by @avestura
If you work with Postgres or learning about #SQL databases in general, this is such a good article to read through and find the topics you're not comfortable with so you can dive deeper.
Brilliant work!
Sharing this overview of the #PostgreSQL work done at Microsoft over the last 12 months. I'm in awe of my teammates, & the many other ppl in PG world we collaborate with 🙌
The workstreams:
- Postgres open source
- Citus database extension
- PG ecosystem e.g. Patroni, PgBouncer, pgcopydb, pg_cron...
- PG community work
- #AzureDBPostgres
- #AzureCosmosDB for PostgreSQL
+1 to the many ppl who reviewed this post & helped bring it to life #OpenSource #Postgres #Citus
https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/what-s-new-with-postgres-at-microsoft-august-2023/ba-p/3914506
I've got a PHP website with a PostreSQL backend. It's entirely read-only in production, and its largest table has about 10,000 rows. Postgres, PHP, and a Caddy proxy all run in separate Docker containers.
Is it crazy to think that a simple system like this would run just fine with SQLite instead? In the longer term I’d like to move the whole thing to running with the Django Rest Framework and rework the front-end bit entirely.
This is a pretty cool and detailed article on clearing up space in a #PostgreSQL DB:
https://hakibenita.com/postgresql-unused-index-size
Anyone know a similar article about #MySQL ?
Database layer of the new framework now has working introspection with all classes corresponding to information_schema tables being automagically generated.
Tables.select().where(Tables.table_schema == 'public').execute().fetchall() now gives you a list of all existing tables in the database.
This works to query for columns, constraints, triggers, privileges, features and literally everything else #postgresql exposes through information_schema. :3
pgbackrest is awesome! And the user guide is easy to follow and gets a working backup/restore setup after 30mins (or less). #PostgreSQL #PGAdmin
Wow, this was a fantastic talk about indexes in #Postgresql and #Django. Thank you for the recommendation @sabderemane!
etlhelper is a #Python ETL library to simplify data transfer into and out of databases. It takes care of importing drivers, formatting connection strings and cursor management to reduce boilerplate code required to use relational databases.
https://pypi.org/project/etlhelper/
v0.14.3 is out now. It contains minor fixes and a deprecation warning because v1.0.0 is coming soon with breaking changes.
Release notes: https://github.com/BritishGeologicalSurvey/etlhelper/releases/tag/v0.14.3
@danslimmon - Please, tell us more.
Anyone out there have success with a #DigitalOcean Managed PostgreSQL 15 live DB migration?
I can not get the thing to work at all and oh boy have I tried.
I published a new blog post, about upgrading #postgresql containers to a newer major version. Also, there’s an elephant.
Beta release in less than 2 weeks! 😬 Mark your calendars 📆 for August 30! 🐘💎 #pgrailsbook #PostgreSQL https://pgrailsbook.com
Am I the only one to really prefer #sqlserver #mssql compared to #postgresql and #mysql ? 😆
I love the features, quality of the tooling and backup management.
Just a shame than this one is not open source, and still waiting for ARM support (until that there is Azure SQL edge that is kind of equivalent).
And you what is your favorite and why ?
Calling all #PostgreSQL / #SQL / #DBA enthusiasts to help improve #lemmy performance.
Please share your thoughts @ https://lemmy.ml/post/3352605
> There are 1 to 2 millions comments stored on lemmy.ml and lemmy.world - this rebuild of child_count can take hours, and may not complete at all. Even on 100,000 rows in a test system, it’s a harsh UPDATE statement to execute.
#PostgreSQL 15.4 has been released (#SQL / #DBMS / #RDBMS / #Database / #DB) https://postgresql.org/
Hot off the press is Episode 6 of the #PathToCitusCon podcast for developers who love Postgres.
Live recording happened on Discord earlier this week & now it's available in all the podcast places 🎙️ so you can listen while you walk the dog or do dishes or _whatever_.
Thanks 🙌 to @floord & Chelsea Dole for joining Pino & me for the convo on "You're probably already using Postgres: What you need to know"
And +1 to Carol Smith & @asw for producing! #PostgreSQL #podcast https://pathtocituscon.transistor.fm/episodes/youre-probably-already-using-postgres-what-you-need-to-know-with-chelsea-dole-floor-drees
Do you use #opensource software with geoscience-related spatial data?
The #foss4guk conference is on 7th September, with venues across the country.
https://uk.osgeo.org/foss4guklocal2023/index.html
The British Geological Survey are hosting a venue. Come spend the day at the Keyworth (Nottingham) HQ and hang out with our geologists and developers.
I was able to recover most of the data from a #postgresql database with file corruption (ERROR: could not read block 0123 in file "base/567/8910": read only 4096 of 8192 bytes) by setting zero_damaged_pages to on (https://www.postgresql.org/docs/current/runtime-config-developer.html). Warning: This zeros out the damaged page, but allowed me to get the rest of the data.
Thanks to Łukasz for leaving a hint to it in a comment on stackexchange in 2017 https://dba.stackexchange.com/questions/44508/error-could-not-read-block-x-of-relation-base-y-z#comment359627_44508
Reasons to use MariaDB: It's what I know.
Reasons to use PostgreSQL: It's probably better.
Reasons against using PostgreSQL: I don't know it... and I'm scared to learn. I'll just use MariaDB! :D
Jokes aside, I should look into PostgreSQL at some point.
I'll be presenting virtually tomorrow for the SF Bay Area PostgreSQL User Group (SFPUG). Please RSVP if you're interested and I hope to see you there! #postgresql #postgres #rubyonrails #partitioning
Do you like podcasts? Are you a developer? Do you love #PostgreSQL?
I'm co-hosting a new #podcast for developers who love Postgres
This Wed Aug 9 @ 10:00am PDT you can join the live recording of #PathToCitusCon Ep06 w/guests Chelsea Dole & @floord
Topic = You're probably already using Postgres: What you need to know
Boost please & tell friends
Mark your calendar (invite says how to join) https://aka.ms/PathToCitusCon-Ep06-cal
Or listen to past episodes anytime anywhere https://aka.ms/PathToCitusCon-transistor
A Postgres LSP is being actively worked on!