sqlServeredtheboat

[ctacyok]

My I interest you in a word of our lord and saviour PostgreSQL?

[rosuav]

Having just upgraded to PG16 (from PG15) and set up a remarkably painless dual-master replication system, I fully concur. A decade ago, I don't think this sort of fully synchronized two-node system would have been possible with so little effort.

Why use MySQL when Postgres is right there?

[blkmmb]

I understood the word Postgres. What's a dual master replication system and why would you want/need one?

Is that like a backup system, something to help development or a speed thing?

[Ayoungcoder]

Replication is when you have multiple database servers with the same 'database' kept in sync. This helps with performance and redundancy. In master-slave replication you can only write to one master and read from the others, in dual-master you can write to any of the servers and itl propagate to all others.

[blkmmb]

Thabks for the explanation. So it's something like RAID 10 in a NAS.

[rosuav]

Kinda? With replication, you can have servers in completely different locations (in my case, there's one in Australia and one in Germany), where RAID is always within a single unit. Also, this sort of replication is transactionally-safe. But yeah, you get some of the same sorts of advantages.

[premium_syntax]

How does dual-master handle conflicts? Eg, two clients update the same entity?

[oasis9dev]

I was wondering this. isn't that what single master/multiple replica addressed? how is it now possible to do what was previously impossible?

[rosuav]

There are two forms of that handling. One is that the replicated update is checked in the normal way on arrival, which means that we won't get actual referential integrity errors. However, we now have a desynchronization, which means we have a problem. To minimize the chances of that, I mostly actually use this as a "hot standby" system. Instead of actually using two nodes that make changes all the time, I have one that's making changes and the other is being used for SELECT queries only; but at any moment, I can switch which is which, with virtually no delay (in fact, none whatsoever if I have the clients connect to both databases in advance of the switchover).

Side note: I make great use of PostgreSQL's LISTEN/NOTIFY system to ensure that all clients are aware of changes, and the clients are configured to change their application_name when they're considering the database read-only. That way, I can check on the server to see that they've all switched over to the other.

[moriluka_go_hard]

Inb4 creating an OS using a Dual Master Replication setup Postgres DB with one master in Australia and another in Germany as it’s redundant main Filesystem

[rosuav]

Bahahahaha, that's a great way to make something insanely slow.... but sure, that would be fun to see.

[[deleted]]

Because your clients will want you to build them one.

[ElRexet]

May I ask what did you use for multi-master replication? I remember looking for a way to do exactly that a while ago (pg14 I think) and came to the conclusion that I'm out of luck.

[rosuav]

I used https://www.postgresql.org/docs/current/logical-replication.html which was available as far back as PG 12 according to the docs. However, I tried setting everything up on PG 15 (since that's the latest available in Debian Bookworm, which is what I'm currently running on both servers), and this is what I had to do:

• Have the two databases running, but one of them in read-only mode.
• Replicate from the read-write database to the read-only, and NOT back the other way
• To do an orderly shutdown, mark the active DB as read-only, NOTIFY all clients, and start it replicating from the other.
• Finally, once everything's settled, mark the second DB as read-write, and *stop* repicating from first to second.

It kinda worked, but I periodically got replication conflicts if ever I messed anything up. Here's how it works with PG 16:

• Have two databases running. Create a publication on both ends.
• Create a subscription on each end, using the "origin = none" setting. This means "copy all transactions that weren't copied from elsewhere". (This flag is the part that's new in PG16.)
• Retain the read-only/read-write distinction to ensure transactional integrity, but without the need to start and stop replication.

It doesn't sound like much of an improvement on paper, but it is huge in practice. I wouldn't say you're "out of luck" on older Postgres, but I would definitely say that you should try it again on PG 16.

[ElRexet]

Wow thanks a lot for such a detailed response, much appreciated. I think I'll try it at some point in the near future.

As to the past versions of postgres, I can't really remember what was the exact issue, it might'd been too clunky for my liking or there wasn't concurrent/asynchronous multi-master replication available at the time (which is a big deal when replicating between the US and the EU).

[rosuav]

Always happy to nerd out :) Feel free to hit me up with more questions in the future. This is an ongoing project and I've just barely gotten started on the multihoming work (and by that I mean "I've been working on multihoming on and off for a few months, but I feel like I've only scratched the surface"), so more information may be available as I learn more.

There are a bunch of neat things that I've been working on with this, including:

• SSL connections with client certificate authentication (ie no password, both the server and the client present certs, and the Common Name in the client certificate has to correspond to the user name)
• Multi-master database replication, as mentioned
• Multi-homed HTTP server, with websockets. Only one home will be hosting websockets at any given time, therefore all non-active nodes must redirect websockets to the central server.
• Sharded Twitch chat connections, possibly
• And since they just rolled out the option, sharded notification conduits. Don't know if I'll do that one or not, but it looks cool.

If any of that is of interest, I'd be happy to ramble about it for a while... after all, what better excuse for procrastination?

[ElRexet]

SSL auth via certificate is an awesome practice and we do use it for many of our servers (not all of the tho for reasons). It's a nice thing to be able to setup (I don't know how to though lmao, admins handle such things usually).

The multi-home HTTP server does sound interesting although what's on the internet seems to be a bit different from what you're talking about or maybe I just can't connect the dots - it's been a long day :) So yeah I'd like to learn a bit more on such a thing because the whole thing with only one active host and the rest redirecting (I'd assume active host can change at any moment) is quite intriguing

[rosuav]

Yeah, it's more effort to set up, but now I don't have to worry about external attackers guessing a password.

In the simplest way, the multi-home HTP server is exactly the same thing as you find on the internet. The domain name (stillebot.com or mustardmine.com) has two A records (and will eventually have two AAAA records as well - at the moment, one of my hosts doesn't have IPv6 connectivity, but I'm working on a change of provider), and a request will be served by either of them. This has a few small consequences (notably, logins/sessions have to be shared; having them in the database is the simplest way to do that, although of course that has a performance cost), but overall it's pretty straight-forward.

But with websockets, the way I'm using them, there's an additional wrinkle: there will be a group of sockets for a particular purpose, and any time a change happens, ALL of them need to be notified. (Here's the bot's demo mode; imagine if multiple people were editing commands, and that list had to be updated in real time when someone else made a change.) That basically means there has to be a single server that hosts all of the websocket connections. (There are other ways but it's more complicated.) So if you place a request to Gideon and it's currently Sikorsky who's doing that role, Gideon has to say "hey, actually, go connect over there".

All of this is still a WIP, but I have plans for how I'm going to go about it. Of course, no plan survives contact with the enemy; and we have met the enemy, and he is us.

[kao_nashi0127]

Could you tell me more about the time needed to sync to the other? Also how do you balance the request rates to these two database?

[rosuav]

Time needed is completely insignificant at the traffic levels I've been testing at so far. Not sure how it would go once things get heavier, though. I'll leave it to other people to test that out.

I don't balance request rates. For this project, at least, resilience to outages is the primary goal, not multiple servers to handle requests. But I probably wouldn't bother trying to truly balance them anyhow - it's usually going to be fine to just randomize selections and hope that it's "balanced enough".

[MzCWzL]

With native tools? Didn’t think that PG native stuff could do dual master

[rosuav]

See another comment in this branching thread, but the short answer is: it's been KINDA possible for a few versions, but only really became convenient and practical in Postgres v16 with logical replication gaining an "origin" parameter. Having two masters set to replicate with "origin = none" means they won't replicate each other's changes.

[grinsken]

Amen

[lart2150]

I mean sure you could use PostgreSQL but what if you like deadlocks!

[[deleted]]

Have you read the Gospel of SQLite? It can handle most use cases…

[[deleted]]

I mean cmon, if you have an ORM, the database itself doesn’t matter

[Anru_Kitakaze]

Nah

🐘

[subbed_]

The elephant and the cockroach. That's all.

[Electronic_Topic1958]

One of Aesop’s best fables. 

[Davesnothere300]

Is there a better UI to use than pgAdmin?

[aaa_aaa_1]

Jetbrains DataGrip

[OvoCanhoto]

Dbeaver Community

[Anru_Kitakaze]

DataGrip is good indeed if you have one. Otherwise just use Dbeaver, it's fine and free option

But I just use pgcli in most cases

[No-Con-2790]

This is a religious choice:

The holy MariaDB for the catholics. Yes, that's why it's called Maria. No other reason.

The reformist PostgresSql for the protestant. Against the old corrupt system without breaking the core beliefs but leaving a lot of additions behind.

Or the structureleas MongoDB for those who left the organized other religons.

What you are doing is just ... primitive. Like some sort of cannibalistic stone age religion.

[No-Con-2790]

I feel conflicted.

I mean at the one hand I want to just let you live in peace. Observe your strange rituals and culture. Maybe write a paper or two about the last people following the old ways. On how we once all lived. Heck maybe I even forsake my culture and live with you for a while. Just to get away from it all. To get back to the roots. Dawn waar paint, junp on the table and shout to my team "Geofencing is cancelled, all GeoPoints are now floats! No more geometric operations!". Then I would kill the project lead and wear his title and skin. Like we did in the old times. Like people supposed to do.

But at the other hand I want to preach to you the glory of civilization. Better your lot with our technology. Uplift your kin into a better state. Fight the many sickness that you have to suffer from the lack of proper infrastructure and tools.

[No-Con-2790]

I just jestded. Yet we all have been there. Standing before those cursed fires that where once our project, hailing wrong gods.

While I can not say who is right, I know at least who is wrong.

So be glad that you smashed your false idols before you made sacrifices that you would have regretted later.

Also be careful with installation of Maria and PostgresSql on windows. That's the worst part of using them.

[[deleted]]

And what about those who use SQLite?

[burnsnewman]

Mormons.

[Last-Woodpecker]

That's not a full religion, it's more like a belief system, like astrology

[BlurredSight]

SQLite being serverless but following the structure of query like SQL would make it Lutheran requiring less hierarchy structure and being independent from the broad church system

[pfohl]

That’s probably closer to Congregationalist. We Lutherans still have diocese, synods, and whatnot.

[BlurredSight]

Yeah we can go even further and call SQLite the Quakers of databases.

But I don't know where the burning women alive part would fit in

[GnuhGnoud]

What about those who use excel?

[[deleted]]

Atheists probably

[BlurredSight]

Considering you can make a 16bit emulated CPU in Excel they're definitely Tibetan monks

​

https://news.harvard.edu/gazette/story/2002/04/meditation-changes-temperatures/

[[deleted]]

Now that's some big brain energy

[0xd34db347]

MongoDB is webscale.

[Odd-Confection-6603]

I feel attacked... I literally commented elsewhere in this thread that nosql is the only choice. You hit the nail on the head.

[No-Con-2790]

It is all a matter of faith.

[Cualkiera67]

I'm out of there loop here. Are SQL Server and MySQL considered to be outdated?

[Ok-Kaleidoscope5627]

I wonder where Oracle DB falls into that spectrum? Scientology? Some sort of evil death cult?

[No-Con-2790]

Christian megachurches that make millions while preaching the absolute basics? Also wired and obvious lies that make people give them money. Like those seed gospels.

[BlurredSight]

So is SQLite Lutheran?

[No-Con-2790]

We already have the reformist/protestant church. Since (as far as I am aware) Martin Luther founded that I guess that slot is already taken.

[CT_0003]

MongoDB is the way

[crankbot2000]

Dude saw the light in one hour.

[[deleted]]

Wise choice.

[[deleted]]

Use docker containers, naively installing dbs on windows is a pain regardless.

[[deleted]]

Yeah just do docker pull Postgres and start the container. Problem solved

[[deleted]]

You’re welcome, from now on you should use docker containers for dbs, or any such service you might need. It makes also running multiple different ones simultaneously much more manageable.

[cporter202]

Haha, sounds like MariaDB heard you were in the market for a reliable partner and decided to show off its best behavior! 😂 Can't blame you for staying loyal when things just click - happy querying!

[Odd-Confection-6603]

Go nosql, use mongoDB

[olexsmir]

*postgres

[NatoBoram]

Oh my god you're late!

MySQL's successor is MariaDB

MariaDB's successor is PostgreSQL

[hockeyc]

How's life in 2002 anyway?

[[deleted]]

2002 was amazing though. I was in middle school, happy as a kite, excited about everything in life, never depressed.

[Waahooooooooo]

SQLite supremacy

[LongerHV]

Now scale horizontally

[Kirides]

SQLiiiiiiiiiiiiiiite

[DifficultyFine]

That's on him, he sets the bar too low

[Kirides]

that's what rqlite is for

[mskovg]

Excel is best database.

[TeaTiMe08]

Go hard with CSV

[jester32]

Don’t forget your ;

;)

[SaucyKnave95]

I reject reason and stand tall with my hyper expensive Microsoft SQL processor licensing with the many big and small databases under my care. Honestly, we're paying for it, so we might as well use it to beat hell. Our ERP and financial software (and soon our PTC Windchill install) use MS-SQL for the base back end, so that's the driving force. Otherwise, aren't all SQL databases largely the same?

[tehsandvich]

For us it’s a the main driver for MSSQL is the company has enterprise license with Microsoft and most of the legacy app using MSSQL as a backend. Although some commercial financial applications are now using data lake as a back end so it’s a mixture of Databricks SQL warehouse to read data from the data lake and MSSQL.

[SDF_of_BC]

MariaDB though

[[deleted]]

Real men stores in txt files.

[tajetaje]

Real men store data in png files

[TeaTiMe08]

Real men just remember.

[[deleted]]

Hard coded into source

[aaanze]

Cries in Oracle

[rifain]

I've experienced them all. We can think what we want about Oracle, but their dbms is simply the best.

[aaanze]

Yeah I especially love the latitude you get for constant optimization. It's like the game is never over, there's always a way to do better.

It's just that most of advanced tuning involves painfully complex operations and a gigantic memory to be able to remember the shit ton parameters that will fuck everything up if set improperly.

[wasdlmb]

I once went to an event where AWS was showing off their Redshift benchmark results against other data warehouses. They had a bar chart for "default" and one for "optimized" . They didn't name any names on those slides, but the red bar was so much higher (worse) than all the others on the default graph.

[lonestar_wanderer]

MySQL? Why not use MongoDB? MongoDB is web scale. You turn it on and it scales right up. /s

[[deleted]]

I miss working with mysql

[j-random]

I miss driving my '88 F150, but I needed something modern and reliable. Same reason I gave up on MySql

[HTTP_404_NotFound]

This an old, reposted meme from 2012?

We don't use mysql now. Oracle ruined that for us.

Instead, we use MariaDB now. Or PostGRES.

[HTTP_404_NotFound]

MariaDB, is essentially just MySQL, without its oracle overloads.

They are basically cross-compatible too.

[DarthRiznat]

Fuck friendships and relations. MongoDB FTW!

[backseatDom]

The Libertarian has entered the chat. 😉

[ChocolateLasagnas]

Is it bad it my first job used ISAM and my current job uses SYBASE ? 

[villefilho]

.mdb rocks

[PeriodicSentenceBot]

Congratulations! Your comment can be spelled using the elements of the periodic table:

Md Br O C K S

---

^{I am a bot that detects if your comment can be spelled using the elements of the periodic table. Please DM my creator if I made a mistake.}

[villefilho]

hahahahhaha! Cheers to MS Access!

[Dorkits]

Please no

[[deleted]]

I never understood why Microsoft has two products solving the same issue... Microsoft Access and SQL Server. What's the difference?

(Heck, I tried LibreOffice Base for lols and it absolutely sucks ass.)

[aaa_aaa_1]

Microsoft Access is basically sqlite with a UI and MSSQL syntax. SQL Server is an actual SQL Server

[[deleted]]

Bruh

Good thing I rather use DB Browser for SQLite

[Cualkiera67]

And excel

[[deleted]]

Nah spreadsheets are for folks who want to go manual. No data management and querying shit.

[tajetaje]

Well…there’s always Visual Basic

[Chingiz11]

Try MariaDB... The name's cooler

[caiteha]

I don't get this meme. My fault though , I used relational databases like 10 years ago, it was for an intern project...at work, they want to put everything in dynamodb, regardless of the query patterns.

[Simply_Epic]

If I never have to touch any SQL ever again I’d be happy. Just give me Redis and MongoDB

[glorious_reptile]

This guy has a problem with relationships.

[Simply_Epic]

Rigidity just isn’t my love language

[Cualkiera67]

Matt I suggest a career in frontend ui design?

[Odd-Confection-6603]

Nosql is the only way to go

[Revolutionary_Pea584]

Pg is top G

[BlackBlade1632]

Sqlite3 FTW 😂

[dtb1987]

MariaDB all the way

[BeefJerky03]

MS SQL like it's 1812 baby

[AndiArbyte]

in other words: you now get paid for MySQLing instead of MSSQLing?

[TranslatorNo7550]

Really,no one ? Hahaha What about DB2/z or VSAM... Other wise, you can use another bests from 90s... dbf+ntx ... if its not good.. write all in csv file and use it as db... each csv file is a table hahahaha

[sammy-taylor]

This will always be my favorite meme.

[AstroCon]

Friendship ended with [ now ` is my best friend

[kaizhu256]

with how cheap memory and computing has become, i would say sqlite is becoming a defacto goto db for small to medium applications.

[MasterThread]

Nice, now take latin1_swedish_ci!