PostgreSQL

04 Dec 2024 05:41

Honestly, this gets complicated... Whatever monitoring tool you use, you should point at these things. And set appropriate alarms. I've written my own, as well as leveraged a few others. A couple free Nagios, etc. As well as paid ones.
The important things are:
1) Monitoring
2) At least DUMPING (pg_dump), and practicing your restores
3) Having Backups, and testing the restores. PgBackrest, etc. etc.

Without that. You are BEGGING for data loss. (My personal NAS just died b/c the power supply).
Stuff Happens. And you are not a professional if you don't know how to recover your DB! LOL

PostgreSQL

04 Dec 2024 05:37

thank for remind brother

PostgreSQL

04 Dec 2024 05:36

remember, you can run out of diskspace for 2 reasons. WAL (logging), and DATA (tables,rows, etc).

PostgreSQL

04 Dec 2024 05:30

ok noted, base on default_tablespace it's will base on on PGDATA , and PGDATA will depend on disk drive i am correct?

PostgreSQL

04 Dec 2024 03:51

do you have any recommendation to share about geojson
is possible to save it from a POST api rest and save it in a postgres db

sample something like this
https://gist.github.com/rogergcc/913dee43310d35cda9a3383e094bed1b

or a simple crud project about this

PostgreSQL

04 Dec 2024 03:26

my question is the pg_default tablespace is auto growth base on filesystem ?

PostgreSQL

04 Dec 2024 01:52

A while ago, I was editing this PostgreSQL wiki page:

https://wiki.postgresql.org/wiki/Count_estimate

The function count_estimate has the same issue; it accepts a query as text.

I spent a little time playing around but couldn't figure out how to pass an actual query to it, thus ended up adding a warning:

Do not pass an unsanitized query to this function, as it is subject to SQL injection.

PostgreSQL

03 Dec 2024 20:39

Oracle uses a PIVOT syntax and simple sub-query logic. No strings required.

PostgreSQL

03 Dec 2024 20:36

Yeah, that kind of dynamic column list was something SQL didn't have a design item, and it pops up once in a while.

PostgreSQL

03 Dec 2024 20:33

I think there's something weird about the way it works right now, and not in a good way. It is unclear to me what would be better, though.

PostgreSQL

03 Dec 2024 20:31

We both said the same thing at basically the same time. Jinx! LOL

PostgreSQL

03 Dec 2024 20:30

A tricky point about this is that in a crosstab, you don't know what the output column labels should be until you see what rows you get. But that means you don't know it until execution time. Which is a problem because the plan needs to contain that information. So maybe your CREATE CROSSTAB mycrosstab syntax would have to include a column list.

PostgreSQL

03 Dec 2024 20:20

I am curious... Exactly how do you pass a query? Would it be an open cursor? Or the name of the prepared statement to be executed?

PostgreSQL

03 Dec 2024 20:06

Here is a SIMPLE TRICK for the future. Aggregations can cause confusion.
So, I recommend you remove ANY Aggregation, and run the query for a limited set of data.
This will help you discover things like "statuses" or other tables that reference the same package more than once.

Keep in mind, if I join Cars, with Color_Options (by allowed_for_car_id, for example). I expect to see the SAME Car_ID for each color that is allowed. (Joining is like multiplying!) [Matrix Multiplying is one way to think of it]

But we live in a world where we think we are joining just "one" attribute (status), and if there are multiple, then we get multiple answers... (it happens all of the time).

PostgreSQL

03 Dec 2024 20:02

Whole day I was trying resolved it

PostgreSQL

04 Dec 2024 05:37

what we should do it require to monitoring filesystem regularly .

PostgreSQL

04 Dec 2024 05:36

ok thank. that part show on PGDATA by default

PostgreSQL

04 Dec 2024 05:34

Yes, you can usually find it:
SHOW data_directory;

PostgreSQL

04 Dec 2024 05:23

Effectively a Tablespace in PG is just a folder.
When that device fills up, you are cooked.

Unlike Oracle (where you can add additional "files" to a tablespace, that can be on any or many different drives)...
This is one of the limitations of PG.
When you start running out of space. You need to create a new tablespace somewhere else (on a new drive).
And start moving things to be stored there.
Or you need to use a type of storage that allows you to add to the filesystem.

HTH

PostgreSQL

04 Dec 2024 03:27

anyone experience on that pls help clearify me or provide support document.thank q

PostgreSQL

04 Dec 2024 03:26

Hello team,
I wonder one thing on postgres regrading with Posgres tablespace.

by default is will using pg_default for all database creation .

PostgreSQL

03 Dec 2024 20:39

SELECT *
FROM (
    SELECT product, year, sales
    FROM sales_data
)
PIVOT (
    SUM(sales)
    FOR year IN (2021 AS year_2021, 2022 AS year_2022)
);

PostgreSQL

03 Dec 2024 20:37

Oracle has something, I believe. But I don't know how they make it work.

PostgreSQL

03 Dec 2024 20:34

Said another way, introducing a new SQL construct because it doesn't feel right to you might actually be a great idea, but not if the new thing also doesn't feel right....

PostgreSQL

03 Dec 2024 20:32

I could live with that. It's also probably not worth introducing a whole new SQL construct just because I don't like the way passing queries as strings "feels." :-)

PostgreSQL

03 Dec 2024 20:30

To be more clear. You would use the (...) to define the columns returned.

That's interesting. But that would create a rather specific object that needs to be destroyed/recreated as the years would change in the future?

CREATE CROSSTAB mycrosstab(product TEXT, year_2021 int, year_2023 int) AS SELECT …

PostgreSQL

03 Dec 2024 20:21

I was thinking of something like view syntax:

CREATE CROSSTAB mycrosstab(...) AS SELECT …

PostgreSQL

03 Dec 2024 20:12

Today in idle wishes for PostgreSQL features: A version of tablefunc's crosstab that takes an actual query rather than a string. I dunno, the "pass a query as a string" thing always feels a bit unfinished to me.

PostgreSQL

03 Dec 2024 20:03

Yes, so it was WHAT I was suspecting... You fixed it by using an Aggregate type query in the WITH () section.

PostgreSQL

03 Dec 2024 20:01

It is so hard query for me