How to know how much CPU, RAM is used by existing 1 database
Hi, There are many databases in our production patroni cluster and it seems it is overloaded, so we decide to migrate the busiest database to a new patroni cluster. pgwatch2 is implemented, how to know how much CPU, RAM is used by the database please? Then we can use it to prepare the new patroni cluster hardware. Thank you Best regards Dennis
How to remove the partition from table .
We would like to remove the partition from one of the tables , although that partition has some business data. Now business doesn't need that partition any more .. How could we remove it? although they need the data . -- Thanks & Regards Rakesh
Re: How to remove the partition from table .
> On 25/10/2022 12:47 CEST Rakesh Nashine wrote: > > We would like to remove the partition from one of the tables , although that > partition has some business data. Now business doesn't need that partition > any more .. How could we remove it? although they need the data . Detach the partition: https://www.postgresql.org/docs/15/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION -- Erik
Re: How to remove the partition from table .
On 10/25/22 05:47, Rakesh Nashine wrote: We would like to remove the partition from one of the tables , although that partition has some business data. Now business doesn't need that partition any more .. How could we remove it? although they need the data . That depends on which version of Postgresql you're using. -- Angular momentum makes the world go 'round.
please give me select sqls examples to distinct these!
typedef enum SetOperation
{
SETOP_NONE = 0,
SETOP_UNION,
SETOP_INTERSECT,
SETOP_EXCEPT
} SetOperation;
jack...@gmail.com
Re: PostgreSql Service different path
But I'm using a different path when I initdb /usr/pgsql-14/bin/initdb -D */home/dmartuser/pgsql/14/data *so "/var/lib/pgsql/14/data/" is really empty. Is there a way to point the startup script to the new path */home/dmartuser/pgsql/14/data* ? Thanks, Chris Albert Navarroza Information Technology Officer I CTCO - ITDS - RDMD On Mon, Oct 24, 2022 at 6:57 PM Jeffrey Walton wrote: > On Mon, Oct 24, 2022 at 6:38 AM chris navarroza > wrote: > > > > I install postgresql14.5 with the following commands > > > > sudo yum install postgresql14-server postgresql14-contrib > > > > > > sudo su postgres > > > > cd /tmp > > > > /usr/pgsql-14/bin/initdb -D /home/dmartuser/pgsql/14/data > > > > and update the postgresql.conf to the new path, now when I start the > service, it has an error and when I check it says > > > > -- Unit postgresql-14.service has begun starting up. > > Oct 24 15:16:45 datamartds postgresql-14-check-db-dir[132338]: > "/var/lib/pgsql/14/data/" is missing or empty. > > Oct 24 15:16:45 datamartds postgresql-14-check-db-dir[132338]: Use > "/usr/pgsql-14/bin/postgresql-14-setup initdb" to initialize the database > cluster. > > Oct 24 15:16:45 datamartds postgresql-14-check-db-dir[132338]: See > /usr/share/doc/postgresql14/README.rpm-dist for more information. > > Oct 24 15:16:45 datamartds systemd[1]: postgresql-14.service: Control > process exited, code=exited status=1 > > Oct 24 15:16:45 datamartds systemd[1]: postgresql-14.service: Failed > with result 'exit-code'. > > > > How can I point the service to read the new path ( > /home/dmartuser/pgsql/14/data )? > > The startup script that is calling systemd should perform a `mkdir -p > /var/lib/pgsql/14/data` before calling the PostgreSQL binary. > > Jeff >
Re: please give me select sqls examples to distinct these!
> On Oct 25, 2022, at 7:55 AM, jack...@gmail.com wrote:
>
>
>
> typedef enum SetOperation
> {
> SETOP_NONE = 0,
> SETOP_UNION,
> SETOP_INTERSECT,
> SETOP_EXCEPT
> } SetOperation;
> jack...@gmail.com
Please use just text.
What ‘dialect’ are using? In Postgres
0: select * from table
1: select * from table union select * from table is same shape
2: select * from table join table b on Id = idb
3: select * from table except select * from tableb
Re: Re: please give me select sqls examples to distinct these!
> On Oct 25, 2022, at 7:55 AM, jack...@gmail.com wrote:
>
>
>
> typedef enum SetOperation
> {
> SETOP_NONE = 0,
> SETOP_UNION,
> SETOP_INTERSECT,
> SETOP_EXCEPT
> } SetOperation;
> jack...@gmail.com
Please use just text.
What ‘dialect’ are using? In Postgres
0: select * from table
1: select * from table union select * from table is same shape
2: select * from table join table b on Id = idb
3: select * from table except select * from tableb
can you give me a sql example to explain this?
/* * In a "leaf" node representing a VALUES list, the above fields are all * null, and instead this field is set. Note that the elements of the * sublists are just expressions, without ResTarget decoration. Also note * that a list element can be DEFAULT (represented as a SetToDefault * node), regardless of the context of the VALUES list. It's up to parse * analysis to reject that where not valid. */ List*valuesLists; /* untransformed list of expression lists */ I need to understand what this is used for? jack...@gmail.com
Re: can you give me a sql example to explain this?
On Tue, Oct 25, 2022 at 7:54 AM jack...@gmail.com wrote:
> /*
> * In a "leaf" node representing a VALUES list, the above fields are all
> * null, and instead this field is set. Note that the elements of the
> * sublists are just expressions, without ResTarget decoration. Also note
> * that a list element can be DEFAULT (represented as a SetToDefault
> * node), regardless of the context of the VALUES list. It's up to parse
> * analysis to reject that where not valid.
> */
> List*valuesLists; /* untransformed list of expression lists */
>
> I need to understand what this is used for?
>
That's a fairly broad question...does this help?
In the SQL command:
VALUES ('one', 'two', 1+2, DEFAULT)
The valuesLists List will effectively contain three elements, {'one'},
{'two'}, {1+2}, and {DEFAULT}.
Though if it contains DEFAULT and the VALUES is not part of an INSERT an
error should eventually occur during parse analysis since a plain VALUES
command has no context from which to retrieve a default.
David J.
Re: please give me select sqls examples to distinct these!
On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote:
>
>
> On Oct 25, 2022, at 7:55 AM, jack...@gmail.com wrote:
>
>
>
> typedef enum SetOperation
> {
> SETOP_NONE = 0,
> SETOP_UNION,
> SETOP_INTERSECT,
> SETOP_EXCEPT
> } SetOperation;
> --
> jack...@gmail.com
>
>
> Please use just text.
> What ‘dialect’ are using? In Postgres
> 0: select * from table
> 1: select * from table union select * from table is same shape
> 2: select * from table join table b on Id = idb
> 3: select * from table except select * from tableb
>
>
#2 is probably conceptually correct but in this context should be written
as:
SELECT *
FROM table
INTERSECT
SELECT *
FROM table
Distinct is a default modifier, one can specify ALL if that isn't desired.
This is trivially answered by the documentation as well:
https://www.postgresql.org/docs/current/queries-union.html
David J.
Re: please give me select sqls examples to distinct these!
On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote: > >> >> >> 2: select * from table join table b on Id = idb >> >> >> > #2 is probably conceptually correct but in this context should be written > as: > > Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong. David J.
Re: please give me select sqls examples to distinct these!
On 10/25/22 09:24, David G. Johnston wrote: On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston wrote: On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote: 2: select * from table join table b on Id = idb #2 is probably conceptually correct but in this context should be written as: Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong. David J. OK. TBH I thought this was the JOOQ list! Didn't notice the typedef. So this is all about exact match "tables". I suppose my #2 could be framed as select a* from table as a join table as b on a. = b. to be a functional equivalent of INTERSECT?
Re: please give me select sqls examples to distinct these!
On Tue, Oct 25, 2022 at 8:56 AM Rob Sargent wrote: > On 10/25/22 09:24, David G. Johnston wrote: > > On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent >> wrote: >> >>> >>> >>> 2: select * from table join table b on Id = idb >>> >>> >>> >> #2 is probably conceptually correct but in this context should be written >> as: >> >> > Actually, a join is NOT conceptually correct here - the output columns for > "SELECT *" will be wrong. > > David J. > > OK. TBH I thought this was the JOOQ list! Didn't notice the typedef. So > this is all about exact match "tables". > > I suppose my #2 could be framed as > select a* from table as a join table as b on a. = > b. > to be a functional equivalent of INTERSECT? > SELECT DISTINCT a.* David J.
Re: please give me select sqls examples to distinct these!
On 10/25/22 09:58, David G. Johnston wrote: On Tue, Oct 25, 2022 at 8:56 AM Rob Sargent wrote: On 10/25/22 09:24, David G. Johnston wrote: On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston wrote: On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote: 2: select * from table join table b on Id = idb #2 is probably conceptually correct but in this context should be written as: Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong. David J. OK. TBH I thought this was the JOOQ list! Didn't notice the typedef. So this is all about exact match "tables". I suppose my #2 could be framed as select a* from table as a join table as b on a. = b. to be a functional equivalent of INTERSECT? SELECT DISTINCT a.* David J. Yeah, sorry. Meant to distinguish to two "table" defs in some way.
Re: synchronous streaming replication
pon., 24 paź 2022, 06:08 użytkownik Laurenz Albe napisał: > On Sat, 2022-10-22 at 17:28 +0200, Joseph Kennedy wrote: > > Hi,I have a question. Its stays at the intersection of software > engineering and PostgreSQL. > > > > I have configured streaming synchronous replication and whit setting > "synchronous_commit=remote_apply" > > to make sure that the slave will always respond the same as the MASTER > (this is a developers' > > requirement that the MASTER always responds the same as SLAVE). I set > "hot_standby_feedback=on" > > and "max_standby_streaming_delay=-1", > > max_standby_streaming_delay set to -1 to make MASTER wait indefinitely > before SELECT conflicts on the SLAVE will end. > > > > Here's where the problem arises, because not long after the replication > has been started some serious > > delays occur in the form of "replay_lag" - which rather indicates the > appearance of conflicts; > > the replication stops working properly. > > > > From the server logs it appears that UPDATE (select for update) has > occurred on the MASTER, and SELECT > > queries are in progress on SLAVE causing replication conflicts, with > setting "max_standby_streaming_delay=-1" > > they never ends and there are huge lags. > > > > From the findings with the developers it emerged that they do not want > me to set max_standby_streaming_delay > > to a value after which the queries conflicted with replication will be > canceled. > > * So I'm wondering if, in this configuration, it can work properly at > all without setting, for example, > >"max_standby_streaming_delay=30" ? > > * On the other hand I wonder if the application should not be developed > in such a way to support replication > >of PostgreSQL configured as a streaming synchronous replication > cluster with "synchronous_commit=remote_apply" ? > > * Or perhaps "synchronous streaming replication" is a bad choice, maybe > logical replication would be better ? > > * What are the best practices? > > * Perhaps you just need to force/teach applications to work with > synchronous replication in such a way that > >when the SELECT causes conflicts with replication such queries are > canceled and the application should resend/repeat query ? > > * I also think that after setting, for example, > "max_standby_streaming_delay=30" queries (addressed) to the > >database should be very well optimized, so that too long queries are > not canceled too frequently? > > * Do you know any books focused on applications adapted to work in > postgresql synchronous streaming > >replication environment i.e. High Availability? > > This can never work properly. If you have synchronous replication with > "synchronous_commit = remote_apply", > COMMIT on the primary will wait until the information has been replayed on > the standby. If you set > "max_standby_streaming_delay = -1", replication can be delayed > indefinitely long in the event of a replication > conflict, so COMMIT can take arbitrarily long. > > You can reduce replication conflicts (by setting "hot_standby_feedback = > on" and by altering all tables to > set "vacuum_truncate = off"), but you will never get rid of them > completely. > > You will either have to accept stale ready on the standby (by setting > "synchronous_commit" to something lower) > or you have to accept canceled queries on the standby (by lowering > "max_standby_streaming_delay"). > > Yours, > Laurenz Albe Bloated tables are a serious matter then, after setting vacuum_truncate=off auto vacuum will be turned off , then how to reduce size of tables ? Use pg_repack to reduce locks ?
Re: How to know how much CPU, RAM is used by existing 1 database
Hi Guys, Who can help me with this please? I researched but still no result yet, thank you On Tue, 25 Oct 2022 at 16:30, Yi Sun wrote: > Hi, > > There are many databases in our production patroni cluster and it seems it > is overloaded, so we decide to migrate the busiest database to a new > patroni cluster. > > pgwatch2 is implemented, how to know how much CPU, RAM is used by the > database please? Then we can use it to prepare the new patroni cluster > hardware. Thank you > > Best regards > Dennis >
