Author: depesz

OmniPITR – update

As of yesterday OmniPITR got following changes/fixes:

  1. Fixed bug which caused immediate finish request be treated the same as smart finish request.
  2. Fixed problem with using omnipitr-backup-slave on PostgreSQL 9.0 slave, which is using streaming replication.
  3. Added option to omnipitr-restore, so that you can now use it for streaming-replication slaves

I'm very ashamed of the first thing (smart/immediate finish request), as it was simply my lack of test.

Second thing – the problem was that with streaming replication slave behaves a bit differently than normally, and so the backup procedure had to be modified.

As for third – restore command in streaming replication environment has to behave differently than normal restore command – i.e. it should finish, with error, as soon as it will be called for wal file that does not exist in wal archive. Which is direct opposite of what should be done normally.

Now, omnipitr-restore got switch (-sr) to make it work correctly in SR situation.

Links for svn/docs are listed on project page.

Waiting for 9.1 – sepgsql

On 24th of January, Robert Haas committed very important patch:

sepgsql, an SE-Linux integration for PostgreSQL
 
This is still pretty rough - among other things, the documentation
needs work, and the messages need a visit from the style police -
but this gets the basic framework in place.
 
KaiGai Kohei

Now, In this place I usually show you what it is all about, but it's not going to happen with this patch. Reason is very simple – I know that it's important, and that it integrates PostgreSQL with SE/Linux, but since I never played with SE/Linux – I cannot really show you anything in here.

But – if you know anything about SE/Linux and sepgsql, I would like to ask you to describe this extension so that even I could understand it.

Sorry – I know I should put more in here, but it simply is so far above my knowledge limits, it would take me too long time to get through documentation for SE/Linux and sepgsql.

For now – if you want to know more – simply check the docs.

Waiting for 9.1 – pg_basebackup

On 23rd of January, Magnus Hagander committed patch which adds:

Add pg_basebackup tool for streaming base backups
 
This tool makes it possible to do the pg_start_backup/
copy files/pg_stop_backup step in a single command.
 
There are still some steps to be done before this is a
complete backup solution, such as the ability to stream
the required WAL logs, but it's still usable, and
could do with some buildfarm coverage.
 
In passing, make the checkpoint request optionally
fast instead of hardcoding it.
 
Magnus Hagander, reviewed by Fujii Masao and Dimitri Fontaine

Continue reading Waiting for 9.1 – pg_basebackup

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.

Continue reading Waiting for 9.1 – Unlogged tables

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.

Continue reading Two years of explain.depesz.com

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

Continue reading Waiting for 9.1 – KNNGIST

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“.