PostgreSQL

16 Oct 2024 22:51

>and people don't read the documentation, or they don't understand it, or they don't follow it. in so, so many different ways.

Which circles back to my answer. People do not care about knowing what they are doing

PostgreSQL

16 Oct 2024 22:49

i think people messing up something around base backups or restores is probably the most common cause of database corruption that gets escalated to me within EDB

PostgreSQL

16 Oct 2024 22:47

Exactly. And you pg_dump that and restore it into your live system...

PostgreSQL

16 Oct 2024 22:46

You have a PITR of a particular DB/schema

PostgreSQL

16 Oct 2024 22:46

so we're discussing performance?

PostgreSQL

16 Oct 2024 22:45

you DROP everything but that schema

PostgreSQL

16 Oct 2024 22:45

Wasn't it times when -X stream was not the default?

PostgreSQL

16 Oct 2024 22:43

That SQL seems sound at a quick glance. What more are you looking for?

PostgreSQL

16 Oct 2024 22:41

My take is: pg_basebackup is just as bad as pg_dump *if you don't have a proper WAL archive*. RTO/RPO is just as bad (RTO is 'duration of the backup' later for pg_basebackup, obviously).
Anyway. If you're fine with the RPO, I see few reasons not to use pg_dump[all].

PostgreSQL

16 Oct 2024 22:35

I have seen WAY more people mess up backups using pg_basebackup or the low-level backup API than I have seen people mess up pg_dump. Like probably two orders of magnitude.

PostgreSQL

16 Oct 2024 22:35

Nobody should feel obliged to provide free technical support, but that doesn't justify giving deliberate wrong answers.

PostgreSQL

16 Oct 2024 22:32

OP could search online in a couple of seconds and get an answer. Posing the question that way means you just don't care and I'd say a response like that is correct to perhaps further stimulate OP to research themselves

PostgreSQL

16 Oct 2024 22:30

I bet a lot of people would disagree with that too, in the sense that most people shouldn't be running that themselves rather than via a tool. But what I think is that it's silly to argue about what it means to be a backup tool. What's important is to educate people about what to do and why to do it like that. Getting stuck on the words we use to describe things isn't the way forward, in my opinion.

PostgreSQL

16 Oct 2024 22:26

Perhaps we should say "pg_dump is not a backup tool, pg_basebackup is" :)

PostgreSQL

16 Oct 2024 22:25

Very few problems are solved by getting mad at people for having bad opinions.

PostgreSQL

16 Oct 2024 22:50

it's enough to make you crazy. there is absolutely no end to ways to do it wrong. and people don't read the documentation, or they don't understand it, or they don't follow it. in so, so many different ways.

PostgreSQL

16 Oct 2024 22:48

no, people do way more things wrong than that. tons and tons of different mistakes. i wrote about some of them here: https://rhaas.blogspot.com/2012/03/why-is-my-database-corrupted.html

PostgreSQL

16 Oct 2024 22:46

I'm running out of +1's here :)

PostgreSQL

16 Oct 2024 22:46

I really disagree with this. It sounds like you're saying "I'm going to give that person an intentionally unhelpful answer because I think they're too dumb to not mess it up". Well, how will anyone ever get smarter with that attitude? If you don't want to give a helpful answer, just let it go.

PostgreSQL

16 Oct 2024 22:46

bet you didn't expect that

PostgreSQL

16 Oct 2024 22:45

+1 to that.

I include both pg_dump *and* pg_basebackup in my trainings btw. Imagine you need a PITR of a particular DB/schema. You PITR from your basebackup. And then???

PostgreSQL

16 Oct 2024 22:44

thanks 😊, but currently I'm spending like 8 secs in that query, I want to check if there's something doable there before optimize it changing database structure

PostgreSQL

16 Oct 2024 22:42

No.

Strangely, this seems an obvious answer — but if he really wants a backup — it's a deeply wrong answer.
A person not able to find -n by itself definitely shouldn't do backups by pg_dump. It would probably lead to catastrophic consequences.

PostgreSQL

16 Oct 2024 22:39

Hi there, someone know how can I get a list of elements joined to the first element of another table in a many to many relation ?
like:

with second_table as (
Select id, a_id, name,  row_number() over (partition by B.a_id order by B.date desc) as rn From B
)
 select * from A  join second_table on A.id = second_table.a_id;

PostgreSQL

16 Oct 2024 22:35

And what's also ironic is that the first comment of that answer is somebody complaining that it doesn't work due to a conflict ( https://dba.stackexchange.com/questions/53185/backup-only-one-schema-and-restore-it-on-another-server#comment640604_53190 )

PostgreSQL

16 Oct 2024 22:33

Searching for "backup schema postgresql" pulls up the exact command needed to do it in my favorite search engine, along with a description from some stackexchange website

PostgreSQL

16 Oct 2024 22:32

RPO/RTO may be just fine. There arw different cases...


But pg_dump requires a lot of knowledge to make a reliable backup. No DDL at time of a backup, no gigabyte rows, some mess with functions order and some care about recursive foreign keys to name a few troubles...

This could be called exceptional knowledge today for non-DBA. So my answer is: you can make a usable backup system out of pg_dump, but it's a non-trivial work, usually impossible in typical environment.

PostgreSQL

16 Oct 2024 22:28

Also, pretending like you don't know what someone wants when they say they're looking for a way to back up one schema is pretty unhelpful, IMHO. You know they're looking for pg_dump -n right? So like if you want to also tell them to maybe look into other options, cool, but I mean, telling them that there's no way to do it because you hate pg_dump is just being confusing oon purpose.

PostgreSQL

16 Oct 2024 22:26

Well, pg_dump is a tool for dumping data, pg_**basebackup** is the tool for creating a proper backup

PostgreSQL

16 Oct 2024 22:24

Well, the blog post hardly recommends pg_dump. And these reasons are mentioned. But my main point is that saying "don't use pg_dump because the RPO/RTO suck" is a perfectly fine argument, but saying "pg_dump is not a backup tool" is 100% useless because it doesn't help anybody understand anything.