Mammoth
  • |
  • Contact
  • |
CMD | Command Prompt, Inc. - PostgreSQL Solutions, Support & Hosting
  • |
  • |
  • |
  • |
  • |
A new user discovers the PostgreSQL public schema
Posted Tuesday Jun 2nd, 2015 12:16pm
by Joshua Drake
| Permalink

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;
CREATE ROLE
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 help.
postgres=> create table bar (id text);
CREATE TABLE
postgres=> 


What? Yes. I connected to the postgres database as the unprivileged user foo. I then proceeded to create a table and as I own that table, I can insert data into that table. If we continue the example:


postgres=# \z bar
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges 
--------+------+-------+-------------------+--------------------------
 public | bar  | table |                   | 
(1 row)


As noted above there are zero access privileges on the table bar. Now let's create a new unprivileged user, baz.


postgres=# create user baz;
CREATE ROLE
postgres=# \q


Now we reconnect as the user baz and try to insert data into table bar.


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

postgres=> insert into bar values ('1');
ERROR:  permission denied for relation bar
postgres=> 


The error represents exactly what should happen but I could just create a new table as user baz and start adding data. As a very simple example of why this could have undesirable results, try this as the user foo:


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

postgres=> insert into bar values (generate_series(1, 1000000000));


1. postgres db permissions


Categories: Business, OpenSource, PostgreSQL, Python, SQL

WhatcomPUG meeting on 04/21. Start date, end date, calculate
Posted Thursday Apr 23rd, 2015 09:27am
by Joshua Drake
| Permalink

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 queries much, much faster. Either he or I will be submitting a blog post on that feature alone.


If you are interested in seeing what he has to say you can visit us at the Whatcom PgDay being hosted at LinuxFestNorthwest this weekend!


Categories: Business, OpenSource, PostgreSQL, Python, SQL

Reflections on PgConf.US 2015
Posted Monday Apr 20th, 2015 11:20am
by Joshua Drake
| Permalink

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. I only spoke at it, Command Prompt sponsored, and I occasionally offered feedback to the conference committee (as it is run by PgUS) via PgUS board meetings. This conference has become everything (and more) I tried to make the United States PostgreSQL Conference series. It is a conference of the best, the brightest and most importantly the users of our beloved elephant. In the United States, it is "The" PostgreSQL Conference.


That isn't to say there aren't other PostgreSQL conferences in the states. There are at least two others that run, but somehow after this latest conference I feel they are destined to niche attendance. There is nothing wrong with that and frankly we need the niche conferences. They fill a hole, else people wouldn't attend. It is just that experiencing the level of effort and greatness that was created by Jonathan and Jim was truly something awesome to behold.


They aren't paid to run these conferences. They do it because they want to help our community. They do it for free and speaking as somebody who has run more PostgreSQL conferences than any other current PostgreSQL conference organizer in the U.S., it is a thankless job. It is a volunteer effort that everyone should take a moment to thank them for. Jonathan, Jimmy: Thank you for all your efforts. The community could not have had such a great conference without you.


I have only two constructive feedback points for the organizers:


  1. Do not allow tutorials based on sponsorship. Allow them based on merit. You will get a much more positive return from them.
  2. Move the cocktail party offsite where there will be full food available at a more reasonable price.

I have three constructive feedback points for other sponsors:

  1. Plan your events in conjunction with the conference. Don't take attendees away from a conference for your invitation only event.
  2. Don't over staff your booths. Even a large both (10x10) only has room for ~ 3 people.
  3. Lighten up. This is supposed to be fun.


Other than that, I have only praise for the conference. Command Prompt has already made back all of the money it spent on sponsoring and traveling. We acquired quite a few new customers and are exceedingly happy with the result. Expect us to sponsor again.


You should also expect that I will speak again. I gave away three excellent bottles of whiskey to attendees of my presentation. I enjoy speaking at this conference because this conference understands what a conference is really about.


What else would I say about the conference? I had a blast. I would definitely go again. I will do everything I can to help the excellent organization team grow not just 25%, but 50% next year. What if the community stepped up and planned on attending "PgConf U.S." in 2016? The community should step up, promote and advocate throughout every community they are involved in. It's worth it.


Let's get more of everyone involved and save them from the nefarious reaches of those "other" databases.


Categories: Business, OpenSource, PostgreSQL, Python, SQL

WhatcomPUG meeting last night on: sqitch and... bitcoin friends were made!
Posted Wednesday Mar 18th, 2015 09:28am
by Joshua Drake
| Permalink

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 hottest middle aged technology for their database, PostgreSQL. It was great to see that they are also located in Whatcom County. The longer I am here, the more I am convinced that Whatcom County (and especially Bellingham) is a quiet tech center working on profitable ventures without the noise of places like Silicon Valley. I just keep running into people doing interesting things with technology.


Oh, for reference:


  • Twitter: @coinbeyond
  • Facebook: CoinBeyond
  • LinkedIn: Linkedin



    Categories: Business, OpenSource, PostgreSQL, SQL

  • Stomping to PgConf.US: Webscale is Dead; PostgreSQL is King! A challenge, do you accept?
    Posted Tuesday Mar 17th, 2015 08:35am
    by Joshua Drake
    | Permalink

    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, the abstract was submitted on the fly, improvised and in one take. Guess which talk was accepted? "Webscale is Dead; PostgreSQL is King!". They changed the first sentence of the title which is absolutely acceptable. The conference organizers know their audience best and what should be presented.


    What I have since learned is that the talk submission committee was looking for dynamic talks, dynamic content, and new, inspired ideas. A lot of talks that would have been accepted in years past weren't and my attempt at humor fits the desired outcome. At first I thought they were nuts but then I primed the talk at SDPUG/PgUS PgDay @ Southern California Linux Expo.


    I was the second to last presenter on Thursday. I was one hour off the plane. I was only staying the night and flying home the next morning, early. The talk was easily the best received talk I have given. The talk went long, the audience was engaged, laughter, knowledge and opinions were abound. When the talk was over, the talk was given enthusiastic applause and with a definite need for water, I left the room.


    I was followed by at least 20 people, if not more. I don't know how many there were but it was more than I have ever had follow me after a talk before. I was deeply honored by the reception. One set of guys that approached me said something to the effect of: "You seem like you don't mind expressing your opinions". At this point, some of you reading may need to get a paper towel for your coffee because those that know me, know I will readily express an opinion. I don't care about activist morality or political correctness. If you don't agree with me, cool. Just don't expect me to agree with you. My soapbox is my own, rent is 2500.00 a minute, get in line. I digress, what did those guys ask me about? Systemd, I don't think they were expecting my answer, because I don't really have a problem with Systemd.


    Where am I going with this post? I am stomping my way to PgConf.US with an updated version of this talk (You always learn a few things after giving a performance). I am speaking in the first slot on Friday and I am going to do everything I can to bring it. I can't promise to be the best, I can promise to do everything in my power to be my best. I am being recorded this time. My performance will be on the inner tubes forever. I have no choice.


    A challenge, do you accept?


    I challenge all speakers at this voyage of PgConf.US to take it up a notch. If you were accepted, you have a responsibility to do so. Now, now, don't get me wrong. I am not suggesting that you put on a chicken suit and Fox News t-shirt to present. I am however suggesting that if you are a monotone speaker, try not to be. If you are boring, your audience will be bored and that is the last thing the conference, you or the audience wants. So speak from your diaphragm, engage the audience and make their time worth it!


    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    PostgreSQL is King! Last week was quite busy being a servant.
    Posted Wednesday Feb 25th, 2015 10:13am
    by Joshua Drake
    | Permalink

    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 fish to brighten the week. Once back in Bellingham, we moved on to a WhatcomPUG meeting where I presented, "Practical PostgreSQL: AWS Edition". It was the inaugural meeting but was attended by more than just the founders which is a great start!


    I got to rest from community work on Wednesday and instead dug my head into some performance problems on a client High Availability Cluster. It is amazing that even with proper provisioning how much faster ASYNC rep is over SYNC rep. Some detailed diagnosis and proving data demonstrated, we switched to ASYNC rep and all critical problems were resolved.


    On Thursday it was off to Southern California Linux Expo where I presented, "Suck it! Webscale is dead; long live PostgreSQL!". The room was packed, people laughed and for those who might have been offended, I warned you. Your offense is your problem. Look inside yourself for your insecurities! All my talks are PG-13 and it is rare that I will shy away from any topic. My disclosure aside, I had two favorite moments:



    1. When someone was willing to admit they hadn't seen Terminator. I doubt that person will ever raise his hand to one of my questions again.
    2. When Berkus (who knew the real answer) suggested it was Elton John that wrote the lyrics at the end of the presentation.


    After I spent the evening with JimmyM (BigJim, my brother), Joe Conway of SDPUG/Credativ , Jim Nasby of the fledgling bird that is Blue Treble and the very enjoyable, I don't remember her name but she works at Enova (a well known PostgreSQL installation). Flying out the next morning at 8am probably should have been avoided though.


    I am glad to be on the ground for the next few weeks before I head off to PgConf.US. It is looking like this conference is once again prove why PostgreSQL is King! Bring your people from all the lands, you are about to enter utopia.


    Categories: Business, OpenSource, PostgreSQL, SQL

    AWS performance: Results included
    Posted Wednesday Nov 12th, 2014 09:00am
    by Joshua Drake
    | Permalink

    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 the graphic):

    port = 5432                             
    max_connections = 500                   
    ssl = true                              
    shared_buffers = 4GB                    
    temp_buffers = 8MB                      
    work_mem = 47MB                         
    maintenance_work_mem = 512MB            
    wal_level = hot_standby                 
    synchronous_commit = on         
    commit_delay = 0                        
    commit_siblings = 5                     
    checkpoint_segments = 30               
    checkpoint_timeout = 10min              
    checkpoint_completion_target = 0.9      
    random_page_cost = 1.0                  
    effective_cache_size = 26GB
    


    Each test was run using pgbench against 9.1 except for configuration 9 which was 9.3:

    pgbench -F 100 -s 100 postgres -c 500 -j10 -t1000 -p5433


    Here are some of our latest findings:






    The AWS configuration is:


    16 Cores

    30G of memory (free -h reports 29G)

    (2) PIOPS volumes at 2000 IOPS a piece.

    The PIOPS volumes are not in A RAID and are mounted separately.

    The PIOPS volumes are formatted with xfs and default options

    The PIOPS volumes were warmed.


    1. Configuration 1:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = on


    2. Configuration 2:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = off


    3. Configuration 3:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = off

      commit_delay = 100000

      commit_siblings = 50


    4. Configuration 4:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = off

      commit_delay = 100000

      commit_siblings = 500


    5. Configuration 5:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = off

      commit_delay = 100000

      commit_siblings = 500


    6. Configuration 6:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = on

      commit_delay = 100000

      commit_siblings = 500


    7. Configuration 7:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = on

      commit_delay = 0

      commit_siblings = 5


    8. Configuration 8:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = on

      checkpoint_segments = 300

      checkpoint_timeout = 60min


    9. Configuration 9:

      $PGDATA and pg_xlog on different partitions

      PostgreSQL 9.3

      synchronous_commit = on

      checkpoint_segments = 300

      checkpoint_timeout = 60min




    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    Don't kill yourself
    Posted Tuesday Oct 7th, 2014 10:11am
    by Joshua Drake
    | Permalink

    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 your
    expertise for 30.00/hr from India" or my favorite: calling you directly when
    it is after hours to "chat".


    How are these not bad clients? They are not bad clients because it is you that
    controls the relationship with the client. You as the consultant have to set
    proper boundaries with the client to insure that the relationship as a whole
    is positive and profitable. If you can't manage that relationship you have two
    choices:



    1. Hire someone who can
    2. Fire the client


    Woah! Fire the client? Yes. Terminate the relationship with the client.


    It is always amazing to me how many people can't fathom the idea of firing a
    client. It is always some sacred vow that a client can fire you but you are
    left holding the bag, somehow that bag is filled with the feces of some
    dog and you are expected to light it on fire and leave it on the porch of
    some unsuspecting high-school football coach.[1]


    The counter argument to this is usually "I need the money". This is
    a valid argument but do you need the money so badly that you are willing to
    sacrifice your health or your relationships? It is astonishing how many
    consultants are willing to do exactly that. In the words of the legendary
    band Big Fun, "Suicide, don't do it"[2].


    The better you manage a client, the better the relationship. Good luck!


    1. http://en.wikipedia.org/wiki/All_the_Right_Moves_(film)

    2. https://www.youtube.com/watch?v=i-w1GeH8KPU



    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    Along the lines of GCE, here are some prices
    Posted Thursday Sep 18th, 2014 01:38pm
    by Joshua Drake
    | Permalink

    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

    16 CPUS

    60G Memory

    2500GB HD space


    GCE: 763.08

    16 CPUS

    104G Memory

    2500GB HD space


    Amazon: 911.88

    16 CPUS

    30G Memory

    3000GB HD Space


    Amazon: 1534.00

    r3.4xlarge

    16 CPUS

    122.0 Memory

    SSD 1 x 320

    3000GB HD Space


    Amazon: 1679.00

    c3.8xlarge

    32 CPUS

    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

    RAID 10

    4TB (4 2TB drives)

    48GB Memory



    Softlayer: ~1035 (with 72GB memory ~ 1150)

    16 Cores

    RAID 10

    3TB (6 1TB drives, I also looked at 8-750GB and the price was the same. Lastly I also looked at using 2TB drives but the cost is all about the same)

    48GB Memory


    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    GCE, A little advertised cloud service that is perfect for PostgreSQL
    Posted Monday Sep 15th, 2014 09:48am
    by Joshua Drake
    | Permalink

    Maybe...


    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 we in our right minds can think up) is that GCE is current limited to 16 virtual CPUS and 104GB of memory.


    What does that mean? Well it means that it is likely that GCE is perfect for 99% of PostgreSQL workloads. By far the majority of PostgreSQL need less than 104GB of memory. Granted, we have customers that have 256GB, 512GB and even more but those are few and far between.


    It also means that EC2 is no longer your only choice for dynamic cloud provisioned VMs for PostgreSQL. Give it a shot, the more competition in this space the better.


    Categories: Business, OpenSource, PostgreSQL, Python, SQL


    Copyright © 2000-2015 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.