rapidloop / pgmetrics Goto Github PK
View Code? Open in Web Editor NEWCollect and display information and stats from a running PostgreSQL server
Home Page: https://pgmetrics.io
License: Apache License 2.0
Collect and display information and stats from a running PostgreSQL server
Home Page: https://pgmetrics.io
License: Apache License 2.0
Hey,
I've just started playing around with pgmetrics and noticed the following:
in postgresql.conf I have maintenance_work_mem = 128MB
which seems to be internally set as value "131072", which means the base unit is KB and not B.
In the pgmetrics output (in the "Vacuum Progress" block) however this seems to be interpreted as B:
maintenance_work_mem | 131072 (128 KiB)
I am not sure if the memory values are internally always in KB (which would make the fix rather easy) or if depending on configuration settings / environment variable / postgres version this might require a lookup for the base unit before pretty-printing it.
When popular enough (>50 stars, I think), would be great to have this as a homebrew package so one could just use brew install pgmetrics
. I tried to create this package, but it requires building from source and I didn't have the time to figure that out for a go
project right now. Also, they want it to be around for more than 30 days (a semi-valid concern).
I created chocolatey package for Windows:
https://chocolatey.org/packages/pgmetrics
Src: https://github.com/majkinetor/au-packages/tree/master/pgmetrics
It will be kept up to date via bot within single day from the Github release.
Thanks for the tool.
dear team,
when I read doc about pgmetrics, I find pgmetrics can collect system info, like cpu/memory load and disk usage,
but I can't find any tip in the help list, or command demo in the doc,
my env:
$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
$ pgmetrics -V
pgmetrics 1.15.0
could you add more info on it?
I have a database (PostgreSQL 10.3) with password of dbuser encrypted with scram-sha-256.
The entry in pg_hba.conf is "host [database] [user] [ip] md5"
When running pgmetrics for the database, it got an error
pgmetrics: pq: unknown authentication response: 10
This indicates scram authentication is not supported.
I have to change the password encryption of dbuser with md5 or disable authentication method in pg_hba.conf by adding entry "host trust" to overcome the error.
In production environment, neither of the above workaround is allowed.
Can you please check and resolve the above issue?
Citus 11.x introduced a couple of breaking changes to the tables that pgmetrics queries, which causes errors when pgmetrics is run against Citus clusters on Citus 11. See release notes for details: https://www.citusdata.com/updates/v11-0/. The specific breaking changes are:
citus_worker_stat_activity
has been dropped in favor of citus_stat_activity
and citus_dist_stat_activity
. citusdata/citus#5760. This causes this error when querying:
pgmetrics: warning: citus_worker_stat_activity query failed: pq: relation "citus_worker_stat_activity" does not exist
citus_dist_stat_activity
does not have query_hostname
and is a view over citus_stat_activity
: citusdata/citus#5760 which causes an issue when trying to get query_hostname
:
pgmetrics: warning: citus_dist_stat_activity query failed: pq: column "query_hostname" does not exist
citus_lock_waits
no longer has column waiting_pid
but waiting_gpid
(see: citusdata/citus#5766) which causes this issue when trying to query the table:
pgmetrics: warning: citus_lock_waits query failed: pq: column "waiting_pid" does not exist
Would it be possible to get these models updated for the most recent major version of Citus?
pgmetrics -h localhost -p 5432 -U postgres --no-password mydb
pgmetrics: pg_class query failed: sql: Scan error on column index 2, name "pg_get_expr": unsupported Scan, storing driver.Value type <nil> into type *string
Using CentOS 7 64 bit and PostgreSQL 11.1.
Installation method:
wget https://github.com/rapidloop/pgmetrics/releases/download/v1.5.1/pgmetrics_1.5.1_linux_amd64.tar.gz
tar -zxvf pgmetrics_1.5.1_linux_amd64.tar.gz
cd pgmetrics_1.5.1_linux_amd64
cp ./pgmetrics /usr/bin/pgmetrics
pgmetrics -V
-->
pgmetrics 1.5.1
pgmetrics -h 127.0.0.1 -p 5441 -U postgres --no-password example
pgmetrics: pq: unexpected error: "setting PGSYSCONFDIR not supported"
I have the right entries in .pgpass so this works:
psql -h 127.0.0.1 -p 5441 -U postgres example
psql (10.2 (Ubuntu 10.2-1.pgdg14.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
I executed the below command and got the error:
pgmetrics -t 180 -h <> -p 5432 -U <> --az-resource <> -o <> <>
Error:
pg_stat(io)_user_tables query failed: ERROR: canceling statement due to lock timeout (SQLSTATE 55P03)
After upgrading to AWS RDS Aurora Postgres 11.11, I started getting this error:
pg_stat_archiver query failed: sql: Scan error on column index 6, name "date_part": converting NULL to int64 is unsupported
I was able to fix it with this patch on v1.10.5:
diff --git a/collector/collect.go b/collector/collect.go
index fe4e4f4..262ee19 100644
--- a/collector/collect.go
+++ b/collector/collect.go
@@ -611,7 +611,7 @@ func (c *collector) getWALArchiver() {
failed_count,
COALESCE(last_failed_wal, ''),
COALESCE(EXTRACT(EPOCH FROM last_failed_time)::bigint, 0),
- EXTRACT(EPOCH FROM stats_reset)::bigint
+ COALESCE(EXTRACT(EPOCH FROM stats_reset)::bigint, 0)
FROM pg_stat_archiver`
a := &c.result.WALArchiving
if err := c.db.QueryRowContext(ctx, q).Scan(&a.ArchivedCount, &a.LastArchivedWAL,
If anyone else needs to quickly get this fixed before a new release is available that fixes it, here's a Dockerfile snippet to help out building a patched version of 1.10.5.
FROM golang:1.15-buster as pgmetrics_builder
RUN git clone -b v1.10.5 https://github.com/rapidloop/pgmetrics.git
COPY pgmetrics-fix-wal-archiver-stats.patch ./
RUN cd pgmetrics \
&& git apply ../pgmetrics-fix-wal-archiver-stats.patch \
&& CGO_ENABLED=0 go build -ldflags '-s -w -X main.version=1.10.5-patched -extldflags "-static"' -trimpath ./cmd/pgmetrics
FROM your_image
COPY --from=pgmetrics_builder /go/pgmetrics/pgmetrics ./
is it possible to enforce iso date time formats everywhere in the report? it lines up much nicer in a report table and it's more international...
I have postgresql instance I am running it as a pod in kubernetes cluster. I want to monitor the 1). Shared Memory 2). Process Memory or Backend Memory using pgDash tool. Can you please check on this and add the required features
./pgmetrics --no-password
pgmetrics: pg_roles/pg_auth_members query failed: pq: bigint en dehors des limites
psql -V
psql (PostgreSQL) 10.3 (Debian 10.3-1.pgdg80+1)
is there a way to get the detailed, per database report for the whole cluster without the need to specify a list of dbnames?
I'm not a go magician. It would be nice to know how to build it myself.
Thanks! :D
Hi,
When using the collector package with other code that uses the lib/pq standard package, you get an error:
panic: sql: Register called twice for driver postgres
This is due to pgmetrics using the fork of lib/pq, rapidloop/pq.
Is it possible to change this to use the standard library so that it will work with other code?
Thanks
Congratulations on 1.0!
I saw your release, and I figured I'd take a look at what I have in PgZabbix that I'd really miss, in order to replace my collection with the one you're doing here. ( If I can maintain less code, that's good for me)
Now, before I sit down and butcher something in Go, I figured I'd chat a bit about it to see:
Rationale: We're monitoring on insertion /update rates in the DB level to track anomalies in production environments. Our loads are usually predictable with some time-based variance, causing this to be a very reliable predictor of anything from a peer net outage to application services not working.
Rationale: A growing amount of queries over a certain threshold of speed is usually the sign of something being wrong.
We're separating this into select like queries, all queries, and dml queries (not "insert/update/delete")
Actually, we use two methods here, both tracking connections per application, and connections used for replication.
We're checking the medium term number of connections used here to track backups (should run), and state of applications.
If there's interest here, I'll look at moving over my own code to instead integrate directly with pgmetrics, it seems like a nice way to learn Go.
Hi!
I´m trying to run pgmetrics from linux to get the metrics for RDS Aurora Cluster and I´m getting this error:
pgmetrics: warning: failed to collect from AWS RDS: failed to get CloudWatch metric data: ValidationError: The value idEBSIOBalance% for parameter MetricDataQueries.member.30.Id is not matching the expected pattern ^[a-z][a-zA-Z0-9_]*$. The value idEBSByteBalance% for parameter MetricDataQueries.member.31.Id is not matching the expected pattern ^[a-z][a-zA-Z0-9_]*$. status code: 400, request id: 72bfee0a-a37f-4c4b-aa88-a5a304b4b660
The command that we are running is:
PGPASSWORD=XXXXXXXX ./pgmetrics -h writer-instance-endpoint -U SuperUser --aws-rds-dbid=clstr-instance-identifier -w -fjson database
The database output is ok, just fail with the RDS stats.
Thanks very much!
I am doing a lot of testing with PG 11 beta4, but pgmetrics does not work for that yet. Any idea when I can start beta testing pgmetrics for PG 11?
Hey there, love the details with these metrics!
I wrote a NodeJS script to flatten / convert json output into a graphite line protocol ready format here:
https://gist.github.com/JohnnyMarnell/0ab7e95e2994b7960fe7a32667a529df
If I were to start porting it to Go (n00b), would you consider a pull request?
Something like pgmetrics -f graphite
?
Got this in the latest postgres
pgmetrics: pg_locks query failed: sql: Scan error on column index 4, name "pid": converting driver.Value type ("") to a int: invalid syntax
As noticed in 17be4a7 getting index definition waits for an exclusive lock. This causes a lot of scary ERROR
entries in PostgreSQL logs like:
2021-09-21 05:40:54.826 EDT [1880711-3] app=pgmetrics ERROR: canceling statement due to lock timeout
2021-09-21 05:40:54.826 EDT [1880711-4] app=pgmetrics STATEMENT: SELECT indexrelid, pg_get_indexdef(indexrelid) FROM pg_stat_user_indexes
It'd be useful to have extra omit option that would disable just the retrieval of index definitions, while keeping index stats.
erdb, syslog, pki, dd, ext_ip4r, archiveopteryx, public
(1 row)
Time: 0,617 ms
psql (10.3, server 10.5)
SSL connection (protocol: TLSv1.2, cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
operations=# \q
ajr$ PGSSLMODE=require pgmetrics -h db2 -p 2222 -w
pgmetrics: remote error: tls: handshake failure
ajr$
In pg_hba.conf the related line is
hostssl ... cert clientcert=1
./pgmetrics_1.4.0_linux_amd64/pgmetrics --host=127.0.0.1 --port=5432
pgmetrics: pg_stat_progress_vacuum query failed: sql: Scan error on column index 1: converting driver.Value type <nil> ("<nil>") to a int: invalid syntax
postgres=> SELECT * from pg_stat_progress_vacuum;
pid | datid | datname | relid | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
-------+-------+----------+-------+-------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
14048 | 12996 | postgres | | | | | | | |
14058 | 12996 | postgres | | | | | | | |
14069 | 12996 | postgres | | | | | | | |
stack
In 1.10.0, displaying max_wal_size
in text reports for PG versions >= 10 has been fixed (513dcc0). However, this has not been done for min_wal_size
, which leads to outputs like:
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 0 |
| wal_compression | off |
| max_wal_size | 10240 (10 GiB) |
| min_wal_size | 1024 (16 GiB) |
Hello,
can you provide more information about how to build the project from scratch ? I can't build it with methods on site:
$ go get github.com/rapidloop/pgmetrics/...
../go/src/github.com/rapidloop/pgmetrics/cmd/pgmetrics/report.go:773:32: error: reference to undefined field or method ‘Truncate’
return time.Duration(1e6 * ms).Truncate(time.Millisecond).String()
Then I download the source code and try again:
GOROOT=/root/pgmetrics-1.2.0/vendor go build ./cmd/pgmetrics
cmd/pgmetrics/report.go:26:2: cannot find package "github.com/dustin/go-humanize" in any of:
/root/pgmetrics-1.2.0/vendor/src/github.com/dustin/go-humanize (from $GOROOT)
/root/go/src/github.com/dustin/go-humanize (from $GOPATH)
cmd/pgmetrics/main.go:30:2: cannot find package "github.com/howeyc/gopass" in any of:
/root/pgmetrics-1.2.0/vendor/src/github.com/howeyc/gopass (from $GOROOT)
/root/go/src/github.com/howeyc/gopass (from $GOPATH)
cmd/pgmetrics/collect.go:32:2: cannot find package "github.com/lib/pq" in any of:
/root/pgmetrics-1.2.0/vendor/src/github.com/lib/pq (from $GOROOT)
/root/go/src/github.com/lib/pq (from $GOPATH)
cmd/pgmetrics/main.go:31:2: cannot find package "github.com/pborman/getopt" in any of:
/root/pgmetrics-1.2.0/vendor/src/github.com/pborman/getopt (from $GOROOT)
/root/go/src/github.com/pborman/getopt (from $GOPATH)
cmd/pgmetrics/collect.go:33:2: cannot find package "github.com/rapidloop/pgmetrics" in any of:
/root/pgmetrics-1.2.0/vendor/src/github.com/rapidloop/pgmetrics (from $GOROOT)
/root/go/src/github.com/rapidloop/pgmetrics (from $GOPATH)
cmd/pgmetrics/main.go:33:2: cannot find package "golang.org/x/crypto/ssh/terminal" in any of:
/root/pgmetrics-1.2.0/vendor/src/golang.org/x/crypto/ssh/terminal (from $GOROOT)
/root/go/src/golang.org/x/crypto/ssh/terminal (from $GOPATH)
Thanks
Hi there!
Love the project, it's super useful and provides very interesting insights.
I note that you have documented that you can collect from the log files and display detailed information from these logs, but it's not super clear in which situations it applies.
Specifically, there are two separate use cases where I see issues (or confusion on my part) in getting log parsing to work:
--log-file
pathsCan you clarify if it's possible to enable the log parsing (and seemingly associated info in the output, like Slow Queries
) when using pgmetrics with either of these configurations?
If it's not possible to do so in either of these situations, can the specific configurations/environment necessary for this be documented?
Thanks very much!
PGBOUNCER with 1.21 introduced max_prepared_statements and I believe along that came a prepared_statements
inside the SHOW SERVERS and SHOW CLIENTS
I've caused psql14 to crash multiple times trying to run pgmetrics with a hash partitioned table with 1024 partitions.
Getting following error:
pgmetrics: pgmetrics scan failed: sql: Scan error on column index 3, name "left": converting NULL to string is unsupported
from time to time. Running select pg_stat_statements_reset
helps, but it does come back
pgmetrics version: 1.8.1
postgres version: 12.1 (Ubuntu 12.1-1.pgdg18.04+1)
I'm trying to run pgmetrics against our Heroku postgres database, but I'm receiving the following error message:
pgmetrics: pq: no pg_hba.conf entry for host "xxx.xxx.xx.xx", user "xxxxxxxx", database "xxxxxxxx", SSL off
The the host ip in the error message is not the public ip of the database instance, but looks like the private ip.
Connecting to the database using psql from the same terminal with the same credentials works as expected.
pgmetrics runs succesfully against my localhost database.
I'm using the darwin build of v1.6.3 from https://github.com/rapidloop/pgmetrics/releases
I also tried running the docker image version but there I get the same error message.
Is there a setting I need to provide on the command line for it to work with a Heroku postgres database?
I am trying to use pgmetrics
on a big (10TB+), busy (1GB/s RW) database. It takes around 5 minutes for pgmetrics to run. I traced the problem to the "bloat query" spinning in CPU, doing no I/O.
I have traced the problem to the bloated pg_class
(the irony: pgmetrics
does not collect bloat on pg_catalog
):
vacuum (full, analyze, verbose) pg_class;
INFO: vacuuming "pg_catalog.pg_class"
INFO: "pg_class": found 1 removable, 7430805 nonremovable row versions in 158870 pages
DETAIL: 7429943 dead row versions cannot be removed yet.
CPU 1.36s/6.40u sec elapsed 9.85 sec.
INFO: analyzing "pg_catalog.pg_class"
INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live rows and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
VACUUM
pg_class
has so many dead rows because the workload is temp-table heavy (creating/destroying 1M+ temporary tables per day) and has long running analytics queries running for 24h+.
PG query planner assumes that index scan on pg_class
will be very quick and plans Nested loop with Index scan. However, the index scan has 7M dead tuples to filter out and the query takes more than 200 seconds.
If I create a temp table from pg_class
to contain only the live tuples:
CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON pg_class_alive(relname, relnamespace);
CREATE INDEX pg_class_tblspc_relfilenode_index ON pg_class_alive(reltablespace, relfilenode);
ANALYZE pg_class_alive;
and run the bloat query on pg_class_alive
instead of pg_class
:
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(CEIL((cc.reltuples*((datahdr+8-
(CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8 END))+nullhdr2+4))/(8192-20::float)),0) AS otta
FROM
pg_class_alive cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
LEFT JOIN
(
SELECT
foo.nspname,foo.relname,
(datawidth+32)::numeric AS datahdr,
(maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE nullhdr%8 END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
23+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname
WHERE att.attnum > 0 AND tbl.relkind='r'
GROUP BY 1,2
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
it runs in 10s, 20x faster
WDYT about adding such a hack (maybe under an option?) to pgmetrics
?
If PostgreSQL is listening on loopback interface, but not necessarily on 127.0.0.1
then
pgmetrics/collector/collect.go
Line 625 in 1d87609
/etc/hosts
with
127.0.0.1 localhost
127.0.1.1 pg
and you configure PG with
listen_addresses = 'pg'
then PG will listen on 127.0.1.1
but the TCP connection to 127.0.1.1
will appear from 127.0.0.1
, i.e. inet_client_addr()
will be 127.0.0.1
and inet_server_addr()
will be 127.0.1.1
.
Maybe a special case for 127.0.0.0/8
would be useful here?
Version used: pgmetrics_1.9.2_linux_amd64
Command line parameters: pgmetrics -h HOST -U USER -w DB
I'm executing pgmetrics against two separate environments that run the same database with the same schema. I wanted to see a side-by-side comparison of the results. The bulk of the pgmetrics output is the detailed information listed about the tables in the database being inspected but the sort order for the tables varies wildly between the two environments.
What determines the sort order of the tables when pgmetrics exports that data? Can that sort order be controlled via any parameter so I can get both environments to list table details in the same order? I was not able to find any parameters that might control that.
Thank you.
in its current shape, pgmetrics is a simple and useful command line tool. but we are not able to use it in our environment today because we already have some monitoring code running and we don't want to add another binary which is only responsible for postgresql monitoring.
ideal solution would be use pgmetrics as a library.
the code is pretty clean and as far as i can understand, if we export collect
, any other package should be able to use pgmetrics as a library.
In Postgres 13, pgmetrics displays no value for wal_keep_segments
because it has been renamed to wal_keep_size
.
First of all, thanks for your new tool! Here are a few suggestions:
Make table output optional or filterable through a new command parameter. Thousands of tables within a database can cause a lot of unwanted output, perhaps something like --no-tables.
Add a schema option to limit table output, --schema schema1, schema2
Put system information output before table output. That way top level stuff is always at top.
Keep up the good work!
Hello! Thanks for pgmetrics.
Please add connect by Keyword/Value Connection Strings
In the keyword/value format, each parameter setting is in the form keyword = value, with space(s) between settings. Spaces around a setting's equal sign are optional. To write an empty value, or a value containing spaces, surround it with single quotes, for example keyword = 'a value'. Single quotes and backslashes within a value must be escaped with a backslash, i.e., ' and \.
Example:
host=localhost port=5432 dbname=mydb connect_timeout=10
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
For example my cloud:
pgmetrics "host=host \
port=6432 \
sslmode=verify-full \
dbname=dbname \
user=user \
target_session_attrs=read-write"
Collect and report notification queue usage:
pg_notification_queue_usage()
- fraction of the asynchronous notification queue currently occupied. Documented at https://www.postgresql.org/docs/current/static/functions-info.htmlIf the pg_stat_statements extension is installed, collect information from the pg_stat_statements view also.
We are executing pgmetrics for AWS aurora instances with Postgres 14/15 versions but immediately command failed like below.
AWS Aurora PG 14.9 :
[root@localhost pgmetrics_1.13.1_linux_amd64]# ./pgmetrics -h *********.us-east-1.rds.amazonaws.com -U postgres --aws-rds-dbid=bk-pg-poc -f json -o /u01/Aws_Aurora_PG_14.9_Json_OP.txt postgres
Password:
pgmetrics: pg_stat_wal query failed: pq: Function pg_stat_get_wal() is currently not supported for Aurora
[root@localhost pgmetrics_1.13.1_linux_amd64]#
writer Instance:
[root@localhost pgmetrics_1.13.1_linux_amd64]# ./pgmetrics -h *********.us-east-1.rds.amazonaws.com -U postgres --aws-rds-dbid=bk-pg-poc-instance-1 -f json postgres
Password:
pgmetrics: pg_stat_wal query failed: pq: Function pg_stat_get_wal() is currently not supported for Aurora
[root@localhost pgmetrics_1.13.1_linux_amd64]#
AWS Aurora PG 15.4
[root@localhost pgmetrics_1.13.1_linux_amd64]# ./pgmetrics -h ********.us-east-1.rds.amazonaws.com -U postgres --aws-rds-dbid=bk-pg-poc -f json -o /u01/Aws_Aurora_PG_15.4_Json_OP.txt postgres
Password:
pgmetrics: pg_stat_wal query failed: pq: Function pg_stat_get_wal() is currently not supported for Aurora
[root@localhost pgmetrics_1.13.1_linux_amd64]#
Trying to authenticate using SSPI on Windows - see
https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows
I can open a shell window using the Windows domain account and run PostgreSQL commands (Windows domain account is PostgreSQL superuser).
Can I do the same with pgmetrics? I try:
pgmetrics.exe -h localhost -U username -w databasename
Output:
pgmetrics: pq: unknown authentication response: 9
Or is this not supported?
it report following with PG16:
pgmetrics: pg_stat_activity query failed: pq: invalid input syntax for type xid: ""
it works well on PG15.
-bash-4.2$ /instance1/dl/pgm/pgmetrics_1.1.0_linux_amd64/pgmetrics --no-password
pgmetrics: pq: unexpected error: "setting PGLOCALEDIR not supported"
i can get past this by unsetting it but why is this raised?
thanks
erdb, syslog, pki, dd, ext_ip4r, archiveopteryx, public
(1 row)
Time: 7,388 ms
psql (10.5, server 10.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \d pg_available_extensions
View "pg_catalog.pg_available_extensions"
Column | Type | Collation | Nullable | Default
-------------------+------+-----------+----------+---------
name | name | | |
default_version | text | | |
installed_version | text | | |
comment | text | | |
postgres=#
Hello. I think i've an issue similar to #2 Command SHOW shared_buffers;
shows
shared_buffers
----------------
4GB
(1 row)
But pgmetrics reports the following value 524288.
+---------------------------------+-------------------+
| Setting | Value |
+---------------------------------+-------------------+
| shared_buffers | 524288 (4.0 GiB) |
| work_mem | 32768 (32 MiB) |
| maintenance_work_mem | 1048576 (1.0 GiB) |
| temp_buffers | 1024 (8.0 MiB) |
What are the units and the values on the left ?
Similarly, and more inconsistently SHOW max_wal_size;
returns
max_wal_size
--------------
2GB
(1 row)
But pgmetrics reports as follows
+--------------------+---------------+
| Setting | Value |
+--------------------+---------------+
| wal_level | replica |
| archive_timeout | 0 |
| wal_compression | off |
| max_wal_size | 2048 (32 GiB) |
| min_wal_size | 512 (8.0 GiB) |
| checkpoint_timeout | 300 |
| full_page_writes | on |
| wal_keep_segments | 0 |
+--------------------+---------------+
In the json output created by:
pgmetrics --no-password --format=json
All of the setting values are stored as strings in the JSON output:
"maintenance_work_mem": {
"setting": "65536"
},
"max_connections": {
"setting": "100"
},
"max_files_per_process": {
"setting": "1000"
},
Would it be possible (or sensible) to output parameters that we "know" will be numbers as actual numbers in JSON? That just makes it easier later on to do mathematics on the result.
We can automatically get the three main groupings of string
/integer
/real
with:
string
SELECT name, setting, vartype, COALESCE(boot_val,'') AS boot_val, source,
COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
FROM pg_settings
WHERE vartype NOT IN ('integer','real')
ORDER BY name ASC;
integer
SELECT name, setting::integer, vartype, COALESCE(boot_val,'')::integer AS boot_val, source,
COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
FROM pg_settings
WHERE vartype = 'integer'
ORDER BY name ASC;
real
SELECT name, setting::real, vartype, COALESCE(boot_val,'')::real AS boot_val, source,
COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
FROM pg_settings
WHERE vartype = 'real'
ORDER BY name ASC;
I guess we don't really need vartype
in there, but I thought it may be helpful (or needed) when the JSON is created.
Also, I've added column aliases "AS boot_val
", etc. just so that the SQL output is human readable, rather than columns being called "coalesce".
I did a basic test of the theory here:
integer
postgres=# select array_to_json(array_agg(row_to_json(t)))
postgres-# from (
postgres(# SELECT name, setting::integer
postgres(# FROM pg_settings
postgres(# WHERE vartype = 'integer'
postgres(# AND name IN ('maintenance_work_mem','max_connections','max_files_per_process')
postgres(# ORDER BY name ASC
postgres(# )t
postgres-# ;
array_to_json
--------------------------------------------------------------------------------------------------------------------------------------------
[{"name":"maintenance_work_mem","setting":65536},{"name":"max_connections","setting":100},{"name":"max_files_per_process","setting":1000}]
(1 row)
real
postgres=# select array_to_json(array_agg(row_to_json(t)))
postgres-# from (
postgres(# SELECT name, setting::real
postgres(# FROM pg_settings
postgres(# WHERE vartype = 'real'
postgres(# ORDER BY name ASC
postgres(# LIMIT 3
postgres(# )t
postgres-# ;
array_to_json
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"name":"autovacuum_analyze_scale_factor","setting":0.1},{"name":"autovacuum_vacuum_scale_factor","setting":0.2},{"name":"bgwriter_lru_multiplier","setting":2}]
(1 row)
Hi,
I am trying to use pgmetrics against an AWS PostgreSQL RDS (Version 11.5).
It appears the AWS RDS superuser does not have permissions to pg_ls_dir and pg_current_logfile that I see in the code https://github.com/rapidloop/pgmetrics/blob/master/collector/collect.go.
I am seeing the below error messages on the logfile.
ERROR: 42501: permission denied for function pg_current_logfile
ERROR: 42501: permission denied for function pg_current_logfile
Is there any options I can use to eliminate these errors?
Also, is there any plans to modify this to work on opensource PostgreSQL and AWS RDS PostgreSQL?
Thanks and keep up the great work!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.