Viewing entries tagged with 9.1. Return to full view.

Extensions in PostgreSQL 9.1 fixes another pet-peeve

One thing I've really disliked is the fact that contrib modules had installation scripts that enforced the schema to public for the installation. In my opinion, for no useful reason at all.

For example, I often install the pgcrypto contrib module. And I install this in the pgcrypto schema, that I then either add to the search_path variable or just explicitly use in my queries, with things like pcrypto.crypt('foobar','barfoo'). For versions prior to 9.1, being able to do this required me to manually edit the installed pgcrypto.sql file, to remove the SET search_path = public; command.

Extensions in 9.1 makes this so much nicer. To get pgcrypto into it's own schema, I now just need to do:

postgres=# CREATE SCHEMA pgcrypto;
CREATE SCHEMA
postgres=# CREATE EXTENSION pgcrypto SCHEMA pgcrypto;
CREATE EXTENSION

If I happen to create it in public by mistake, I can even move it after the fact!

postgres=# ALTER EXTENSION pgcrypto SET SCHEMA pgcrypto;
ALTER EXTENSION

You still need to create the schema manually - in theory we could auto-create that, but the work is still a lot easier than before. And fully supported!

Remote log reading in PostgreSQL 9.1

PostgreSQL 9.1 beta1 now available - now is a great time to start testing it, and trying out all the great new features.

There have always been a number of ways to read your PostgreSQL logs remotely, over a libpq connection. For example, you can use the pg_read_file() function - which is what pgadmin does. PostgreSQL 9.1 adds a new and more convenient way (in some ways) to do this - using SQL/MED.

PostgreSQL 9.1 comes with SQL standard SQL/MED functionality. The MED in is short for "Managemend of External Data", and as the name sounds, it's about accessing data that's external to the PostgreSQL server. The SQL/MED functionality is not (yet) complete, but it's already very useful in it's current state.

In SQL/MED, there is something called a Foreign Data Wrapper, that can be compared to a driver. Using this FDW, we can create one or more Foreign Servers, which is a definition of how to connect to a specific instance of the service - if any. Finally, we can create one or more Foreign Tables on each of the Foreign Servers, giving us direct access to the remote data using SQL.

Continue reading

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

SCALE 18x
Mar 5-8, 2020
Pasadena, USA
Nordic PGDay 2020
Mar 24, 2020
Helsinki, Finland
pgDay.paris 2020
Mar 26, 2020
Paris, France
PGCon 2020
May 26-29, 2020
Ottawa, Canada
PGConf.EU 2020
Oct 20-23, 2020
Berlin, Germany

Past

FOSDEM PGDay 2020
Jan 31-Feb 2, 2020
Brussels, Belgium
PGDaySF 2020
Jan 21, 2020
San Francisco, USA
DevOpsSaar
Dec 5, 2019
Saarbr├╝cken, Germany
Stockholm PostgreSQL Meetup
Dec 3, 2019
Stockholm, Sweden
Berlin PostgreSQL Meetup
Nov 21, 2019
Berlin, Germany
More past conferences