On 7th of January (I know, it was quite some time ago, I apologize for delay) Itagaki Takahiro committed patch:
New system view pg_stat_replication displays activity of wal sender processes. Itagaki Takahiro and Simon Riggs.
Tablespaces support for omnipitr-backup-*
Really cool news. Thanks to sponsoring from AWeber.com, and code by Brian Dunavant OmniPITR has now support for additional tablespaces in backup creation.
This works on both master and slave, and happens automatically without any kind of user interaction or changing options – OmniPITR simply detects if you have additional tablespaces and backs them up to data tarball.
More details are places in TABLESPACES part of omnipitr-backup-* docs.
Waiting for 9.1 – Unlogged tables
On 29th of December, Robert Haas committed interesting patch, which does:
Support unlogged tables. The contents of an unlogged table aren't WAL-logged; thus, they are not available on standby servers and are truncated whenever the database system enters recovery. Indexes on unlogged tables are also unlogged. Unlogged GiST indexes are not currently supported.
(edited commit message, due to this mail.
Two years of explain.depesz.com
First of all – just today I committed patch for Pg::Explain – which is the workhorse behind explain.depesz.com.
This patch fixes calculation of exclusive time for explain nodes, and the best thing about it is – I didn't write it. It's full patch provided by someone else – Filip Rembiałkowski – my former colleague, friend, and PostgreSQL DBA (not olny PG!).
This is (as far as I recall) first patch that was provided to me for this library, and I'm really grateful for the contribution.
New version of Pg::Explain will hit CPAN mirrors shortly (it's already uploaded, now we're waiting for CPAN mirrors to get it).
When I was updating it, I checked state of database. And I learned that explain.depesz.com is over 2 years now! Some stats follow.
A tale of slow ssh connections
At home I have couple of machines, but one of them takes relatively long time to login to.
It looks like this:
=$ time ssh blob id -u 1000 real 0m0.401s user 0m0.010s sys 0m0.000s
While 0.4s is not very long, it bugged me. And today, I decided to look into it, to see what's happening.
Waiting for 9.1 – KNNGIST
On 4th of December, Tom Lane committed really cool patch:
KNNGIST, otherwise known as order-by-operator support for GIST.
This commit represents a rather heavily editorialized version of Teodor's builtin_knngist_itself-0.8.2 and builtin_knngist_proc-0.8.1 patches. I redid the opclass API to add a separate Distance method instead of turning the Consistent method into an illogical mess, fixed some bit-rot in the rbtree interfaces, and generally worked over the code style and comments. There's still no non-code documentation to speak of, but I'll work on that separately. Some contrib-module changes are also yet to come (right now, point <-> point is the only KNN-ified operator). Teodor Sigaev and Tom Lane
Named interfaces for OpenVPN and Cisco VPN
I use quite a lot of vpns. On any given moment I have between 3 and 10 active vpn connections from the machine I'm working on.
I generally tend to use OpenVPN, but I also do use vpnc (Cisco VPN client).
One thing that I noticed is not very commonly known, and ( in my case – helps a lot ), is that you don't have to have your tunnel interfaces named tun0, tun1, tun2 and so on.
You can do something like this:
in /etc/vpnc/tunnel.conf, add such line:
Interface name <strong>depesz</strong>
and enable the tunnel – you will see that instead of boring, and somewhat cryptic “tunX" interface you'll have interface named “depesz“.
Similar thing for openvpn – in it's conf file for tunnel add these 2 lines:
dev-type tun dev <strong>smart</strong>
First line is new one (usually), and is required so that OpenVPN can know if you want TUN or TAP tunnels. “dev" is usually “tun", but it can be changed, and with above lines, will make the tunnel interface named “smart“.
Auto refreshing password file for pgbouncer
As you perhaps know I'm fan of pgbouncer – connection pooling solution for PostgreSQL.
It can do many really cool things, but has one slight issue.
Since it can reuse connections – it has to provide a way to check if user supplied password is correct without consulting database. And it lately (since 9.0 to be exact) became somewhat of a problem.
Continue reading Auto refreshing password file for pgbouncer
Waiting for 9.1 – auth_delay
On 27th of November, Robert Haas committed patch which adds new, interesting contrib module:
New contrib module, auth_delay. KaiGai Kohei, with a few changes by me.
Waiting for 9.1 – format()
On 21st of November Robert Haas committed new patch, which adds new function:
Add new SQL function, format(text).
Currently, three conversion format specifiers are supported: %s for a string, %L for an SQL literal, and %I for an SQL identifier. The latter two are deliberately designed not to overlap with what sprintf() already supports, in case we want to add more of sprintf()'s functionality here later. Patch by Pavel Stehule, heavily revised by me. Reviewed by Jeff Janes and, in earlier versions, by Itagaki Takahiro and Tom Lane.