A new user discovers the PostgreSQL public schema

A new user of PostgreSQL recently discovered that PostgreSQL allows any PostgreSQL user to create objects and data by default.[1] I know you are saying, "What... PostgreSQL has some of the most advanced and flexible security in the industry!" and you are absolutely correct, we do. However, once you can connect to PostgreSQL, you have some interesting default capabilities. Consider the following example:

postgres@sqitch:/# psql -U postgres
psql (9.2.11)
Type "help" for help.

postgres=# create user foo;
postgres=# \q

No biggy, we created a user foo as the super user postgres. All is good. However, what can that user foo do?

postgres@sqitch:/# psql -U foo postgres
psql (9.2.11)
Type "help" for ...

Read More

Let's delete contrib!

There has been a lot of discussion about the upcoming extension pg_audit and whether or not it should be in contrib. You can read about that here. The end result of the discussion is that pg_audit is going to be reverted and not in contrib. There were plenty of technical reasons why people didn't want it in contrib but I have a different reason. It is an extension. It doesn't need to be in contrib. In fact, I argue that because of pgxs and extensions we don't need contrib at all. If you don't follow the mailing lists my argument is blow and please feel free to comment here. The discourse is very much needed on ...

Read More

Updating the .Org docs on backups

I spent a great deal of time working through the SQL DUMP portion of the 9.5devel docs this past week. Below is the current text of what I have and it would be great if my readers would take a look and offer some thoughtful feedback. What would you like to see added? What would you like to see changed? Please note that this is reference documentation not tutorial documentation.

This is just the straight HTML dump that is generated from Docbook but since it is inline the links won't work. The current -devel docs are here and the updated version I am working is below:

24.1. SQL Dump

PostgreSQL provides the program pg_dump for generating a ...

Read More

WhatcomPUG meeting on 04/21. Start date, end date, calculate

The PUG meeting was good. We now have a consistent if small group that are attending. Before the presentation we spoke about possibly moving the group to meetup to get a little better visibility. G+ Communities are awesome but Meetup seems to be where the people in the area look.

The presentation was provided by Eric Worden who happens to be a CMD employee. The talk overall is very good and provided a lot of information that I didn't know about dates. It also lead to the development of a new PostgreSQL extension (more on that at a later time).

The most interesting part of the talk to me was the use of a dimensions table to make date ...

Read More

Reflections on PgConf.US 2015

Saturday the 18th of April, I woke up to the following:

It was one of those moments that you realize just how blessed of a life you have. A moment where you stop and realize that you must have done something right, at least once. I was with my all of my ladies, there were no other people at the camp site, the weather was clear and it was set to hit 68F. The only sound was the gentle lapping of water and the occasional goose.

It was at this time that I was able to finally take a step back and reflect on PgConf.US. This conference meant a lot to me professionally. I didn't organize it ...

Read More

WhatcomPUG meeting last night on: sqitch and... bitcoin friends were made!

Last night I attended the second WhatcomPUG. This meeting was about Sqitch, a interesting database revision control mechanism. The system is written in Perl and was developed by David Wheeler of PgTap fame. It looks and feels like git. As it is written in Perl it definitely has too many options. That said, what we were shown works, works well and appears to be a solid and thorough system for the job.

I also met a couple of people from CoinBeyond. They are a point-of-sale software vendor that specializes in letting "regular" people (read: not I or likely the people reading this blog) use Bitcoin!

That's right folks, the hottest young currency in the market today is using the ...

Read More

Stomping to PgConf.US: Webscale is Dead; PostgreSQL is King! A challenge, do you accept?

I submitted to PgConf.US. I submitted talks from my general pool. All of them have been recently updated. They are also all solid talks that have been well received in the past. I thought I would end up giving my, "Practical PostgreSQL Performance: AWS Edition" talk. It is a good talk, is relevant to today and the community knows of my elevated opinion of using AWS with PostgreSQL (there are many times it works just great, until it doesn't and then you may be stuck).

I also submitted a talk entitled: "Suck it! Webscale is Dead; PostgreSQL is King!". This talk was submitted as a joke. I never expected it to be accepted, it hadn't been written ...

Read More

PostgreSQL is King! Last week was quite busy being a servant.

Last week was one of the busiest community weeks I have had in a long time. It started with an excellent time in Vancouver, B.C. giving my presentation, "An evening with PostgreSQL!" at VanLUG. These are a great group of people. They took all my jibes with good humor (Canadians gave us Maple Syrup, we gave them Fox News) and we enjoyed not only technical discussion but discussions on technology in general. It is still amazing to me how many people don't realize that Linux 3.2 - 3.8 is a dead end for random IO performance.

After VanLUG I spent the next morning at the Vancouver Aquarium with my ladies. Nothing like beautiful weather, dolphins and jelly ...

Read More

AWS performance: Results included

I am not a big fan of AWS. It is a closed platform. It is designed to be the Apple of the Cloud to the Eve of Postgres users. That said, customers drive business and some of our customers use AWS, even if begrudgingly. Because of these factors we are getting very good at getting PostgreSQL to perform on AWS/EBS, albeit with some disclosures:
  1. That high IO latency is an acceptable business requirement.
  2. That you are willing to spend a lot of money to get performance you can get for less money using bare metal: rented or not. Note: This is a cloud issue not an AWS issue.

Using the following base configuration (see adjustments for each configuration after ...

Read More

Don't kill yourself

As a PostgreSQL consultant you end up working with a lot of different types of clients and these clients tend to all have different requirements. One client may need high-availability, while another needs a DBA, while yet another is in desperate need of being hit with a clue stick and while it is true that there can be difficult clients, there is no bad client.

What!!! Surely you can't be serious?

Don't call me shirley.

I am absolutely serious.

A bad client is only a reflection of a consultants inability to manage that client. It is true that there are difficult clients. They set unrealistic expectations, try to low ball you by with things like: "We can get ...

Read More

Along the lines of GCE, here are some prices

I was doing some research for a customer who wanted to know where the real value to performance is. Here are some pricing structures between GCE, AWS and Softlayer. For comparison Softlayer is bare metal versus virtual.

GCE: 670.00
60G Memory
2500GB HD space

GCE: 763.08
104G Memory
2500GB HD space

Amazon: 911.88
30G Memory
3000GB HD Space

Amazon: 1534.00
122.0 Memory
SSD 1 x 320
3000GB HD Space

Amazon: 1679.00
60.0 Memory
SSD 2 x 320
3000GB HD Space

None of the above include egress bandwidth charges. Ingress is free.

Softlayer: ~815 (with 72GB memory ~ 950)
16 Cores ...

Read More

GCE, A little advertised cloud service that is perfect for PostgreSQL


I have yet to run PostgreSQL on GCE in production. I am still testing it but I have learned the following:

  1. A standard provision disk for GCE will give you ~ 80MB/s random write.
  2. A standard SSD provisioned disk for GCE will give you ~ 240MB/s.

Either disk can be provisioned as a raw device allowing you to use Linux Software Raid to build a RAID 10 which even further increases speed and reliability. Think about that, 4 SSD provisioned disks in a RAID 10...

The downside I see outside of the general arguments against cloud services (shared tenancy, all your data in a big brother, lack of control over your resources, general distaste for $vendor, or whatever else ...

Read More

PDXPGDay 2014

I had the honor of being asked to give the introduction at PDXPGDay 2014 this past Saturday. I didn't speak very long but it was great to see a lot of the old stomping ground. It had been quite some time since I had been in the group of Wheeler, Roth, Wong, Berkus and a few others.

The conference was really a mini-conference but it was great. It was held in the exact same room that PostgreSQL Conference West was held all the way back in 2007. It is hard to believe that was so long ago. I will say it was absolutely awesome that PDX still has the exact same vibe and presentation! (Read: I got to wear ...

Read More a wonderful if flawed apt repository

The site is a great resource for those who live in the Debian derived world. It keeps up to date with the latest postgresql packages and has a whole team dedicated to creating these packages. Of course, this is the Open Source world so not everyone agrees 100% with the way things are done in this project. As I noted here, there are some issues.

These issues are not to detract from otherwise excellent work but a note to those who use the repository to look for further problems. I also have a video displaying specifically what the issues are, here.

Read More

Kicking the Donkey of PostgreSQL Replication

This is the title of a talk I am developing for the matured PostgreSQL Conference: PGConf NYC 2014 . Formerly a PgDay, this is now a full blown conference extending two days with three tracks. From all reports it is set to be the largest PostgreSQL Conference ever in the United States, surpassing even the old West and East series (which no conference in the U.S. has done to date). It is truly exciting times for our community.

This talk will be a departure from my standby talks of PostgreSQL Performance and Choosing the right hardware. Katz asked me, "to bring your full East Coast from the West Coast personality.". I plan on doing so. So cinch up the boot ...

Read More

Security Considerations While Using ssh-agent.

Recently we got contacted by a customer, and started the typical remote access setup: ask them to open ssh port for our bastion server, create a new user for us, and add the corresponding ssh public key. Well, they promptly responded, and we tested access, got into their bastion server and then tried to get to one of the broken servers but, it was asking for a password. We just asked the customer for the password, and got a reply stating that they had assumed we were using agent forwarding.

The first instinct of the engineer handling the ticket was: well, lets just start ssh-agent, load the key, and forward it! Of course, after a brief discussion, it became clear ...

Read More

Managing pg_hba.conf With Ansible

pg_hba.conf is perhaps one of the easiest to understand configuration files in PostgreSQL. Its syntax is straightforward, the concept seems to resemble that of any popular IP filter or ACL mechanism in various software packages. pg_hba.conf is also well documented, like the rest of PostgreSQL, and we love it because it lets us do what we want without getting in our way. What else could we possibly ask of it?

Perhaps, it can be a bit of a nuisance when for you pg_hba.conf means not just one file but many. You may have a dozen read-only standbys and your infrastructure is expanding -- a good sign that you're probably all the rage on the market -- so at ...

Read More is over, it was a blast but I am curious about the future

First let me say that I attended like I attend every conference (that I am not running). I show up for a few hours on the first day, then I come back and attend my talk. I don't take travel lightly and as much as I bromance my fellow elephant bretheren, I want to explore the sights and this was freaking Ireland people.

I had an odd feeling for the time I was there. The community was in full force, there was at least 240 people there and that was great. It was the commerce side, the sponsor side, the **money** side that was lacking. EnterpriseDB, Cybertec and 2ndQuadrant were there with booths but I wonder if ...

Read More

5 Things a Non-Geek Girl Learned from Playing with Geeks at CMD

When I began at Command Prompt, Java was coffee, Python was a snake, and a Ruby was best used on the color of glittery slippers. If you would have asked me two and a half years ago what "PostgreSQL" does, I would have asked you what language you were speaking.

A year later, I took my first sales call with out Joshua Drake (jd, @linuxhiker). I was shaking in my boots and it was inevitable that I was going to be sick. Then something happened as soon as I heard the customer say, "Hello".

I understood what the customer needed and most importantly, I knew we could do it. I was able to say confidently and with out doubt, "Yes ...

Read More

A pg_basebackup Wish List

pg_basebackup was introduced in Postgres 9.1 as a simple way to copy the data directory of a running database cluster. This was a great addition to a small group of PostgreSQL Client Applications.

The pg_basebackup approach differs from the standard pg_start_backup(), rsync (or other file system copy), pg_stop_backup() approach in that it uses the replication protocol over a standard Postgres connection to make the base backup. A few highlights:

  • A completely standalone backup can be created easily by using the --xlog-method argument (the stream option here is particularly nice so that you don’t have to muck with the wal_keep_segments Postgres setting).
  • Thanks to the cascading replication machinery in 9.2 pg_basebackup can take a backup from an active hot ...

Read More

Just back from NYCPug August, on to more talks

In August I spoke at NYCPUG on Dumb Simple PostgreSQL Performance. The talk was well accepted and there was about 60 people in attendance. I have always enjoyed my trips to NYC but this is the first time I have taken a leisurely look at the city. I found myself enjoying a water front walk from 42nd, through the Highline, to Battery Park, all the way to the Brooklyn Bridge and over to Brooklyn to a great pub for dinner. What I enjoyed most about the walk outside of the 10 miles was the community that was present. I think it is easy to get jaded by "midtown" and all that is the tourist in that area. The hustle and ...

Read More

Compiling and installing OpenSRF 2.2 on Centos 5.9

We do quite a bit of work for King County Library systems. The library system has 45 branches and runs the Open Source Evergreen ILS. One of the very smart things that the Evergreen project decided was that their database of choice would be PostgreSQL. One of the things that the Evergreen project is not good at is supporting LTS releases of Linux and therefore certain things can be a chore. For example, by default OpenSRF 2.2 which is the current stable OpenSRF release can not be installed via RPM or compiled from source by default on CentOS 5.9.

When discussing with the community about CentOS, the response was the classic responses of, "just upgrade", "move to Fedora ...

Read More

Calling Bullsh*t in Open Source communities

We are all human. We all lose our temper. We all have our moments of, "I really wish I could take that back". Of course not if you are not Linus Torvalds. Now everyone knows that Linus has a temper, that he is a foul mouth, lacks certain social graces and is generally one of the, if not the most important developers to surface in the last 20 years. Does that mean he gets to be a jerk? In his mind, yes.

In some ways I agree with Linus. If you are a donkey butt and you don't pay attention to your community and follow its guidelines, then just leave. We don't have time for you anyway. We ...

Read More

postgres_fdw for 9.2

We have backported the postgres_fdw to 9.2. It is read only of course as the infrastructure for writes is not in 9.2 but it is usable. Enjoy it!

  • Postgres-FDW
  • Read More

    The steaming pile that is Precise with kernel 3.2

    I don't know if it is a mainline kernel problem but I can tell you that on Ubuntu Precise, Linux kernel 3.2 is a disaster for PostgreSQL. I am not even going to go into a huge rant about it. I am just posting the numbers. See for yourself. There should be a public service announcement about it.

    before upgrade to 3.9

    08:35:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
    08:45:01 AM     all     30.91      0.00      5.66     40.05      0.00     23.38
    08:55:02 AM     all     29.32      0.00      5.10     39.66      0.00     25.92
    09:05:02 AM     all     31.71      0.00      6 ...

    Read More