PostgreSQL for Oracle people

Below is the video of the webinar I did recently on PostgreSQL and Oracle. This webinar went very well. This is the first time I had ever performed a webinar that I recall. It was an interesting experience.   PostgreSQL for Oracle Developers and DBA's   If you would like more information on this topic or any other topic surrounding PostgreSQL and Open Source, don't hesitate to contact us.

Read More

Do not buy the closed source lie of free videos

There is a nice lie out there. A lot of people want to believe it. They think by believing this lie it will somehow increase something for them. In some ways that is true. If you want what you are doing to be about you. If you are a believer in Open Source, it isn't about you. It is about the community and bettering that community as a whole. If we provide the videos of our sessions for free, you won't attend the conference. The PgConf US conference grows every year and guess what, they provide their videos for free. If you pay for the conference, we will provide the videos for free. LinuxFest Northwest which is larger ...

Read More

What should I submit to PgConf US 2017?

From the title, that is the question. This is the last week of the PgConf US 2017 CFP (you can submit here: and I have no idea what to submit. I am blessed that my talks are very well attended, the audience is engaged and we all have a good time. Many times laughing at me because I have a hard time staying on one specific topic (especially if someone brings a kid into the room). There is the disclaimer I have to put up on my slides because there are some in the community that can't handle humor or PG-13 content but we must all love our neighbor and enjoy them ...

Read More

Install LAPP in Containers

Install LAPP in Containers
(Linux Containers and Linux, Apache, PostgreSQL, PHP)
In this blog post I will detail how to install Apache, PHP, PostgreSQL in Linux containers on Ubuntu 16.04 LTS.
It can be desirable to isolate certain software from the rest of a system for a variety of reasons. These reasons can range, but one of the most common is security. There are a multitude of methods for isolation ranging from process sandboxing to full hardware virtualization. Regarding the former, we have a tool called chroot.
Change root, or chroot for short, has been a UNIX utility for “sandboxing” processes since 1982. Sandboxing is a general term to describe the act of executing processes outside of the root ...

Read More

Snap packages for 9.3.15, 9.4.10, 9.5.5 and 9.6.1 available

The snap packages for 9.3.15, 9.4.10, 9.5.5 and 9.6.1 are now available. To install them:
sudo snap install postgresql$version
Where $version is one of 93, 94, 95 or 96. The snap packages for PostgreSQL are a community project being lead by Command Prompt. You can visit the repo at github. To learn more about snap packages please visit the Ubuntu snapcraft developer FAQ.

Read More

Can I make initdb quiet?

A #postgresql user today asked:
noob question - trying to write a Dockerfile that runs postgres... how do I get the effect of a non-interactive `service postgresql initdb` call?
  While several other community members provided the truthful but not helpful answer of, "Just throw Docker in the Trash", I worked out the following hack/trick/snipe hunt. The answer is, you can't. You have to call initdb directly. This took a few tries because PostgreSQL does not ship -q (quiet) flag with initdb. It will always make noise even when you don't want it to. However, if you call initdb directly, pass a few flags that have nothing to do with actually being quiet and redirect STDERR then ...

Read More

psql tips: Change the location and filtering of the history file

Anyone who uses PostgreSQL knows of the best client available: psql. This is the client that ships with PostgreSQL. Yes it is a command line client (which turns some people off) but that also means that it is the most efficient at everyday tasks for a DBA. What a lot of people don't know is that psql is rather configurable. Here is an example: Problem 1: I want my history file in a place other that ~/.psql_history Problem 2: I want my history file to be per database not global Solution 1: Edit the .psqlrc file and change the history file settings \set HISTFILE ~/psql_history/.psql_history

This will put your .psql_history file into the directory psql_history under your home ...

Read More

Will Postgres use the second element of an index if it is the only element in the WHERE clause?

This is a test table from an Oracle to Postgres migration. The table has had a dozen or so columns removed for the illustration of this test case. I did not design this table but the customer is fixing it (adding proper primary key, changing to boolean and integer where appropriate etc...). Table "public.costcenter"

       Column       |            Type             |     Modifiers
  costcenterid      | numeric                     | not null
  costcenterno      | character varying(100)      | not null
  amount            | numeric(18,2)               |
  closed            | boolean                     | not null
  enteruser         | integer                     |
  phonelines        | numeric                     |
  cckeyid           | character varying(100)      |
  country           | character varying(2)        |
  level4            | character varying(100)      |
  level5            | character varying(100)      |
  level6            | character varying(100)      |
  level7            | character varying(100)      |
  level8            | character varying(100)      |
  level9            | character varying(100)      |
  latitude          | character varying(20)       |
  longitude         | character varying(20 ...

Read More

Rich in the Jungle: A AWS to Softlayer comparison for PostgreSQL

I have updated my Rich in the Jungle presentation with new pricing for AWS vs. Softlayer. Things haven't changed much, in terms of raw performance per dollar (which is not the only qualifier). Softlayer is clearly the winner.

Read More

The fall of Open Source

Once upon a time FOSS was about Freedom. It was about exposing equality within source code. It allowed everyone equal rights and equal access to the technology they were using. An idea that if you were capable, you could fix code or pay someone to fix code. An ideology that there was something greater than yourself and that there was an inherent right built into what it is to be human with software. Leaders to lemmings I sat in a bar slowly nursing beers with other community members over a period of hours. We spoke of many things. We spoke of the never-done new PostgreSQL website. We spoke of my distaste for Amazon Web Services since reformed, with the exception ...

Read More

Upgrading Ubuntu LTS and PostgreSQL

If your PostgreSQL instance is running on an Ubuntu LTS system that you need to upgrade to the most recent release, say from precise to trusty – because, well, sooner or later you must – you need to consider what is going to happen to your database. Note
The upgrade process described in this article is similar to what you would have to do if you were upgrading from Trusty to Xenial, the newest Ubuntu LTS release. Ubuntu attempts to make the process of upgrading to the newest distribution release easy and hassle-free. In fact, it is the case in many situations but not when there is PostgreSQL running in the system. If you just go ahead and try to run ...

Read More

What is good for the community is good for the company (profit is the reward)

As the PostgreSQL community continues down its path of world domination I can't help but wonder whether the various PostgreSQL companies are going to survive the changes. Once upon a time there was an undercurrent of understanding that what was good for the community was good for the company. Whatever company that may be. However, over the last few years it seems that has changed. It seems there is more prevalance toward: What is good for the company is good for the community, or in other words, "The goal is profit." That is a flawed discipline to follow in the Open Source world. A truly beneficial, strong and diverse community has to eliminate that thought entirely. The goal is ...

Read More

Simpycity 2.0.0 released (An ORM in Python)

What Simpycity Is

Simpycity is an object-relational mapper. It seamlessly maps PostgreSQL query
and function result sets to Python classes and class attributes.

It allows for the easy and rapid development of query- and
stored procedure-based data representations. Simpycity leverages PostgreSQL's
powerful composite type system, and the advanced type handling of the psycopg2
database access library.

What Simpycity is Not

Simpycity is not a SQL generator and does not attempt to abstract or hide SQL.
Simpycity is designed for developers who deeply understand SQL and
desire to write the best possible SQL representations for their database.
Simpycity also rejects the Active Record paradigm, whose simplistic patterns
fail in even moderately complex systems.

Core Philosophy

The core philosophy behind Simpycity ...

Read More

PgConf.US: 2016 Kicking the donkey of PostgreSQL Replication

My slides from my presentation and PgConf.US 2016:  

Read More

Spreading the conference love

The PostgreSQL community has a lot of conferences in the United States:
  • PgUS United States PostgreSQL Conference 
  • Citus Data PgConfSV
  • PgUS SCALE PgDay (which as of 2016 is really a conference within a conference)
  • PostgresOpen
  • EDB PostgresVision
And that doesn't come even close to the number of various conferences in Europe.

As Bruce Momjian pointed out in his excellent blog this is a good thing. It is true that in the United States there is the big boy on the block and it will likely hit 600 people in 2017 but other than that the conferences are all small and regional. The exception is PostgresVision which hasn't yet run and therefore we don't know how many people ...

Read More

You are my fellow community member

I attended the fantastically presented PgConf US 2016 last week. An amazing conference, my training was well attended, my talk was at capacity, the 20th Anniversary Party was phenomenal and the conference raised money for an excellent cause. There were over 435 attendees, giving our brothers and sisters at PgConf EU something to work for during their conference in November. While attending the hallway track, I was talking to a gentleman whose name escapes me. He asked me how he could contribute to the community. I am always excited to have that conversation because we are able to discuss all kinds of different ways to contribute, whether it be social (user groups, pgday, speaking at alternative conferences), documentation, code, or ...

Read More

PostgreSQL, FOSS, SCALE, NYCPUG, SPI, LFNW and Ruby oh my!

Three weeks ago I was in Pasadena for SCALE 14. I had over 100 people in my room as I blistered the behind of PostgreSQL and how it handles backups. If you are interested in seeing my considered opinion I will also be training on the same topic at PgConf.US. I am also speaking on PostgreSQL Replication and finally, I was told that I am running the Lightning Talks. I have never coordinated something like Lightning Talks before. It should be an interesting experience. If that wasn't enough news, I have more! The Ruby community has adopted the draft PostgreSQL Code of Conduct. As one of the primary authors of that document, I am honored to have such ...

Read More

.Org developer meeting @ FOSDEM

A lot of people probably don't know this but PostgreSQL does plan. It is true that we take all contributions and they are reviewed based on their merit but it is also true that the community tries very hard to have a road map of some sort. Those road maps are created by the more prolific contributors in the community. In the past there was a yearly Developer Meeting. That meeting would take place at PgCon. PgCon is held in May at the University of Ottawa, Canada. It is a small but great developer conference. This year we are going to have two plus probably an informal one for a total of three. The first of which is taking ...

Read More

Scale 14x, PostgreSQL mini-conf, PgConf.US and NYCPUG

It is really not fair to call it a mini-conf. The Scale 14x, PostgreSQL Day attendance was larger than every conference except PgConf.US (EDIT: in the United States/Canada). It is a great opportunity to integrate with a wider community that is diverse, technologically capable and at the front lines of production installations. I spoke on Backups: The Good, the Bad, and the Ugly. I had over 100 attendees in my room. It was obvious throughout the talks that this is going to be the new West coast conference for PostgreSQL. The opportunity for advocacy and integration into alternative technologies is just too great to ignore from either PostgreSQL or the wider FOSS community that attends SCALE. This is ...

Read More

13:58, the sun is shining, the sky is blue, and I just had the best tomatoes -- ever. Welcome to Vienna and

I am sitting in a glorious (although not the conference) hotel, writing this article. It is 13:58, at least it is where I am from, the great Pacific Northwest. I must admit, I miss the trees although I hear there are lots of them if you leave the city. What is there to say about this great city that the European community picked for their latest conference? First and probably obvious, it is full of history. Second, don't bother doing anything on a Sunday. Third, smoking is allowed in restaurants (one ridiculous thing I have noted). Fourth, they sell water that has, "Natural Oxygen". Fifth, it feels a lot like Paris but the people seem friendlier. Lastly, this ...

Read More

PostgreSQL 9.5, Community, Features and More!

I spoke at the Whatcom PUG meeting last night on PostgreSQL 9.5. This is my fourth time giving this talk. The previous locations were DCPUG, PhillyPUG, and NYCPUG last month. The talk was well received and this was Whatcom PUG's best turn out yet! We even had an Open Street Map developer visit from Vancouver B.C. The presentation does discuss some of the more popular features of 9.5, but as a whole it discusses the state of PostgreSQL as of 9.5. That includes features, community, and process. I think the most important item is the user interaction. At each presentation location I brought up the fact that PostgreSQL has no bug/issue tracker. This led ...

Read More

Tip for West side U.S. folks going to PgConf.EU in October

This tip works very well for me because of my physical location (Bellingham, WA) but it would also work reasonably well for anyone flying from Denver->West Coast including places such as Houston. It does take a little bit of patience though. A normal trip for myself would mean driving down to SEA which is 90 minutes to 2 hours. This year, I decided on whim to see what it would take to fly out of YVR (Vancouver, B.C.) which is only 60 minutes driving. Since I would be flying out of YVR on a non-connecting flight, I paid Canadian Dollars. For those that haven't been paying attention, the U.S. dollar has been doing very well lately ...

Read More

Elevating your confidence with the Elephant's restoration capabilities

In the beginning There was Unix, Linux and Windows. They all run on hardware and that hardware all has bugs. What is the best way to work around hardware bugs? Backups. You haven't had bad hardware, only bad developers? That's o.k., we have a solution to them too. It is called backups. You haven't had bad hardware or bad developers, just bosses who still demand to have direct access to the data even though they haven't proven an ability to extract useful information without an extreme amount of hand holding? That's o.k., we have a solution to them too. It is called backups. You haven't had any of the above? Lucky you ...

Read More

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