People are often hesitant to test out a new PostgreSQL release because they're concerned it'll break their current working installation.
This is a perfectly valid concern, but it's easily resolved with a few simple protective measures:
Build PostgreSQL from source as an unprivileged user
Install your PostgreSQL build within that user's home directory
Run PostgreSQL as that user, not postgres
Run on a non-default port by setting the PGPORT env var
If you take these steps the install its self cannot interfere at all. Starting and running the new PostgreSQL can only interfere by using too many resources (shared memory, file descriptors, RAM, CPU, etc) and you can just stop the new version if it causes any issues. The shared memory improvements in 9.3 make the shared memory (more…)
(This article is written with reference to PostgreSQL 9.3. If you're using a newer version please check to make sure any limitations described remain in place.)
PostgreSQL offers several tools for searching and pattern matching text. The challenge is choosing which to use for a job. There's:
LIKE and ILIKE SQL pattern matching;
~ and ~* operators for mostly-perl-compatible regular expressions;
full text search with @@, to_tsvector and to_tsquery
Use of an external search provider like Apache Lucene / Solr.
There's also SIMILAR TO, but we don't speak of that in polite company, and PostgreSQL turns it into a regular expression anyway.
Each of the built-in searching options comes with multiple choices of index:
b-tree indexes with the text_pattern_ops opclass for left (more…)
En noviembre del 2012 asistí al PgDay en Buenos Aires. Ahí presente la charla "Autovacuum, ¿bendición o maldición? Afrontando el reto". En uno de los puntos de la charla explique, o al menos eso trate, que es necesario tratar de prevenir que el autovacuum ejecute VACUUM FREEZE.
El problema con VACUUM FREEZE ejecutado por el autovacuum es que leerá y escribirá toda la tabla y las probabilidades son que el autovacuum decida hacer esto en un momento de alta actividad. Durante la charla entonces explique que podemos tratar de adelantarnos al autovacuum chequeando el valor del campo relfrozenxid en la tabla de sistema pg_class, ahora agregare que la consulta es similar a: select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc;
En todo caso, como era de (more…)
In our previous article we went through describing what retention policies are and how they can be enforced on your PostgreSQL server backups with Barman 1.2. In this post, we will go through the configuration aspects.
Defining a disaster recovery plan involves defining backup policies. A key aspect of backup policies is to define how long backup data is retained for disaster recovery purposes. This applies to all digital content, including PostgreSQL databases.
Barman 1.2.0 introduces automated management of backup retention policies of PostgreSQL servers.
The first Australian PostgreSQL conference has come to an end and I am extremely happy that I was part of it.
The event took place in Melbourne, at the offices of Experian Hitwise in St Kilda Rd. I must confess that, going back to the same building where I had worked in 2005/2006, was a strange but positive feeling. Hitwise is a long time Postgres user and it would appear that their cluster of PostgreSQL databases has grown to become one of the largest in the world. So, a double-special occasion for me!
As interest in PostgreSQL grows, so does the rate at which new patches are proposed. To maintain the high level of quality in PostgreSQL it is important that all patches be checked and reviewed, so that what gets added to the codebase is good quality.
Some of this evaluation requires a lot of expertise in the PostgreSQL core code, but most of it requires little development experience at all. The more initial checking and review gets done before patches get evaluated by the experts, the less work those experts have to do. So: please step up and review a patch. There are patch review guidelines on the wiki, and it's quite an accessible process. Step right up if you want to contribute a little back to the software you use and rely on, or if there's a particular enhancement you want to make (more…)
If you use PostgreSQL on Cygwin, please try out this build fix, verifying that it works on Cygwin, and that it doesn't break the Linux/BSD builds or the MinGW Windows builds. Your help would be appreciated in ensuring that Cygwin remains a supported platform into the future.