Re: Best opensource Postgresql monitoring tool
Thank you Bhaumik, I was also searching for and found one - pgwatch from cybertec. Has anybody got views or feedback on this one? Has anybody used it for monitoring purposes? Thank you all for your valuable feedback and suggestions. Regards On Tue, 10 Jan 2023, 16:47 Bhautik Chudasama, wrote: > pgAdmin is one of the most popular tool. You can see all necessary metrics > such as qps, tps, and interaction with table. If you've Prometheus setup > then you can scrap necessary metrics. > > On Tue, Jan 10, 2023, 7:59 PM Vikas Sharma wrote: > >> Hi there, >> >> Could you please advise on the best opensource monitoring tool for >> Postgresql? >> >> Thanks & Best Regards >> Vikas >> >
Re: PITR and instance without any activity
On 1/9/23 11:23, Torsten Förtsch wrote: On Mon, Jan 9, 2023 at 10:59 AM Adrien Nayrat mailto:adrien.nay...@anayrat.info>> wrote: * We can't perform PITR on a cluster without any activity since 13 * It seems creating restore point doesn't record a timestamp in wal. I have a cron job that runs this every 5 minutes: SELECT txid_current() WHERE (pg_last_committed_xact()).timestamp+'5min'::INTERVAL < now() Combine that with a suitable value for archive_timeout. Hello, I don't get how it could be useful ? When we perform PITR, we don't know if there was activity or not. -- Adrien NAYRAT
Re: Disallow execution of shell commands from psql
On Tue, Jan 10, 2023 at 07:01:24PM +0100, Wiwwo Staff wrote: > Hi! > Happy new (gregorian calendar) year! > > Somehow related to the proposal of having a `psql --idle` option, is there > a way to disallow the command `\!` (and anything of the likes in psql? > > Sure, I can set the SHELL env var at run-time, but I still want to have > postgres user to be a normal user, with its shell etc, which means it can > change this SHELL setting somewhere. As far as I know, it's not possible. Why is that a problem though? \! will run command as the user that ran psql. So it's not a security issue. What's the problem then? Best regards, depesz
Autovacuum Hung Due to Bufferpin
I have a very small table of 28 rows that is less than 10kB. One of the AV workers has been stuck autovac'ing it for over 20 hrs now with no progress in heap blocks scanned or vac'd. I terminated the AV worker and ran a manual vac which also ended up stuck waiting for a bufferpin. pg_locks shows no blockers while this is happening. This view shows a constant 13 sessions running SELECT statements on this table posting AccessShareLock. Of course, these is also the AV sessions with ShareUpdateExclusiveLock I have also disabled AV for the table, but AV does not appear to honor that due to 'to prevent wraparound'. Why is AV blocked by bufferpin given the fact that this table does not get an DML changes. It is purely read only. What can be done to resolve this? Thank you
Re: Autovacuum Hung Due to Bufferpin
Fred Habash writes: > pg_locks shows no blockers while this is happening. This view shows a > constant 13 sessions running SELECT statements on this table posting > AccessShareLock. Of course, these is also the AV sessions > with ShareUpdateExclusiveLock > ... > Why is AV blocked by bufferpin given the fact that this table does not get > an DML changes. It is purely read only. What can be done to resolve this? Apparently there has been some DML on it in the past, leaving dead rows that vacuum now needs to clean up --- but it needs a transient buffer lock for long enough to do that. If you have a constant stream of readers it will never be able to get that lock. You'll need to find a way to momentarily block those readers. regards, tom lane
Re: Autovacuum Hung Due to Bufferpin
According to pgstattuple, dead_tuple_count = 0. If this is the case, then
what other explanations do we have? I mean, how can I find out what blocker
session is holding the bufferpin to terminate it?
SELECT * FROM pgstattuple('.*'::regclass);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
---+-+---+---+--++++--
8192 | 28 | 2224 | 27.15 |0 |
0 | 0 | 5764 |70.36
(1 row)
On Wed, Jan 11, 2023 at 10:32 AM Tom Lane wrote:
> Fred Habash writes:
> > pg_locks shows no blockers while this is happening. This view shows a
> > constant 13 sessions running SELECT statements on this table posting
> > AccessShareLock. Of course, these is also the AV sessions
> > with ShareUpdateExclusiveLock
> > ...
> > Why is AV blocked by bufferpin given the fact that this table does not
> get
> > an DML changes. It is purely read only. What can be done to resolve this?
>
> Apparently there has been some DML on it in the past, leaving dead rows
> that vacuum now needs to clean up --- but it needs a transient buffer
> lock for long enough to do that. If you have a constant stream of readers
> it will never be able to get that lock. You'll need to find a way to
> momentarily block those readers.
>
> regards, tom lane
>
--
Thank you
Disabling triggers on tables dumped with pg_extension_config_dump()
Hello, I have an extension that contains a configuration table with a before insert trigger that I setup to dump with pg_extension_config_dump(). Because the table and trigger are setup during CREATE EXTENSION time emitted by the dump, and the dumped table is then COPY'd outside of that, the triggers get re-fired. This doesn't happen for non-extension tables, which don't run CREATE TRIGGER until after the COPY. Trying to find the best way to solve this problem in general for extension users so that they don't have to use --disable-triggers on the dump or restore as this seems like a rare edge case. Can anyone suggest a better way? I'm wondering, would it make sense to extend pg_extension_config_dump() with a disable_triggers argument, that defaults to false, and if set to true, pg_dump will emit DISABLE/ENABLE guards around the config table's COPY statement? Just a wild idea. Thanks! -Michel
Changing displayed time zone in RAISE NOTICE output?
How do I get clock_timestamp() to display the time in a different time zone? This is America/Chicago, but I'd like to display it in a different TZ. psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1)) postgres=# DO $$ postgres$# BEGIN postgres$# RAISE NOTICE '%', clock_timestamp(); postgres$# END$$; NOTICE: 2023-01-11 14:52:34.408225-06 DO -- Born in Arizona, moved to Babylonia.
Re: Changing displayed time zone in RAISE NOTICE output?
On 1/11/23 13:00, Ron wrote: How do I get clock_timestamp() to display the time in a different time zone? This is America/Chicago, but I'd like to display it in a different TZ. psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1)) postgres=# DO $$ postgres$# BEGIN postgres$# RAISE NOTICE '%', clock_timestamp(); postgres$# END$$; NOTICE: 2023-01-11 14:52:34.408225-06 DO test(5432)=# select clock_timestamp(); clock_timestamp 01/11/2023 13:05:54.646178 PST (1 row) test(5432)=# select clock_timestamp() at time zone 'UTC'; timezone 01/11/2023 21:06:04.742478 -- Adrian Klaver adrian.kla...@aklaver.com
Re: Autovacuum Hung Due to Bufferpin
Fred Habash writes: > According to pgstattuple, dead_tuple_count = 0. If this is the case, then > what other explanations do we have? Could be that old tuple(s) now require freezing. > I mean, how can I find out what blocker > session is holding the bufferpin to terminate it? I don't think there's any ready way to discover that from SQL level. regards, tom lane
Re: Changing displayed time zone in RAISE NOTICE output?
On 1/11/23 15:06, Adrian Klaver wrote: On 1/11/23 13:00, Ron wrote: How do I get clock_timestamp() to display the time in a different time zone? This is America/Chicago, but I'd like to display it in a different TZ. psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1)) postgres=# DO $$ postgres$# BEGIN postgres$# RAISE NOTICE '%', clock_timestamp(); postgres$# END$$; NOTICE: 2023-01-11 14:52:34.408225-06 DO test(5432)=# select clock_timestamp(); clock_timestamp 01/11/2023 13:05:54.646178 PST (1 row) test(5432)=# select clock_timestamp() at time zone 'UTC'; timezone 01/11/2023 21:06:04.742478 Hmm. I'd have sworn this didn't work when I tried it: postgres=# postgres=# DO $$ BEGIN RAISE NOTICE '%', clock_timestamp() at time zone 'UTC'; END$$; NOTICE: 2023-01-12 05:22:40.517299 But it does work, so all's well that ends well. -- Born in Arizona, moved to Babylonia.
EXPLAIN and FK references?
Pg 12.11 Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why. Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (index scan, seq scan, etc) during deletes (and inserts and updates)? -- Born in Arizona, moved to Babylonia.
Re: EXPLAIN and FK references?
Ron writes: > Deletes are slow in one table with many indices and FK references. That's > not surprising, but it's *VERY* slow, and I'm trying to figure out why. > Is there any EXPLAIN option which shows what "query plans" Pg is using when > checking FK references (index scan, seq scan, etc) during deletes (and > inserts and updates)? No, not directly, but you could look at EXPLAIN ANALYZE to see which of the RI triggers is eating the time. It's not going to be hard to figure out which one(s) are using indexed plans and which are not. regards, tom lane
Re: EXPLAIN and FK references?
> On Jan 11, 2023, at 22:07, Tom Lane wrote: > No, not directly, but you could look at EXPLAIN ANALYZE to see which > of the RI triggers is eating the time. It's not going to be hard to > figure out which one(s) are using indexed plans and which are not. Also, IIRC, the SELECTs generated to do foreign key checks do appear in pg_stat_statements, so that might provide a guide to ones that are consuming an unusually large amount of resources.
Re: EXPLAIN and FK references?
On Wed, Jan 11, 2023 at 10:13:11PM -0800, Christophe Pettus wrote: > > Also, IIRC, the SELECTs generated to do foreign key checks do appear in > pg_stat_statements, so that might provide a guide to ones that are consuming > an unusually large amount of resources. Yes, but you need to have pg_stat_statements.track = all configured for that, which isn't the default value (and requires superuser privileges to change).
Re: EXPLAIN and FK references?
On 1/12/23 00:07, Tom Lane wrote: Ron writes: Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why. Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (index scan, seq scan, etc) during deletes (and inserts and updates)? No, not directly, but you could look at EXPLAIN ANALYZE to see which of the RI triggers is eating the time. Good to know, but even deleting one day of data (90,000 rows using an index scan on the date field) takes forever. This is the DELETE explain plan, and the table definition *after* I deleted its FK constraints. (All July 2020 records were previously deleted from tables referencing strans.transmission.) sides=> explain (format yaml) DELETE FROM strans.transmission WHERE part_date BETWEEN '2020-07-01'::timestamp AND '2020-07-01'::timestamp + INTERVAL'1 DAY' - INTERVAL'1 SECOND'; QUERY PLAN - Plan: + Node Type: "ModifyTable" + Operation: "Delete" + Parallel Aware: false + Relation Name: "transmission" + Alias: "transmission" + Startup Cost: 0.56 + Total Cost: 297639.15 + Plan Rows: 94500 + Plan Width: 6 + Plans: + - Node Type: "Index Scan" + Parent Relationship: "Member" + Parallel Aware: false + Scan Direction: "Forward" + Index Name: "xif_sit_part_date" + Relation Name: "transmission" + Alias: "transmission" + Startup Cost: 0.56 + Total Cost: 297639.15 + Plan Rows: 94500 + Plan Width: 6 + Index Cond: "((part_date >= '2020-07-01 00:00:00'::timestamp without time zone) AND (part_date <= '2020-07-01 23:59:59'::timestamp without time zone))" (1 row) sides=> \d strans.transmission Table "strans.transmission" Column | Type | Collation | Nullable | Default ---+-+---+--+- transmission_id | numeric(38,0) | | not null | transmission_type | character varying(20) | | not null | endpoint_id | numeric(38,0) | | not null | destination_endpoint_id | numeric(38,0) | | | begin_transmission_dts | timestamp without time zone | | not null | processing_completed_dts | timestamp without time zone | | | failed_ind | character varying(1) | | | message_size | numeric(38,0) | | | record_count | numeric(38,0) | | | attachement_count | numeric(38,0) | | | attachment_size | numeric(38,0) | | | file_guid | character varying(36) | | | acknowledge_by_dts | timestamp without time zone | | | acknowledged_dts | timestamp without time zone | | | endpoint_ip | character varying(220) | | | duplicate_ind | numeric(38,0) | | not null | 0 parent_transmission_id | numeric(38,0) | | | message_code | character varying(4) | | | acknowledged_override_dts | timestamp without time zone | | | push_attempt | numeric(8,0) | | | bundle_parent_id | numeric(38,0) | | | partition_date | timestamp without time zone | | | part_date | timestamp without time zone | | not null | Indexes: "transmission_pkey" PRIMARY KEY, btree (transmission_id, part_date) "xif8transmission" UNIQUE, btree (transmission_id, endpoint_id, destination_endpoint_id, part_date) "apr25_begin_transmission_dts" btree (begin_transmission_dts) "apr25_bundle_parent_id" btree (bundle_parent_id) "apr25_parent_transmission_id" btree (parent_transmission_id) "xif1transmission" btree (endpoint_id) "xif4transmission" btree (destination_endpoint_id) "xif5transmission" btree (processing_completed_dts) "xif6transmission" btree (file_guid) "xif7transmission" btree (failed_ind) "xif9transm
Re: EXPLAIN and FK references?
Ron writes: > On 1/12/23 00:07, Tom Lane wrote: >> No, not directly, but you could look at EXPLAIN ANALYZE to see which >> of the RI triggers is eating the time. > Good to know, but even deleting one day of data (90,000 rows using an index > scan on the date field) takes forever. So delete a relatively small number of rows, and do it with EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any relevant data. regards, tom lane
Re: EXPLAIN and FK references?
On 1/12/23 01:11, Tom Lane wrote: Ron writes: On 1/12/23 00:07, Tom Lane wrote: No, not directly, but you could look at EXPLAIN ANALYZE to see which of the RI triggers is eating the time. Good to know, but even deleting one day of data (90,000 rows using an index scan on the date field) takes forever. So delete a relatively small number of rows, and do it with EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any relevant data. Doing that when trying to delete *one minute* of data is exactly what was needed. 99.999% of the time was spent on a checking another table which didn't have a supporting index. Adding that index makes things run as expected. -- Born in Arizona, moved to Babylonia.
Re: EXPLAIN and FK references?
On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote: > On 1/12/23 01:11, Tom Lane wrote: > > Ron writes: > > > On 1/12/23 00:07, Tom Lane wrote: > > > > No, not directly, but you could look at EXPLAIN ANALYZE to see which > > > > of the RI triggers is eating the time. > > > Good to know, but even deleting one day of data (90,000 rows using an > > > index > > > scan on the date field) takes forever. > > So delete a relatively small number of rows, and do it with > > EXPLAIN *ANALYZE*. Without ANALYZE, you aren't getting any > > relevant data. > > Doing that when trying to delete *one minute* of data is exactly what was > needed. 99.999% of the time was spent on a checking another table which > didn't have a supporting index. > > Adding that index makes things run as expected. Just in case, I just remembered that pgcluu [1] has a feature to automatically detect missing indexes on FK. You can see an example report at [2]. [1] https://github.com/darold/pgcluu [2] https://pgcluu.darold.net/example/dolibarr-missing-index.html
