Posts - Dave PageRSS

Published: Aug. 24, 2023, 5:12 a.m.

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back then, all of the automated testing was performed using Jenkins, with a number of jobs that ran various test suites whenever new code was checked in. All of this infrastructure ran in a virtual private cloud on AWS consisting of a large number of virtual machines and other resources, hosted for the project by EDB.

I recently undertook a project to move the testing infrastructure to our Github project, using Github Actions.

Published: July 25, 2023, 11:14 a.m.

Introduction
Monitoring and alerting on various aspects of your PostgreSQL database is crucial for ensuring optimal performance and locating any bottlenecks. In this blog, we explore the views pg_stat_all_tables and pg_stat_all_indexes that are used to monitor index usage and table statistics. We can pinpoint areas for optimization and raise the general performance of our PostgreSQL database by effectively monitoring these indicators and taking appropriate actions.
Understanding the views pg_stat_all_tables and pg_stat_all_indexes
The pg_stat_all_tables and pg_stat_all_indexes views

Published: March 28, 2023, 10:20 a.m.

Vice President & Chief Architect, Database Infrastructure Dave Page shares the secret to deploying pgAdmin in Kubernetes.

Published: March 17, 2023, 10:06 a.m.

Tuning of the max_wal_size configuration parameter in PostgreSQL can have a profound effect on performance, but it's often overlooked or not given the priority it deserves in favour of other parameters, such as those that are easier to understand. In this blog we'll discuss what the parameter does, how to tune it, and how to monitor the system to ensure the tuning is effective.

Published: March 1, 2023, 1:11 p.m.

 
Securing data is mission-critical for the success of any enterprise, as well as for the safety of its customers. This article is intended as a comprehensive overview that will help you examine the security of your Postgres deployment from end to end. 
The vast majority of the discussion will focus on features, functionality, and techniques that apply equally to both PostgreSQL and EDB Postgres Advanced Server (EPAS); however, it will also touch on a couple of features that are only available in EPAS. These will be clearly noted. This article references the latest version of Postgres

Published: Jan. 24, 2023, 11:01 a.m.

For those that aren't aware, pgAdmin 4 is the fourth complete rewrite of pgAdmin since the first code was written way back in 1998(!). One of the major aims of the technology change we made for the new version was to help attract new developers as we always found them extremely hard to find when we used C++ (we now use Python and Javascript).

Published: Jan. 20, 2023, 3:38 p.m.

Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using pgAdmin in a container (of which there have now been over 10 million pulls)!

Published: Jan. 20, 2023, 3:29 p.m.

During a conversation with $BOSS a couple of days ago the topic of the new Full Text Search in MongoDB came up. He (knowing of, but presumably having never worked with FTS in PostgreSQL) suggested I might create a tool like the one the MongoDB guys were proudly showing off in their keynote video from their recent conference to make it easy to index and search HTML documents on disk.

Published: Aug. 25, 2022, 11:03 a.m.

This blog introduces a new benchmark framework for performance testing of PostgreSQL and EDB Postgres Advanced Server. Based on Ansible, it offers a highly configurable and easy to use way to run pgbench, TPROC-C, and TPROC-H tests across multiple servers at once in a consistent and reproducible manner. PostgreSQL and the operating system kernel can be configured and tuned as required, all using Ansible's Jinja2 templating to allow different runs to automatically use different parameters. Extensive logging and monitoring is also included, as well as hooks to allow customisation for installing and configuring your own extensions during test initialisation and logging custom stats.

Published: Aug. 2, 2022, 11:28 a.m.

This blog summarises the results of the pgAdmin User Survey run in 2022.

Published: May 30, 2022, 12:01 p.m.

By default, pl/python3 functions and procedures in PostgreSQL will use the system Python environment. In order to avoid pollution of that environment and to separate requirements for different projects we can utilise virtual environments, either on a server-wide or per-session basis.

Published: Dec. 9, 2021, 11:53 a.m.

Vice President & Chief Architect of Database Infrastructure, Dave Page, gets hands-on to show you how to get your Django application to utilise multiple database connections, and how those connections are provided by PgBouncer

Published: May 10, 2021, 8:59 a.m.

In part 1 of this blog mini-series, we looked at how to setup PostgreSQL so that we can perform regression analysis on our data using TensorFlow from within the database server using the pl/python3 procedural language. In part 2, we looked at how to understand and pre-process the data prior to training a neural network. 
In this third and find part, we'll use the data that we've prepared and optimised to train a network and then use it to analyse data - all from within a PostgreSQL database!
 
Splitting the Data
When we finished the last part of the blog series, we had a set of data

Published: April 13, 2021, 8:33 a.m.

In part 1 of this blog mini-series on machine learning, we'll look at how to setup PostgreSQL so that we can perform regression analysis on our data using TensorFlow from within the database server using the pl/python3 procedural language.

Published: March 4, 2021, 4:23 p.m.

In my last Machine Learning blog, PostgreSQL and Machine Learning, I looked at why we might want to integrate machine learning into our databases and showed examples using Apache MADlib and Tensorflow to analyse the Boston Housing Dataset.
In this blog mini-series, we'll take a deeper dive into the code I wrote to perform this analysis using Tensorflow. Whilst MADlib is certainly useful for building intelligent analytics into your databases, use of Tensorflow is arguably much more interesting as we can easily build whatever we want using pl/python3 as we'll have access to all the nuts and

Published: Feb. 3, 2021, 8:45 a.m.

In previous blog posts, I've discussed the use of Machine Learning for Capacity Management when monitoring your PostgreSQL infrastructure. I’ve also covered some experiments I undertook that investigated the possibility of using Machine Learning for Text Prediction to improve the user experience when searching on the pgAdmin website.
In this post, I'll explore some of the options available to us to integrate machine learning techniques directly into our PostgreSQL database servers.
 
Why?
In one word, flexibility. 
Our data is already in the database, and we have the powerful

Published: Jan. 27, 2021, 11:31 a.m.

Check out Episode 4 of our "How To" video series with PostgreSQL expert, Dave Page!

Published: Jan. 20, 2021, 4:53 p.m.

Check out Episode 3 of our "How To" video series with PostgreSQL expert, Dave Page!

Published: Jan. 4, 2021, 11:15 a.m.

Episode 2 of our "How To" video series with PostgreSQL expert, Dave Page, is here!

Published: Dec. 22, 2020, 9:52 a.m.

Introducing our new "How To" video series with PostgreSQL expert, Dave Page!

Check out this video to learn the steps to install pgAdmin on Fedora, CentOS, and Red Hat.


Watch on YouTube and subscribe to our channel to see more!
Stay tuned for episode 2.
 

Published: Nov. 16, 2020, 4:13 p.m.

Update: I, and others have subsequently been unable to reproduce the problem below. If you do run into the issue, please let me know in the comments below.

Some of the readers of my blog may be aware that I'm responsible for the PostgreSQL Installers produced by EDB for the PostgreSQL Community. This includes both the Windows and macOS installers, and the now-deprecated Linux installers (use RPMs/DEBs instead)!

Over the weekend I upgraded my laptop to the new Big Sur release from Apple, a.k.a. macOS 11.0. Everything went just fine until I tried to connect to my PostgreSQL 13 development server and found it had failed to startup. I did some digging, and quickly found that there was a permissions problem:

dpage@hal:~$ sudo su - postgres -c '/Library/PostgreSQL/13/bin/postmaster -D/Library/PostgreSQL/13/data'
2020-11-16 16:00:18.544 GMT [9532] FATAL:  data directory "/Library/PostgreSQL/13/data" has invalid permissions
2020-11-16 16:00:18.544 GMT [9532] DETAIL:  Permissions should be u=rwx (07...

Published: Nov. 5, 2020, 9:21 a.m.

 
In a previous blog post, I talked about using Machine Learning for Capacity Management as I began a journey exploring how machine learning techniques can be used with and as part of PostgreSQL. Machine Learning has numerous applications of course, and the idea of text prediction piqued my interest.
Those who know me may be aware that not only do I lead the pgAdmin project (part of which involves developing and running the website), but I'm also part of the PostgreSQL web and sysadmin teams who look after the PostgreSQL website. As I was researching natural language processing, I

Published: Oct. 14, 2020, 4:04 p.m.

Machine Learning has always been a fascinating topic for me, but until recently I've had limited time to really explore it. In my previous role at EDB, I was responsible for our monitoring and management tools, and for a long time I've wanted to look at how we might use machine learning techniques to automate monitoring and management of PostgreSQL deployments. My new role in our CTO office allows me the opportunity to research and learn about technologies such as these so I took the time to start digging in.
I plan on writing a number of blog posts over the coming months as I learn more

Published: Aug. 25, 2020, 1:58 p.m.

Like any software, pgAdmin has bugs. At the time of writing there are 119 new or in-progress issues in the bug tracker, which is pretty good for a piece of software with so many moving parts, and regular-as-clockwork monthly releases. Over 10% of these issues are currently assigned to the reporter to gather further information, plus there will undoubtedly be some duplicates and support issues/feature requests in that number that haven’t yet been picked up in our weekly backlog refinement meeting and either closed or moved to the feature or support trackers. Manual TestingWe have one person, Fahar, who is permanently assigned as QA to the pgAdmin team at EDB, though he is also responsible for a number of other product releases such as the PostgreSQL installers for Windows and macOS (pgAdmin is the vast majority of his work however). Fahar primarily does four things:Works to verify that issues reported to users are reproducible, when they require complex setup or the developers have failed. . .

Published: July 22, 2020, 3:24 p.m.

 
The team at Enterprise DB is pleased to announce the release of its new system_stats extension for Postgres and EDB Postgres Advanced Server. 
 
What is system_stats extension?
The system_stats extension is a library of stored procedures that allow users to access system-level statistics for monitoring Postgres activity. These procedures reveal a variety of system metrics to the database server.
 
By using system stats, which allows you to review information about system performance as part of your database monitoring without the need for adding a separate third-party

Published: June 25, 2020, 3:32 p.m.

One of my colleagues at EDB spent some time working on a new extension for PostgreSQL and EDB Postgres Advanced Server as part of a proof of concept that I asked him to look into. I'm pleased to say that we've decided to make that work Open Source, releasing it under the PostgreSQL licence.The system_stats extension offers a number of stored procedures that are useful when monitoring Postgres. They expose various system metrics to the database server, allowing you to extend your database monitoring to include information about CPU, memory, disk and network usage without having to add a separate mechanism to gain access to that information.A number of functions are included:

pg_sys_os_info()

postgres=# SELECT * FROM pg_sys_os_info();
-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------------
name                | Darwin
version             | Darwin Kernel Version 19...

Published: July 10, 2019, 1:33 p.m.

Reverse proxying requests to a pgAdmin server is becoming more and more popular if posts to the mailing lists are to be taken as an indicative measure; more often than not when using pgAdmin in a container (of which there have now been over 10 million pulls)! Typically users will deploy a reverse proxy for a couple of reasons; to host multiple applications in different subdirectories under the same domain, or to add SSL/TLS support independently of the application.

Because of the number of questions asked, I spent a little time over the last couple of days doing some testing and updating the documentation with some examples. Here's a blog-ified version of that work.



Nginx

Nginx is winning the battle of the web servers these days, beating out Lighttpd (which is still a fine piece of software) and the ageing and arguably bloated Apache HTTPD. All of these servers support reverse proxying, and whilst I've looked at Nginx, the examples shown below can easily be translated to the othe. . .

Published: June 30, 2019, 4:28 p.m.

During a conversation with $BOSS a couple of days ago the topic of the new Full Text Search in MongoDB came up. He (knowing of, but presumably having never worked with FTS in PostgreSQL) suggested I might create a tool like the one the MongoDB guys were proudly showing off in their keynote video from their recent conference to make it easy to index and search HTML documents on disk. I looked at him with a slight tilt of the head and remarked that it's already easy to do that, and just takes a few lines of Python code or similar.

So, here I am on my flight back to the UK having written 36 lines of code whilst munching on my breakfast and enjoying my free seat upgrade. I should probably note that those 36 lines of code include import statements, comments, blank lines for readability, and a nicely formatted 7 line SQL query that could easily be just one line. Plus, the vast majority of the code is really just boilerplate to read a directory of files and extract their contents.

Anyway, . . .

Published: May 29, 2019, 10:11 p.m.

So this is one of the very few (maybe the first?) blog entries I've written that aren't directly related to PostgreSQL, however, it does affect how I (and others) may work on the project.

Last night I received email from Google about my personal G Suite account which I use for all my day-to-day email, which for the most part is related to work on pgAdmin and PostgreSQL. Google were proudly announcing the rollout of their new Gmail Confidential Mode update. If you've not come across this yet, then essentially what it does is allow users to send emails that can be deleted or expired after a certain amount of time, optionally require SMS verification to open them, and prevent printing (but not screen-shots of course), forwarding or downloading etc.

When using the Gmail web interface, this all works fairly seamlessly. I can see why some people would want it if that's all they use, however, like many people, I also use other clients, for example, via IMAP. In that case, instead of the or. . .

Published: Oct. 23, 2018, 3:32 p.m.

Looking back at recent releases of the PostgreSQL database it is interesting to speculate whether there is an overall theme and if you can tie it to the changing way people are using information technology.

Published: Sept. 11, 2018, 2:06 p.m.

Why do we install as root?
 
A couple of common questions I hear from customers (usually long-time users of a particular database from Redwood) via our guys in the field is “why do we install our software as root?” And “why do we run services as postgres?”. The simple, TLDR; answer is “for security”. 
 
A basic principle when securing a software installation is “install with maximum privilege requirements and run with minimal”. In practice, this equates to having software being installed and binaries/executables etc. owned by the root user, whilst the services themselves are

Published: Sept. 5, 2018, 12:04 p.m.

A couple of common questions I hear from customers (usually long-time users of a particular database from Redwood) via our guys in the field is “why do we install our software as root?” And “why do we run services as postgres?”. The simple, TLDR; answer is “for security”. For a detailed explanation, read on…



A basic principle when securing a software installation is “install with maximum privilege requirements and run with minimal”. In practice this equates to having software being installed and binaries/executables etc. owned by the root user, whilst the services themselves are actually run under a minimally privileged (and ideally dedicated) service user account, typically postgres in a PostgreSQL installation. Data files, and any other files that need to be modified by the software in normal operation are also owned by the service user account.



Let’s look at the running software first. Postgres (which will in fact refuse to run as root), is a server process which is often ru. . .

Published: Nov. 2, 2017, 12:06 p.m.

The world in which PostgreSQL operates today is very different from its origins in 1986.  Where once PostgreSQL might have been considered an academic exercise even following its initial Open Source release, today it operates in a much more dynamic environment. Open source and traditional commercial software now work alongside one another in many data centers. Indeed, they are often competing to perform essential tasks in enterprise IT infrastructures. 
In the last 31 years Postgres has matured significantly in the range of functionality it offers and the mission-critical workloads it

Published: July 13, 2017, 4:45 p.m.

It's been far too long since I wrote a blog post (again!), so in a bid to start fixing that, here's an easy one:


pgAdmin 4 v1. 6 released
The pgAdmin Development Team are pleased to announce the release of pgAdmin 4 version 1. 6. This release of pgAdmin 4 includes over 70 bug fixes and a dozen new features. For details, please see the release notes. Notable changes in this release include:

Significant performance improvements on Windows, massively reducing initial load time and improving UI response for the vast majority of users during testing.


Enhancements to the Query Tool enabling the viewing of large query resultsets far more quickly. For example, a simple test query with 96K rows rendered results within 1 second vs. 22 seconds in pgAdmin III during testing!


A major rewrite of the Query History tab allows browsing of queries executed in the query tool with full details including the entire query, in a much nicer user interface.


The Desktop Runtime now supports detachable t. . .

Published: April 26, 2016, 7:24 p.m.

As you may know, many of us from the pgAdmin team have been hard at work on pgAdmin 4 for some time now.

Published: April 15, 2016, 10:25 a.m.

As you may know, many of us from the pgAdmin team have been hard at work on pgAdmin 4 for some time now. pgAdmin 4 is a complete rewrite of pgAdmin (the fourth, as you may guess), the previous version having reached the end of it's maintainable life after 14 years of development.

Work on the project began slowly, almost two years ago, however the team at EnterpriseDB have ramped up the development pace over the last few months. Right now, we're approaching alpha-readiness which we expect to be at within a few weeks.



Architecture
This new application is designed for operation on both the desktop and a webserver. Written in Python using the Flask framework for the backend, and Javascript/jQuery/Backbone for the frontend, it can easily be deployed as a WSGI application for multiple users in practically any network environment. A small runtime application allows it to be run as a desktop application - this is a Qt executable that incorporates a Python interpreter and web browser along. . .

Published: Dec. 7, 2014, 12:30 p.m.

I’m often asked how I first became involved in PostgreSQL, and how the pgAdmin project got started. Much as I’m happy to tell the story over beer, it becomes tedious to do so over email after the first half-dozen or so times. So in a vain attempt to save my fingers from future carpal tunnel syndrome, here’s the tale…



I first discovered PostgreSQL in 1997. I was working at a social housing provider in the UK where, at the time, we were running Informix SE and Pick databases. We wanted to start building in-house web applications for managing additional areas of the business. There were no useful interfaces for Pick that could be used with any of the web technologies available to us at the time and the licensing model for Informix and its ODBC driver was prohibitively expensive, so I started looking for alternatives. 



After spending some time researching mSQL and MySQL, I eventually found PostgreSQL, which seemed to offer everything we needed—a SQL-based DBMS with ODBC and C inter. . .

Published: Dec. 7, 2014, 11:19 a.m.

I’m often asked how I first became involved in PostgreSQL, and how the pgAdmin project got started. Much as I’m happy to tell the story over beer, it becomes tedious to do so over email after the first half-dozen or so times. So in a vain attempt to save my fingers from future carpal tunnel syndrome, here’s the tale. . .
 

Published: Sept. 28, 2012, 4:54 p.m.

There are less than four weeks until this years PostgreSQL Conference Europe, to be held at the Corinthia Hotel, Prague, organised by PostgreSQL Europe. This years event is shaping up to be our best ever, with a higher rate of attendee registrations than we've ever seen before, a bumper crop of talks from PostgreSQL users and contributors from around the world, and a keynote presentation from well known industry veteran, renowned author on all things SQL, and one of the original authors of the SQL89 and SQL92 standards, Joe Celko.

For more information on the event, visit the conference website.

This years conference will start with a day of training sessions (available at extra cost), places on which must be pre-booked when you register:

Joe Celko - A day of SQL with Celko: How often does an opportunity to learn SQL from the master come along? Not very often! Not to be missed if you're new to SQL or want to polish up your skills.
Bruce Momjian, Devrim Gunduz (EnterpriseDB) - Master. . .

Published: March 26, 2012, 3:54 p.m.

For the last few years I've organised a by-invitation meeting of the most active developers in the PostgreSQL community the day before PGCon's sessions start in Ottawa. This is undoubtedly the best time of the year to do this as the vast majority of PostgreSQL hackers attend this event - many only attend local events for the rest of the year.

As you can imagine, figuring out how to keep the meeting productive is not an easy task. Opening it up to anyone to attend is not really an option unfortunately, as we would be unlikely to be able to provide a suitable conference room for large numbers of people due to the cost of the space (which really needs to be comfortable as we'll be sitting around a table for nine hours or so) and the food. Budget aside; having too many people in attendance makes it very difficult to have a productive meeting, a problem we believe we had last year when we had our highest number of attendees to date (around 30) and what many felt was our least productive m. . .

Published: Dec. 21, 2011, 3:01 p.m.

Back at the tail end of November, the PostgreSQL sysadmin/web team successfully migrated the PostgreSQL website to a new platform, based on PostgreSQL, Django, Lighttpd, and Varnish, to replace the old somewhat complex and messy PHP platform used in the past. Functionally and visually the website is almost identical to what it was, though the behind-the-scenes management interface is now vastly improved, as is the "Your Account" section which now offers users much more control over their submissions like news and events etc.

One other change that went largely unnoticed however was in the downloads section of the website, specifically the FTP area. This is a web interface over the content on the PostgreSQL FTP site, ftp. postgresql. org, that gives users a nice way to browse the files and directories on the site. On the old website, when the user clicked to download a file they would then be taken to a page of flags where they could select a mirror site to download the file from. The mi. . .

Published: Oct. 11, 2011, 3:38 p.m.

On a fairly regular basis the Postgres community hear from users who are complaining of bugs in old versions of Postgres - they'll post a bug report or a request for help on one of the mailing lists saying something along the lines of having run into an issue with PostgreSQL 8. 4. 2 for example, and immediately be met with suggestions to upgrade to the latest version because there have been 37 bug fixes and 5 security issues resolved since 8. 4. 2 was released.

Generally this happens with one of two types of users. There are those that just don't bother to upgrade (who we can do little about), and those that don't upgrade because they are concerned that changes to PostgreSQL will break their application. This latter class of user is sometimes also restricted by what they can do by corporate policies in their workplace.

The Postgres developers are mindful of this issue and have practices in place to allow users to safely upgrade without significant risk of behavioural changes breaking th. . .

Published: Oct. 5, 2011, 5:31 p.m.

If you've ever used one of the PostgreSQL installers for v8. 2 or above, either the old Windows MSI installer or the newer "one click" installers that also support Linux and Mac, you'll probably have come across StackBuilder. For those that haven't or those that never found the time, StackBuilder was introduced with the PostgreSQL 8. 2 installer to allow us to distribute different components of PostgreSQL independently of the server itself. Originally in 8. 0/8. 1, the installer included lots of additional products, such as the ODBC, JDBC, OLEDB and . NET drivers, Slony and PostGIS.  As you can imagine, this proved near impossible to maintain as we needed to try to coordinate the release of products from multiple independently run projects.

StackBuilder was the solution to this. The installers were cut down to essentially include just the PostgreSQL server, pgAdmin and StackBuilder, which allowed us to provide all the other components on independent release schedules. It also gave us a v. . .

Published: Oct. 5, 2011, midnight

If you've ever used one of the PostgreSQL installers for v8. 2 or above, either the old Windows MSI installer or the newer "one click" installers that also support Linux and Mac, you'll probably have come across StackBuilder. For those that haven't or those that never found the time, StackBuilder was introduced with the PostgreSQL 8. 2 installer to allow us to distribute different components of PostgreSQL independently of the server itself.

Published: Oct. 4, 2011, 9:11 a.m.

PostgreSQL Conference Europe 2011 starts 2 weeks from today in the beautiful city of Amsterdam in the Netherlands. This is the fourth annual conference hosted by PostgreSQL Europe, following on from extremely successful events in Prato (Italy), Paris and Stuttgart, and is aimed at developers, DBAs, technologists and decision makers either using, or considering using the world's most advanced Open Source database. This year we have four days on the schedule, with a kick-off day of training sessions hosted by respected PostgreSQL developers such as Greg Smith, Bruce Momjian, Magnus Hagander, Guillaume Lelarge and more. Topics will cover performance tuning, application development, database administration, replication & high availability and geospatial. The training sessions are available on their own, or as part of a regular conference attendance at additional - but very reasonable - cost. We had a record number of talk proposals submitted this year but we've resisted the urge to host even. . .

Published: Aug. 1, 2011, 4:57 p.m.

So having spent the last few months with my head buried deep in a project at work, I finally managed to get back to my previous hacking on SQL/MED at the weekend after the kids went away for a week (good $DEITY it's quiet here)! Within a couple of hours, I had my half-baked Foreign Data Wrapper for MySQL up and running, and am now able to create foreign table objects in PostgreSQL 9.1 that map either directly to tables in a MySQL database, or to queries run on the MySQL server. Here's an example:

-- Create the required functions for the FDW.
CREATE FUNCTION mysql_fdw_handler()
    RETURNS fdw_handler
    AS '$libdir/mysql_fdw'
    LANGUAGE C STRICT;

CREATE FUNCTION mysql_fdw_validator(text[], oid)
    RETURNS void
    AS '$libdir/mysql_fdw'
    LANGUAGE C STRICT;

-- Create the data wrapper or "transport".
CREATE FOREIGN DATA WRAPPER mysql_fdw
    HANDLER mysql_fdw_handler
    VALIDATOR mysql_fdw_validator;

-- Create the foreign server, a pointer to the MySQL server.
CREATE SERVER mysql_svr
    FOREIGN ...

Published: Aug. 1, 2011, midnight

So having spent the last few months with my head buried deep in a project at work, I finally managed to get back to my previous hacking on SQL/MED at the weekend after the kids went away for a week (good $DEITY it's quiet here)! Within a couple of hours, I had my half-baked Foreign Data Wrapper for MySQL up and running, and am now able to create foreign table objects in PostgreSQL 9. 1 that map either directly to tables in a MySQL database, or to queries run on the MySQL server.
 
Here's an example:

-- Create the required functions for the FDW.
CREATE FUNCTION mysql_fdw_handler()

Published: April 6, 2011, 5:14 p.m.

As a reminder, Google's Summer of Code 2011 program has started and PostgreSQL is participating!  We are looking for students who are interested in hacking on pgAdmin or PostgreSQL all summer.  You only have until April 8, 2011 at midnight Pacific time to submit a proposal.

If you are a student, or know a student, or are a professor, please urge your students to participate in Summer Of Code.  Not only is it a fantastic opportunity to learn how open source code is really made, and get paid to do it, but it can also be career-building.

Read up on the PostgreSQL GSOC wiki page.

Published: April 1, 2011, 3:45 p.m.

One of the cool new features in the upcoming PostgreSQL 9. 1 release is support for SQL/MED, or Management of External Data. Essentially what this allows us to do is connect external data sources to PostgreSQL and query them as if they were tables. The initial support in 9. 1 will offer read-only support for foreign tables, with the ability to include qualifier pushdown - i. e, the ability to push the applicable WHERE part of the SQL query to the remote server, to minimise data transfer. This optimisation is dependent on each individual Foreign Data Wrapper (FDW) - the driver used to connect to the remote data source - which may or may not implement it.  


PostgreSQL 9. 1 will ship with an FDW that allows access to files in formats that can be read by the existing COPY command, and Andrew Dunstan has been working on an FDW that will allow access to files with fixed-width data as well as one for accessing ragged CSV files.


I've been a big proponent of SQL/MED support, particularly at wor. . .

Published: March 24, 2011, 3:45 p.m.

If you follow Twitter for keywords like "postgres" or "postgresql", you may well have seen a number of tweets over the last day or so regarding a so-called "Payload Execution" exploit in PostgreSQL. This supposed attack was apparently first described in this paper, and has hit Twitter after code to demonstrate the issue was added to Metasploit. The "attack" works like this:

A user uploads a payload as a large object to the database. This can be done using the client side lo_create() and lo_import() functions.The user then uses the server-side lo_export() function to export the payload to the server's filesystem.

The user then executes the payload, by creating an SQL level function to wrap the C function in the payload, eg:

CREATE FUNCTION do_bad_stuff() RETURNS int4 AS '$libdir/bad_stuff.dll' LANGUAGE C;

SELECT do_bad_stuff();

Published: Nov. 24, 2010, 11:38 a.m.

Yup, you heard correctly - there are just two (and a bit) days left to register for the annual European PostgreSQL Conference, pgDay. EU 2010, being held in Stuttgart on December 6th and 7th, with a day of training sessions on the 8th.

With over 40 talks in a mix of English and German, this is an event not to be missed if you're a PostgreSQL user, developer, hobbyist, or are considering a deployment. There are a wide range of topics including talks on GIS, interoperability and migration, high availability and monitoring, business around PostgreSQL and case studies, as well as more academic topics.

On day three we have a number of training courses available, including a two part course on PostGIS, presented by one of the leading developers, Mark Cave-Ayland from Sirius, deployment of applications in the Cloud with Servoy presented by Robert Ivens from Roclasi, and a two part PostgreSQL administration course (in German) given by Andreas Scherbaum for EnterpriseDB.

Finally, as attendees. . .

Published: Nov. 6, 2010, 5:54 a.m.

PGWest; the final day. It started (for me at least) with Greg Smith giving another excellent talk on scaling PostgreSQL with Hot Standby. This was followed by Robert Hodges and Linas Virbalas talking about replication of data from MySQL to PostgreSQL using Continuent Tungsten.

The highlight/lowlight of the day - depending on your viewpoint - was Rob Wultsch's talk on MySQL. Rob primarily looked at some of the things MySQL does better than PostgreSQL, and also talked about the forks (or lack of them, if you discount patchsets - which I personally, do not) and the FUD. Now lets be honest here - Rob did make some perfectly valid points about MySQL; for example, the fact that it's replication is easy to setup. Now to take this example, I would argue that PostgreSQL isn't that hard to get going either - Robert Haas' tutorial illustrates that nicely - but MySQL is arguably better at the moment. For most of the points he raised, there are easy counter-arguments that can be made by PostgreSQL. . .

Published: Nov. 4, 2010, 4:42 p.m.

If you're planning on attending this year's European PostgreSQL conference (increasingly inaccurately known as PGDay!), then you might want to think about registering and booking your travel and accommodation now. It's just over a month until the conference, and isn't uncommon for the price of flights and trains to start to rise as the date gets nearer.

We have 42 sessions this year, with a wide variety of PostgreSQL talks in English and German, followed by a day of training sessions, covering PostgreSQL Administration, PostGIS, Hot Standby and using Servoy with PostgreSQL. Places on the training sessions are limited, and available on a first come, first served basis at a (relatively small) extra cost.

We have two guest keynote speakers: Simon Phipps is giving the opening keynote, and will be giving a talk entitled "Back To The Future of Open Source", looking issues around corporate involvement in Open Source projects, and what that means for the PostgreSQL community and contributors. . .

Published: Nov. 4, 2010, 12:27 a.m.

So my blogging of PGWest was a FAIL on the first day, as I never got around to following up my Day 0 post with anything, so with apologies, here's a quick roundup of day 1 and day 2.

Day 1 


Started with breakfast with Magnus, Devrim and Guillaume before heading up to register on the mezzanine. The first half of the day was a number of three hour tutorials which were on some interesting topics, but none which particularly interested me, so I spent the time catching up with a number of colleagues who I haven't seen in a few months.

After lunch, my talk on "Securing your web application" was one of the first 'normal' talks to be given. It was intended as a wide but shallow look at some of the security issues to consider when building a web app - a completely new talk which unfortunately didn't work as well as I'd hoped and needs some tweaking should I give it again; trimming the length a little, and focusing a little more on the database end of the stack. Still, I think it covered mos. . .

Published: Nov. 2, 2010, 2:46 p.m.

. . . or day 1, depending on how you look at it. Anyway, mostly yesterday, the day before the conference starts. Which is today. Obviously. $DEITY I hate jetlag-induced early mornings, especially when coupled with reminders for meetings on the East coast that go "ping" right as I'm finally drifting off to sleep again at 6AM.

Anyway, enough about that. The flight from LHR to SFO was mostly uneventful, barring an APU failure which meant the HVAC was barely working until we took off. It got a little hot, but otherwise everything was good, and being completely cut off from email, twitter and IM for nearly 11 hours meant that I managed to get a bunch of work done that's been piling up for ages and watch the A-Team.

Immigration at SFO was remarkably fast (I've only seen similar speeds in the past in Boston), which gave me plenty of time to queue up with a bunch of other passengers for over 10 minutes before a single cab showed up. What the *$£% is that about?

Made it to the hotel, which too. . .

Published: July 28, 2010, midnight

By far the most common issues we see reported with the "one-click" PostgreSQL installers that we build here at EnterpriseDB are password related. In this post I'll explain what the passwords are, why we need them, and how to reset them.

Superuser Password
 

Published: May 27, 2010, midnight

I've been asked a few times recently for my opinion on VoltDB, the new database server architected by the 'father' of Postgres, Dr. Michael Stonebraker so rather than repeating myself over and over again it seems like a good idea to write it all down.

Published: May 7, 2010, midnight

A colleague of mine (thanks Jimbo!) pointed me towards a benchmark comparison of Postgres 8. 3. 8 on RHEL 5. 4 versus SQL Server 2008 R2 on Windows Server 2008 R2, performed and written up by Red Hat. I hadn't seen it before, so figured that maybe others hadn't either:

http://www. redhat. com/pdf/rhel/bmsql-postgres-sqlsrvr-v1. 0-1. pdf

Can't wait for the result? The elephant wins :-)