Ernst-Georg Schmid - 12- Josh Berkus (pgExperts) - 11
- Andrew Dunstan (pgExperts) - 6
- Greg Sabino Mullane (EndPoint) - 4
- Hans-Juergen Schoenig (Cybertec) - 4
- Hubert 'depesz' Lubaczewski - 4
- Michael Paquier (VMware) - 4
- Erik Van Norstrand - 3
- Pavel Stehule - 3
- Daniel Pocock - 2
- David Fetter (Farmers Business Network) - 2
- gabrielle roth - 2
- Jehan-Guillaume (ioguix) de Rorthais (Dalibo) - 2
- Leo Hsu and Regina Obe - 2
- Luca Ferrari (ITPUG) - 2
- Andreas Scherbaum - 1
- Baji Shaik (EnterpriseDB) - 1
- Bruce Momjian (EnterpriseDB) - 1
- Christoph Berg (credativ) - 1
- Christophe Pettus (pgExperts) - 1
- pgExperts - 18
- VMware - 5
- EndPoint - 5
- Cybertec - 4
- Dalibo - 3
- ITPUG - 2
- Farmers Business Network - 2
- EnterpriseDB - 2
- 2ndQuadrant - 2
- CommandPrompt - 1
- Follow @planetpostgres on Twitter for the latest updates!
- Policy for being listed on Planet PostgreSQL.
- Add your blog to Planet PostgreSQL.
- List of all subscribed blogs.
- Manage your registration.
- Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.
Mark & I left David Wheeler in charge of the PDXPUG February meeting while we were at SCALE last week.
Here’s David’s report:
—
This week Dave Kerr discussed using Bucardo mutli-master
replication to gradually migrate a production database from EC2 to
RDS. This work allowed his team to switch back and forth between
the two systems with the assurance that the data would be the same
on both. It also allowed them a fallback in case the RDS database
didn’t work out: the S3 system would still be there. The discussion
allowed those present to complain about Bucardo, EC2, RDS, and the
French.
—
Thanks!
Of course this could be done with pgAgent or it could be done with cron, but since it should be elegant, this calls for a background worker process.
For illustration purposes, I wrote a sample implementation called worker_ltt based on the worker_spi sample code. Sloppy - even the orginal comments are still in there.
Adding worker_ltt to shared_preload_libraries and
worker_ltt.naptime = 60
worker_ltt.database = 'yourdb'
worker_ltt.user = 'youruser'
worker_ltt.function = 'move_longtail'
to postgresql.conf starts executing move_longtail()
every 60 seconds in yourdb as youruser. If the user
is omitted, it runs with superuser rights!Since move_longtail() basically can do anything, restricting the user is a good idea.
For more security, the SQL statements could be moved entirely into the background worker, but then the approach loses much of its flexibility... But this is a concept anyway, there is always room for improvement.
But it really works.
In part III I'll try to give a raw estimate how big the performance penalty is when the partitioned table switches from fast to slow storage during a query. And there is another important problem to be solved...
PostBooks has been successful on Debian and Ubuntu for a while now and for all those who asked, it is finally coming to Fedora.
The review request has just been submitted and the spec files have also been submitted to xTuple as pull requests so future upstream releases can be used with rpmbuild to create packages.
Can you help?
A few small things outstanding:
- Putting a launcher icon in the GNOME menus
- Packaging the schemas - they are in separate packages on Debian/Ubuntu. Download them here and load the one you want into your PostgreSQL instance using the instructions from the Debian package.
Community support
The xTuple forum is a great place to ask any questions and get to know the community.
Screenshot
Here is a quick look at the login screen on a Fedora 19 host:
| Attachment | Size |
|---|---|
 postbooks-fedora.png |
227.54 KB |
Wouldn't it be nice if you could just tell the database server "don't run expensive queries for the 'web' user"? Well, thanks to my colleague Andrew Dunstan, who wrote plan_filter with support from Twitch.TV, now you can.
Sort of. Let me explain.
PostgreSQL has had statement_timeout for a while, which can be set on a per-user basis (or other places) to prevent application errors from running queries for hours. However, this doesn't really solve the "overload" issue, because the query runs for that length of time, gobbling resources until it's terminated. What you really want to do is return an error immediately if a query is going to be too costly.
plan_filter is a loadable module which allows you to set a limit on the cost of queries you can execute. It works, as far as we know, with all versions of Postgres starting at 9.0 (we've tested 9.1, 9.3 and 9.4).
Let me show you. First, you have to load the module in postgresql.conf:
shared_preload_libraries = 'plan_filter'
Then you alter the "web" user to have a strict limit:
ALTER USER web SET plan_filter.statement_cost_limit = 200000.0
Then try some brain-dead query as that user, like a blanket select from the 100m-row "edges" graph table:
\c - web
SELECT * FROM edges;
STATEMENT: select * from edges;
ERROR: plan cost limit exceeded
HINT: The plan for your query shows that it would probably
have an excessive run time. This may be due to a logic error
in the SQL, or it maybe just a very costly query. Rewrite
your query
Having said that, any input is welcome. :-)
Storing tons of rows in a table of which only a small percentage of rows are frequently queried is a common scenario for RDBMS, especially with databases that have to keep historical information just in case they might be audited, e.g. in environments regulated by law.
With the advent of the first affordable 8TB harddisk and fast SSDs still being much more expensive per GB, I wondered if such long-tailed tables could be split over a SSD holding the frequently accessed pages and a near-line storage HDD keeping the archive - elegantly - with PostgreSQL.
With elegant, I mean without fiddling around with VIEWs, INSTEAD OF triggers and exposing a clean and familiar interface to the developer.
OK, since PostgreSQL already supports horizontal partitioning, spreading one table transparently over many parallel tables, how about vertical partitioning, spreading one table over a hierarchy of speed?
The speed zones can be mapped to tablespaces:
CREATE TABLESPACE fast LOCATION '/mnt/fastdisk';
CREATE TABLESPACE slow LOCATION '/mnt/slowdisk';
Next comes the table(s):
CREATE TABLE the_table
(
id integer NOT NULL,
value real
)
WITH (
OIDS=FALSE
)
TABLESPACE fast;
CREATE TABLE the_table_archive
(
)
INHERITS (the_table)
WITH (
OIDS=FALSE
)
TABLESPACE slow;
Table inheritance in PostgreSQL is so cool...
And a function to move data from fast to slow:
CREATE OR REPLACE FUNCTION move_longtail()
RETURNS boolean AS
$BODY$
DECLARE worked BOOLEAN;
DECLARE rowcount INTEGER;
BEGIN
worked := false;
rowcount := count(*) FROM ONLY the_table WHERE id >= 5000000;
IF (rowcount > 100000) THEN
INSERT INTO the_table_archive SELECT * FROM ONLY the_table WHERE id >= 5000000;
DELETE FROM ONLY the_table WHERE id >= 5000000;
worked := true;
END IF;
RETURN worked;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT;
This function runs only if a minimum of movable rows qualify. This is recommended since SMR di
Up to Postgres 9.4, when a node in recovery checks for the availability of WAL from a source, be it a WAL stream, WAL archive or local pg_xlog and that it fails to obtain what it wanted, it has to wait for a mount of 5s, amount of time hardcoded directly in xlog.c. 9.5 brings more flexibility with a built-in parameter allowing to control this interval of time thanks to this commit:
commit: 5d2b45e3f78a85639f30431181c06d4c3221c5a1
author: Fujii Masao <fujii@postgresql.org>
date: Mon, 23 Feb 2015 20:55:17 +0900
Add GUC to control the time to wait before retrieving WAL after failed attempt.
Previously when the standby server failed to retrieve WAL files from any sources
(i.e., streaming replication, local pg_xlog directory or WAL archive), it always
waited for five seconds (hard-coded) before the next attempt. For example,
this is problematic in warm-standby because restore_command can fail
every five seconds even while new WAL file is expected to be unavailable for
a long time and flood the log files with its error messages.
This commit adds new parameter, wal_retrieve_retry_interval, to control that
wait time.
Alexey Vasiliev and Michael Paquier, reviewed by Andres Freund and me.
wal_retrieve_retry_interval is a SIGHUP parameter (possibility to update it by reloading parameters without restarting server) of postgresql.conf that has the effect to control this check interval when a node is in recovery. This parameter is useful when set to values shorter than its default of 5s to increase for example the interval of time a warm-standby node tries to get WAL from a source, or on the contrary a higher value can help to reduce log noise and attempts to retrieve a missing WAL archive repetitively when for example WAL archives are located on an external instance which is priced based on the amount of connections attempted or similar (note as well that a longer interval can be done with some timestamp control using a script that is kicked by restore_command, still it is good to have a built-in option to do it instea
After you have built and installed it, you add a couple of settings to the postgresql.conf file, like this:
Then if the planner estimates the cost as higher than the statement_cost_limit it will raise an error rather than allowing the query to run.shared_preload_libraries = 'plan_filter'
plan_filter.statement_cost_limit = 100000.0
This module follows an idea from a discussion on the postgresql-hackers mailing list some time ago. It was developed by PostgreSQL Experts Inc for our client Twitch.TV, who have generously allowed us to make it publicly available.
New repository
It’s been almost a year now that I wrote the first version of the btree bloat estimation query. Then, came the first fixes, the bloat estimation queries for tables, more fixes, and so on. Maintaining these queries as gists on github was quite difficult and lack some features: no documented history, multiple links, no doc, impossible to fork, etc.
So I decided to move everything to a git repository you can fork right away: http://github.com/ioguix/pgsql-bloat-estimation. There’s already 10 commits for improvements and bug fixes.
Do not hesitate to fork this repo, play with the queries, test them or make pull requests. Another way to help is to discuss your results or report bugs by opening issues. This can lead to bug fixes or the creation of a FAQ.
Changelog
Here is a quick changelog since my last post about bloat:
- support for fillfactor! Previous versions of the queries were considering any extra space as bloat, even the fillfactor. Now, the bloat is reported without it. So a btree with the default fillfactor and no bloat will report a bloat of 0%, not 10%.
- fix bad tuple header size for tables under 8.0, 8.1 or 8.2.
- fix bad header size computation for varlena types.
- fix illegal division by 0 for the btrees.
- added some documentation! See README.md.
In conclusion, do not hesitate to use this queries in your projects, contribute to them and make some feedback!
- This builds against PostgreSQL 9.4 and OpenBabel 2.3.2 on Linux.
- It contains all fixes and contributions of the previous versions.
- Windows is not supported anymore - and since it builds and runs way better on Linux, probably never will be again.
- Depiction functions have been removed. Their run time was too unpredictable to be run inside a database server.
- Theoretical isotope pattern generation with MERCURY7 is now available with isotopes for 39 elements.
So: CREATE EXTENSION pgchem_tigress;
After VanLUG I spent the next morning at the Vancouver Aquarium with my ladies. Nothing like beautiful weather, dolphins and jelly fish to brighten the week. Once back in Bellingham, we moved on to a WhatcomPUG meeting where I presented, "Practical PostgreSQL: AWS Edition". It was the inaugural meeting but was attended by more than just the founders which is a great start!
I got to rest from community work on Wednesday and instead dug my head into some performance problems on a client High Availability Cluster. It is amazing that even with proper provisioning how much faster ASYNC rep is over SYNC rep. Some detailed diagnosis and proving data demonstrated, we switched to ASYNC rep and all critical problems were resolved.
On Thursday it was off to Southern California Linux Expo where I presented, "Suck it! Webscale is dead; long live PostgreSQL!". The room was packed, people laughed and for those who might have been offended, I warned you. Your offense is your problem. Look inside yourself for your insecurities! All my talks are PG-13 and it is rare that I will shy away from any topic. My disclosure aside, I had two favorite moments:
- When someone was willing to admit they hadn't seen Terminator. I doubt that person will ever raise his hand to one of my questions again.
- When Berkus (who knew the real answer) suggested it was Elton John that wrote the lyrics at the end of the presentation.
After I spent the evening with JimmyM (BigJim, my brother), Joe Conway of SDPUG/Credativ , Jim Nasby of the
So ... Prague PostgreSQL Developer Day 2015, the local PostgreSQL conference, happened about two weeks ago. Now that we collected all the available feedback, it's probably the right time for a short report and sketch of plans for next year.
The first year of Prague PostgreSQL Developer Day (P2D2) happened in 2008, and from the very beginning was organized as a community event for developers - from students of software engineering to people who use PostgreSQL at work.
We've changed the venue a few times, but in most cases we've just moved from one university / faculty to another one, and the same happened this year for capacity reasons. The previous venue at Charles University served us well, but we couldn't stuff more than 120 people in, and we usually reached that limit within a week after opening the registration. The new venue, located at Czech Technical University can handle up to ~180 people, which should be enough for the near future - this year we registered 150 people, but a few more ended on a wait list.
The most obvious change was adding a full day of trainings on February 11 (i.e. the day before the main conference day), similarly to what happens at pgconf.eu and various other conferences. The feedback to this is overwhelmingly good, so we're pretty sure we'll preserve this for the next years.
The main conference (on February 12) consisted of 9 talks, not counting the initial "welcome" speech. We had the usual mixture of talks, from a brief talk about features introduced in 9.4, talks about using PostgreSQL in actual projects, to a talk about Bi-Directional Replication.
Although the conference is aimed at local users, and thus the majority of talks is either in Czech or Slovak, every year we invite a few foreign speakers to give talks in english. This year we had the pleasure to welcome Marc Balmer, who gave a talk "Securing your PostgreSQL applications", and Keith Fiske explaining that "When PostgreSQL Can't You Can".
In the early years of the conference we've been getting "too many talks in english"
Way back in 2005 I added the ON_ERROR_ROLLBACK feature to psql, the Postgres command line client. When enabled, any errors cause an immediate rollback to just before the previous command. What this means is that you can stay inside your transaction, even if you make a typo (the main error-causing problem and the reason I wrote it!). Since I sometimes see people wanting to emulate this feature in their application or driver, I thought I would explain exactly how it works in psql.
First, it must be understood that this is not a Postgres feature, and there is no way you can instruct Postgres itself to ignore errors inside of a transaction. The work must be done by a client (such as psql) that can do some voodoo behind the scenes. The ON_ERROR_ROLLBACK feature is available since psql version 8.1.
Normally, any error you make will throw an exception and cause your current transaction to be marked as aborted. This is sane and expected behavior, but it can be very, very annoying if it happens when you are in the middle of a large transaction and mistype something! At that point, the only thing you can do is rollback the transaction and lose all of your work. For example:
greg=# CREATE TABLE somi(fav_song TEXT, passphrase TEXT, avatar TEXT);
CREATE TABLE
greg=# begin;
BEGIN
greg=# INSERT INTO somi VALUES ('The Perfect Partner', 'ZrgRQaa9ZsUHa', 'Andrastea');
INSERT 0 1
greg=# INSERT INTO somi VALUES ('Holding Out For a Hero', 'dx8yGUbsfaely', 'Janus');
INSERT 0 1
greg=# INSERT INTO somi BALUES ('Three Little Birds', '2pX9V8AKJRzy', 'Charon');
ERROR: syntax error at or near "BALUES"
LINE 1: INSERT INTO somi BALUES ('Three Little Birds', '2pX9V8AKJRzy'...
greg=# INSERT INTO somi VALUES ('Three Little Birds', '2pX9V8AKJRzy', 'Charon');
ERROR: current transaction is aborted, commands ignored until end of transaction block
greg=# rollback;
ROLLBACK
greg=# select count(*) from somi;
count
-------
0
When ON_ERROR_ROLLBACK is enabled, psql will issue a SAVEPOINT before every command you sen
Buffers: shared read=442478 vs. Buffers: shared read=221239
Since I loaded all rows into slow first and then moved 50% of them into fast, this makes sense, I guess.
If I understand it correctly, those pages in slow are now empty, but PostgreSQL keeps them for future use.
So I tried a VACUUM FULL on slow and ran my queries again. That changed the plans:
Buffers: shared read=221239 vs. Buffers: shared read=221239
And execution times are now about equal.
Some of our customers really like writing their business logic inside of PostgreSQL. While this is really cool that PostgreSQL is capable of handling, trying to performance tune large amounts of PL/pgSQL code becomes unwieldy. If your functions are small enough, it’s possible add some logging statements, but that is not possible with hundreds or even thousands lines of legacy code.
Several years ago as part of the PL/pgSQL debugger, Korry Douglas wrote a PL/pgSQL profiler, but over the years, it seems to have suffered from bit rot. A profiler for PL/pgSQL code helps solve a lot of problems and gives us insight into how your server side code is running.
Below is an example output from the profiler showing how many times each line of code executed and what was the time taken for each line.
The plprofiler has not been tested is many different environments yet, so be careful in rolling it out to production servers. Check it out and let me know if you find any issues.
https://bitbucket.org/openscg/plprofiler
Not later than this week a bug regarding pg_dump and compression with zlib when dumping data has been reported here.
The issue was that when calling -Fd, the compression level specified by -Z was ignored, making the compressed dump having the same size for Z > 0. For example with a simple table:
=# CREATE TABLE dump_tab AS SELECT random() as a,
random() as b
FROM generate_series(1,10000000);
SELECT 10000000
A dump keeps the same size whatever the compression level specified:
$ for num in {0..4}; do pg_dump -Fd -t dump_tab -f \
level_$num.dump -Z num ; done
$ ls -l level_?.dump/????.dat.gz level_0.dump/????.dat
-rw-r--r-- 1 michael staff 419999247 Feb 20 22:13 level_0.dump/2308.dat
-rw-r--r-- 1 michael staff 195402899 Feb 20 22:13 level_1.dump/2308.dat.gz
-rw-r--r-- 1 michael staff 195402899 Feb 20 22:14 level_2.dump/2308.dat.gz
-rw-r--r-- 1 michael staff 195402899 Feb 20 22:15 level_3.dump/2308.dat.gz
-rw-r--r-- 1 michael staff 195402899 Feb 20 22:16 level_4.dump/2308.dat.gz
After a couple of emails exchanged, it was found out that a call to gzopen() missed the compression level: for example to do a compression of level 7, the compression mode (without a strategy) needs to be something like "w7" or "wb7" but the last digit was simply missing. An important thing to note is how quickly the bug has been addressed, the issue being fixed within one day with this commit (that will be available in the next series of minor releases 9.4.2, 9.3.7, etc.):
commit: 0e7e355f27302b62af3e1add93853ccd45678443
author: Tom Lane <tgl@sss.pgh.pa.us>
date: Wed, 18 Feb 2015 11:43:00 -0500
Fix failure to honor -Z compression level option in pg_dump -Fd.
cfopen() and cfopen_write() failed to pass the compression level through
to zlib, so that you always got the default compression level if you got
any at all.
In passing, also fix these and related functions so that the correct errno
is reliably returned on failure; the original coding supposes that free()
cannot changeCREATE UNLOGGED TABLE everything.slow
(
id integer NOT NULL,
value real
)
WITH (
OIDS=FALSE
)
CREATE UNLOGGED TABLE everything.fast
(
)
INHERITS (everything.slow)
WITH (
OIDS=FALSE
)
No indexes. Default tablespace.
If I then load 50 million records into each of those tables and query them individually using the ONLY restrictor, a count(*) on the parent table (slow) is slower than on the descendant (fast):
select count(*) from only everything.slow;
"Aggregate (cost=1067783.10..1067783.11 rows=1 width=0) (actual time=4973.812..4973.813 rows=1 loops=1)"
" Output: count(*)"
" Buffers: shared read=442478"
" -> Seq Scan on everything.slow (cost=0.00..942722.08 rows=50024408 width=0) (actual time=1012.708..3416.349 rows=50000000 loops=1)"
" Output: id, value"
" Buffers: shared read=442478"
"Planning time: 0.118 ms"
"Execution time: 4973.901 ms"
select count(*) from only everything.fast;
"Aggregate (cost=846239.00..846239.01 rows=1 width=0) (actual time=3988.235..3988.235 rows=1 loops=1)"
" Output: count(*)"
" Buffers: shared read=221239"
" -> Seq Scan on everything.fast (cost=0.00..721239.00 rows=50000000 width=0) (actual time=0.101..2403.813 rows=50000000 loops=1)"
" Output: id, value"
" Buffers: shared read=221239"
"Planning time: 0.086 ms"
"Execution time: 3988.302 ms"
This works with other aggregates like avg() too.
I had expected some overhead when querying without ONLY on slow, because of the traversal of the inheritance hierarchy, but not when I restrict the query to a specific table with ONLY...
Can someone explain this?
I started to learn by installing the latest version of CentOS 7. CentOS closely follows RHEL 7 and coming from SLES 11 and older CentOS 6.5, I saw many new changes which are pretty interesting.
New commands to learn immediately as I started navigating:
systemctl
firewall-cmd
I admit that I missed my favorite files in /etc/init.d and looking at new location of /etc/systemd/system/multi-user.target.wants/ will take me a while to get used to.
firewall-cmd actually was more welcome considering how hard I found to remember the exact rule syntax of iptables.
There is new Grub2 but honestly lately I do not even worry about it (which is a good thing). Apart from that I see XFS is the new default file system and LVM now has snapshot support for Ext4 and XFS and many more.
However the biggest draw for me was the support for Linux Containers. As a Sun alumni, I was always draw to the battle of who did containers first and no longer worry about it, but as BSD Jails progressed to Solaris Containers to now the hottest technology: Docker container, it sure has its appeal.
In order to install docker however you need the "Extras" CentOS 7 repository enabled. However docker is being updated faster so the "Extras" repository is getting old at 1.3 with the latest out (as of last week) is Docker 1.5. To get Docker 1.5 you will need to enable "virt7-testing" repository on CentOS 7
This is a question that may not always be easy to answer, even with the excellent TIMESTAMPTZ data type. While it stores the UTC timestamp equivalent to the time it sees, it throws away the time zone of the client.
Here's how to capture it.
Continue reading "Stalking the Wild Timezone"
This list is also for anyone who was unaware of the amount of Postgres content available this year at conferences everywhere.
- SCALE, Los Angeles, this week: 2-day Postgres track, booth. Use code "SPEAK" if you still haven't registered for a small discount. I'm speaking on 9.4 (Friday), and PostgreSQL on AWS (Sunday).
- March 10, Burlingame, CA: pgDay SF 2015 Running the event, and a lightning talk.
- March 25-27, NYC, NY: pgConf NYC: speaking on PostgreSQL on PAAS: a comparison of all the big ones.
- April 25-26, Bellingham, WA: LinuxFest NorthWest, tentatively. Talks haven't been chosen yet. If I go, I'll also be working a booth no doubt. I understand there are plans to have a bunch of Postgres stuff at this event.
- June 16-20, Ottawa, Canada: pgCon of course.
- July 20-24, Portland, OR: OSCON (tentatively, talks not selected). Postgres talk of some sort, and probably booth duty.
Normally, you'd want to upgrade the software in one step, and restart the service that depends on it in a different step.
Here's how.
Continue reading "Upgrading PostgreSQL on Ubuntu Without Automatically Restarting"
One of the many reasons I love Postgres is the responsiveness of the developers. Last week I posted an article about the dangers of reinstating some implicit data type casts. Foremost among the dangers was the fact that pg_dump will not dump user-created casts in the pg_catalog schema. Tom Lane (eximious Postgres hacker) read this and fixed it up - the very same day! So in git head (which will become Postgres version 9.5 someday) we no longer need to worry about custom casts disappearing with a pg_dump and reload. These same-day fixes are not an unusual thing for the Postgres project.
For due diligence, let's make sure that the casts now survive a pg_dump and reload into a new database via psql:
psql -qc 'drop database if exists casting_test'
psql -qc 'create database casting_test'
psql casting_test -xtc 'select 123::text = 123::int'
ERROR: operator does not exist: text = integer
LINE 1: select 123::text = 123::int
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
psql casting_test -c 'create function pg_catalog.text(int) returns text immutable language sql as $$select textin(i\
nt4out($1))$$'
CREATE FUNCTION
psql casting_test -c 'create cast (int as text) with function pg_catalog.text(int) as implicit'
CREATE CAST
psql casting_test -xtc 'select 123::text = 123::int'
?column? | t
psql -qc 'drop database if exists casting_test2'
psql -qc 'create database casting_test2'
pg_dump casting_test | psql -q casting_test2
psql casting_test2 -xtc 'select 123::text = 123::int'
?column? | t
Yay, it works! Thanks, Tom, for commit 9feefedf9e92066fa6609d1e1e17b4892d81716f). The fix even got back-patches, which means it will appear in Postgres version 9.5, but also versions 9.4.2, 9.3.7, 9.2.11, 9.1.16, and 9.0.20. However, does this mean that pg_dump is logically complete, or are there similar dangers lurking like eels below the water in the source code for pg_dump? You will be happy
A real-time monitoring screen based on Dashing and Sinatra
PostgreSQL Dashboard is simple monitoring tool that provides a live activity report of a PostgreSQL instance.
It is designed to be displayed on a large screen in a monitoring room or an open space office. The current dashboard is currently composed of 5 widgets :
- General Info : Version, number of host databases, etc.
- Hit Ratio : The % of data found in cache
- Buffers : The number of new buffers allocated
- Queries : The number of active queries currently running of the instance
- Twitter : A glimpse of the #PostgreSQL feed
Extensibility
Adding a new widget should be fairly easy. This tool is designed so that you can write a custom widget to display the stats you find relevant or a dynamic graph on some “business logic” valuation.
The layout is also entirely flexible. You can easily drag’n’drop any widget to put it wherever you want on the screen. And HTML code of the dashboard can be modified for specific needs, such as specific screen dimensions.
Links & Credits
Some parts of this tool are based on the work done on other PostgreSQL tools, especially pgstats and pgcluu
PostgreSQL Dashboard is an open project distributed under the PostgreSQL licence. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools.
Links :