PostgreSQL

04 Dec 2024 14:02

Hi do anyone know about role based access control

PostgreSQL

04 Dec 2024 08:51

Pgbackrest has encryption built in. If you use a repo host on top, you can divide your eggs into different baskets, which is a Good Thing (TM)

PostgreSQL

04 Dec 2024 08:21

Can we encrypt backup file for security purpose

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

04 Dec 2024 13:51

Can you tell me to create tsdb infra in HA

PostgreSQL

04 Dec 2024 08:48

Use whatever method you prefer. Postgresql does not do it for you

PostgreSQL

04 Dec 2024 07:25

Hello and welcome to this chat. Here we discuss PostgreSQL and provide best-effort voluntary support on Postgresql issues.
If you have any question, try asking kindly, including log files, error descriptions and perhaps what you're trying to do.

- 🚫 photos and screenshots
- 🚫 spam and offtopic
- ✅ questions with log files and clear explanation of what's gone wrong

⬇️ Press the button down here ⬇️

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 …