PostgreSQL

17 Oct 2024 07:20

Yes, design is wrong. I know it for sure, it is designed by me

PostgreSQL

17 Oct 2024 07:16

Maybe pg_basebackup should have too. But wal-g design of this feature is too crazy for being adapted by pg community as-is

PostgreSQL

17 Oct 2024 06:51

dm I could help yeah

PostgreSQL

16 Oct 2024 23:35

Also, the encoding must be the same as of the database, and should include the first row as headers also in pgadmin

PostgreSQL

16 Oct 2024 23:32

You're using pgadmin?

PostgreSQL

16 Oct 2024 23:28

I opened it on excel, it looks so arranged

PostgreSQL

16 Oct 2024 23:26

the a_id column is in join table B only have date

PostgreSQL

16 Oct 2024 23:08

Maybe an index on B.date or composite of B.a_id, B.date might make the lateral approach fast.

PostgreSQL

16 Oct 2024 23:06

nope, how can I do that?

PostgreSQL

16 Oct 2024 23:04

Mind sharing the plan? On explain.dalibo.com for example?

PostgreSQL

16 Oct 2024 23:01

OP obviously was at the *very beginning* of his PG journey. As much as I'd *love* to give every newbie coming here a 4-day beginners course, *especially* on backup & recovery, as much do I not have the time to do so.

I do link my 50-min talk here on a regular basis (it seems), which I - to this day - conceive as a nice primer.

Let's just settle on that we won't overwhelm newbies with bold claims like "pg_dump is not a backup tool" in the future, eh?

PostgreSQL

16 Oct 2024 22:56

it's honestly shocking to me that we're having this discussion.

PostgreSQL

16 Oct 2024 22:54

Not clueless person @ his home, playing with postgresql (not understanding)

PostgreSQL

16 Oct 2024 22:53

i have used pg_dump -n many times and not once has it made my database unrecoverable.

PostgreSQL

16 Oct 2024 22:51

"how to do [very trivial stuff the don't understand]"
"do [direct simple answer]"

"aight the output looks fine sounds good 👍 (the database will be unrecoverable)"

PostgreSQL

17 Oct 2024 07:19

“too crazy”
Are there any disadvantages?

PostgreSQL

17 Oct 2024 07:15

Wal-g https://github.com/wal-g/wal-g has partial restore feature btw.

PostgreSQL

17 Oct 2024 01:07

Hello Gurus ..any expert willing to help setup Postgres patroni cluster , willing to pay for the expertise .Will need to show failiver,failback,recovery,ha etc ..please let know the total fees ...admins please remove this chat if this is not the place or if this is not alllowed

PostgreSQL

16 Oct 2024 23:32

Excel of all tools is not a reference for checking the sanity of a CSV...
I'm quite certain you'll have to adjust your quoting and/or delimiter settings (COPY expects tabs, Excel expects semicolons (in COMMA separated values, oh the irony))

PostgreSQL

16 Oct 2024 23:28

What copy command are you using?

PostgreSQL

16 Oct 2024 23:26

It means one of the rows has more columns than expected. Usually happens for example when you have unquoted text


For example this csv file:

id,sentence
1,Hello
2,I'm fine thanks
3,I like bananas, you?

PostgreSQL

16 Oct 2024 23:23

hello, please can anyone help?
I tried import csv file into my PostgreSQL 14 server and it is giving me error
ERROR: extra data after last expected column

PostgreSQL

16 Oct 2024 23:06

the problem is the join is based on the M-M relation, the index for the FK is there, but the date to sort it is in the B table

PostgreSQL

16 Oct 2024 23:05

Would have thought lateral would be the way to go. Is there an index on the column determining the 'first' row?

PostgreSQL

16 Oct 2024 23:02

Uh well, these days the optimizer will produce the same plan for a CTE and a classic JOIN anyway.

*Do* you have an index on that a_id column?

PostgreSQL

16 Oct 2024 22:59

the CTE is a current improvement, the 'in use' version doesn't have it, it's just a left join, I tried the lateral join and the query time increase

PostgreSQL

16 Oct 2024 22:55

well, he's certainly not going to be any less clueless after seeing your answer.

PostgreSQL

16 Oct 2024 22:54

But you are VP, Chief Database Scientist @ EnterpriseDB, PostgreSQL Major Contributor and Committer

PostgreSQL

16 Oct 2024 22:52

TBTH, a classic JOIN (without the CTE) will probably do better. I.e., assuming you have an index on a_id.
If you need those line numbers, you'd have to improve you OVER() part, of course.

You may also want to look into JOIN LATERAL, depending on how many records you pull out of that JOIN, that might be what you're looking for.

PostgreSQL

16 Oct 2024 22:51

I don't think that my answer is unhelpful.

It's just plain my recomendation on most pg setups: use physical backup tools to backup.

No separate bases/schemas/tables. Just a cluster as a whole. Don't mess with that (without exceptional conditions). If you designed your workflow on schema backup — forget this, there is no schema BACKUP. Design something else. Your attempt was wrong, just make this another way.