As you may know, Command Prompt, Inc. develops and maintains PostgreSQL snap packages as a service to community. If you used it, you also know that unfortunately it is not yet a drop-in replacement for DEB builds distributed via PGDG APT repository.

PostgreSQL is one of the major open source projects out there that is extremely popular with all sorts of crowds: from enthusiasts to unicorn startups. And as the Snapcraft ecosystem matures more and more, a fully-functional PostgreSQL snap package becomes absolutely necessary for reasons ranging from developers being able to easily run PostgreSQL in strictly isolated test environments, to providing more complex software solutions that build on PostgreSQL (e.g. Travis CI people expressly stated they were very ...

One of my favorite annual technology events takes place each October in my hometown of Austin, Texas -- Innotech Austin. I've attended for several years in support of local nonprofit groups including Chick Tech Austin and Austin Women in Technology, as well as for Command Prompt Inc. I thoroughly enjoy the networking opportunities and the professional yet relaxed atmosphere of this event. While attending and exhibiting at Innotech Austin earlier this month, I met several potential partners in supporting PostgreSQL not only for users, but also for students aspiring to a career in full stack and open source development.

This year Innotech has expanded to three other major metroplexes and tech hubs in Texas -- San Antonio, Dallas/Fort Worth, and Houston ...

At PostgresConf US 2018, Bruce Momjian, Grant Zhou, and I had a meeting to discuss potential opportunities for the Chinese PostgreSQL community to participate in the wider International community, including submitting patches to PostgreSQL.Org. Then at Postgres Open China the International Consultants Committee had a meeting to discuss more opportunities in depth. Between the two meetings there were a lot of ideas but one opportunity that was considered needs further discussion:

There are many volumes within the PostgreSQL community. Email volume (over 3000 emails to -hackers alone in the first 6 months of 2018), side channel volume, idea volume, and, arguably most important, code volume. The investment someone has to make in order to submit a feature to the ...

I spent the week of June 25th in Bejing, China with the outstanding Chinese Open Source and Postgres Communities. I was there to speak atboth Postgres Open China and the China Open Source World conferences as well as participate in a Chinese Open Source panel and the International Consultants committee meeting, of which I am the President. This was my first trip to Asia and it was amazing. The Chinese culture, hospitality, and friendliness was unparalleled, as was their drive to be more influential and helpful to the International Open Source and Postgres communities.

The entire week was spent trying to answer the question, “How can China participate more thoroughly in the International Open Source and Postgres communities?” We ...

We launched the Silicon Valley Postgres Meetup.

We have reached 401 members in what is proving to be one of the fastest growing Postgres meetups in the United States. We launched the meetup along with Vancouver B.C., Denver, Salt Lake City, and Phoenix.

Between these and other meetups we help organize such as New York, Philly, and Dallas, we are reaching more people than ever in education, advocacy, and applicability of Postgres!

Why is this important? The majority of potential Postgres contributors are not part of the internal network of PostgreSQL.Org and other international organizations. They are developers, users, consultants, companies, project managers, documentation writers, etc. These professionals ...

PostgreSQL V10: An Amplified Version Of PostgreSQL

For more than a decade, the Postgres community has released new major versions almost annually to meet the evolving needs of the Database Industry. The first great release of Postgres was 8.3 in 2008, with a clean and evolutionary release of 8.4 eighteen months later.
The 2017 v10 release of Postgres is the first version that Command Prompt Inc. founder and lead consultant Joshua D. Drake would consider truly, “Enterprise Ready.” Read more

Command Prompt Inc. Joins the AWS Partner Network

With the recent advancements of AWS PostgreSQL instances and their Database Freedom initiative alluded to in Andy Jassy’s AWS re:Invent 2017 Keynote, more and more AWS customers are ...

Customers often ask us what is the correct setting for max_connections on their PostgreSQL cluster?  There is a short answer to this question, and there is a very, very long answer. The short answer is: accept the default if that works for you, or try 10 times the number of CPU cores and see if that works ok.

There is another short answer which is: you may be asking the wrong question.

The original implicit assumption is "more connections will get me more throughput". This is often false. A question usually worth pursuing is "How can my existing set of connections be used efficiently?"

If you think that your application wants more connections and you are considering raising max_connections ...

I leave this Sunday for beautiful and ecclectic Austin, Tx. I will be providing the training Postgres Performance and Maintenance as well as speaking on The Power of Postgres Replication. The training is one that I give several times a year but the Replication talk is new. Although I have spoke on replication before, this new presentation is all about the power of Logical Replication. If you would like to learn more, pick up a ticket and let's have some fun. If you can't make it to Austin, perhaps you can make it to the PGConf Mini: NYC on December 14th. I will be presenting the same Replication presentation at that event. Let's bring about the new ...

For more than a decade, the Postgres community has released new major versions almost annually to meet the evolving needs of the Database Industry. The first great release of Postgres was 8.3 in 2008, with a clean and evolutionary release of 8.4 eighteen months later. The 2017 v10 release of Postgres is the first version that I would consider truly, “Enterprise Ready.”

PostgreSQL has seen great success in the commercial enterprise for years. However, that success has largely been relegated to departmental success; e.g. the installation is solving a specific problem for a specific department. Often it’s the public facing data source for a larger Oracle instance or to manipulate GIS data. In these roles, PostgreSQL ...

Jim Mlodgenski in attendence at the 9th Annual PGConf.EU and I am working hard on updating my slides for my presentation at PGConf Seattle. PGConf Seattle is being held at the downtown Seattle Sheraton on November 13th and 14th.  I will be speaking on Postgres version 10. I will also be training on Postgres Performance and Maintenance (you can buy your ticket here). If you are planning on attending my training or any of the others I recommend that you register quickly. There are only half a dozen seats left for mine and only a few more than that for Robert Bernier's.

The Postgres V10 presentation is the same presentation I gave at PGConf Ohio as well as ...

Many argue which is better: PostgreSQL or MySQL. A recent post by MySQL evangelist and community manager Frederic Descamps prompted some criticism of the amount of time that it took for a particular bug to be fixed -- 14.5 years to be exact, from the initial report.

There’s a long list of technical and performance comparisons, but here’s the number one reason to choose Postgres over MySQL.

The PostgreSQL community is different than MySQL. First, MySQL is not a project; it is a product. Yes, they are both Open Source but Postgres is vendor neutral, contributor centric, and not controlled by any single entity. Using a product or project defines the culture you are embracing when you ...

Here is a fun and useful Linux console hack you might like.

If you use ssh A LOT, maybe you encounter situations where you have a shell that is three or more hops removed your local workstation. And maybe you've been in the situation where there is some file that you need to get from "there" to "here" or vice versa. And you really are annoyed by the thought of scp-ing the file over 3+ hops (and not so lucky that all the keys and permissions in the pipeline enable easy piping through ssh).

Lucky you! In a moment of inspiration, I concocted this ultra-unixy hack to copy the file in ONE hop (more or less). Assuming the file ...

One of the fantastic characteristics of Postgres leaders is their willingness to serve the community.

Yesterday I found out that one of our former team members, Devrim Gunduz, has created a new London PostgreSQL user group and they had their inaugural meeting in October. At the time of this writing there were 123 members in the group. This level of response shows a great demand for Postgres content. I spoke with Devrim yesterday, and he shared that he has a mission to provide the London community with new content each month. It is a bold goal as running meetups that frequently can be daunting, but we believe there is enough support in the area to warrant this frequency. Devrim has ...

Here I'm posting a bash script that implements PostgreSQL's new (since 9.6) "non-exclusive" base backup.

I often find that new customers are shy about binary PostgreSQL backups and only schedule logical pg_dump backups. PostgreSQL provides a brilliantly simple binary backup solution that enables easy point in time recovery, unlike pg_dump backups. So I always try to steer people towards these binary "base backups", as they are called in the PostgreSQL world.

In version 9.6, this base backup feature became more flexible, with an additional option allowing more than one base backup to run at a time. For customers with aggressive (or badly scheduled) backup schedules, this allows both backups to succeed, where they would both fail ...

The first meeting of the Silicon Valley Postgres Meetup was last night. Amazon Web Services sponsored the facilities in Cupertino and Roland Lee from Hemdalldata presented on:

How to Auto-Cache Postgres with no code changes.

There were about 20 people in attendance as well as another half a dozen that participated via Amazon Chime. Debbie Cerda, our Director of Business Development flew out from Austin, Tx to host. When we launched the Silicon Valley Meetup we wanted to ensure that it would not conflict with the well respected San Francisco PUG. Based on initial response, there is not a conflict and we are very happy about that. We attribute the lack of conflict two items:

1. San Francisco is not part ...

"I'm not technical enough to even understand what your firm does."

This message from a new LinkedIn connection last week. The message literally stopped me in my tracks. I was preparing to spend the day amongst new and seasoned developers at DevDay Austin, a free full-day technical event hosted by Amazon Web Services. Cloud computing, IoT, Containers, Artificial Intelligence, and Mobile application development were just a few of the topics to be covered. 

Ironically, I'd spent some time the day before trimming my LinkedIn profile summary, focusing on my primary career in business development for Command Prompt, Inc. 

I've managed a portfolio career approach for several years, working in the craft beer and film industries ...

Postgres: The center of your data universe

This talk is proving to be great content for those who are not necessarily Postgres Users. Last night I presented this talk at the Seattle Web Developers Meetup. The location was Adobe, next to Google and Tableau. I didn't even know there was a small tech complex on N. 34th in Seattle. There were about 27 people, which falls in line with the 50% rule of Meetups[1]. Here are my observations from the audience:

1. Surprisingly the majority of developers were Python web developers (as a group)
2. Many of them do not use Postgres and wanted to hear more about it
3. There were several government employees that were wondering how to get ...

Now that summer is over and we have officially decided never to schedule anything in August again, we need to share a bunch of upcoming community goodness!

Never lose sight of the goal

• Postgres the center of your data universe at the Seattle Web Developers meetup on September 14th. This is an updated presentation that I gave at Datalayer last May. I am adding some goodies specifically for Web developers.

• PGConf US and NYCPUG are hosting: PGConf US Mini: NYC tomorrow! Bruce Momjian will be speaking on Postgres v10. This is the second mini that PGConf US has organized. The first was last May in Austin. It looks like a great lineup and turnout.

• I ...

Overview

Usually we want to test before deploying changes to a production Postgresql cluster. Commonly the test itself is executed in a context that is not very similar to the production environment. How can you run a test that is realistic so there are no horrible surprises when you deploy on the production system?  Read on! This document describes a procedure where changes can be tested on a system that is running a load that is nearly identical to a production system.

This procedure utilizes pgreplay, which reads postgres' server logs and executes the sql statements found there, with the same timing that they were executed on the production system.

This procedure also uses lvm and its volume snapshot ...

If you are looking for all the skinny on Postgres v10, I have just the meetup for you. I will be giving my presentation: Postgres V10: An Amplified version of PostgreSQL tonight at a joint meeting of Vancouver Developers Network and Vancouver Postgres Meetup. Be there or be square.

In this presentation I go over all the major features in Postgres v10 including but not limited to:

1. BigData
2. Replication and Scaling
3. Administration
4. SQL Features
5. XML and JSON

Join the meetups, its fun and will make you a better human.

• Vancouver Postgres Meetup
• Vancouver Developers Meetup

After much deliberation with the CMD community team we have launched the Denver Postgres User Group! We hope that our community in Denver, Boulder, and Colorado Springs will join us at upcoming events and submit content. It has been a long time since we have had an active Denver group and Denver is a hot bed for Postgres external development. Our first meeting will be announced soon and should be expected in October. Have ideas on facilities or content? Please contact us on the meetup page. We would love to see 3 - 4 meetings before PGConf US National!

That is how this all started. A gentleman tweeted about a Postgres limitation that Oracle has not had since at least since Oracle 7.3. 

As you can see in the tweet, Postgres by default will not defer a PRIMARY KEY check. Without the check being deferred the following will not work:

postgres=# select * from demo; 
 id  
---- 
  1 
  2 
(2 rows)
postgres=# alter table demo add primary key(id);   
ALTER TABLE 
postgres=# begin; 
BEGIN 
postgres=# update demo set id=id-1; 
UPDATE 2 
postgres=# update demo set id=id+1; 
ERROR:  duplicate key value violates unique constraint "demo_pkey" 
DETAIL:  Key (id)=(1) already exists.

The solution, as mentioned ...

For most workloads the Postgres Autovacuum daemon works just fine. You go about your day with 3 workers that wake up once a minute to make sure that everything is nice and tidy. If things are dirty enough (around 10%) then one of the workers gets in gear and cleans things up. Unfortunately, if you have an inverted load from the norm, Autovacuum may not be able to keep up and you will suffer increased fragmentation and bloat.

The most common workload for Postgres (especially web based apps) includes many reads and some writes. It is usually somewhere around 75-95% reads and 5-25% writes. Assuming an average database, Autovacuum will keep up just fine and with tuning will ...

A recent poll was conducted @amplifypostgres to determine where the Postgres community should have its interactive communication. Options included were Google Hangouts, Slack, Reddit or “Other”.

The results were not surprising, with Google Hangouts beating Slack with 157 votes cast. There were also notable mentions of IRC, and Gitter. A couple of long time Postgresql.Org members asked the inevitable, “What is wrong with IRC?” Of course there is nothing wrong with IRC but when you tell many community users to use IRC, the most common response is “IRWhat?” which is either a sign of disdain or ignorance depending on the user.

The problem and what is driving this post was an additional comment made by a long time community ...

You may ask yourself, how did I get here?

The journey to this place in my professional career as the newly hired Director of Business Development for Command Prompt, Inc. is a long and winding one, and so i'd like to share a couple of stories to enlighten curious folks:

“Why Postgres?!”

Last September the fledgling consulting firm that I was handling sales and business development for was shuttering, and my best bud and business colleague Jim Nasby and I were on the market for job opportunities. Word had gotten out, and I had an introductory call with a potential employer. An advantage of my former position at Blue Treble was the ability to engage regularly in the ...