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

Let's delete contrib!
Posted Thursday May 28th, 2015 09:50am
by Joshua Drake
| Permalink

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 this topic.

Hello,


This is a topic that has come up in various ways over the years. After the long thread on pg_audit, I thought it might be time to bring it up again.


Contrib according to the docs is:


"These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness."


It has also been mentioned many times over the years that contrib is a holding tank for technology that would hopefully be pushed into core someday.


What I am suggesting:


1. Analyze the current contrib modules for inclusion into -core. A few of these are pretty obvious:


pg_stat_statements

citext

postgres_fdw

hstore

pg_crypto

[...]


I am sure there will be plenty of fun to be had with what should or shouldn't be merged into core. I think if we argue about the guidelines of how to analyze what should be in core versus the merits of any particular module, life will be easier. Here are some for a start:



A. Must have been in contrib for at least two releases

B. Must have visible community (and thus use case)



2. Push the rest out into a .Org project called contrib. Let those who are interested in the technology work on them or use them. This project since it is outside of core proper can work just like other extension projects. Alternately, allow the maintainers push them wherever they like (Landscape, Github, Savannah, git.postgresql.org ...).


Why I am suggesting this:



1. Less code to maintain in core

2. Eliminates the mysticism of contrib

3. Removal of experimental code from core

4. Most of the distributions package contrib separately anyway

5. Some of core is extremely small use case (sepgsql, tsearch2, lo ...)

6. Finding utilities for PostgreSQL used to be harder. It is rather dumb simple teenage snapchat user easy now.

8. Isn't this what pgxs is for?

9. Everybody hates cleaning the closet until the end result.

10. Several of these modules would make PostgreSQL look good anyway (default case insensitive index searching with citext? It is a gimme)

11. Contrib has been getting smaller and smaller. Let's cut the cord.

12. Isn't this the whole point of extensions?



Sincerely,


jD



Categories:

Updating the .Org docs on backups
Posted Thursday May 21st, 2015 10:48am
by Joshua Drake
| Permalink

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 backup file
with SQL commands that, when fed back to the server, will recreate
the database in the same state as it was at the time of the dump.
The basic usage of pg_dump is:



pg_dump -C "REPLACEABLE">-F p "REPLACEABLE">-f "OPTION">outfile dbname

The use of -C ensures that the dump file
will contain the requisite CREATE
DATABASE command within the dump file. The use of "REPLACEABLE">-Fp ensures that
you are using the plain text format and the use of "REPLACEABLE">-f allows you to specify the name of the
file the dump will be written to. It is also possible for
pg_dump to create files in other
formats that allow for parallelism and fine-grained control of
object backup or restoration. For more details on all options
available to pg_dump please refer
to the pg_dump reference page.


The pg_dump application
requires read access to all objects within the database that it
will be operating with. This generally requires database super-user
access. It is possible for any database user to use pg_dump to
backup the objects that they own regardless of super-user access.
This can be achieved using options such as "REPLACEABLE">-n schema or
-t "OPTION">table.


The primary advantage of using "APPLICATION">pg_dump over the other backup methods
described is that pg_dump output
is architecture independent. A backup made with "APPLICATION">pg_dump can generally be moved between
operating systems and different architectures (32bit, 64bit, Sparc,
Intel). Whereas file-level backups and continuous archiving are
both server-version-specific.


The text files created by "APPLICATION">pg_dump are internally consistent, meaning,
the dump represents a snapshot of the database at the time
pg_dump began running.
pg_dump does not block other
operations on the database while it is working. (Exceptions are
those operations that need to operate with an exclusive lock, such
as most forms of ALTER TABLE.)




Note: Like any other "PRODUCTNAME">PostgreSQL client application, "APPLICATION">pg_dump will by default connect with the
database user name that is equal to the current operating system
user name. To override this, either specify the "REPLACEABLE">-U option or set the environment variable
PGUSER.





"ADVANCED-PGDUMP">24.1.1. Advanced pg_dump


The pg_dump application
provides other formats. The most notable are the use of "REPLACEABLE">-F c or
-F d.
The use of the custom format ( "REPLACEABLE">-F c) is an
excellent option for smaller databases when you need fine grained
control of the objects you chose to restore. The use of the
directory format ( "REPLACEABLE">-F d) allows for
parallel connection based backups. If you are performing a backup
with many objects and using pg_dump then the "LITERAL">directory format will be the most efficient. This
option also allows for fine grained control of the objects you
chose to restore.


If PostgreSQL was built on a
system with the zlib compression
library installed, the custom dump format will compress data as it
writes it to the output file. This will produce dump file sizes
similar to using gzip, but it has the
added advantage that tables can be restored selectively.



Example 24-1. Backup a single table



pg_dump -U "OPTION">user "REPLACEABLE">-h host1 -F c -f outfile -t table1 dbname



Example 24-2. Using wildcards with table list



pg_dump -U "OPTION">user "REPLACEABLE">-h host1 -F c -f outfile -t table* dbname



Example 24-3. Using parallelism and a wildcard table
list



pg_dump -U "OPTION">user "REPLACEABLE">-h host1 -F d -f outfile -t table* -j 8 dbname




Note: The use of the custom or
directory "APPLICATION">pg_dump formats requires the use of "app-pgrestore.html">pg_restore and will not work with "app-psql.html">psql. There is
more information on using "APPLICATION">pg_restore in section "backup-dump.html#ADVANCED-RESTORE-PGDUMP">Section 24.1.3.






"BACKUP-DUMP-RESTORE">24.1.2. Restoring the Dump


The psql application is the
default client that ships with "PRODUCTNAME">PostgreSQL. It is also the default application
used when restoring text based dumps created by the "APPLICATION">pg_dump application. For details information
on psql please see "app-psql.html">psql. For the
purposes of restoring a dump the basic usage is:



psql -f "OPTION">infile "REPLACEABLE">-d dbname



Note: If you omitted "REPLACEABLE">-C when executing "APPLICATION">pg_dump the CREATE
DATABASE
command will not be in the text file. You will need
to create the database yourself from "LITERAL">template0 before the executing the restore (e.g.,
with createdb "REPLACEABLE">-T template0
dbname).












Warning

pg_dump does not backup users,
roles and other global objects. To properly backup global objects
you must use "APPLICATION">pg_dumpall with the "REPLACEABLE">-g parameter. If you do not restore the
globals before the text based dump, the database will implicitly
restore all objects as the owner passed by "REPLACEABLE">-U username. If
-U is not passed then the
operating system user executing "APPLICATION">psql will be used.






Important: The dumps produced by "APPLICATION">pg_dump are relative to "LITERAL">template0. This means that any languages,
procedures, etc. added via template1 will
also be dumped by pg_dump. As a
result, when restoring, if you are using a customized "LITERAL">template1, you must create the empty database from
template0, as in the example above.




After restoring a backup, one should execute "sql-analyze.html">ANALYZE on each database so the query
optimizer has useful statistics; see "routine-vacuuming.html#VACUUM-FOR-STATISTICS">Section 23.1.3
and Section 23.1.6
for more information. For more advice on how to load large amounts
of data into PostgreSQL
efficiently, refer to Section 14.4.




"ADVANCED-RESTORE-PGDUMP">24.1.3. Advanced restore



Example 24-4. Using pipes to restore to new server



pg_dump -h "OPTION">host1 "REPLACEABLE">-d "OPTION">dbname | psql "REPLACEABLE">-h "OPTION">host2 "REPLACEABLE">-d dbname


If one is using the custom, "LITERAL">directory or tar formats
the restore command is "app-pgrestore.html">pg_restore. The "APPLICATION">pg_restore program has many benefits over the
use psql including fine grained
object restore and parallelism.



Example 24-5. Extracting a text dump from a custom format
backup


The following will extract the backup to the standard output.
The use of -F is optional as
pg_restore should be able to
detect the format.



pg_restore -F "OPTION">c infile



Example 24-6. Restoring a single table



pg_restore -U "OPTION">username "REPLACEABLE">-h "OPTION">host1 "REPLACEABLE">-d "OPTION">dbname "REPLACEABLE">-t "OPTION">table infile



Example 24-7. Using parallelism to restore databases


The use of parallelism will normally allow databases to restore
much faster than a single connection based restore. The restore
will only execute as quickly as it can restore your largest table
but for databases with many objects it is the fastest "APPLICATION">pg_dump based restore.



pg_restore -U "OPTION">username "REPLACEABLE">-h "OPTION">host1 "REPLACEABLE">-d "OPTION">dbname "REPLACEABLE">-t "OPTION">table "REPLACEABLE">-j 8 "OPTION">infile




"BACKUP-DUMP-ALL">24.1.4. Using "APPLICATION">pg_dumpall


pg_dump dumps only a single
database at a time, and it does not dump information about roles or
tablespaces (because those are cluster-wide rather than
per-database). To support convenient dumping of the entire contents
of a database cluster, the "app-pg-dumpall.html"> "APPLICATION">pg_dumpall program is provided.
pg_dumpall backs up each database
in a given cluster, and also preserves cluster-wide data such as
role and tablespace definitions. The basic usage of this command
is:



pg_dumpall > outfile

The resulting dump can be restored with "APPLICATION">psql:



psql -f infile postgres

It is necessary to have database superuser access when using a
pg_dumpall dump. The superuser
acess is required to restore the role and tablespace
information.


pg_dumpall works by emitting
commands to re-create roles, tablespaces, and empty databases, then
invoking pg_dump for each
database. This means that while each database will be internally
consistent, the snapshots of different databases are not
sychronized.


Cluster-wide data can be dumped alone using the "APPLICATION">pg_dumpall "OPTION">--globals-only option. This is necessary to fully
backup the cluster if running the "APPLICATION">pg_dump command on individual databases.




Note: If you use tablespaces, make sure that the
tablespace paths in the dump are appropriate for the new
installation.






"BACKUP-DUMP-LARGE">24.1.5. Handling Large Databases


The act of backing up a normal sized database is relatively
simple. The act of backing up a large database (>500GB) can be
challenging. Fortunately, "PRODUCTNAME">PostgreSQL is very flexible in its ability to
provide a reliable backup. Here is a list of things you might want
to consider when backing up a large database.




  1. Use the directory format and the
    -j NUM
    option. This will ensure the quickest and most flexible pg_dump
    style backup.




  2. Use continuous archiving as described
    in Section 24.2. You can
    then backup the replica without putting load on the master.




  3. Use pg_basebackupas described
    in Section
    24.2.2.












Warning

The pg_dump methods utilize at least one connection if not many
connections (via -j). They also
utilize long running transactions. This can cause problems with
maintenance. If you find that your database contains a lot of
growing bloat consider using a backup method on the master that
does not require pg_dump.








Categories: 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


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