Difference between running Postgres for yourself and for others
ubicloud.com
ubicloud.com
It's also worth calling out the first diagram shows dependencies between features for Ubicloud's managed Postgres. AWS, Azure, and GCP's managed Postgres service would have a different diagram. That's because we at Ubicloud treat write-ahead logs (WAL) as a first class citizen.
I think you may benefit from researching the ecosystem some more.
If you have concrete knowledge, please share it and don't be cryptic!
In this blog post, we wanted to focus on running Postgres for others. AWS, Azure, and GCP's managed Postgres services, and those offered by startups like Crunchy, don't use k8s-based solutions. For us, one reason was operational simplicity. You can write a control plane for managed Postgres in 20K lines of code, including unit tests. This way, if anything breaks at scale, you can quickly figure out the issue without having to dive into dependencies.
We also understand different tradeoffs apply when you're running Postgres for yourself or for others. In this blog post, we wanted to focus on the latter scenario.
You can overcome this problem by setting the archive_timeout and forcing Postgres to generate a new WAL file every minute. With this configuration, Postgres would create a new WAL file when it hits the 1-minute or 16 MB threshold, whichever comes first.
The second issue with backup/restore is “no activity”. In this case, PostgreSQL wouldn’t create a new file even if the archive_timeout is set. As a solution, you can generate artificial write activity by calling pg_current_xact_id().”
Can you explain why to create a WAL file even though there is no activity?
That's why.
How can they ensure that? Sounds more like they ensure that people can pick point in time with minute granularity.
I’m no expert, but doesn’t postgres write a timestamp with each record in the WAL? I know that when you start postgres in recovery mode it lets you specify a timestamp you want to restore to, and I don’t think that timestamp needs to be on a WAL file boundary. (So the tricks seem weird / unnecessary to me.)
Then, as @pintxo, @bjornsing, and @Doxin asked below; Why not let users pick a time and then replay the closest backup? Why create empty backups? This is a valid question.
The answer is that without an empty backup, it's not possible to confirm there was no write activity. Maybe some write activity occurred but you fail to archive the WAL file. You need at least one WAL file (empty or not) with a timestamp equal to or more recent than the target restore time to ensure there were no write activity.
I didn't explain this in detail in the blog post for the sake of brevity, but you don't need to create an empty WAL file for each minute with no write activity. For instance, if there was no write activity for 10 minutes and then a WAL file is generated, PostgreSQL can deduce there was no write activity during those 10 minutes and is able restore to any minute within that period. However, if the user wants to restore to 5 minutes ago and there has been no write activity for the past 10 minutes, PostgreSQL doesn't have a WAL file to confirm lack of write activity. In this case, you need to call pg_current_xact_id() to generate a WAL file. So the trick is, when you get a restore request, call pg_current_xact_id() only once, just in case if there were no write activity.
This is a good example of difference between running PostgreSQL for yourself vs. others. When running PostgreSQL for yourself, such edge cases is not a concern. However, when managing it for others, an edge case like this could prevent a restore and create an operational burden. Automating this process is required especially if you are managing lots of PostgreSQL databases.
For the first one, the answer would be no, because I don't have any expertise on MySQL. I only used it years ago on my hobby projects. I'm not the best person to write something like this for MySQL.
For the second one, the answer would be maybe, but not any time soon. We have list of services that we want to build at Ubicloud and currently services like K8s have more priority.
Second unrelated question that anyone might want to add to, why is MySQL falling out of favour in recent years in comparison to Postgres?
I'm curious about this myself! Anyone know, or care to share?
I used to work with/host lots of small php apps that tied in with MySQL. PHP has dropped in popularity over the years.
Add to this hosting for Postgres has become common so you're not tied to cheap hosting for MySQL only.
At least I think that the Oracle name being tied to MySQL made it icky for a lot of people that think the big O is a devil straight from hell. This is something that got me looking at Postgres way back when.
There's probably a myriad of 'smaller' reasons just like this that enforce the trend.
From just over a year ago. And honestly the recent reasons look like all the reasons why I moved away from it in the past.
UTF8 isn't, which is a holdover from during UTF8 design, but even then, it should have been retired and re-aliased from the next major release (as a breaking change, that wouldn't break much).
Magic (stylized) single-quotes work as apostrophes in queries, meaning you can't use simple escape logic if you're writing a custom client, and a whole new level risk of security bugs. Yeah, you should use parameterized queries, but still.
If your default indexing is case-insensitive, if you do an index on a binary field, it's going to be case-insensitive (which can/will cause annoying issues).
There are so many more one-off and analogies people have when working with MySQL. PostgreSQL by contrast has been far more consistent in the usage, development and application of query support. Custom enhancements and so many other features just work, and better.
I'm asking because logical replication seems to become more and more common as a solution to automatically replicate data to external systems (another Postgres instance, Kafka, data warehousing, or offline sync systems in my case), but many cloud providers appear to not support it. Many others do (including AWS, GCP), so I'd also be interested in how they handle high availability.
There are many reasons that cloud providers don't want to support logical replication;
- It requires giving superuser access to user. Many cloud providers don't want to give that level of privilege. Some cloud providers fork PostgreSQL or write custom extensions to allow managing replication slots without requiring superuser access. However, doing it securely is very difficult. You suddenly open up a new attack vector for various privilege escalation vulnerabilities.
- If user creates a replication slot, but does not consume the changes, it can quickly fill up the disk. I dealt many different failure modes of PostgreSQL, and I can confidently say that disk full cases one of the most problematic/annoying ones to recover from.
- It requires careful management of replication slots in case of fail over. There are extensions or 3rd party tools helping with this though.
So, some cloud providers don't support logical replication and some support it weakly (i.e. don't cover all edge cases).
Thankfully there are some improvements are being done in PostgreSQL core that simplifies failover of logical replication slot (check out this for more information https://www.postgresql.org/docs/17/logical-replication-failo...), but it is still too early.
Some examples:
1. I've seen a delay of hours without any messages being sent on the replication protocol, likely due to a large transaction in the WAL not committed when running out of disk space.
2. `PgError.58P02: could not create file \"pg_replslot/<slot_name>/state.tmp\": File exists`
3. `replication slot "..." is active for PID ...`, with some system process holding on to the replication slot.
4. `can no longer get changes from replication slot "<slot_name>". ... This slot has never previously reserved WAL, or it has been invalidated`.
All of these require manual intervention on the server to resolve.
And that's not even taking into account HA/failover, these are just issues with logical replication on a single node. It's still a great feature though, and often worth having to deal with these issues now and then.
What is MMW on the flowchart?
* You describe them as wanting to deploy the database in container: why would anyone do that (unless for throwaway testing or such)?
* The certificate issue seems also very specific to the case when something needs to go over public Internet to some anonymous users... Most database deployments I've seen in my life fall into one of these categories: database server for a Web server, which talk in a private local network, or database backing some kind of management application, where, again the communication between the management application and the database happen without looping in the public Internet.
* Unwilling to wait 10 minutes to deploy a database. Just how often do they need new databases? Btw. I'm not sure any of the public clouds have any ETAs on VM creation, but from my practice, Azure can easily take more than 10 minutes to bring up a VM. With some flavors it can take a very long time.
The only profile I can think of is the kind of user who doesn't care about what they store in the database, in the sense that they are going to do some quick work and throw the data away (eg. testing, or maybe BI on a slice of data). But then why would these people need HA or backups, let alone version updates?
* We saw many deployments where communication between web server and database were going through public internet. It doesn't need to be for anonymous users. It is also even somewhat common where web server and database are managed by different SaaS providers, so they have to (in most cases) communicate through public network.
* We (and all cloud providers) are trying to reduce overall provisioning time, mostly to reduce the friction for first time users. There is no SLA but for common instance types, it would be unusual to wait for more than 1 minute at AWS, Azure or Ubicloud for VM provisionings.
Maybe you and I just experience different parts of the big computing ecosystem, hence what is "usual" for each of us is different. Out of curiosity, are you coming from enterprise background?
I certainly did not know that.
If you need to execute commands on database server for admin work, use something designed for this (such as ssh) - this will keep right management and logging simple, only one source of shell command execution.
If you need to execute commands periodically, use some sort of task scheduler, running as a dedicated user. To avoid 2nd connection, you may use use postgres-controllable job queues. Either way, limit to allowed commands only, so that even if postgres credentials are leaked no arbitrary commands can be executed.
Inboth approaches, this would have allow high speed, localhost-specific transports instead of local shell.. if postgresql would have supported them.
Postgres already has "COPY FROM STDIN" command, which makes database server use postgres connection for the raw data. However, since it uses the existing connection, it needs to be compatible with Postgres protocol, which means that there is an extra overhead in wrapping streaming data.
On the other hand, "COPY FROM COMMAND" has no wrapping overhead, as it opens direct pipe to/from command, so no Postgres protocol parsing is involved - as only short command string is sent via postgres connection, while bulk data goes over dedicated channel. This makes it faster, although I am not sure how much does this actually save.
amluto's point was that one can achieve no-wrapping performance of "COPY FROM COMMAND" if one could pass dedicated data descriptor to "COPY FROM STDIN". This could be done using SCM_RIGHTS (a AF_UNIX protocol feature that passes descriptors between processes) or pipes+slice (not 100% sure how those would help). But with SCM_RIGHTS, you'd have your psql client create pipe, exec process, and then pass the descriptor to the sql server. This would have exactly the same speed as "COPY FROM COMMAND" (no overhead, dedicated pipe) but would not mix security contexts and would execute any code under server's username - overall better solution.
Your point was "only on localhost", which I interpreted as "this approach would only work if psql runs on localhost (because SCM_RIGHTS only works on localhost); while "COPY FROM COMMAND" could even be executed remotely".
This is 100% correct, but as I said, I think an ability to execute commands remotely as a server user is a bad idea and should have never existed.
psql … < file
If you are trying to write a table to a CSV file remotely and you want it to land in a file on the server, then (a) that’s weird and I struggle to come up with a case in which you actually want that and (b) use SSH or some other bespoke protocol to do what you actually want, rather than having the database server kludge it up for you.
On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy.
COPY … STDIN/STDOUT is fine, but it takes some potentially awkward code that is, as far as I can tell, not particularly portable between languages or between database protocols.
> that’s weird and I struggle to come up with a case in which you actually want that
oh, that's easy:
1. your san backplane is faster than ssh
2. you want to make a backup in a non-postgres-dependent format on the server's tape drive
3. you want to import the data into a new test database you're creating on the server
i mean, sure, nowadays you would usually do all those things on multiple servers. but postgres still scales down to the scale where your servers are pets, not cattle, and that's a feature. remember ingres got renamed postgres in the 90s, and ingres itself is from the 70s
> On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy.
that's great, but would you mind parking your car somewhere that isn't my lawn?
you're right about fd passing tho
From grandparent:
> That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc..
Here the project homepage with list of supported software: https://linux-system-roles.github.io/#toggleMenu
"The problem with the server certificate is that someone needs to sign it. Usually you would want that certificate to be signed by someone who is trusted globally like DigiCert. But that means you need to send an external request; and the provider at some point (usually in minutes but sometimes it can be hours) signs your certificate and returns it back to you. This time lag is unfortunately not acceptable for users. So most of the time you would sign the certificate yourself. This means you need to create a certificate authority (CA) and share it with the users so they can validate the certificate chain. Ideally, you would also create a different CA for each database."
Couldn't you automate this with Let's Encrypt instead?
Thanks!
Self signing probably causes quite a few other issues, even though you have more control of the process, doesn't it?
Thanks!
I think if you need to generate a certificate once in a while, using Let's Encrypt or DigiCert is OK. Even if they are down, you can wait for few hours. If you need to generate a certificate every few minutes, few hours of downtime means hundreds of failed provisionings. Hence, we opted for self-signing.
In terms of reliability, it is great, because we control everything. It is also quite fast; it takes few seconds to generate and sign a certificate. The biggest drawback is that you need to distribute the certificate for CA as well. Historically, this was fine, because you need to pass CA cert to PostgreSQL as a parameter anyway, so the additional friction for users that we introduced due to CA cert distribution was low. However with PG16, now there is an option sslrootcert=system, which automatically uses OS trusted CA roots certs. Now the alternative is much seamless and requires almost no action from user, which tilted the balance in favor of globally trusted CAs, but still it doesn't give me enough reason for the switch.
I have few ideas around simultaneously self signing a cert and also requesting certificate from Let's Encrypt. The database can start with the self signed certificate at the beginning and we can switch to Let's Encrypt certificate when it is ready. Maybe I'd implement something like that in the future.
Is this a problem of multi-tenancy in k8s specifically or something else?
Also for us, one reason was operational simplicity. You can write a control plane for managed Postgres in 20K lines of code, including unit tests. This way, if anything breaks at scale, you can quickly figure out the issue without having to dive into dependencies.
DigitalOcean uses 5 minutes as archive_timeout, which is also a reasonable value. In our experience, we saw that most of our customers prefer less exposure and we settled on 1 minute as archive_timeout value.
Roughly archive_timeout defines your RPO(recovery point objective).
I would think you'd want at most one certificate authority per customer rather than per database. Why am I wrong?
It is likely that we would add a organization-like entity in the future to our data model and at that time sharing certificate authority would make more sense.