CMD: Joshua Drake's Blog Command Prompt Blog Feed: Joshua Drake's Blog Thu, 09 Jul 2015 11:33:15 -0700 Command Prompt Mammoth 46 46 Thu, 09 Jul 2015 11:33:15 -0700 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. <p> 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. <p> 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 (even overtaking the euro on some days). For example, the Canadian dollar is 27% cheaper right now than the U.S. dollar and thus my flight was 27% cheaper. <p> You can't connect to YVR, you must fly out of YVR. Therefore if you are in the aforementioned areas, you would fly into Seattle or Bellingham and then drive to YVR to connect to a new flight. Be patient and give yourself enough time (to not miss your flight), and you are going to save a lot of money. <p> Cheerio and make sure you <a href="">register for my class!</a> Business OpenSource PostgreSQL Thu, 02 Jul 2015 10:49:45 -0700 Elevating your confidence with the Elephant's restoration capabilities <strong>In the beginning</strong> <p> 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. <p> You haven't had bad hardware, only bad developers? That's o.k., we have a solution to them too. It is called backups. <p> 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. <p> You haven't had any of the above? Lucky you! Can we move to your country or will you share whatever it is that you are putting in your tea? It sounds fantastic. <p> Either way, we have a solution to your data confidence, it is called backups and that is what this training is about. <p> <a href="">See more here!</a> <p> <a href="">Register here!</a> Business OpenSource PostgreSQL Python SQL Tue, 02 Jun 2015 12:16:55 -0700 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: <p> <pre> postgres@sqitch:/# psql -U postgres psql (9.2.11) Type "help" for help. postgres=# create user foo; CREATE ROLE postgres=# \q </pre> <p> No biggy, we created a user foo as the super user postgres. All is good. However, what can that user foo do? <p> <pre> postgres@sqitch:/# psql -U foo postgres psql (9.2.11) Type "help" for help. postgres=> create table bar (id text); CREATE TABLE postgres=> </pre> <p> 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: <p> <pre> postgres=# \z bar Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | bar | table | | (1 row) </pre> <p> As noted above there are zero access privileges on the table bar. Now let's create a new unprivileged user, baz. <p> <pre> postgres=# create user baz; CREATE ROLE postgres=# \q </pre> <p> Now we reconnect as the user baz and try to insert data into table bar. <p> <pre> 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=> </pre> <p> 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: <p> <pre> postgres@sqitch:/#psql -U foo postgres; psql (9.2.11) Type "help" for help. postgres=> insert into bar values (generate_series(1, 1000000000)); </pre> <p><p> 1. <a href="">postgres db permissions</a> Business OpenSource PostgreSQL Python SQL Thu, 28 May 2015 09:50:12 -0700 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 <a href="">read about that here.</a> 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. <blockquote> Hello, <p> 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. <p> Contrib according to the docs is: <p> "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." <p> 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. <p> What I am suggesting: <p> 1. Analyze the current contrib modules for inclusion into -core. A few of these are pretty obvious: <p><p> pg_stat_statements <br /> citext <br /> postgres_fdw <br /> hstore <br /> pg_crypto <br /> [...] <br /> <p> 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: <p> <blockquote> A. Must have been in contrib for at least two releases<br /> B. Must have visible community (and thus use case)<br/> </blockquote> <p> 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, ...). <p> Why I am suggesting this: <p> <blockquote> 1. Less code to maintain in core <br /> 2. Eliminates the mysticism of contrib <br /> 3. Removal of experimental code from core <br /> 4. Most of the distributions package contrib separately anyway <br /> 5. Some of core is extremely small use case (sepgsql, tsearch2, lo ...) <br /> 6. Finding utilities for PostgreSQL used to be harder. It is rather dumb simple teenage snapchat user easy now. <br /> 8. Isn't this what pgxs is for? <br /> 9. Everybody hates cleaning the closet until the end result. <br /> 10. Several of these modules would make PostgreSQL look good anyway (default case insensitive index searching with citext? It is a gimme) <br /> 11. Contrib has been getting smaller and smaller. Let's cut the cord. <br /> 12. Isn't this the whole point of extensions? <br /> </blockquote> <p> Sincerely, <p> jD </blockquote> Thu, 21 May 2015 10:48:32 -0700 Updating the .Org docs on backups I spent a great deal of time working through the SQL DUMP portion of the 9.5devel docs this past week. Below is the current text of what I have and it would be great if my readers would take a look and offer some thoughtful feedback. What would you like to see added? What would you like to see changed? Please note that this is reference documentation not tutorial documentation. <p> This is just the straight HTML dump that is generated from Docbook but since it is inline the links won't work. <a href="">The current -devel docs are here</a> and the updated version I am working is below: <p> <div class="NAVHEADER"> <p> <hr align="left" width="100%"></div> <div class="SECT1"> <h1 class="SECT1"><! a name="BACKUP-DUMP" id="BACKUP-DUMP">24.1. <acronym class="ACRONYM">SQL</acronym> Dump</a></h1> <p><span class="PRODUCTNAME">PostgreSQL</span> provides the program <!a href="app-pgdump.html">pg_dump</a> 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 <!a href="app-pgdump.html">pg_dump</a> is:</p> <pre class="SYNOPSIS"> pg_dump <tt class="REPLACEABLE"><i>-C</i></tt> <tt class= "REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">p</tt> <tt class= "REPLACEABLE"><i>-f</i></tt> <tt class= "OPTION">outfile</tt> <tt class="OPTION">dbname</tt> </pre> <p>The use of <tt class="OPTION">-C</tt> ensures that the dump file will contain the requisite <!a href="sql-createdatabase.html">CREATE DATABASE</a> command within the dump file. The use of <tt class= "REPLACEABLE"><i>-F</i></tt><tt class="OPTION">p</tt> ensures that you are using the plain text format and the use of <tt class= "REPLACEABLE"><i>-f</i></tt> allows you to specify the name of the file the dump will be written to. It is also possible for <span class="APPLICATION">pg_dump</span> 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 <span class="APPLICATION">pg_dump</span> please refer to the <!a href="app-pgdump.html">pg_dump</a> reference page.</p> <p>The <span class="APPLICATION">pg_dump</span> 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 <tt class= "REPLACEABLE"><i>-n</i></tt> <tt class="OPTION">schema</tt> or <tt class="REPLACEABLE"><i>-t</i></tt> <tt class= "OPTION">table</tt>.</p> <p>The primary advantage of using <span class= "APPLICATION">pg_dump</span> over the other backup methods described is that <span class="APPLICATION">pg_dump</span> output is architecture independent. A backup made with <span class= "APPLICATION">pg_dump</span> 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.</p> <p>The text files created by <span class= "APPLICATION">pg_dump</span> are internally consistent, meaning, the dump represents a snapshot of the database at the time <span class="APPLICATION">pg_dump</span> began running. <span class="APPLICATION">pg_dump</span> 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 <tt class="COMMAND">ALTER TABLE</tt>.)</p> <div class="NOTE"> <blockquote class="NOTE"> <p><b>Note:</b> Like any other <span class= "PRODUCTNAME">PostgreSQL</span> client application, <span class= "APPLICATION">pg_dump</span> 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 <tt class= "REPLACEABLE"><i>-U</i></tt> option or set the environment variable <tt class="ENVAR">PGUSER</tt>.</p> </blockquote> </div> <div class="SECT2"> <h2 class="SECT2"><!a name="ADVANCED-PGDUMP" id= "ADVANCED-PGDUMP">24.1.1. Advanced pg_dump</a></h2> <p>The <span class="APPLICATION">pg_dump</span> application provides other formats. The most notable are the use of <tt class= "REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">c</tt> or <tt class="REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">d</tt>. The use of the <tt class="LITERAL">custom</tt> format (<tt class= "REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">c</tt>) is an excellent option for smaller databases when you need fine grained control of the objects you chose to restore. The use of the <tt class="LITERAL">directory</tt> format (<tt class= "REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">d</tt>) allows for parallel connection based backups. If you are performing a backup with many objects and using pg_dump then the <tt class= "LITERAL">directory</tt> format will be the most efficient. This option also allows for fine grained control of the objects you chose to restore.</p> <p>If <span class="PRODUCTNAME">PostgreSQL</span> was built on a system with the <span class="APPLICATION">zlib</span> 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 <tt class="COMMAND">gzip</tt>, but it has the added advantage that tables can be restored selectively.</p> <div class="EXAMPLE"><!a name="AEN37590" id="AEN37590"></a> <p><b>Example 24-1. Backup a single table</b></p> <pre class="SYNOPSIS"> pg_dump <tt class="REPLACEABLE"><i>-U</i></tt> <tt class= "OPTION">user</tt> <tt class= "REPLACEABLE"><i>-h</i></tt> <tt class="OPTION">host1</tt> <tt class="REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">c</tt> <tt class="REPLACEABLE"><i>-f</i></tt> <tt class="OPTION">outfile</tt> <tt class="REPLACEABLE"><i>-t</i></tt> <tt class="OPTION">table1</tt> <tt class="OPTION">dbname</tt> </pre></div> <div class="EXAMPLE"><!a name="AEN37605" id="AEN37605"></a> <p><b>Example 24-2. Using wildcards with table list</b></p> <pre class="SYNOPSIS"> pg_dump <tt class="REPLACEABLE"><i>-U</i></tt> <tt class= "OPTION">user</tt> <tt class= "REPLACEABLE"><i>-h</i></tt> <tt class="OPTION">host1</tt> <tt class="REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">c</tt> <tt class="REPLACEABLE"><i>-f</i></tt> <tt class="OPTION">outfile</tt> <tt class="REPLACEABLE"><i>-t</i></tt> <tt class="OPTION">table*</tt> <tt class="OPTION">dbname</tt> </pre></div> <div class="EXAMPLE"><!a name="AEN37620" id="AEN37620"></a> <p><b>Example 24-3. Using parallelism and a wildcard table list</b></p> <pre class="SYNOPSIS"> pg_dump <tt class="REPLACEABLE"><i>-U</i></tt> <tt class= "OPTION">user</tt> <tt class= "REPLACEABLE"><i>-h</i></tt> <tt class="OPTION">host1</tt> <tt class="REPLACEABLE"><i>-F</i></tt> <tt class="OPTION">d</tt> <tt class="REPLACEABLE"><i>-f</i></tt> <tt class="OPTION">outfile</tt> <tt class="REPLACEABLE"><i>-t</i></tt> <tt class="OPTION">table*</tt> <tt class="REPLACEABLE"><i>-j</i></tt> <tt class="OPTION">8</tt> <tt class="OPTION">dbname</tt> </pre></div> <div class="NOTE"> <blockquote class="NOTE"> <p><b>Note:</b> The use of the <tt class="LITERAL">custom</tt> or <tt class="LITERAL">directory</tt> <span class= "APPLICATION">pg_dump</span> formats requires the use of <!a href= "app-pgrestore.html">pg_restore</a> and will not work with <!a href= "app-psql.html"><span class="APPLICATION">psql</span></a>. There is more information on using <span class= "APPLICATION">pg_restore</span> in section <!a href= "backup-dump.html#ADVANCED-RESTORE-PGDUMP">Section 24.1.3</a>.</p> </blockquote> </div> </div> <div class="SECT2"> <h2 class="SECT2"><!a name="BACKUP-DUMP-RESTORE" id= "BACKUP-DUMP-RESTORE">24.1.2. Restoring the Dump</a></h2> <p>The <span class="APPLICATION">psql</span> application is the default client that ships with <span class= "PRODUCTNAME">PostgreSQL</span>. It is also the default application used when restoring text based dumps created by the <span class= "APPLICATION">pg_dump</span> application. For details information on <span class="APPLICATION">psql</span> please see <!a href= "app-psql.html"><span class="APPLICATION">psql</span></a>. For the purposes of restoring a dump the basic usage is:</p> <pre class="SYNOPSIS"> psql <tt class="REPLACEABLE"><i>-f</i></tt> <tt class= "OPTION">infile</tt> <tt class= "REPLACEABLE"><i>-d</i></tt> <tt class="OPTION">dbname</tt> </pre> <div class="NOTE"> <blockquote class="NOTE"> <p><b>Note:</b> If you omitted <tt class= "REPLACEABLE"><i>-C</i></tt> when executing <span class= "APPLICATION">pg_dump</span> the <tt class="COMMAND">CREATE DATABASE</tt> command will not be in the text file. You will need to create the database yourself from <tt class= "LITERAL">template0</tt> before the executing the restore (e.g., with <span class="APPLICATION">createdb</span> <tt class= "REPLACEABLE"><i>-T</i></tt> <tt class="OPTION">template0</tt> <tt class="OPTION">dbname</tt>).</p> </blockquote> </div> <div class="WARNING"> <table class="WARNING" border="1" width="100%"> <tr> <td align="center"><b>Warning</b></td> </tr> <tr> <td align="left"> <p><span class="APPLICATION">pg_dump</span> does not backup users, roles and other global objects. To properly backup global objects you must use <!a href="app-pg-dumpall.html"><span class= "APPLICATION">pg_dumpall</span></a> with the <tt class= "REPLACEABLE"><i>-g</i></tt> 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 <tt class= "REPLACEABLE"><i>-U</i></tt> <tt class="OPTION">username</tt>. If <tt class="REPLACEABLE"><i>-U</i></tt> is not passed then the operating system user executing <span class= "APPLICATION">psql</span> will be used.</p> </td> </tr> </table> </div> <div class="IMPORTANT"> <blockquote class="IMPORTANT"> <p><b>Important:</b> The dumps produced by <span class= "APPLICATION">pg_dump</span> are relative to <tt class= "LITERAL">template0</tt>. This means that any languages, procedures, etc. added via <tt class="LITERAL">template1</tt> will also be dumped by <span class="APPLICATION">pg_dump</span>. As a result, when restoring, if you are using a customized <tt class= "LITERAL">template1</tt>, you must create the empty database from <tt class="LITERAL">template0</tt>, as in the example above.</p> </blockquote> </div> <p>After restoring a backup, one should execute <!a href= "sql-analyze.html">ANALYZE</a> on each database so the query optimizer has useful statistics; see <!a href= "routine-vacuuming.html#VACUUM-FOR-STATISTICS">Section 23.1.3</a> and <!a href="routine-vacuuming.html#AUTOVACUUM">Section 23.1.6</a> for more information. For more advice on how to load large amounts of data into <span class="PRODUCTNAME">PostgreSQL</span> efficiently, refer to <!a href="populate.html">Section 14.4</a>.</p> </div> <div class="SECT2"> <h2 class="SECT2"><!a name="ADVANCED-RESTORE-PGDUMP" id= "ADVANCED-RESTORE-PGDUMP">24.1.3. Advanced restore</a></h2> <div class="EXAMPLE"><!a name="AEN37695" id="AEN37695"></a> <p><b>Example 24-4. Using pipes to restore to new server</b></p> <pre class="SYNOPSIS"> pg_dump <tt class="REPLACEABLE"><i>-h</i></tt> <tt class= "OPTION">host1</tt> <tt class= "REPLACEABLE"><i>-d</i></tt> <tt class= "OPTION">dbname</tt> | psql <tt class= "REPLACEABLE"><i>-h</i></tt> <tt class= "OPTION">host2</tt> <tt class= "REPLACEABLE"><i>-d</i></tt> <tt class="OPTION">dbname</tt> </pre></div> <p>If one is using the <tt class="LITERAL">custom</tt>, <tt class= "LITERAL">directory</tt> or <tt class="LITERAL">tar</tt> formats the restore command is <span class="APPLICATION"><!a href= "app-pgrestore.html">pg_restore</a></span>. The <span class= "APPLICATION">pg_restore</span> program has many benefits over the use <span class="APPLICATION">psql</span> including fine grained object restore and parallelism.</p> <div class="EXAMPLE"><!a name="AEN37715" id="AEN37715"></a> <p><b>Example 24-5. Extracting a text dump from a custom format backup</b></p> <p>The following will extract the backup to the standard output. The use of <tt class="REPLACEABLE"><i>-F</i></tt> is optional as <span class="APPLICATION">pg_restore</span> should be able to detect the format.</p> <pre class="SYNOPSIS"> pg_restore <tt class="REPLACEABLE"><i>-F</i></tt> <tt class= "OPTION">c</tt> <tt class="OPTION">infile</tt> </pre></div> <div class="EXAMPLE"><!a name="AEN37724" id="AEN37724"></a> <p><b>Example 24-6. Restoring a single table</b></p> <pre class="SYNOPSIS"> pg_restore <tt class="REPLACEABLE"><i>-U</i></tt> <tt class= "OPTION">username</tt> <tt class= "REPLACEABLE"><i>-h</i></tt> <tt class= "OPTION">host1</tt> <tt class= "REPLACEABLE"><i>-d</i></tt> <tt class= "OPTION">dbname</tt> <tt class= "REPLACEABLE"><i>-t</i></tt> <tt class= "OPTION">table</tt> <tt class="OPTION">infile</tt> </pre></div> <div class="EXAMPLE"><!a name="AEN37736" id="AEN37736"></a> <p><b>Example 24-7. Using parallelism to restore databases</b></p> <p>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 <span class= "APPLICATION">pg_dump</span> based restore.</p> <pre class="SYNOPSIS"> pg_restore <tt class="REPLACEABLE"><i>-U</i></tt> <tt class= "OPTION">username</tt> <tt class= "REPLACEABLE"><i>-h</i></tt> <tt class= "OPTION">host1</tt> <tt class= "REPLACEABLE"><i>-d</i></tt> <tt class= "OPTION">dbname</tt> <tt class= "REPLACEABLE"><i>-t</i></tt> <tt class= "OPTION">table</tt> <tt class= "REPLACEABLE"><i>-j</i></tt> <tt class="OPTION">8</tt> <tt class= "OPTION">infile</tt> </pre></div> </div> <div class="SECT2"> <h2 class="SECT2"><!a name="BACKUP-DUMP-ALL" id= "BACKUP-DUMP-ALL">24.1.4. Using <span class= "APPLICATION">pg_dumpall</span></a></h2> <p><span class="APPLICATION">pg_dump</span> 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 <!a href= "app-pg-dumpall.html"><span class= "APPLICATION">pg_dumpall</span></a> program is provided. <span class="APPLICATION">pg_dumpall</span> 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:</p> <pre class="SYNOPSIS"> pg_dumpall &gt; <tt class="REPLACEABLE"><i>outfile</i></tt> </pre> <p>The resulting dump can be restored with <span class= "APPLICATION">psql</span>:</p> <pre class="SYNOPSIS"> psql -f <tt class="REPLACEABLE"><i>infile</i></tt> postgres </pre> <p>It is necessary to have database superuser access when using a <span class="APPLICATION">pg_dumpall</span> dump. The superuser acess is required to restore the role and tablespace information.</p> <p><span class="APPLICATION">pg_dumpall</span> works by emitting commands to re-create roles, tablespaces, and empty databases, then invoking <span class="APPLICATION">pg_dump</span> for each database. This means that while each database will be internally consistent, the snapshots of different databases are not sychronized.</p> <p>Cluster-wide data can be dumped alone using the <span class= "APPLICATION">pg_dumpall</span> <tt class= "OPTION">--globals-only</tt> option. This is necessary to fully backup the cluster if running the <span class= "APPLICATION">pg_dump</span> command on individual databases.</p> <div class="NOTE"> <blockquote class="NOTE"> <p><b>Note:</b> If you use tablespaces, make sure that the tablespace paths in the dump are appropriate for the new installation.</p> </blockquote> </div> </div> <div class="SECT2"> <h2 class="SECT2"><!a name="BACKUP-DUMP-LARGE" id= "BACKUP-DUMP-LARGE">24.1.5. Handling Large Databases</a></h2> <p>The act of backing up a normal sized database is relatively simple. The act of backing up a large database (&gt;500GB) can be challenging. Fortunately, <span class= "PRODUCTNAME">PostgreSQL</span> 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.</p> <ol type="1"> <li> <p>Use the <tt class="LITERAL">directory</tt> format and the <tt class="REPLACEABLE"><i>-j</i></tt> <tt class="OPTION">NUM</tt> option. This will ensure the quickest and most flexible pg_dump style backup.</p> </li> <li> <p>Use <tt class="LITERAL">continuous archiving</tt> as described in <!a href="continuous-archiving.html">Section 24.2</a>. You can then backup the replica without putting load on the master.</p> </li> <li> <p>Use <span class="APPLICATION">pg_basebackup</span>as described in <!a href="continuous-archiving.html#BACKUP-BASE-BACKUP">Section 24.2.2</a>.</p> </li> </ol> <div class="WARNING"> <table class="WARNING" border="1" width="100%"> <tr> <td align="center"><b>Warning</b></td> </tr> <tr> <td align="left"> <p>The pg_dump methods utilize at least one connection if not many connections (via <tt class="REPLACEABLE"><i>-j</i></tt>). 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.</p> </td> </tr> </table> </div> </div> </div> <div class="NAVFOOTER"> <hr align="left" width="100%"> <table summary="Footer navigation table" width="100%" border="0" cellpadding="0" cellspacing="0"> <tr> <td width="33%" align="left" valign="top"><!a href="backup.html" accesskey="P">Prev</a></td> <td width="34%" align="center" valign="top"><!a href="index.html" accesskey="H">Home</a></td> <td width="33%" align="right" valign="top"><!a href= "continuous-archiving.html" accesskey="N">Next</a></td> </tr> <tr> <td width="33%" align="left" valign="top">Backup and Restore</td> <td width="34%" align="center" valign="top"><!a href="backup.html" accesskey="U">Up</a></td> <td width="33%" align="right" valign="top">Continuous Archiving and Point-in-Time Recovery (PITR)</td> </tr> </table> </div> OpenSource PostgreSQL Python SQL Thu, 23 Apr 2015 09:27:34 -0700 WhatcomPUG meeting on 04/21. Start date, end date, calculate The PUG meeting was good. We now have a consistent if small group that are attending. Before the presentation we spoke about possibly moving the group to meetup to get a little better visibility. G+ Communities are awesome but Meetup seems to be where the people in the area look. <p> 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). <p> 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. <p> 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! Business OpenSource PostgreSQL Python SQL Mon, 20 Apr 2015 11:20:25 -0700 Reflections on PgConf.US 2015 Saturday the 18th of April, I woke up to the following: <p> <div align="center"> <a href="" target="_blank"><img src="" width="35%" height="35%"></a> </div> <p> 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. <p> It was at this time that I was able to finally take a step back and reflect on <a href="">PgConf.US</a>. 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. <p> 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. <p> 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. <p> I have only two constructive feedback points for the organizers: <ol> <li> Do not allow tutorials based on sponsorship. Allow them based on merit. You will get a much more positive return from them. <li> Move the cocktail party offsite where there will be full food available at a more reasonable price. </ol> I have three constructive feedback points for other sponsors: <ol> <li>Plan your events in conjunction with the conference. Don't take attendees away from a conference for your invitation only event. <li>Don't over staff your booths. Even a large both (10x10) only has room for ~ 3 people. <li>Lighten up. This is supposed to be fun. </ol> <p> 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. <p> 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. <p> 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. <p> Let's get more of everyone involved and save them from the nefarious reaches of those "other" databases. Business OpenSource PostgreSQL Python SQL Wed, 18 Mar 2015 09:28:02 -0700 WhatcomPUG meeting last night on: sqitch and... bitcoin friends were made! Last night I attended the second <a href="">WhatcomPUG</a>. This meeting was about <a href="">Sqitch</a>, 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. <p> I also met a couple of people from <a href="">CoinBeyond</a>. 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! <p> 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. <p> Oh, for reference: <p> <li>Twitter: <a href="">@coinbeyond</a> <li>Facebook: <a href="">CoinBeyond</a> <li>LinkedIn: <a href="">Linkedin</a> </p> Business OpenSource PostgreSQL SQL Tue, 17 Mar 2015 08:35:28 -0700 Stomping to PgConf.US: Webscale is Dead; PostgreSQL is King! A challenge, do you accept? I submitted to <a href="http://wwww.PgConf.US/">PgConf.US</a>. 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). <p> 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. <p> 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 <a href="http://">SDPUG/PgUS PgDay @ Southern California Linux Expo.</a> <p> 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. <p> 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? <a href="">Systemd</a>, I don't think they were expecting my answer, because I don't really have a problem with Systemd. <p> 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. <p> A challenge, do you accept? <p> I challenge all speakers at this voyage of <a href="">PgConf.US</a> 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! Business OpenSource PostgreSQL Python SQL Wed, 25 Feb 2015 10:13:50 -0700 PostgreSQL is King! Last week was quite busy being a servant. Last week was one of the busiest community weeks I have had in a long time. It started with an excellent time in Vancouver, B.C. giving my presentation, "An evening with PostgreSQL!" at VanLUG. These are a great group of people. They took all my jibes with good humor (Canadians gave us Maple Syrup, we gave them Fox News) and we enjoyed not only technical discussion but discussions on technology in general. It is still amazing to me how many people don't realize that Linux 3.2 - 3.8 is a dead end for random IO performance. <p> 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! <p> 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. <p> 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: <p> <ol> <li>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. <li>When Berkus (who knew the real answer) suggested it was Elton John that wrote the lyrics at the end of the presentation. </ol> <p> 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. <p> 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. Business OpenSource PostgreSQL SQL Wed, 12 Nov 2014 09:00:08 -0700 AWS performance: Results included I am not a big fan of AWS. It is a closed platform. It is designed to be the Apple of the Cloud to the Eve of Postgres users. That said, customers drive business and some of our customers use AWS, even if begrudgingly. Because of these factors we are getting very good at getting PostgreSQL to perform on AWS/EBS, albeit with some disclosures: <ol> <li>That high IO latency is an acceptable business requirement. <li>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. </ol> <p> Using the following base configuration (see adjustments for each configuration after the graphic): <pre> 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 </pre> <p> Each test was run using pgbench against 9.1 except for configuration 9 which was 9.3: <br /> <pre>pgbench -F 100 -s 100 postgres -c 500 -j10 -t1000 -p5433</pre> <p> Here are some of our latest findings: <p> <div align="center"> <img src=""> </div> <p> The AWS configuration is: <blockquote> 16 Cores<br /> 30G of memory (free -h reports 29G) <br /> (2) PIOPS volumes at 2000 IOPS a piece. <br /> The PIOPS volumes are not in A RAID and are mounted separately. <br /> The PIOPS volumes are formatted with xfs and default options <br /> The PIOPS volumes were warmed. </blockquote> <ol> <li> Configuration 1: <blockquote> $PGDATA and pg_xlog on the same partition <br /> synchronous_commit = on <br /> </blockquote> <li> Configuration 2: <blockquote> $PGDATA and pg_xlog on the same partition <br /> synchronous_commit = off <br /> </blockquote> <li> Configuration 3: <blockquote> $PGDATA and pg_xlog on the same partition <br /> synchronous_commit = off <br /> commit_delay = 100000 <br /> commit_siblings = 50 <br /> </blockquote> <li> Configuration 4: <blockquote> $PGDATA and pg_xlog on the same partition <br /> synchronous_commit = off <br /> commit_delay = 100000 <br /> commit_siblings = 500 <br /> </blockquote> <li> Configuration 5: <blockquote> $PGDATA and pg_xlog on different partitions <br /> synchronous_commit = off <br /> commit_delay = 100000 <br /> commit_siblings = 500 <br /> </blockquote> <li> Configuration 6: <blockquote> $PGDATA and pg_xlog on different partitions <br /> synchronous_commit = on <br /> commit_delay = 100000 <br /> commit_siblings = 500 <br /> </blockquote> <li> Configuration 7: <blockquote> $PGDATA and pg_xlog on different partitions <br /> synchronous_commit = on <br /> commit_delay = 0 <br /> commit_siblings = 5 <br /> </blockquote> <li> Configuration 8: <blockquote> $PGDATA and pg_xlog on different partitions <br /> synchronous_commit = on <br /> checkpoint_segments = 300 <br /> checkpoint_timeout = 60min <br /> </blockquote> <li> Configuration 9: <blockquote> $PGDATA and pg_xlog on different partitions <br /> PostgreSQL 9.3 <br /> synchronous_commit = on <br /> checkpoint_segments = 300 <br /> checkpoint_timeout = 60min <br /> </blockquote> </ol> Business OpenSource PostgreSQL Python SQL Tue, 07 Oct 2014 10:11:03 -0700 Don't kill yourself As a PostgreSQL consultant you end up working with a lot of different types of clients and these clients tend to all have different requirements. One client may need high-availability, while another needs a DBA, while yet another is in desperate need of being hit with a clue stick and while it is true that there can be difficult clients, there is no bad client. <p> <blockquote> What!!! Surely you can't be serious? <p> Don't call me shirley. <p> I am absolutely serious. </blockquote> <p> 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". <p> 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: <p> <ol> <li>Hire someone who can <li>Fire the client </ol> <p> Woah! Fire the client? Yes. Terminate the relationship with the client. <p> 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] <p> 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]. <p> The better you manage a client, the better the relationship. Good luck! <ol> <li><br /> <li><br /> </ol> Business OpenSource PostgreSQL Python SQL Thu, 18 Sep 2014 13:38:43 -0700 Along the lines of GCE, here are some prices I was doing some research for a customer who wanted to know where the real value to performance is. Here are some pricing structures between GCE, AWS and Softlayer. For comparison Softlayer is bare metal versus virtual. <p> GCE: 670.00 <br /> 16 CPUS <br /> 60G Memory <br /> 2500GB HD space <p> GCE: 763.08 <br /> 16 CPUS<br /> 104G Memory<br /> 2500GB HD space <p> Amazon: 911.88<br /> 16 CPUS<br /> 30G Memory <br /> 3000GB HD Space <p> Amazon: 1534.00 <br /> r3.4xlarge<br /> 16 CPUS <br /> 122.0 Memory <br /> SSD 1 x 320 <br /> 3000GB HD Space <p> Amazon: 1679.00 <br /> c3.8xlarge <br /> 32 CPUS <br /> 60.0 Memory <br /> SSD 2 x 320 <br /> 3000GB HD Space <br /> <p> None of the above include egress bandwidth charges. Ingress is free. <p> Softlayer: ~815 (with 72GB memory ~ 950) <br /> 16 Cores <br /> RAID 10 <br /> 4TB (4 2TB drives) <br /> 48GB Memory <br /> <p> Softlayer: ~1035 (with 72GB memory ~ 1150)<br /> 16 Cores <br /> RAID 10 <br /> 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) <br /> 48GB Memory Business OpenSource PostgreSQL Python SQL Mon, 15 Sep 2014 09:48:23 -0700 GCE, A little advertised cloud service that is perfect for PostgreSQL Maybe... <p> I have yet to run PostgreSQL on <a href="">GCE</a> in production. I am still testing it but I have learned the following: <p> <ol> <li>A standard provision disk for GCE will give you ~ 80MB/s random write. <li>A standard SSD provisioned disk for GCE will give you ~ 240MB/s. </ol> <p> 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... <p> 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. <p> 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. <p> 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. Business OpenSource PostgreSQL Python SQL Mon, 08 Sep 2014 11:54:19 -0700 PDXPGDay 2014 I had the honor of being asked to give the introduction at PDXPGDay 2014 this past Saturday. I didn't speak very long but it was great to see a lot of the old stomping ground. It had been quite some time since I had been in the group of Wheeler, Roth, Wong, Berkus and a few others. <p> The conference was really a mini-conference but it was great. It was held in the exact same room that PostgreSQL Conference West was held all the way back in 2007. It is hard to believe that was so long ago. I will say it was absolutely awesome that PDX still has the exact same vibe and presentation! (Read: I got to wear shorts and a t-shirt). <p> Some items of note: Somebody was peverse enough to write a FUSE driver for PostgreSQL and it was even bi-directional. This means that PostgreSQL gets mounted as a filesystem and you can even use Joe (or yes VIM) to edit values and it saves them back to the table. <p> Not nearly enough of the audience was aware of PGXN. This was a shock to me and illustrates a need for better documentation and visibility through .Org. <p> The success of this PgDay continues to illustrate that other PUGS should be looking at doing the same, perhaps annually! <p> Thanks again Gab and Mark for entrusting me with introducing your conference! Business OpenSource PostgreSQL Python Wed, 03 Sep 2014 10:52:17 -0700 a wonderful if flawed apt repository The site is a great resource for those who live in the Debian derived world. It keeps up to date with the latest postgresql packages and has a whole team dedicated to creating these packages. Of course, this is the Open Source world so not everyone agrees 100% with the way things are done in this project. <a href="">As I noted here, there are some issues.</a> <p> These issues are not to detract from otherwise excellent work but a note to those who use the repository to look for further problems. I also have a video displaying specifically what the <a href="">issues are, here.</a> <p> Business OpenSource PostgreSQL SQL Tue, 04 Feb 2014 12:50:29 -0700 Kicking the Donkey of PostgreSQL Replication This is the title of a talk I am developing for the matured PostgreSQL Conference: PGConf NYC 2014 . Formerly a PgDay, this is now a full blown conference extending two days with three tracks. From all reports it is set to be the largest PostgreSQL Conference ever in the United States, surpassing even the old West and East series (which no conference in the U.S. has done to date). It is truly exciting times for our community. <p> This talk will be a departure from my standby talks of PostgreSQL Performance and Choosing the right hardware. Katz asked me, "to bring your full East Coast from the West Coast personality.". I plan on doing so. So cinch up the boot straps it is going to be a ride. In classic JD style I am going to be blunt and to the point about the good, the bad, and the, "WTH were they thinking" of PostgreSQL Replication. <p> So outside of personality what am I really trying to deliver to the community? I think the description of the talk says it all: <p> <blockquote> <li> Have you ever wondered how to configure PostgreSQL Replication? <li> Have you ever wondered how to configure PostgreSQL Log Shipping? <li> Have you ever wondered: Which one is best for my application? <li> Are you aware of the pitfalls of Replication? Where it breaks? When it will act in a way that is counter-intuitive? Do you know how to fix it? <li> Do you know how to monitor it? </blockquote> <p> If you have asked yourself any of these questions, this is the talk for you. We will step through PostgreSQL replication, the technology involved, the configuration parameters and how to configure it in a manner that isn't fragile. We will also cover gotcha's how to prepare for them and understanding what replication is doing. <p> This talk is not not for the faint of heart. I will take a no holds barred approach and give you the real deal, the dirt and the gold that is one of the most sought after PostgreSQL features. </pre> <p> On a closing note, this conference is showing how a PostgreSQL User Group, combined with the resources of United States PostgreSQL can help grow and educate the community. They don't just help NYCPUG, they also help PDXPUG, PHILLYPUG and SEAPUG. Maybe your PUG should consider working with PgUS? If so, give "Jonathan S. Katz" [jonathan.katz {@}] a jingle. Business OpenSource PostgreSQL Python SQL Wed, 06 Nov 2013 12:09:09 -0700 is over, it was a blast but I am curious about the future First let me say that I attended like I attend every conference (that I am not running). I show up for a few hours on the first day, then I come back and attend my talk. I don't take travel lightly and as much as I bromance my fellow elephant bretheren, I want to explore the sights and this was freaking Ireland people. <p> I had an odd feeling for the time I was there. The community was in full force, there was at least 240 people there and that was great. It was the commerce side, the sponsor side, the **money** side that was lacking. EnterpriseDB, Cybertec and 2ndQuadrant were there with booths but I wonder if it was even worth it? <p> It is great that 3 of the largest European commercial contributors were sponsors. I think the traditional model of sponsorship is over and those companies are not getting their investment back. This is certainly not the fault of nor is it the fault of the sponsors. It is habit to fall into what we know. It is that what we know, is broken. There is no true return on investment. You might pull a couple or even a half a dozen leads. Yes you get 20 minutes to give your speech at the end of the conference (when a lot of the people have left anyway) but business is about relationships. I don't see how 3 booths at a 240 person conference enables relationships to be built. <p> I wonder what the attendees are getting out of it? Do we see these sponsors as more beneficial than companies (such as Credativ) who wasn't sponsoring with a booth but was speaking? Or are we just silently thankful that they are there because that way we don't have to spend 500 Euro to attend the conference? If the latter, does it make sense for companies to continue to contribute? What could a company do for the community (and themselves) with a 20k sponsorship versus having a booth and their name on a shirt? <p> Which brings me to my final point. If (or Pgcon, NYCPgConf) were to charge 500.00, and it was sponsor free (or at least more creative in presence) would you still go? What if that included a half day tutorial? What if the conference was only about PostgreSQL + Community. Yes, sponsors are part of the community and they are more than welcome to show up in matching shirts and speak when their talks are accepted but no booths, no littered t-shirts, just good old fashion community, and education. <p> What do you think? Business OpenSource PostgreSQL SQL Tue, 10 Sep 2013 13:36:22 -0700 Just back from NYCPug August, on to more talks In August I spoke at NYCPUG on Dumb Simple PostgreSQL Performance. The talk was well accepted and there was about 60 people in attendance. I have always enjoyed my trips to NYC but this is the first time I have taken a leisurely look at the city. I found myself enjoying a water front walk from 42nd, through the Highline, to Battery Park, all the way to the Brooklyn Bridge and over to Brooklyn to a great pub for dinner. What I enjoyed most about the walk outside of the 10 miles was the community that was present. I think it is easy to get jaded by "midtown" and all that is the tourist in that area. The hustle and bustle, the pollution and dirt of a city. The waterfront walk however reminded me very much of Seattle, there was green and water everywhere, very little litter, lots of families and bike riders. All around a wonderful day and it made me look forward to coming back to NYC again in March for the NYC PostgreSQL Conference. <p> Alas my travel schedule is just beginning. I will be speaking at the Seattle PostgreSQL User Group on Oct 1st. I will be giving the same talk as I did in NYC as it has been updated and always seems to be well received. If you are in town you should come join us: <p> <blockquote> 1100 Eastlake Ave E, Seattle, WA 98102 </blockquote> <p> I always enjoy Seattle and will also be making a side trip to Bellingham as it looks like I will be moving there soon. It will be sad to say good bye to the Columbia River Gorge but I am looking forward to the Mount Baker area as well as hopefully starting Vancouver, B.C. and Whatcom county PostgreSQL user groups. <p> Just three weeks after Seattle, I will be cross the pond to wonderful Ireland where it is bound to be cold, dark and wet. That's alright though as I will plan on staying from Oct 26th - Nov 3rd, allowing for a full trip of sight seeing. Of course I will be dropping by on Friday the 1st to speak on PostgreSQL Backups which reminds me that I need to update the talk for the new pg_dump features found in 9.3. Business OpenSource PostgreSQL SQL Fri, 26 Jul 2013 10:39:46 -0700 Compiling and installing OpenSRF 2.2 on Centos 5.9 We do quite a bit of work for King County Library systems. The library system has 45 branches and runs the Open Source Evergreen ILS. One of the very smart things that the Evergreen project decided was that their database of choice would be PostgreSQL. One of the things that the Evergreen project is not good at is supporting LTS releases of Linux and therefore certain things can be a chore. For example, by default OpenSRF 2.2 which is the current stable OpenSRF release can not be installed via RPM or compiled from source by default on CentOS 5.9. <p> When discussing with the community about CentOS, the response was the classic responses of, "just upgrade", "move to Fedora", "it isn't that hard to migrate to Debian" showing a clear misunderstanding of what it takes to support infrastructure on this scale. I digress. <p> So what to do next? Well, get your hands dirty of course. <p> <pre> Use yum to (make sure you have EPEL): * Install gcc44 gcc44-c++ libevent-devel httpd-devel ** Note: make sure you only have the 64bit versions installed. ** Yum will sometimes install both i386 and x86_64. Download compile and install: memcache 1.4.5 * wget * CC=/usr/bin/gcc44 CXX=/usr/bin/g++44 ./configure --prefix=/usr/local/memcache/ * make install Download compile and install: libmemcache 1.0.7 ** Note: 1.0.5 may also work as the requirement is libmemcache library version 0.8.0 * wget \ * CC=/usr/bin/gcc44 CXX=/usr/bin/g++44 ./configure \ --prefix=/usr/local/libmemcache/ \ --with-memcached=/usr/local/memcache/bin/memcached * make install Add the new libmemcache to (or create a /etc/ file): * /usr/local/libmemcache/lib * ldconfig -vv|grep mem (you should see something like this:) -> -> -> /usr/local/libmemcache/lib: -> -> -> -> -> -> -> The .so.3 is a dependency from the installed RPM. This should not cause any problems as long as you see the /usr/local/libmemcache/lib line. Download compile and install: opensrf 2.2.0 * cd (where you unpacked opensrf)/src/extras * make -f Makefile.install fedora # this will install any outstanding perl packages etc * cd (where you unpacked opensrf); CC=/usr/bin/gcc44 \ CXX=/usr/bin/g++44 CFLAGS="-I/usr/local/libmemcache/include" \ LDFLAGS="-L/usr/local/libmemcache/lib" \ memcached_CFLAGS=/usr/local/libmemcache/include \ memcached_LIBS=/usr/local/libmemcache/lib LIBS=-lmemcached \ ./configure --prefix=/usr/local/openils --with-gnu-ld * make install cd / ln -sf /usr/local/openils . </pre> At some point I might turn this into some rpm packages but right now, we solved the problem. Business OpenSource PostgreSQL SQL Tue, 16 Jul 2013 12:44:07 -0700 Calling Bullsh*t in Open Source communities We are all human. We all lose our temper. We all have our moments of, "I really wish I could take that back". Of course not if you are not Linus Torvalds. Now everyone knows that Linus has a temper, that he is a foul mouth, lacks certain social graces and is generally one of the, if not the most important developers to surface in the last 20 years. Does that mean he gets to be a jerk? In his mind, yes. <p> In some ways I agree with Linus. If you are a donkey butt and you don't pay attention to your community and follow its guidelines, then just leave. We don't have time for you anyway. We are trying to solve problems here. In other ways I don't agree with Linus because he exhibits a distinct lack of grace and grace will always get you further than angst. I have been taught this over and over through the years. Sometimes I get it right, sometimes I call out half the Postgres hackers in a room of 200+ and upset lots of itty bitty sensitive egos. So grace it is and grace I try to exhibit although sometimes fail. <p> Linus could learn from this. Although he sits upon a high perch, he is greatly flawed in his thinking and there are some developers that are calling him on it. <p> <p> Linus excuses his behavior by stating it is just who he is: <p> <p> I once said that the days of Cowboy Open Source is over. Linus apparently didn't see that talk. People are going to tire of the, "I am a socially inept geek and therefore have an excuse to be a jackass excuse". In fact I would argue that most have already tired of it. <p> Linus I would suggest you look at the -hackers community at There you will find a wide disparate group of hackers from dozens of different cultures who all manage to get the job done without juvenile threats, and foul language. They almost are always able to do it professionally too. Have a nice day. <p> Business OpenSource PostgreSQL Python SQL Wed, 19 Jun 2013 12:51:58 -0700 postgres_fdw for 9.2 We have backported the postgres_fdw to 9.2. It is read only of course as the infrastructure for writes is not in 9.2 but it is usable. Enjoy it! <p> <li><a href="">Postgres-FDW</a> Business OpenSource PostgreSQL Python SQL Thu, 06 Jun 2013 12:03:14 -0700 The steaming pile that is Precise with kernel 3.2 I don't know if it is a mainline kernel problem but I can tell you that on Ubuntu Precise, Linux kernel 3.2 is a disaster for PostgreSQL. I am not even going to go into a huge rant about it. I am just posting the numbers. See for yourself. There should be a public service announcement about it. <p> before upgrade to 3.9 <p> <pre> 08:35:01 AM CPU %user %nice %system %iowait %steal %idle 08:45:01 AM all 30.91 0.00 5.66 40.05 0.00 23.38 08:55:02 AM all 29.32 0.00 5.10 39.66 0.00 25.92 09:05:02 AM all 31.71 0.00 6.24 40.99 0.00 21.06 09:15:01 AM all 32.45 0.00 6.59 46.74 0.00 14.21 09:25:01 AM all 20.62 0.00 5.39 60.00 0.00 14.00 09:35:01 AM all 31.03 0.00 3.61 33.95 0.00 31.41 09:45:01 AM all 36.54 0.00 3.22 34.13 0.00 26.11 09:55:02 AM all 40.17 0.00 3.66 30.98 0.00 25.19 10:05:01 AM all 33.49 0.00 3.04 32.28 0.00 31.19 10:15:01 AM all 48.63 0.00 2.87 25.50 0.00 23.00 10:25:01 AM all 51.34 0.00 3.56 26.06 0.00 19.04 10:35:01 AM all 39.41 0.00 3.44 29.86 0.00 27.29 10:45:02 AM all 36.07 0.00 8.79 30.94 0.00 24.20 10:55:03 AM all 38.04 0.00 7.98 32.98 0.00 21.01 11:05:11 AM all 39.25 0.00 8.81 36.75 0.00 15.19 11:15:02 AM all 35.19 0.00 8.76 41.98 0.00 14.07 11:25:03 AM all 38.21 0.00 9.65 38.86 0.00 13.28 11:35:02 AM all 42.92 0.00 11.66 34.28 0.00 11.14 11:45:02 AM all 39.40 0.00 9.96 39.03 0.00 11.61 11:55:01 AM all 28.72 0.00 3.27 36.32 0.00 31.69 </pre> <p> after upgrade to 3.9 <p> <pre> CPU %user %nice %system %iowait %steal %idle 08:35:02 AM all 40.08 0.00 4.46 10.66 0.00 44.80 08:45:01 AM all 38.80 0.00 3.94 7.96 0.00 49.29 08:55:01 AM all 31.48 0.00 3.03 2.58 0.00 62.91 09:05:01 AM all 32.18 0.00 3.09 3.86 0.00 60.87 09:15:01 AM all 26.71 0.00 2.39 3.52 0.00 67.39 09:25:01 AM all 30.49 0.00 3.10 2.80 0.00 63.61 09:35:01 AM all 32.50 0.00 3.49 3.42 0.00 60.60 09:45:01 AM all 36.76 0.00 3.85 6.39 0.00 53.01 09:55:01 AM all 44.45 0.00 4.63 9.23 0.00 41.69 10:05:02 AM all 38.39 0.00 4.28 8.60 0.00 48.72 10:15:01 AM all 33.57 0.00 3.53 4.10 0.00 58.80 10:25:01 AM all 29.42 0.00 2.96 3.16 0.00 64.45 10:35:01 AM all 32.90 0.00 3.37 5.33 0.00 58.40 10:45:01 AM all 34.56 0.00 3.62 4.32 0.00 57.50 10:55:01 AM all 34.84 0.00 3.37 4.27 0.00 57.52 11:05:02 AM all 38.30 0.00 4.05 7.56 0.00 50.08 11:15:01 AM all 36.80 0.00 3.54 9.51 0.00 50.16 11:25:01 AM all 34.79 0.00 3.82 8.17 0.00 53.21 11:35:01 AM all 32.68 0.00 3.07 4.97 0.00 59.28 11:45:02 AM all 31.77 0.00 3.45 6.07 0.00 58.72 11:55:01 AM all 33.58 0.00 3.92 6.39 0.00 56.10 </pre> Business OpenSource PostgreSQL Python SQL Fri, 31 May 2013 10:58:57 -0700 Returning multiple results without a round trip My blog on changes to the wire protocol [1] prompted this question from a reader: <p> <blockquote> "Would it be necessary to modify the wire protocol to support multiple query/result-set combinations per server round-trip? That is, to be able to send a hundred different queries (each with a different number and type of columns in the result set) and receive a hundred different results all in a single network round-trip? That is a feature supported by some other databases that can have an order-of-magnitude effect on the performance of high-latency installations (satellite, etc.)." </blockquote> <p> I did a little research into this and it seems that we can already do this, sort of. See the following: <p> <pre> postgres=# select 1; select 'two'; select 'three'; select 4; ?column? ---------- 1 (1 row) ?column? ---------- two (1 row) ?column? ---------- three (1 row) ?column? ---------- 4 (1 row) </pre> <p> This will avoid the round trip. However there is a limitation. If these were tables, the server would only send the metadata of the last SELECT. The metadata being things such as table description, and number of rows returned. So if you return data from 4 tables, you are only going to know what to do with the last of the tables returned. <p> So it appears that the wire protocol does indeed support the ability to send multiple sets back but the server / libpq does not support sending multiple sets with isolated metadata. <p> The benefit of supporting this could be huge. Consider in a web word you can often end up calling dozens if not hundreds of queries for a single page draw. A network round trip on its own is not expensive but if you add up 30 or 200 of them, the performance degradation adds up quickly. This is especially true when you consider that they each have their own overhead with the server processing the query. <p> 1. <a href="">Modifying the backend protocol for 9.4/10.0</a> Business OpenSource PostgreSQL Python SQL Wed, 29 May 2013 13:17:10 -0700 Modifying the backend protocol for 9.4/10.0. A recent discussion on the lists about potentially incompatible changes to 9.4/10.0 of PostgreSQL the idea of things we wanted to do to the wire protocol in upcoming releases. <p> The wire protocol is the language spoken between a client and the server of postgresql. The majority of programming languages out there do not implement their own version of the protocol instead opting to bind to the C library libpq. There are notable exceptions, specifically C# and Java both of which implement native versions of the wire protocol for their respective languages. <p> The current wire protcol is v4 and was developed for 7.4 of PostgreSQL. That was released in 2003. I think we can safely say there is a lot of new thinking and things we want to change in the protocol to bring it up to newer thoughts on performance and features. A list of which can be found here: <p> <p> One of them is particularly entertaining: Use Commpression. We had a patch for using compression on the wire protocol many years ago and we were essentially told there wasn't a use case. Funny how things change over time. That is the way of the community (replication now in core, cough cough) and we love it for it. The community is relatively conservative and because of that we have arguably the most stable and feature rich RDMS of it. <p> An item I suggested was the ability for the wire protocol to understand the type of connection that was being requested. In short I want the ability to tell a connection that it is a read-write, read-only or write-only connection. In practice a read-only connection would work something like this: <p> <pre> conn-r = psycopg2.connect(database='testdb', user='test', conn-type='r') </pre> <p> I think this would be particularly useful to a tool such as pgPool-II. PgPool-II is a sophisticated software that supports replication, load balancing and query caching. Although we only use it for load balancing, which it is very good at. <p> The problem is, it is relatively slow. It is slow because it has to parse each query that is executed to determine what to do with it. If it is a SELECT only then it can be load balanced. Again, it works well but if pgPool-II knew from the get go the type of connection it was dealing with because the protocol told it on negotiation then it wouldn't need to parse the query because the query was called on the conn-type='r' handle. <p> I could see it also being useful in the future if we were to get load balancing into core. The connection receiving postmaster would see that it is a read-only connection and automatically pass it a replication slave. <p> To see more of what is being discussed <a href="">you can review the thread.</a> Business OpenSource PostgreSQL Python SQL Tue, 28 May 2013 13:37:23 -0700 Considering PITRtools 1.4 We quietly released PITRTool 1.3 last week. This version has been in development for a long time and over the past 6 months became a priority to complete. There is one known minor issue that may or may not be fixed as it doesn't affect production usage in a meaningful way. Release 1.3 contiues to support all the way back to 8.2 with warm standby but we also now support streaming replication and hot standby. <p> With 1.4 we will be making some changes, quite a few of them in fact. Over the years replication and and log shipping has matured in PostgreSQL. We want to take advantage of that maturity. With that here are some of the changes we plan to make: <p> <li>Allow -B in cmd_standby to be multithreaded as well as CPU throttled. <p> This will allow base backups to take advantage of multi-core machines as well as limit the amount of CPU resources that the process takes up. This will effectively eliminate any benefit to pg_basebackup when using PITRtools. <p> <li>Allow for generic start/stop/restart cluster commands. <p> This was an interesting problem we came across with one of our clients who still run Solaris. They are using zones as well SMF. This means that PITRTools out of the box didn't properly work on their environment because we directly call pg_ctl from PITRtools. The solution we came up is to allow the following in the cmd_standby.ini <p> <pre> pg_start_cmd pg_stop_cmd pg_restart_cmd </pre> <p> This will allow custom calls from SMF or any other management interface to stop and start PostgreSQL. <p> <li>Make sane defaults for configuration options <p> The configuration file has grown to a ridiculous number of options in 1.3. For 1.4 we are going to assume most commands are in $PATH. This will remove the need for many of our configuration options. We are considering still allowing specification of location if you don't use a standard path but that has yet to be decided. <p> <li>Add better monitoring and logging options When PITRtools was originally written there was no reasonable way to know what the state of a warm standby was. That has changed with recent releases of PostgreSQL. Thus we will enable the ability to monitor and log in a more verbose manner. <p> <li>Compress wal logs that are shipped/archived <p> PITRtools already has a queuing mechanism for wal logs but they are stored uncompressed. This could benefit from compression on the disk. <p> <li>Potentially adding non replication options <p> PITRtools has all the mechanisms to properly handle postgresql backups. Why not add the option? <p> Of course more things will come as we discuss further. Feel free to join the project and let us know your ideas! <p> 1. <a href="">PITRtools</a> Business OpenSource PostgreSQL Python SQL Fri, 01 Mar 2013 10:06:00 -0700 Remembering to check the docs: Autovacuum I was on a call very late last night with a good customer. Well, it was very early. They were having some performance problems and we were talking through how to resolve them before the EST wake up. It is late, we are all tired and of course there are too many people on the call. <p> So what is the problem? The problem is they weren't running Autovacuum. Now many of my brethren would say, "HERESY!" but in reality there are good reasons not to run Autovacuum (although I would say not Autoanalyze). Autovacuum is unpredictable, and can cause performance problems. 99% of the time you should run Autovacuum but there is a 1% reason to consider other alternatives. <p> The point I trying to make here in my sleep deprived state is that Autovacuum can be turned on with just a reload. It does not require a restart. I swore up and down that it required a restart and I ended up being wrong. I am not sure why I thought it needed a restart. <p> So there you go folks. Tip of the day, "autovacuum = on" only needs a reload. <p> Tallyho read the docs! Business OpenSource PostgreSQL SQL Wed, 06 Feb 2013 12:30:20 -0700 GNU and the FSF should be split up <strong>The FSF should be broken up.</strong> <p> Yes, I really did just write that. I believe the the FSF no longer fulfills its mission. Wait, let's back up a step. I can feel the torches started to be covered in pitch and the frankenstein cry of, "kill the heretic" starting to rumble through the old streets of the Free Software country. I am not here to say that the FSF is useless or that it doesn't have purpose. I am not here to say that Richard Stallman shouldn't continue on his political mission to save the world from the use of rightfully produced and licensed closed source software. <p> What I am saying is that the FSF and GNU should separate and that this separation will act as a catalyst to allow for both to complete its mission in a more productive manner. That's right, and should be two separate non profits, with two different boards. Yes, I am aware that the two are one and it shall and always be. I am declaring that "for better or worse" is now worse and a divorce is now in order. <p> I have the deepest respect for the GNU project. I write this blog entry largely on software that would not be possible without GNU components. I run Linux (no, not GNU/Linux). I run KDE 4.10. I write this in Kate, although I normally prefer Joe. I run PostgreSQL. I run Pidgin, Thunderbird, Gimp, Google Chrome (no not Chromium), Wine, Netflix Desktop, Python, LibreOffice and my music is playing using Amarok. And this my fine Open Source (yes Open Source, not Free Software) denizens is exactly why I think GNU should fork from FSF. <p> FSF/Richard Stallman is a political movement. A political ideal full zealotry. It is uncompromising, unrelenting, stalwart and venerable. It has done a lot of good, it continues to strive to do a lot of good. However... <p> "The Free Software Foundation (FSF) is a nonprofit with a worldwide mission to promote computer user freedom and to defend the rights of all free software users."[1] <p> <strong>On the other hand:</strong> <p> "The primary and continuing goal of GNU is to offer a Unix-compatible system that would be 100% free software. Not 95% free, not 99.5%, but 100%. The name of the system, GNU, is a recursive acronym meaning GNU's Not Unix a way of paying tribute to the technical ideas of Unix, while at the same time saying that GNU is something different. Technically, GNU is like Unix. But unlike Unix, GNU gives its users freedom."[2] <p> As you can see, although both are inextricably intertwined but they are also fundamentally different in their purpose. One is about freedom and rights of users. The other is about developing Free Software. <p> It is my assertion that the continued political movement of the FSF is causing the GNU Project to suffer from slow, politicized and in some ways arcane development. Consider, what tools you use. How many of those tools are actually from the GNU project? All the tools I previously listed, do they need GNU? Absolutely. Are any of them from GNU? Only GIMP. I think you will find this is the case with most modern Open Source (and Free Software) users. <p> It is time for developers not lobbyists to run GNU. <p> <ol> <li> <li> </ol> Business OpenSource PostgreSQL Python SQL Fri, 13 Jul 2012 13:21:46 -0700 In considerations of closed source development Open Source development has a lot going for it, as Bruce Momjian readily points out in a recent blog [1]. However, I believe he missed some key points that are positive for closed source development. Bruce asserts that with Open Source development the developers are the face of the software. That is true but certainly isn't always a good thing. There is a reason that the majority of software development, revenue generation, and software developer employment is closed source (and no it isn't because management and marketing are trying to keep the developer down). <p> It is simple. Most of us Open Source developers aren't generally good with average people. We are good with our "breed" of people but move us out of our element and suddenly we can be awkward, offensive, and generally weird. We talk differently than other people, we have inside humor that doesn't span directions, and are just as inclusive as the richest Skull & Bones society members. Is this bad? No, it is reality. Whenever you take a group of individuals who are on a different playing field than the average person you are going to end up in this situation. <p> The second point that Bruce states is that closed source users have very little interaction with users. I think this is misunderstood. To say that Open Source has more interaction with users is, in my opinion, completely false or is at least given much more weight than is reality. Ask any consultant: the majority of their customers have zero idea about the workings of the community, how to communicate with the community, or interactions with developers. Frankly, they don't want to. They have software to run, businesses to operate, and employees to pay. <p> This can be further illustrated by watching the community. It tooks PostgreSQL years longer than it should have to get replication, and the community is just now starting to look at logical replication, features that were available in closed source versions of PostgreSQL and as open source addons years ago. The users wanted integrated replication but the community wasn't willing to implement them at the time. <p> Please don't get me wrong, I love Open Source. I love Open Source development. Heck, the only closed source software I run is to play Civ5 occasionally. Everything else is Open Source but I do think that we need to keep perspective on what is going on in the very large world that does not involve Open Source. It is much bigger, in a lot of ways more productive, and employ smore people (a rarity in today's economy) than Open Source could ever hope to. <p> 1. Business OpenSource PostgreSQL Python SQL Fri, 20 Apr 2012 10:43:32 -0700 Cool and Sexy: Open Source PostgreSQL enterprise contenders As with any healthy project, there will be offshoots and people will take the source, fork it and try to create something new, better, different or just.... How that person feels it should be. This is a good thing, it leads to new ideas, new communities and sometimes truly interesting pieces of software. <p> Postgres-XC has been around for a while, it is primarily developed by NTT and EnterpriseDB. It has a small community but a dedicated engineering/hacker backing. Postgres-XC is interesting because it keeps reasonably up to date with the latest Postgres (1.0 is set to be based on 9.1 of PostgreSQL) but provides a shared nothing clustering architecture. This type of infrastructure is one of the holy grails of web based applications. <p> Should Postgres-XC deliver on its promises (hint: it does), you will be able to scale out (as opposed to up which Postgres already does extremely well) at an almost 1 to 1 ratio. This means that instead of having to purchase 2 large machines at 10-12k a piece you could purchase 4 machines at 1.5k a piece and achieve similar performance (theoretically, I need to test this). It also means that scaling out in the "cloud" will be easier. <p> I invite everyone interested in PostgreSQL to <a href="">take a look at Postgres-XC</a>. It is going to 1.0 soon and it needs community members to help flesh out the warts that haven't been found yet. <p> Another Postgres fork that has recently appeared is <a href="">tPostgres</a>. tPostgres (doesn't that look wrong at the beginning of a sentence?) is set to do to Microsoft SQL what EnterpriseDB did to Oracle, with one minor, small, interesting, exception: tPostgres is Open Source. Further Microsoft SQL is more in line with PostgreSQL in the types of workloads you usually see it performing. Imagine a tPostgres with Postgres-XC. Imagine an open source way to easily port Microsoft SQL apps to PostgreSQL. <p> Now don't get me wrong, the latest versions of Microsoft SQL are actually good products. Yes, I did just say that. However, they are not Open Source, they are expensive (comparatively) and let's get real, we want everyone to run Postgres. <p> Unfortunately tPostgres is only just announced and they are literally at the beginning of building their community but as it is being initiated by Denis Lussier (co-founder of EnterpriseDB), I imagine that he will come through with something very interesting indeed. Business OpenSource PostgreSQL Python SQL Wed, 18 Apr 2012 11:43:29 -0700 PgNext: Cancelled It is with regret that I announce that PgNext is cancelled. I am not sure what is next for the PostgreSQL Conference series. The reasons are long and myriad and I will not bore you with them. However I will present the following video: <p><div align="center"> <iframe src="" width="400" height="231" frameborder="0" webkitAllowFullScreen mozallowfullscreen allowFullScreen></iframe> </p></div> If you can't see the video, <a href="">here is the video link.</a> <p> That video represents why I would put on the conferences. They were fun. We had a good time. <p> If you are looking for other Postgres conferences there are the following: <p> <ul> <li><a href="">PgCon</a> <li><a href="">PostgresOpen</a> <li><a href="">PgConf.EU</a> </ul> <p> Personally, I would suggest staying local and attending or help organize a local PUG day for PostgreSQL. PUG days are the best in small conferences. You are meeting with many locals, quite a few contributors usually show up, and you get to go home at night. The content is always top notch and chances are you know many of the people there. There are many. We recently had them in NYC, DC/Maryland, and Austin. There is a Denver PgDay on the 26th of October (no website yet) as well. Business OpenSource PostgreSQL Python SQL Tue, 10 Apr 2012 11:22:05 -0700 Remembering our roots Once upon a time, JD was a assistant manager for Block Buster video. This was a very long time ago and before a 23 month employment stint at <a href="">Powells Books</a>. It was at Powells that the world of computers was actually introduced to me as a viable employment option. While there I designed a special order database in DBase IV, was introduced to University Ingres, went through Book Buyer training, became a Novell Netware Administrator, and began a side business selling pre-built computers and parts. I also pretended to go to college and generally just had zero clue about life. I still don't have much of a clue about life. <p> Why does this matter? It doesn't really. I am just rambling because my sister asked me today something that surprised me, "What is UNIX?". I had to just kind of stare at the screen for a moment. Of course she asked me this as she was happily proclaiming that she received an iPhone for her birthday. How far we have come. <p> I explained what UNIX was, the basic history, it's involvement in the Internet and it occurred to me that for me, there was one very specific point in life that my professional world went from, "huh.... give me my 7.50/hr" to, "Hey, I can actually become educated in something useful.". It was the mental <a href=""> absorption of this book</a>. <p> That book, allowed me to learn Unix, which allowed me to learn Linux (back when SLS was king), which brought me to Postgres95, which brought me to PostgreSQL, which brought me to <a href="">co-writing this book,</a> which lead me to be a major contributor to PostgreSQL not only through my work with the Fundraising group (via <a href="">SPI</a>)but also <a href="">. I would also bring up the conferences but those are already mentioned today. <p> While waxing nostalgia I am reminded of a <a href="">recent blog post by Bruce Momjian</a> where he mentions, "Postgres adoption is probably five years behind Linux's adoption.". I would agree with him, and would add that a lot of it is directly contributed to our development model. Many in the community have argued for years that time based releases of PostgreSQL would help development, many others... have argued for years that this is a bad idea. Many of those opponents of time based releasing, and one very influential one at that (TGL) are now starting to come around. More on that later, I have work to do! <p> Business OpenSource PostgreSQL Python SQL Tue, 10 Apr 2012 10:51:10 -0700 PgNext (PostgreSQL Conference) CFP is still open As a reminder, <a href="">the CFP for PgNext</a> is still open. We are in Denver this year, let's make it rock! This year we are keeping it simple and getting back to roots. The conference is about community, networking with professionals, learning and in general having a good time. Who can't have a good time in Denver? Business OpenSource PostgreSQL SQL Thu, 22 Mar 2012 11:45:36 -0700 URI connection strings, PgNext CFP and other generalities (FKlocks) Our team has been hard at work on several things. One is the URI patch for libpq which was just committed and sponsored by Heroku (Thanks Heroku). This is a novel patch that brings standard URI connection handling to libpq and any client/driver that decides to implement the functionality. <a href="">You can see the patch here.</a> <p> We are still actively working on <a href="">PgNext: The Next PostgreSQL Conference</a>. The folks on the organizing team have been an invaluable resource at helping us determine the direction of the conference. We have also been receiving a lot of emails thanking us for the selection of Denver as the location, many of them from new attendees. <a href="">If you haven't submitted a talk yet, now is the time!</a> <p> The FKLocks patch was unfortunately pushed to 9.3 due to some outstanding issues not the least of which was a performance regression under normal FK use. This is a large patch that team member Alvaro Herrera has been working on for a very long time. It is a patch that has the potential to greatly increase the performance of foreign keys. It has been a lesson in patience, evaluation of sponsored work (it was partially, and only partially sponsored), and resource allocation. Hopefully we can be done with this soon. <p> Business OpenSource PostgreSQL SQL Thu, 27 Oct 2011 11:40:08 -0700 What's next for Postgresql conference? West is wrapped up. It was smaller. We split the attendees between <a href="">Postgres Open</a> and <a href="">Surge</a>. It was a good conference. We received a lot of positive feedback and I was even able to be nice (stop laughing, just ask others :P) to people for the conference. <p> We were able to fund two features for PostgreSQL, both of which will hopefully hit for 9.2. The first is work to be done by Greg Smith with pg_stat_statement. The other was fully funded by <a href="">Heroku</a> which is standardized URI support for libpq and psql. It is my hope that we will continue to use PostgreSQL Conference to actively fund features. <p> That said, there are changes in the wind. First, PostgreSQL Conference is changing from a semi-annual conference to an annual conference. There is just no way the community can support four north american conference (PgWest, PgEast, PgCon, Postgres Open). What is unknown at this point is whether or not PgWest and PgEast will continue, or if we will just merge them and have PostgreSQL Conference. What is known is that the next conference will not be on the East coast. We are currently negotiating with Denver, Seattle, and San Jose (for a repeat). <p> So what else is new? Generally speaking, the PostgreSQL conference is operated by a small team within CMD with a few select community members and partners picking up some stray pieces. That has changed this time around. We have an organization and planning committee of 26, all of whom are community members and attendees and/or speakers of the conference. These folks have been an invaluable input to the conference, allowing us to learn from the people that are actually giving us the money to pull this conference off. <p> Stay tuned for more information in the next couple of weeks on various decisions in regards to the conference! Tue, 20 Sep 2011 10:04:50 -0700 PgWest 2011: Only a week away PgWest is only a week a way folks, let's get those <a href="">registrations</a> in! Business OpenSource PostgreSQL Python SQL Tue, 13 Sep 2011 09:54:04 -0700 PgWest 2011: The Schedule is out! This year we have a diverse range of topics on PostgreSQL. Of course we have all the standard topics on backups, performance, mvcc but we also have some very interesting presentations coming from VMWare, Fusion-IO and Translattice. <p> <li><a href="">You can find the schedule here.</a> <li><a href=" ">Registration is open and is available here.</a> Business OpenSource PostgreSQL Python SQL Thu, 08 Sep 2011 09:43:40 -0700 PgWest 2011: Trainings filling up fast As we all know, PgWest is in San Jose this year in just under 3 weeks. The trainings are filling up fast and you will want to get your registrations in. We have great trainings on: <p> <li>Performance <li>High Availability <li>Administration <li>Ruby on Rails (with PostgreSQL focus) <li>Normalization <li>DRBD <p> These are filling up fast, so you will want to get your <a href="">registration in.</a> Business OpenSource PostgreSQL Python SQL Wed, 17 Aug 2011 12:52:00 -0700 PgWest 2011: Initial list of talks is up We have another stellar year of content at PostgreSQL Conference West. The first round of talks has been reviewed and they <a href="">are now published.</a> There are some more talks on the way so stay tuned for the second round. We have also <a href="">opened early registration</a>, although we don't have the training options up yet. Take a look and watch for more official announcement style stuff soon. <p> Of note, Jim Mlodgenski maintainer of Stado (a proper, stable fork of GridSQL) will be teaching a Practical PostgreSQL Administration course. This is a full day course. Jim has graciously agreed to allow his percentage of the training revenue to be used for the feature development community initiative. Business OpenSource PostgreSQL Python SQL Mon, 15 Aug 2011 10:57:17 -0700 PostgreSQL at DEFCON 19 A while ago a gentlemen by the name of Josh (Abstrct) McDougall contacted me about a game he created and subsequent contest being held at DEFCON 19. What makes this so interesting is the majority of the game was created in PostgreSQL. This truly exposed the power of PostgreSQL and the ability to create business (or data) logic directly within the database instead of just using the database as a file system. Josh was looking for a small prize to be able to give the winner of this contest and of course Command Prompt and <a href="">The PostgreSQL Conference</a> was happy to help out. <p> I received (reprinted with permission) this email from him today: <blockquote> Hi Josh, I am excited to say that the Schemaverse contest at DEFCON 19 went great! By the end of the tournament we had 108 registered players and over a million queries ran against the game in a four day span. Not only did the server do great as far as performance goes but the fact that it wasn't exploited during DEFCON is also an impressive stat to note. <p> I can also proudly tell you that your contribution was mentioned during my own two presentations, found in our How To guide, discussed at our contest booth (right by the front doors to the high traffic contest area! :D) AND was announced during the DEFCON 19 closing ceremonies during my allotted 2 minutes of speaking time. <p> The winner of your prize is Ian Haken ( He kicked some butt in the competition and is certainly deserving of it. He has authorized me to send you his name and email. If you need any further details you can talk to him directly. <p> I likely sound like a broken record at this point but I really do need to say thanks again. Your contribution definitely helped us generate some interest in the first year of our competition and has helped us gain the respect needed to return with the contest for years to come. <p> Best Regards, Josh (Abstrct) McDougall <a href=""></a> </blockquote> <p> It was an honor to sponsor this contest. It is great little things like this that truly show the power of PostgreSQL in places you least expect. <p> Just a note, although the CFP is technically closed we have not closed the submission form, <a href="">if you wanted to sneak in a talk or two you are welcome to.</a> Business OpenSource PostgreSQL Python SQL Mon, 01 Aug 2011 11:13:20 -0700 CFP for West extended PostgreSQL Conference West 2011 has extended it's call for papers by 12 days. The new schedule is below: <p> <pre> May 25th: Talk submission opens August 12th: Talk submission closes (EXTENDED!) August 16th: Speaker notification </pre> The Conference, the largest direct financial contributor to the PostgreSQL community but money isn't always what the community needs. One of the things our community needs is a way to create a stable financial environment for developers to receive compensation for development they are performing. <p> Help us continue to provide the overwhelming support to the PostgreSQL Community we always have. Submit your talk today! <p> <a href="">Submit Talks</a> Business OpenSource PostgreSQL Python SQL Tue, 26 Jul 2011 10:31:04 -0700 Learning from mistakes, taking new directions, observations Alright, so they aren't really new directions. Command Prompt has been submitting features to .Org for a long time, we have also been a large generator of content through activity on the mailing lists, the publicly available (if outdated) Practical PostgreSQL and not to mention all the content we provide through the PostgreSQL Conference site. However, with all of these things it is easy to get lost in the mire and slowly forget doing what you are good at. <p> As a team I would like to think that CMD is really good at serving the community, serving its customers, and taking care of each other. However, when you reach out of your expertise and try something new it can fail and come back to bite you. CMD for the last two years tried that, we changed our model. We started listening to companies that have a different model than we did. We felt that we could learn from their success and possibly have some better fortunes of our own. What we learned was that although there was some good information to be had (and we have used that information), the model that was being dictated to us was not the model that would allow CMD to be more successful. We also learned that their model, wasn't nearly as successful as they lead people to believe. <p> One of the things that I love about the .Org business community is that we look out for each other. 2ndQuadrant, Consistent State, PgExperts, Credativ, OmniTI, and CMD (and a host of others, no offense guys). We actively seek ways to work with each other when we can, we lead exchange, we send people to other companies we know are good companies if we can't help a customer, we don't swipe customers (purposely, sometimes things happen) . There is a certain, comfortable quid pro quo. No matter the differences that Josh Berkus, Simon Riggs, Robert Treat, Kevin Kempter or and I may have on list, it is purely professional and at any point we will get together and have beer when we are at a conference or other event. We all have similar goals and they are, grow PostgreSQL, grow our companies and take care of our families (probably not in that order). <p> Although CMD never stopped working with these community partners, we did lose focus through trying to become a more dominant player. It is natural to see opportunity and be slightly blinded by it. To have someone hold out their hand and say, "We know a better way, wink, wink", and think, "Hey maybe they do, let's see where this goes.". In our case, we found the way wasn't better and the way was really smoke and mirrors. This could very well be our fault. Frankly, we were blinded by opportunity that never arose and like many others in this community I am as stubborn as a mule. I just refused to adjust pace before now. <p> I am strong in my convictions and I have beliefs in the way business should be done. CMD is going back to that. Those beliefs are founded on well over a decade of running this company in a manner that has assured that no team member has ever missed a paycheck including myself, that our debt has always been minimal if at all and that our communications with customers are upfront, honest and to the point. In case you hadn't noticed, we also don't employ sales teams. <p> So what does all this really mean? Well for one, CMD has introduced the Google Policy. This means that every technical person in the company can spend 20% of their time on community work and sometimes that will be more. In the case of Alvaro, we have successfully received sponsorship for the rest of the Foreign Key Locks patch and he is currently spending more than 20% time on community work for example. If all goes well, we will continue to be sponsored to write features, modules and other PostgreSQL based software. We have been sponsored to do quite a bit of it lately. <p> It also means that we will be attempting to reinvigorate our cross community relationships, although most of our relationships are solid I feel that not all of them have flourished the way they should. I would like to see those relationships stronger so we can continue to move .Org forward. With stronger relationships in the commercial satisfies the pre-requisite for future growth of the software community. Companies need to know they have someone to call, someone that will treat them right, someone they can trust to implement whatever it is they need with PostgreSQL. If those relationships are strong, any one of us can make sure we get those needs served, either by doing the work ourselves or handing it off to a more suitable candidate. <p> In short it means, more patches, more patch review, more sponsored work and hopefully more intermingling of like interests between PostgreSQL companies. That is one of the goals behind <a href="">PostgreSQL Conference West</a> raising funds for feature development. We want to continue to have PgWest (and PgEast) be a central place where community and commercial can get together and celebrate the common goals of PostgreSQL. Business OpenSource PostgreSQL Wed, 13 Jul 2011 09:48:07 -0700 PostgreSQL Conference West to sponsor PostgreSQL feature development As the primary organizer of <a href="">The PostgreSQL Conference series</a>, Command Prompt has been trying to find ways to continue to support the PostgreSQL Community. The Conference is already the highest direct financial contributor but the money isn't always what the community needs. The documentation may need some work (pg_dump section I am looking at you), we might want server to execute performance testing or there are features that the community wants developed. <p> A recurring question in our community is, "How do I get feature X sponsored?". It is a frustrating question because there are many variables that are at play anytime you do development within an Open Source project. Just because you can develop the feature doesn't mean that the community actually wants the feature. There is also the problem of raising money for a feature, a lot of very skilled hackers do not have the contacts or frankly person skills to "sell" their idea. Once you have the money even more problems arise, what if you underbid the project? What if you can't get everyone to pay that said they would? What if you miss a commit deadline and you have to wait for the next release to start development? The list goes on and on. <p> The traditional method of getting a feature developed would be to contact a company to do so. There are many people and companies that are used to working in the community, who know how to navigate the shark infested waters and are actively willing to work with you to get whatever feature done. Two of the most well known companies that do this are Command Prompt (duh moment), and 2ndQuadrant. We both have had much of the feature development we do for PostgreSQL sponsored by our customers (some of them, mutual customers). If you have a feature that you wish to get developed I highly recommend contacting one of us. <p> The traditional method can be too much for a single entity to bare. If you have a feature that is 15,000 USD to develop, that just might be out of the budget of the sponsoring company and it is certainly out of the budget of most individuals. In some communities there is a bounty system where multiple entities can chose to donate to get a bug squashed or a feature developed. Unfortunately, bounties can go on forever and very little money is normally raised. So what do you do? <p> The PostgreSQL Conference organizers team had an idea. What if, we take one of the resources we are rich in and use that resource in a manner that allows the community as a whole to benefit. What is this resource you might ask? Well people of course. Every 6 months the PostgreSQL Conference pulls more people into a single room than any other PostgreSQL event. What if the PostgreSQL Conference worked out a way to get those people to part with their hard earned dollars to get a feature developed? Imagine, one night instead of pub crawling if we put that 50.00, 100.00 or 250.00 to work for the betterment of PostgreSQL? <p> If every person that attended #PgEast gave 100.00 to feature development, we would have raised $23,000 dollars. That would have more than paid for the ALTER TABLE ALTER COLUMN work that Command Prompt would like to develop. So that is what we are going to do at #PgWest. This year at #PgWest there will be up to 5 proposals on the table to sponsor. Only one will be from Command Prompt, we would like the others to be from other developers and companies are welcome to submit as well. Further, #PgWest will also be donating a portion of the registration fees to get these features developed. <p> Stay tuned for how to submit your proposals. <p> <a href="">PostgreSQL Conference West Call for Papers</a> Business OpenSource PostgreSQL Python SQL Tue, 12 Jul 2011 16:31:39 -0700 Netflix should tell 6,000 users to please cancel their accounts Today, Netflix upped its rates. It was not an excessive increase (dollar wise) but it was enough to get some masses frothing at the mouth [1]. Here is the deal folks, Netflix is cheap, really cheap. It is so cheap that their selection is dwindling as their contracts become due. To get even close to the equivalent service from a cable company you would be looking at upward of 100.00 per month. Netflix is asking for 16.00 and frankly should be asking for 30.00 a month. So please, quit your whining. The only people sympathetic to your cause are the people that will pull the half full soda from the fast food trash to sneak another sip. <p> In reading this article I reminded of <a href=""> First World Problems.</a> <p> 1. Business OpenSource Wed, 06 Jul 2011 09:56:24 -0700 Actually, I am going to #PgWest (and you might not want to) OK, I am just trying to set the record straight. People are still confused thinking I might not be going to <a href="">#PgWest</a>, but I am. I know where the confusion comes from; there are a few other conferences going on during that time frame but the only two that matter are <a href="">#PgWest</a> and <a href="">Surge</a>. So, to be clear, I will be going to #PGWest this year not Surge. Also, to be clear, it's not that I have anything against Surge. I've have never gone to Surge but I have heard nothing but good things about the conference and I suspect I'll probably go to Surge in the future. It's just that this year, I've got something better to go to. That something is #PgWest. <p> <strong>What is #PgWest?</strong> <p> The theme for #PgWest is: <br /> <blockquote> "Next Generation Data" <br /> PostgreSQL Conference West is designed to get you up to speed on how to use the data existing already locked in your organization and cope with the massive amounts of data that is coming. Our industry today is at a major inflection point with Cloud Computing, Big Data, and GeoSpatial all converging into fascatining new architecture resulting in amazing new applications. The existing advanced features and some truely innovative features in PostgreSQL 9.1 will allow PostgreSQL to be the foundation of these new architectures. </blockquote> <p> #PgWest is the PostgreSQL West Coast conference (thus #PgWest) and it happens every year in the fall. Last year we held it in November, normally we hold it in October. This year, we are really close to October at September 27th - 30th in San Jose. #PgWest is also the single largest contributor of educational materials as well as the single largest direct financial contributor to the PostgreSQL community. If you are working with PostgreSQL, #PgWest is where you want to be in September, maybe. #PgWest is the largest PostgreSQL Conference in the United States and will continue to be as we experience easy double digit (not 10%, think 30% - 50%) growth every time we hold it. <p> <strong>Why should you go to Surge?</strong> <p> Surge, from all reports, rocks. It is a great conference full of very smart, fun people who are there for solutions to problems, not minutia political babble. If you want to go to Surge, go to Surge and I guarantee you, it will be worth your time and conference dollars. <p> <strong>Why are you telling me to go to Surge?</strong> <p> Surge is an annual East Coast conference, if you are torn between going to #PgWest and Surge, wait for #PgEast next March. The PostgreSQL Conference is a semi-annual conference with one conference #PgWest on the West coast and one #PgEast on the East coast. By the time Surge and #PgWest happen, #PgEast will only be six months away. I want Surge to be successful. If Surge was on the West coast, I would be reaching out to the Surge folks and trying to share facilities to save costs for everyone. <p> <li><a href="">#PgWest call for papers is still open!</a> <li><a href="">Surge early bird registration</a> Business OpenSource PostgreSQL Python SQL Thu, 26 May 2011 10:47:03 -0700 #PgWest 2011: CFP Open Following on the smashing success of PostgreSQL Conference East, PostgreSQL Conference West, The PostgreSQL Conference for Developers, End Users and Decision Makers, is being held at the San Jose Convention Center, in San Jose, CA from September 27th - 30th. Please join us in continuing to make this the largest PostgreSQL Conference series in North America. <p> <li><a href="">Main site.</a><br /> <li><a href="">Call for papers</a> <p> <strong>Time line:</strong> <blockquote> May 25th: Talk submission opens<br /> July 31st: Talk submission closes<br /> August 8th: Speaker notification<br /> </blockquote> <p> This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: <pre> * General PostgreSQL: * Administration * Performance * High Availability * Migration * GIS * Integration * Solutions and White Papers * The Stack: * Python/Django/Pylons/TurboGears/Custom * Perl5/Catalyst/Bricolage * Ruby/Rails * Java (PLJava would be great)/Groovy/Grails * Operating System optimization (Linux/FBSD/Solaris/Windows) * Solutions and White Papers </pre> Business OpenSource PostgreSQL Python SQL Tue, 03 May 2011 11:27:42 -0700 PgWest 2011: San Jose Convention Center, September 27th-30th I am currently at the Gartner BI conference (yes really), so I won't have time to announce officially until later this week but I have had a lot of people asking me about when West will be. So, here ya go. San Jose, September 27th-30th at the convention center. The conference is already set up at <a href="">Lanyrd if you want to get social.</a> <p> The website is not quite updated yet but should be this week. I will post an official announcement soon with a full wrap up of the Gartner BI conference. Business OpenSource PostgreSQL Python SQL Tue, 05 Apr 2011 16:35:54 -0700 Our company, EnterpriseDB, is the commercial company that brings to market the open source Postgres database., Ed Boyajian. I tend to spend a lot of my time reviewing what is going on in the wider PostgreSQL ecosystem. Over the past few months I have been increasingly intriqued at a overriding theme that is starting to emerge in the market. That "EnterpriseDB" is "The PostgreSQL Company". This isn't about taglines but actual perception in the market. <p> I was speaking with an individual from 10Gen (#PgEast Gold Sponsor and creator of MongoDB). This individual asked me to lay out the history of PostgreSQL, how did it operate (community) etc... While discussing this, the individual asked me a question, "So, do you work for EnterpriseDB?". Luckily I wasn't drinking anything at the time otherwise I would have needed a new monitor. <p> Today, I came across <a href="">this interview with Ed of EnterpriseDB (#PgEast Platinum Sponsor)</a>, where Ed states, "Sure. Our company, EnterpriseDB, is the commercial company that brings to market the open source Postgres database.". <p> I must applaud EnterpriseDB's marketing prowess. They are obviously starting to penetrate the minds of the ignorant and create a thin veneer of false-reality that is truly remarkable (no sarcasm). When you have been in this community as long as I have, and have seen how EnterpriseDB went from, "Who?" in 2004, to "JD works for them" in 2011, that is a testament to some heavy marketing lifting. <p> I don't have any problem with EDB. They are a truly great community contributor but I do think it is important for everyone to remember that while EDB is the elephant in the room (no pun intended), there are hunters who have been around a lot longer. Credativ, 2ndQuadrant, Agliodbs, and yes Command Prompt are who I would argue are actually bringing Postgres to market. We are doing so by providing the professional services, custom development, training, advocacy and many thousands of hours of volunteer time that is what makes up PostgreSQL. <p> Of course, we all know who the real "PostgreSQL Company" is: <pre> Registrant: David Fetter () David Fetter Consulting 2500B Magnolia Street Oakland, CA 94607-2410 US Domain Name: </pre> Disclaimer, if I didn't list your company don't take it personally. I am just going with who was off the top of my head. Business OpenSource PostgreSQL SQL Tue, 05 Apr 2011 14:50:26 -0700 Things you learn.... Reddit runs PostgreSQL (and Cassandra) Not much to say here, but this is a great thread on why the "Cloud" is a serious problem <a href="">real, production quality, database use.</a> Business PostgreSQL SQL Wed, 16 Mar 2011 06:47:51 -0700 #PgEast update, trainings, roundtables and NYC -- Oh my I arrived at the New Yorker yesterday to find that it was an excellent choice for our attendees to sleep. In the last couple of years they have renovated all the rooms and the hotel is quite nice. I have a double and will be here the next 12 days, yes that means I will be here on St. Patrick's day. For all those PostgreSQL Peeps that have never been to NYC, the bars are open till 4AM (although I was long asleep by then). <p> <a href="">Mastering PostgreSQL Administration and Surviving Server Overload are both registering at record pace (in fact if you want to take these classes you better register now). </a> The Pro PostgreSQL and Streaming replication classes are also doing respectably. All four of these classes are being given by well know PostgreSQL Contributors, Bruce Momjian, Greg Smith, Robert Treat and Magnus Hagander respectively. <p> We have also announced a round table at East specifically geared toward a very real problem in the current market, Databases in the Cloud. I am looking forward to this round table as it features and eclectic mix of expert practitioners from Cirrus, to 10Gen, Heroku and Open Hosting. <p> When we decided to change the training format for #PgEast we weren't sure what to expect. We have done a few trainings in the past, specifically at the last #PgEast and they were reasonably successful. We took a shot this year and pushed the envelope on the number of trainings we were going to provide and with all risks there are some possibilities that not all things will work out. Thus, due to low registration for the class, Django. Although the Rails class is doing just fine. While considering why the registration was low, I don't think it was that it was Django. I think that it was a general Django + PostgreSQL class. If the class has been something like, Scaling your application with Django + PostgreSQL it would have been more attractive. Business OpenSource PostgreSQL Python SQL Thu, 10 Mar 2011 13:19:31 -0700 NoSQL, ??? Is there a threat? With <a href="">#PgEast</a> just two weeks away I have been looking for every possible place that I can to advocate the conference. In doing so, I have been finding lots of interesting tidbits of information. For example, did you know (per Stefan/Mastermind) PostgreSQL is lucky to reach 15k tps per core for <strong>SIMPLE QUERIES</strong>, whereas MySQL can do upward of 30k tps per core for <strong>SIMPLE QUERIES</strong>? There is no question that PostgreSQL is going to stomp all over MySQL for complex queries by the maturity of the PostgreSQL planner alone. The problem with PostgreSQL for simple queries is not our execution but is directly related to our parser. It seems there is definitely improvement to be made here. Is this low hanging fruit? Why haven't we fixed this? <p> Next, <a href="">I read today that SourceForge is moving to MongoDB plus Python</a>. Once upon a time Sourceforge was a big time PostgreSQL shop. Now it is not uncommon for a company to chose different platforms. It is also not uncommon for people to move from or to PostgreSQL. However, this is different. <p> For a company like SourceForge who has been embedded in the traditional web world for so many years to migrate wholesale to an entirely new platform which isn't even similar to their existing platform is telling. Most companies may migrate from MySQL to PostgreSQL (this makes sense) but from a fully relational, ACID compliant database to MongoDB? That is a paradigm shift and it marks what I think is an upcoming opportunity for our community. <p> I have been working with 10Gen, the creators of MongoDB for the past couple of months because of PostgreSQL Conference. They even have a training and full track within #PgEast. Generally speaking, MongoDB users are not database people, they are application developers. This is why I wanted MongoDB to be present. I wanted the cross pollination of ideas, hoping that the two platforms could come together and provide a productive discourse for both sides. <p> It is no secret that there is many more application developers in the market than database people and this is where the problem lays. If the majority of people doing the actual development are pushing toward technologies that makes their lives easier then the old and crufty relational database is going to lose out. Certainly PostgreSQL and other relational databases aren't going anywhere but it is important to recognize growing trends, learn from them and try to find a way to monopolize on them. <p> <li>What if there was a NoSQL like grammar for defining relations in PostgreSQL? <li>Since many of the queries you would do in NoSQL are very simple, what if we solved that parser problem? <li>What if you could replicate from/to PostgreSQL and MongoDB, taking advantage of various strengths of each? <p> And with that, I am going to pimp <a href="">The PostgreSQL Conference</a> again. For what should be obvious reasons (it is in 12 days) we are in the final stretch of what is going to be the largest PostgreSQL conference in the Western World. If you do a little bit of legwork (GGIYF) you can even find some pretty hefty discounts to the conference. <p> Oh and don't forget to follow the conference on twitter <a href="">@postgresconf</a> and the hash tag is #PgEast. Business OpenSource PostgreSQL Python SQL Tue, 08 Mar 2011 09:30:20 -0700 #PgEast session schedule is up <a href="">The session schedule is now up.</a> We also have a <a href="">new twitter account for the conference.</a> Further, EFF has also offered a discounted membership for #PgEast attendees, <a href="">more information can be found here.</a> Of course you can <a href="">register in full here.</a> Business OpenSource PostgreSQL Python SQL Tue, 01 Mar 2011 13:14:39 -0700 #PgEast Training Schedule is up The training (not sessions) schedule is up for #PgEast trainings. You can get it <a href="">right off the front page. We are running 7 sessions in parallel with a total of 9 trainings.</a> Business OpenSource PostgreSQL Python SQL Tue, 01 Mar 2011 10:54:35 -0700 The hash code is: #PgEast (want 30% off?) It is 21 days to PostgreSQL Conference East (#PgEast), which is set once again to break attendance records of all the western world conferences. Hosted in NYC, we have large number of sessions, over 60 speakers, a full day of trainings, an excellent NYCPug meeting at the event, and an excessive number of sponsors (nahh... we need 3x more, sponsors ROCK!). I am truly humbled at the amazing level of positive response we have received for hosting PgEast in NYC. CMD and the conference has had so much support from the community we are considering making NYC a permanent home for #PgEast. <p> I have been <a href="">going over the list of talks</a> wondering which I am going to visit or possibly even record. We definitely will not be recording them all this time. Here are a couple (outside of the <a href="">Foursquare</a> talk that I want to see: <ul> <li><a href="">Streaming databases: stepping outside of Postgres</a> <li><a href="">Getting Started with PL/Proxy</a> </ul> Theo Schlossnagle is giving the Streaming databases talk. Theo is always a speaker to attend. He explores interesting topics and as I have no professional experience with Streaming databases, I am excited to see what he has to offer. <p> .Org core member Peter Eisentraut is giving the PL/Proxy talk. I gave a talk on Pl/Proxy at West 2007. I am excited to see how far PL/Proxy has come in the last 3.5 years. <p> Of course thank you to all of our sponsors: <li><a href="">EnterpriseDB</a> <li><a href="">10Gen</a> <li><a href="">Braintree</a> <li><a href="">Continuent</a> <li><a href="">Credativ</a> <li><a href="">EnovaFinancial</a> <li><a href="">OpenSCG</a> <li><a href="">2ndQuadrant</a> <li><a href="">OmniTI</a> <li><a href="">OpenHosting</a> <li><a href="">SQL Manager</a> <p> Now, if you read all the way do to the bottom of this post, the first 25 people that register with the <a href="">coupon code 21DAYSALE will receive 30% off the registration</a> to #PgEast . Business OpenSource PostgreSQL Python SQL Tue, 15 Feb 2011 11:53:43 -0700 PgEast: Talks up and Registration open! That's right folks, the <a href="">PostgreSQL Conference East has listed 98% of its talks</a>. We have a lot of great talks, mini-tutorials and <a href="">trainings.</a> <p> A simple agenda follows: <li>March 22nd is training day. <br /> Trainings range from 199.00 to 349.00 depending on half or full day. <li>March 23rd - 25th is the conference. <br /> The registration is 249.00 <p> In particular I am interested to see how <a href="">Four Square</a> is using MongoDB and PostgreSQL to compliment each other. <p> I also want to give a shout out to all of our very gracious sponsors. This has been a banner year for supporting the conference. It is great to see how we are growing! <p> <li><a href="">EnterpriseDB</a> <li><a href="">10Gen</a> <li><a href="">Braintree</a> <li><a href="">Continuent</a> <li><a href="">EnovaFinancial</a> <li><a href="">OpenSCG</a> <li><a href="">2ndQuadrant</a> <li><a href="">OmniTI</a> <li><a href="">OpenHosting</a> <li><a href="">SQL Manager</a> Business OpenSource PostgreSQL Python SQL Thu, 10 Feb 2011 10:38:56 -0700 PgEast: CFP closes, TODAY! Alright folks, last call. It is beer thirty. CFP closes today. Let's get those talks in: <p> <h1><a href="">Submit Talk to PgEast</a> Business OpenSource PostgreSQL Python SQL Mon, 07 Feb 2011 11:43:16 -0700 PgEast: 2011 CFP closes in 3 days! Alright slackers, you know who you are. The constant presence at every conference. The speaker, that submits their talk at 11:59PM before the CFP closes, I am talking to YOU! In three days, the CFP for what is going to be the largest PostgreSQL Conference in the Western World to date (I say Western because JPUG kicks U.S. butt in attendance just at their user meetings alone), is going to close. <p> We will then start an accelerated process to approve those worthy! <p> Perhaps you are not one of those I speak of, you are not a slacker. You are just a person who would like to speak but you are not sure if you have a decent topic to speak on. If that is the case, email me: <pre>jd < at > commandprompt < . > com</pre> I will be happy to help you. <p> Perhaps you have never spoke before. If you haven't, the PostgreSQL Conference is a great place to start. We are a friendly community that is helpful. Let us be your first jump into the world of conference speaking! <p> No matter your reason, now is the time to get those talks in! <p> <a href="">PostgreSQL Conference East</a> Business OpenSource PostgreSQL Python SQL Tue, 01 Feb 2011 12:00:28 -0700 MongoDB at PgEast.... say what? I was surprised at first too but I have been talking to quite a few community members for MongoDB and due to the positive responses, PostgreSQL Conference East is going to be host to the excellent MongoDB community. <p> <blockquote> JD, Why would you do this? Why do we care? We are relational, they -- well I don't know what they are. </blockquote> <p> One of the goals of <a href="">The PostgreSQL Conference Series</a> is the expand the knowledge of the community and to extend a hand to secondary communities, inviting them learn, collaborate and drink with us. By inviting MongoDB, we are doing exactly that. It is my sincerest hope that we can get a lot of the MongoDB users to better understand our ways, as cranky and old-school as they may be and in return, we will learn some of their ways, perhaps making PostgreSQL even better. <p> Consider, what if our upcoming SQL/MED support in 9.1 had a foreign data wrapper that was optimized for MongoDB? <p> Remember folks, <a href="">CFP is still open</a> <p> And of course, many thanks to the PostgreSQL Conference Sponsors: <p> <ul> <li>Command Prompt, Inc. (Us) <li><a href="">EnterpriseDB</a> <li><a href="">Continuent</a> <li><a href="">2nd Quadrant</a> <li><a href="">SQL Manager</a> </ul> Business OpenSource PostgreSQL Python SQL Fri, 21 Jan 2011 10:37:57 -0700 PostgreSQL vs MySQL @ Oracle NYC Head Quarters NYC (my brain just broke) In my quest to insure an incredible turnout for <a href="">PostgreSQL Conference</a>, I contacted the MySQL meetup group in NYC. Of course my reasons were simple, I want MySQL people to show up and see the value of PostgreSQL. However, I learned a couple of new things. <p> <ul> <li>There is a PHP <a href="">framework called Vork</a> that supports PostgreSQL. <li>Ed Boyajian of <a href="">EnterpriseDB</a> (the new <a href="">Drupal</a> Enterprise <a href="">PostgreSQL</a> <a href="">Company</a>) gave a talk on PostgreSQL vs MySQL @ the Oracle NYC Head Quarters. The video of which is <a href="">available here.</a> </ul> <p> I have seen Ed speak on occasion at <a href="">East and West.</a> He has some interesting (if not so technical points) he makes in the video. I will let you folks analyze it for your own edification. One thing I didn't know was Ed's relationship to the failed <a href="">Red Hat Database</a> initiative (which makes sense, not the failure but why he is involved in PostgreSQL). The failure of Red Hat Database was simple, they were trying to charge 1995.00 (or was it 2995.00?) for PostgreSQL 7.1. Red Hat Database was based on PostgreSQL and essentially caused the death of Great Bridge (yeah..... how many of you have been around long enough to remember that?). Great Bridge once employed Tom Lane and Bruce Momjian. Red Hat now employs Tom Lane and EnterpriseDB now employs Bruce Momjian. Wrap your heads around that chaos theory. <p> Of course <a href="">The PostgreSQL Company</a> has been around longer than any of the other "initiatives" and remains independent, debt and V.C. free. Is this a great time to be alive or what? Let's rock! Tue, 18 Jan 2011 10:47:17 -0700 PgEast: 2011, Second call for papers! January 18th, 2011: Celebrating 15 years of PostgreSQL, early. <p> That's right folks, it is time for second call. Content is being submitted steadily. At PgWest last year, we received well over our capacity of content and we would like to keep that trend going. <p> The PostgreSQL Conference for Developers,End Users and Decision Makers, is being held at the Hotel Pennsylvania,in New York City from March 22nd through 25th 2011. Please join us in continuing to make this the largest PostgreSQL Conference series! <p> <li><a href="">PostgreSQL Conference</a> <li><a href=" ">Call for papers</a> <p> <strong>Thank you to our sponsors:</strong><br /> <li><a href="">Command Prompt, Inc.</a> <li><a href="">EnterpriseDB</a> <p> <strong>Time line:</strong> <br /><blockquote> Dec 16th: Talk submission opens<br /> Feb 10th: Talk submission closes<br /> Feb 15th: Speaker notification<br /> </blockquote> This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: <p> <pre> * General PostgreSQL: * Administration * Performance * High Availability * Migration * GIS * Integration * Solutions and White Papers * The Stack: * Python/Django/Pylons/TurboGears/Custom * Perl5/Catalyst/Bricolage * Ruby/Rails * Java (PLJava would be great)/Groovy/Grails * Operating System optimization (Linux/FBSD/Solaris/Windows) * Solutions and White Papers </pre> Business OpenSource PostgreSQL Python SQL Thu, 16 Dec 2010 10:15:32 -0700 PgEast 2011: NYC Call for papers December 16th, 2010: Celebrating 15 years of PostgreSQL, early. <p> Following on the smashing success of PostgreSQL Conference West, PostgreSQL Conference West, The PostgreSQL Conference for Developers, End Users and Decision Makers, is being held at the Hotel Pennsylvania, in New York City from March 22nd through 25th 2011. Please join us in continuing to make this the largest PostgreSQL Conference series! <p> <li><a href="">PostgreSQL Conference</a> <li><a href=" ">Call for papers</a> <p> <strong>Thank you to our sponsors:</strong><br /> <li><a href="">Command Prompt, Inc.</a> <li><a href="">EnterpriseDB</a> <p> <strong>Time line:</strong> <br /><blockquote> Dec 16th: Talk submission opens<br /> Feb 10th: Talk submission closes<br /> Feb 15th: Speaker notification<br /> </blockquote> This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: <p> <pre> * General PostgreSQL: * Administration * Performance * High Availability * Migration * GIS * Integration * Solutions and White Papers * The Stack: * Python/Django/Pylons/TurboGears/Custom * Perl5/Catalyst/Bricolage * Ruby/Rails * Java (PLJava would be great)/Groovy/Grails * Operating System optimization (Linux/FBSD/Solaris/Windows) * Solutions and White Papers </pre> Business OpenSource PostgreSQL Python SQL Fri, 10 Dec 2010 11:56:52 -0700 R.I.P. PostgreSQL (Mammoth) Replicator In 2004, PostgreSQL Core Team member Josh Berkus wrote[1]: <p> "Slony-I is undoubtedly our most popular replication tool. It supports Master-Slave High Availability Replication. However, there are a number of other solutions, such as dbMirror, eRServer, pgPool, C-JDBC, and the proprietary Mammoth Replicator, all of which are in wide use because they solve different replication problems than Slony-I does. Replication is not a single solution for a single problem; it is several solutions for a wide array of different problems. That's why no one replication tool will ever be the "default" replication for PostgreSQL." <p> I believed well before then it was a fallacy and a bad community decision to not put support behind a single, integrated solution. I also believe it has been one of the single most important policy failures of the .Org community and has lead PostgreSQL to grow much more slowly than it otherwise could have. <p> From a business standpoint it opened the door for many other solutions, a lot of learning experiences and a lot of professional services consulting (Thank you: Slony). It also opened the door for the only solution to look at being integrated into Core. Mammoth PostgreSQL Replicator (Replicator). <p> Replicator opened the door for CMD in many ways. Although it was never a huge commercial success we did have early customers (Cisco) and there was quite a few deployments over time. Eventually, we Open Sourced Replicator hoping to generate some external interest, and although quite a few hackers talked to me about participating, none of it actually materialized into anything useful. <p> To this day, nothing can touch the usability of Replicator for PostgreSQL replication. It is the easiest to configure, the easiest to run, the easiest to manage. Alas that is not enough, as we needed to re-architect to eliminate the one single point of failure (as well as some other issues, crash safety I am looking at you). Further with the emergence of the Hot Standby technology that is in 9.0, the need for Replicator has become even less. <p> Although there were design decisions originally made that were incorrect, I still believe that the overall architecture of Replicator was sound. Implementation, perhaps not but architecture yes. I also believe that the in development version (1.9) would have been a game changer as a whole. Unfortunately we were not able to execute for a number of reasons that don't really matter anymore. <p> Everyone who worked on replicator should be proud of what they did. It should be looked on as a learning experience. We built something, nobody else did, even now. We built integrated and flexible PostgreSQL replication. Was it perfect? No. Should we have open sourced it sooner and tried harder to integrate it into the community? Probably. Were there design decisions that should have been different? Yes. Were there components we should have changed (MCP SPOF)? Yes. <p> Of course, we can say all of that about the current state of PostgreSQL. Let alone Replicator, Slony or any other project. Just read -hackers. We are not perfect, but our team did something good. <p> It took until 2010, 7 years? after Replicator first released for the community to figure out they were wrong. We were pioneers for PostgreSQL and our team should be proud of that. <p> <strong><center>R.I.P. PostgreSQL (Mammoth) Replicator (12/10/2010)</center></strong> <p> 1. Wed, 08 Dec 2010 13:28:27 -0700 Looking for an Intermediate Sysadmin + Junior DBA Command Prompt, Inc. is looking for an intermediate systems administrator and junior PostgreSQL administrator. <p> Command Prompt has provided PostgreSQL support, development, and hosting since 1997. We are looking for another person to join our stellar group of PostgreSQL systems experts. <p> We seek someone who has a deep knowledge of at least one UNIX-like system, and who knows how to manage heterogeneous systems well. You can demonstrate strong skills in basic systems administration. You have a clear understanding of how to administer several systems that are mostly the same, but that have small local differences. If you do not feel comfortable saying, "I'm not familiar with that in your environment," then reading the man page, and coming back with a test plan for deployment that will reveal problems with your strategy, this is not a job for you. The ability to imagine, propose, test, and deliver perfectly-integrated and easily-maintained solutions to users' problems is the key to success in this position. You work well in a team: you don't have to work 24 hours a day, because other people can always log into any system you maintain and know where everything is, how it got there, and how to add new things if they're needed. <p> You must know basic PostgreSQL administration, and understand how to set up, operate, and tune Postgres in elementary ways, such as installing from a package manager. You do not have to be a PostgreSQL guru, but if you are, that is a bonus. <p> You have systems administration capability with shell scripts, as well as either Perl or Python. As well as understanding (or quick to pick up) technologies such as DRBD and LVM2. <p> You have an interest in working on varied projects that use varied (and sometimes legacy) technologies. <p> Command Prompt is a professional services company. You will be interfacing with customers, therefore customer service and professionalism is key. <p> Command Prompt employees usually work from their home offices. The position normally requires minimal travel. For this position, the successful candidate will be able to communicate effectively in English. English as a spoken second language is fine as long as the written skills are clear and effective. <p> This is a remote position and does not require residence in the United States. <p> Applicants should send a resume to Joshua D. Drake, jd(at)commandprompt(dot)com, with the words "intermediate administrator" in the Subject line. Please include any text by way of a cover letter in the body of your email and not as a separate attachment. Business OpenSource PostgreSQL Tue, 09 Nov 2010 14:19:21 -0700 Replication Poll results Back before PostgreSQL Conference West, there was a poll. That poll was replication. Here are the results. There were 367 respondents. Have fun with it!<p> <table class="sticky-enabled"> <thead><tr><th>Q</th><th colspan="10">responses</th> </tr></thead> <tbody> <tr class="odd"><td rowspan="4" valign="top"class="active"><strong>1</strong></td><td colspan="10"><strong>In what waydo you use PostgreSQL?</strong></td> </tr> <tr class="even"><td class="active">Development Only</td><td>72</td> </tr> <tr class="odd"><td class="active">As a hobby (or personaldevelopment)</td><td>106</td> </tr> <tr class="even"><td class="active">Professionally</td><td>325</td> </tr> <tr class="odd"><td rowspan="6" valign="top"class="active"><strong>2</strong></td><td colspan="10"><strong>What versionof PostgreSQL are you running?</strong></td> </tr> <tr class="even"><td class="active">8.3</td><td>117</td> </tr> <tr class="odd"><td class="active">9.0</td><td>175</td> </tr> <tr class="even"><td class="active">8.2</td><td>50</td> </tr> <tr class="odd"><td class="active">8.1</td><td>36</td> </tr> <tr class="even"><td class="active">8.4</td><td>251</td> </tr> <tr class="odd"><td rowspan="4" valign="top" class="active"><strong>3</strong></td><td colspan="10"><strong>Do you use or plan to use replication?</strong></td> </tr> <tr class="even"><td class="active">No</td><td>72</td> </tr> <tr class="odd"><td class="active">Yes</td><td>193</td> </tr> <tr class="even"><td class="active">Once I upgrade to 9.x</td><td>102</td> </tr> <tr class="odd"><td rowspan="8" valign="top" class="active"><strong>4</strong></td><td colspan="10"><strong>What type of replication do you use?</strong></td> </tr> <tr class="even"><td class="active">Londiste</td><td>10</td> </tr> <tr class="odd"><td class="active">Slony</td><td>73</td> </tr> <tr class="even"><td class="active">Streaming Replication</td><td>113</td> </tr> <tr class="odd"><td class="active">Bucardo</td><td>15</td> </tr> <tr class="even"><td class="active">Hot Standby</td><td>90</td> </tr> <tr class="odd"><td class="active">Home Grown / Custom</td><td>34</td> </tr> <tr class="even"><td class="active">DRBD</td><td>17</td> </tr> <tr class="odd"><td rowspan="5" valign="top" class="active"><strong>5</strong></td><td colspan="10"><strong>If you don't use replication, why?</strong></td> </tr> <tr class="even"><td class="active">Backups are enough for me</td><td>51</td> </tr> <tr class="odd"><td class="active">Costs are too high (need more than one server)</td><td>17</td> </tr> <tr class="even"><td class="active">It is a pain in the butt</td><td>56</td> </tr> <tr class="odd"><td class="active">Not a business requirement</td><td>48</td> </tr> </tbody> </table> Business OpenSource PostgreSQL SQL Tue, 09 Nov 2010 11:11:23 -0700 MySQL: The Elephant in the room (Facebook?), oh and me. Rob Wultsch gave an interesting talk at PostgreSQL Conference West, about MySQL and why it doesn't suck. Yes, this was a talk we accepted at a PostgreSQL Conference. It was a good talk but some of the room was a little testy afterward. Business OpenSource PostgreSQL SQL Thu, 28 Oct 2010 13:19:27 -0700 You still don't need no stinking replication! (Replication Poll) So it <a href="">appears my blog yesterday</a> stirred a couple of coals. I love it. In response to <a href="">Josh Berkus's comment here</a>, I offered up a poll. So here it is: <strong><a href="">Replication Poll</a></strong>. You don't have to log in to take it but of course if you do, it helps track validity of results. Bring it on folks. Business OpenSource PostgreSQL SQL Wed, 27 Oct 2010 16:41:53 -0700 Users versus Customers - YOU DON'T NEED NO STINKING REPLICATION I was catching up on the <a href="">max_wal_senders must die</a> thread and I came across this very <a href="">interesting post</a> by fellow Josh Berkus.<p>In the post, Josh Berkus makes the assertion, "50% of PGX's active clients have either already converted to 9.0 replication or have scheduled a conversion with us".<p>I have no doubts of Josh's statement but it brings up an interesting point when arguing about features in PostgreSQL. Josh's response was in regards to a point made by Tom Lane that only a minority of our users are going to want replication. At this point people are going, "What? Of course we want replication!!!!" but you know what? You don't.<p>Yes, Command Prompt customers want replication. Yes, PostgreSQL Experts, EntepriseDB and OmniTI customers want replication. However, customers are *not* users. At least not in the community sense and the users in the community, the far majority of them do not need or want replication. A daily backup is more than enough for them.<p>I think we are going to see an increase in the disparity between customers and users as time goes on. I for example, do not see a real benefit to the 9.0 replication features. That is not to disparage the very hard work that the community members put in, just that we have already defined solutions to solve that problem, years ago. Solutions that work very well. I am more excited about things like SQL/med or PL/psm support.<p><a href="">Want to argue with Josh Berkus or I about this? Catch us at PostgreSQL Conference West next week.</a> Business OpenSource PostgreSQL Python SQL Wed, 27 Oct 2010 13:47:48 -0700 Are hump days, slow days? Wait ... what, No more Gnome for Ubuntu? There are so many things to consider when working through the week. You never send a Press Release never on Monday or Friday. Scheduling meetings is always a bad idea on Monday. Thursdays always seem to be busy, probably because people want to be lazy on Friday. Monday you never know what is going to happen, you might get slammed or you might be waiting for the next email to come in hoping it is important.<p>I am now completely off the above topic because I just got this in my email:<p>Canonical shook the Linux world yesterday when it announced that the next version of Ubuntu -- "Natty Narwhal," or version 11.04 -- will no longer use the GNOME interface by default. Instead, Natty will feature Unity, the multitouch and 3D-enabled interface that made its debut earlier this month in the distribution's netbook edition of Maverick Meerkat, or Ubuntu 10.10.<p><a href="">Read whole article</a><p>I just started using Unity on my notebook. It is quite nice and quite a bit less clunky than default Gnome but... WOW!<p><strong>Oh and <a href="">Register for PostgreSQL Conference West</a> Business OpenSource PostgreSQL Python SQL Tue, 26 Oct 2010 10:43:10 -0700 PgWest 2010: Officially larger that PgEast 2010 As of 5:00PM PST PostgreSQL Conference West 2010 became the largest PostgreSQL Conference in the series. The conference is also still growing as registrations continue to come in.<p>Not only do we have more attendees coming to West than we did East, we have more content than we did at East. It seems that with every 6 months comes a new milestone for the series. PgWest 2010 has 3 full days, 53 speakers and 61 sessions. To top that off, PgEast 2011, is setting up to be 4 full days with an expectation of at least 30% more content and as much as 50% more attendees.<p><a href="">Have you registered for West yet?</a> Business OpenSource PostgreSQL Python SQL Mon, 25 Oct 2010 12:48:04 -0700 PgWest 2010: 8 Days and counting I can't believe how far we have come from a single day, Saturday "PgDay" in 2007 to a full blown three day conference in the middle of the week. Twice a year, every year we have grown, adding content, reaching out to users, bringing the entire ecosystem together. The PgWest and PgEast conferences have grown to comprise the largest PostgreSQL conferences, anywhere.<p> Now that West is upon us in just a short while, I am beginning to think about East. What can we do for East to make it even larger? Another 50% attendance would be a huge win. I do know that East will be a 4 day conference, with full day trainings the first day. I am considering pushing the tutorials to the last day, so the two days in the middle would be sessions. <a href="">Anyway, now is the time to register for West if you haven't yet.</a> Business OpenSource PostgreSQL Python SQL Thu, 21 Oct 2010 14:02:45 -0700 PgWest 2010: Party Announced and Training Added! <a href="">PostgreSQL Conference West 2010</a> also known as PgWest, is having a party for attendees from 5:30pm to 8:30pm on November 3rd. Located on the 21st floor of the Sir Francis Drake Hotel, the 360-degree view from the Starlight Room is as breathtaking as any in the world, encompassing brilliant sunsets or rolling fog, city lights, and landmarks from Telegraph Hill to the Bay Bridge. Harry Denton's Starlight Room is the perfect setting for a total DBA Geek Party!<p><strong><a href="">Register now.</a></strong><p>We have also added a full day training on the 5th. The training, is a repeat of the very successful Mastering PostgreSQL Administration held at PostgreSQL Conference East 2010. It being taught by Core team member, Bruce Momjian. <a href="">You can register for the class at Platinum Sponsor EnterpriseDB's website.</a> Business OpenSource PostgreSQL Python SQL Wed, 20 Oct 2010 13:35:47 -0700 PgWest 2010: Anticipated talk, How To Say Yes To NoSQL: Using Redis With Postgres There has been a lot of talk lately about this idea of "NoSQL". A lot of database traditionalists have been very down on the idea that something else is a better way to extract and store data. I have been in SQL land for so long that I can't even form a credible opinion on the matter. I know that SQL (for the most part) is logical. It makes sense for the paradigm in which it is used.<p>I also know that programmers who don't really know anything about databases have been trying to "fix" them for decades only to eventually come back to earth and realize our way is the best way (thus why all your decent ORMs now support natural keys). Redis has a lot of momentum. I look forward to seeing what they have to offer.<li><a href="">Talk description</a> <li><a href="">Register</a> Business OpenSource PostgreSQL Python SQL Fri, 15 Oct 2010 13:35:17 -0700 PgWest 2010 anticipated talk: Source Forge <a href="">PostgreSQL Conference West</a> is continuing to shape up as the largest PostgreSQL Conference, ever. We have a significant, solid and <a href="">eclectic range of talks.</a><p>One of the talks that was just finalized today is, <a href="">Deployment Best Practices</a>. This is a great beginner talk. What I like about this talk is that is is from a tried and true, in the trenches company that has been using PostgreSQL since the 6.x days. That company is <a href="">SourceForge</a>, the Big Papa of Open Source and Free Software project hosting.<p><a href="">Register for PgWest 2010</a> Thu, 14 Oct 2010 11:09:13 -0700 PgWest 2010 Keynote speaker is: Sun Microsystems Founder, Scott McNealy PostgreSQL Conference West (PgWest) 2010, the premiere PostgreSQL Conference for developers, users and decision makers is pleased to welcome Sun Microsystems founder, Scott McNealy as Key Note Speaker.<p>Please join us November 2nd - 4th at the Sir Francis Drake Hotel in sunny San Francisco for three days of networking, education, geeks, food and fun!<p><a href="">Registration is now open.</a> <a href="">The Agenda is available.</a> As are <a href="">the full talk descriptions!</a><p>And of course, thank you to our sponsors:<p><ul><li><a href="">Command Prompt, Inc.</a><li><a href="">EnterpriseDB</a><li><a href="">2ndQuadrant</a><li><a href="">Continuent</a><li><a href="">JasperSoft</a><li><a href="">EnovaFinancial</a><li><a href="">Redhat</a><li><a href="">PgExperts</a><li><a href="">Credativ</a><li><a href="">Emma</a><li><a href="">ReadWriteWeb</a> Business OpenSource PostgreSQL SQL Tue, 12 Oct 2010 16:23:45 -0700 Evisceration: Learning from colorful mistakes Most people are aware that we use Drupal for the <a href="">PostgreSQL Conference</a>. We are loud advocates of the platform, because it works -- mostly. In terms of being able to run a conference it is flexible enough to make it quirks bearable. However the one place that Drupal is severely lacking is event/scheduling. What is available is either lacking, broken, or just not yet done. Because of this we have always used Google Calendar to do our scheduling. We have also always gotten grief for it. So this year I tried to put a pretty face on top of Google Calendar.<p> The change lead to the removal of all of my vital organs except my heart. The community informed me that I was able to keep my heart because they wanted me to feel the sorrow and anguish they felt when they saw the changes I made.<p>Anyway, thanks to Magnus and a full day of hacking and bashing by myself, we now have a much better <A href="">front end to the schedule.</a> Now, if you all wouldn't be <a href="">too shy to register</a> that would be great. Thanks! Business OpenSource PostgreSQL SQL Mon, 11 Oct 2010 14:57:16 -0700 Do I get to attend a talk? While at <a href="">PgEast or PgWest</a> I normally don't attend talks. Usually I am running around checking on rooms, making sure cameras are working or just generally recovering from yet another round of social interaction with everyone that is there. Do not kid yourself, it is exhausting.<p>This year at West I am hoping to attend a couple of talks. There are a few that are particularly interesting to me. The first is: <a href="">PostgreSQL and Node.JS</a>. Granted I am biased because it is one of my team members speaking but this is a truly interesting thing that she is working on. The ability to write postgresql driven Javascript applications using node.js is 100% buzzword compliant and useful. <p>Of course this also coincides with the recent Alpha release of the driver which can be forked or pulled from its <a href="">github home.</a>.<p>If you are up for it, you could also talk to Aurynn in person about the project either on irc in #postgresql or at <a href="">PostgreSQL Conference West 2010</a>. Business OpenSource PostgreSQL SQL Fri, 01 Oct 2010 09:34:36 -0700 Headed to Utah Open Source Conference The <a href="">Utah Open Source Conference</a> is coming up next week and I will be speaking on PostgreSQL. The presentation I was selected to give is my Dumb Simple PostgreSQL Performance talk.<p>This talk aims to solve the performance (and maintenance) problems most associated with a default install of PostgreSQL. The depth of the talk is limited and is designed specifically for people who are *not* database people, e.g; Web Developers and System Administrators.<p>The week after I will be headed to Boston to attend <a href="">OpenSQL Camp 2010</a> where I will be giving the same talk. Hope to see some community there! Business OpenSource PostgreSQL Python SQL Thu, 30 Sep 2010 12:08:30 -0700 MySQL does what? (Division by integers and 0) I was sitting in #postgresql today (no not the twitter, the irc) talking to some of the community peeps and I came across this tidbit. MySQL casts integers to float before division[1]. Say what? <p><pre> mysql> SELECT 3/5; -> 0.60 </pre> To be honest, I can't fault MySQL for this behavior. It falls in line with the MySQL mantra of make it easy, not "necessarily" correct. A division of 3/5 in a numeric or float would return 0.60. It makes the math easy and normal human consumable. <p>PostgreSQL and Python on the other hand would give you this:<pre> postgres=# select 3/5; ?column? ---------- 0 (1 row) Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) [GCC 4.4.3] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> 3/5; 0 >>> </pre> To get the similar human consumable response you would want:<pre> postgres=# select 3/5.0; ?column? ------------------------ 0.60000000000000000000 (1 row) Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) [GCC 4.4.3] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> 3/5.0; 0.59999999999999998 </pre> Python is using float versus numeric here which explains the disparity. However, MySQL does do something that violates a very basic, as in elementary school math mistake. MySQL defines division by zero as NULL. Yes, you read that correctly.<p><pre> mysql> SELECT 102/(1-1); -> NULL </pre> What should happen is:<pre> postgres=# select 102/(1-1); ERROR: division by zero >>> 102/(1-1); Traceback (most recent call last): File "<stdin>", line 1, in <module> ZeroDivisionError: integer division or modulo by zero </pre> That's correct, an <strong>ERROR</strong> or <strong>EXCEPTION</strong> 1. 2. Business OpenSource PostgreSQL Python SQL Thu, 30 Sep 2010 11:01:07 -0700 PGXN: Are you a benefactor? O.k. so I have been pushing on everyone I know to support <a href="">PGXN</a>. Command Prompt, (you know, us) hadn't bothered to donate. Mainly we hadn't donated because we wanted to be a founding sponsor but just didn't have the budget for it, with the whole <a href="">PostgreSQL Conference</a> thing going on.<p>Today, we put our money where our rather obnoxious mouth is and became a benefactor (at the tune of 1k). So with that, step up people. Don't you want to be able to do this: <pre> pgxn --install py-postgresql </pre> Instead of downloading, compiling, finding missing dependencies, screwing around for an hour doing nothing but swearing at the fact that you don't have what you need to use Python + PostgreSQL? No. You don't want to do that. You want to <a href=""><strong>donate to PGXN</strong></a>, so you don't have to. Business OpenSource PostgreSQL Python SQL Thu, 30 Sep 2010 10:17:15 -0700 PgWest 2010: Talk Descriptions are up The talk descriptions for <a href="">PgWest 2010 are now up.</a> As you can see, there is a lot of content that will be presented over the three days. There is a great mix of developer and user (DBA) content. If you haven't done so already, <a href="">now is the time to register.</a> Business OpenSource PostgreSQL SQL Wed, 29 Sep 2010 14:17:12 -0700 PgWest, have you booked your room? If you have not <a href="">booked your room for PgWest</a>, now is the time. There is a very real possibility that the cost of the hotel will go up in the next week. Now is not the time to be a traditional geek procrastinator. Business OpenSource PostgreSQL SQL Wed, 29 Sep 2010 10:58:22 -0700 Oracle MySQL increasing support pricing Oracle MySQL recently started sending letters to their current clients about upcoming price changes[1]. It is certainly expected that Oracle would increase pricing, but I wonder by how much?<p> Oracle owns another fairly well known Open Source database, BerkeleyDB. <a href="">The pricing for BerkeleyDB.</a> suggests that MySQL may be in for a culture shock. MySQL appears to have removed their support pricing from its website (a move that should not provide comfort to any customer) but as I recall (please correct me if I am wrong) it used to be anywhere from 2500.00 USD to 5000.00 USD, per installation. Whereas Oracle pricing is per processor. Oracle does give you a break if it is a <a href="">multi-core machine but it isn't huge.</a><p>If we use the BerkeleyDB Transactional pricing as a model for what MySQL "could be" a quad core machine would cost a MySQL customer 11,600.00-23,200.00 per year. This is a guess because I don't know the calculation for a Xeon quad core, it would be somewhere between .25 and .75 per core. Oracle is not known for making their pricing clear.<p>Where am I going with all of this? It should be obvious, moving to PostgreSQL with support from a long standing, <a href="/support/support_options">transparent support and pricing schedule</a> can do nothing but benefit you in the future.<blockquote>Updated: 09/29/2010 12:19: <a href="">Current MySQL Pricing</a></blockquote> <strong>1.</strong> <pre> "Hello Customer, I am writing as way of introduction. My name is Juliet and I am your MySQL contact at Oracle. It is my understanding that you are the most appropriate person to speak with at your organization regarding MySQL. If you have any MySQL requirements, questions on the products, support, consulting or training we provide, please do not hesitate in contacting me. I'm sure you are aware that Oracle purchased Sun and therefore MySQL last February. We're being told that there will be changes to MYSQL's pricing and possibly pricing model soon and wanted to let you know. We have not had a price increase for over 6 years but there will be an increase in the next price list that will be available soon. We've been expecting the increase for the past couple of months but I'm told it the new price list will be released soon. For those of you using Basic and Silver support we're being told those options will no longer be available. If you wish to continue with Basic or Silver you will need to sign a multi-year agreement and you would be able to keep using Basic or Silver for up to another 3 years. If you are considering purchasing additional licenses for MySQL support subscription, please let me know, because you can save money if you do it before the changes take place, some time in the next month or two. You can also sign multi-year agreements and lock down current prices for up to 3 years. You can receive up to a 30% discount for a 3 yr. commitments pre-pay but annual payments are available as well for multi-year agreements. If you would like to speak to someone about MySQL Cluster, please let me know and I can arrange for an expert to call you within the next week. Please let me know if you have any questions. </pre> Business OpenSource PostgreSQL SQL Tue, 28 Sep 2010 10:56:47 -0700 Break out your credit card, support #PGXN PGXN is the stuff. It is going to enable a whole new ecosystem of software for PostgreSQL complete with easy install, easy search, modular design and yeah unfortunately Perl.<p>That said, it is time to pony up. David Wheeler has put in some serious effort, well thought out, professionally designed and peer reviewed effort on delivering a new architecture for PostgreSQL software and modules. He needs our financial support.<p><a href="">Go here and contribute, today.</a> He is less than <a href="">7k away from his goal.</a> Business OpenSource PostgreSQL Python SQL Thu, 23 Sep 2010 14:29:18 -0700 Interviewed by So, I broke down at purchased my Linux Foundation membership. Shortly thereafter I was requested to be interviewed. <a href="">Here it is.</a> Business OpenSource PostgreSQL Python SQL Wed, 15 Sep 2010 15:15:38 -0700 PgWest 2010 Early Bird Registration Open! We are still finalizing the three days of content but the first tutorials of the conference have been accepted and <a href="">early bird registration is now open.</a><p[><strong>Tutorials:</strong><p><ul><li>Test Driven <li>Database Development <li>Building an Open Geospatial Analysis Technology Stack <li>Normalization Workshop <li>GUCs: a Three-Hour Tour <li>Django and PostgreSQL</ul> <strong>Mini-tutorials:</strong><ul><li>PostgreSQL Backup and Recovery Methods <li>MVCC Unmasked <li>The PostgreSQL Query Planner <li>Writing C Functions and C User Defined Types on Windows Using Visual <li>Studio (C++) <li>Using the PostgreSQL System Catalogs <li>HTSQL NoSQL for PostgreSQL <li>Blue skies: Replication using Skytools and Londiste <li>Using LVM2 to provide copies of production data for testing</ul><p> The PostgreSQL Conference provides an opportunity for the Postgres community to come together to share and celebrate the most recent advances to the product. If you are currently a Postgres user, or are considering deploying the world's most advanced open source database within your organization, you can't miss this show. This is a cost effective way to receive training, build your skills, hear from your peers and other end users about their implementations, and network with the community leaders to discuss future product directions. (And have some fun, too!)<p>Many thanks to our sponsors:<p><li>Founding: <a href="">Command Prompt, Inc. </a> <li>Diamond: <a href="">EnterpriseDB</a> <li>Gold: <a href="">2ndQuadrant</a> <li>Silver: <a href="">Enova Financial</a> <li>Silver: <a href="">PgExperts</a> Business OpenSource PostgreSQL Python SQL Mon, 13 Sep 2010 11:54:53 -0700 Is your PgWest 2010 presentation submitted? One of the aspects of the Open Source community I love is the agile, on demand nature of getting things done. If a feature is missing, you can just add it and you can add it on demand, as you need it. If a bug is present, you can fix it yourself or pay someone else like <a href="">CMD</a> to fix it for you.<p>Unlike other communities that are closed where requirements and demands can fester for years in an oblivion of "marketability".<p>There is however a downside to this aspect of the Open Source community.<p><p><strong><a href="">We like to wait until the last minute.</a><p>Well, now is the <a href="">last minute!<p>Get your talk in today and be part of the largest PostgreSQL Conference to date!</strong></a> Business OpenSource PostgreSQL SQL Tue, 07 Sep 2010 08:16:48 -0700 PgWest 2010, CFP about to close! Yes, we said it was the 5th that the CFP would be closing but then we belatedly realized that a good portion of the United States would be having a BBQ and drinking whatever their favorite beverage is over the weekend. Thus in true PostgreSQL fashion, PgWest is missing its CFP release date but only for a week! That means, <a href=""><strong>submit your paper, now.</a> Business OpenSource PostgreSQL SQL Wed, 25 Aug 2010 12:14:10 -0700 PgWest: 2010 Call for Papers (2nd call) Yes, it is the second call. That means some of you haven't submitted after the first call. Of course, I haven't submitted mine either; so it is time for everyone to get on it. West is just around the corner and from all observations this West stands to be the largest PostgreSQL Conference, ever. (O.k. we might not over take Brazil).<p>Here is the announcement for everyone to review, enjoy and click on the CFP link:<p>Following on the smashing success of PostgreSQL Conference East, PostgreSQL Conference West, The PostgreSQL Conference for Decision Makers, End Users and Developers, is being held at the Sir Francis Drake Hotel in San Francisco from November 2nd through 4th 2010. Please join us in making this the largest PostgreSQL Conference to date!<p><li><a href="">Main conference site</a> <li><a href="">Call for Papers</a> <p><strong>Thank you to our sponsors:</strong> Founding: <a href="">Command Prompt</a> Diamond: <a href="">EnterpriseDB</a> <p><strong>Time line:</strong><blockquote>July 14th: Talk submission opens Sept 5th: Talk submission closes Sept 10th: Speaker notification </blockquote>This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics:<p><pre> * General PostgreSQL: * Administration * Performance * High Availability * Migration * GIS * Integration * Solutions and White Papers * The Stack: * Python/Django/Pylons/TurboGears/Custom * Perl5/Catalyst/Bricolage * Ruby/Rails * Java (PLJava would be great)/Groovy/Grails * Operating System optimization (Linux/FBSD/Solaris/Windows) * Solutions and White Papers </pre> Business OpenSource PostgreSQL Python SQL Wed, 28 Jul 2010 09:35:05 -0700 FOSSExperts, day 2 I expected feedback from the community on <a href="">FOSSExperts</a>. I did not expect feedback with such immediacy. All the feedback I have received so far is positive. Which is a great feeling. Here are the key points that are coming back.<p><strong>How do deal with disagreement about the deliverable:</strong><p>This is an interesting one. I wanted to keep FOSSExperts simple. That is why the deliverable on the <a href="">ALTER TABLE</a> project is simple, committed to PostgreSQL Core. <p>That may not work in all circumstances. So I am considering one of two options. The first option is courtesy of Josh Berkus. The idea would be to have a board of people that determine whether or not the deliverable has been met. This has merit because you have a panel of experts that make the determination. It can make the review process painless but is also takes the power out of both the funders and developers.<p>The second one is to take a vote. It would work something like this.<ul> <li>Developer states project is complete and demonstrates completeness based on the deliverable. <li>Every person who funded the project votes on whether or not the project is complete.<p><ul><li>If 66% vote the project complete, developer gets their money. <li>If less than 66% vote project incomplete, developer doesn't get their money. </ul><p>There would have to be some caveats. First the funders need to be able to communicate with the developer because they may not have understood part of the spec. Further as we are working with Open Source, the end deliverable may have been changed based on the will of the community versus the developer (see the Hot Standby work with PostgreSQL).<p>The voting would also need to be limited to a period of time. I was thinking 14 days. The 66% would be tallied against those that voted in that 14 days.<p>I like this idea because it removes the third party and it stops a single funder from calling foul as they are part of a collective vote. What do you think? </ul> Other than that the feedback has been extremely positive. I even posted to the LedgerSMB list and multiple people are excited to see this opportunity. </li> If you have ideas, <a href="">please share them. We have setup a flame page just for this.</a> <p>Remember a lot of your questions <a href="">can be answered in the FAQ as well.</a> Business OpenSource PostgreSQL Python SQL Tue, 27 Jul 2010 15:37:04 -0700 FOSSExperts, a new way to fund Open Source (Beta) The cat is out of the proverbial bag. I originally planned to have a quiet roll out with a few close contributors but that has gone by the wayside. Now I am going to be pushing hard for people to test, beat on, object to, argue about, flame upon, scream at, praise and hopefully help us build out something that is truly useful for the FOSS Community. What am I blathering on about? <a href="">FOSSExperts</a> of course. <p>FOSSExperts is a new site specifically engineered to allow FOSS developers to raise money for projects they are trying to develop. The idea stemmed from the very cool <a href="">Kickstarter</a>. With our focus obviously being on a different kind of creative.<p>This is long overdue in the FOSS Community. There are a great deal of communities out there (<a href="">LedgerSMB</a> for example) that can use a place for their developers to try and raise funds for a specific feature. LedgerSMB just recently had a discussion on developing a Payroll module. Developing a Payroll module will be expensive for a single small company to absorb, but 20 small companies? Not nearly as expensive.<p>What FOSSExperts is not, is a place to send money to global projects such as Debian or It is for specific, well defined proposals and has a specific and defined delivery as well as refund policies etc.<p>Right now, we are in closed Beta. If you have a project or proposal you would like to try out you need to email me directly but we are interested. <a href="">So take a look, and let the rage begin!</a> If you like, you can <a href="">review one of the larger proposals</a> already on the site as well. Business OpenSource PostgreSQL Python SQL Fri, 23 Jul 2010 11:23:04 -0700 A better backup with PostgreSQL using pg_dump <blockquote>This is generously borrowed from the PostgreSQL Docs, and updated to something that represents a modern approach to PostgreSQL backups. This documentation has always bothered me because it should have been re-written years ago. Yes I plan on submitting a more comprehensive version as a patch but I don't have time to push it into DocBook right now. If someone else wants to grab it, please do. Yes, I really do believe the use of plain text backups is a mistake. Yes I realize PostgreSQL has the limitation of not being able to backup the cluster in anyway but plain text. </blockquote> The standard for portable backups with PostgreSQL is pg_dump and pg_dumpall. When used properly pg_dump will create a portable and highly customizable backup file that can be used to restore all or part of a single database. The pg_dump application acts as a standard PostgreSQL client. This means that you can perform this backup procedure from any remote host that has access to the database. You do not need to be a super user to use pg_dump but you must have read (and EXECUTE for functions) access to every object within the database. Backups created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. The backup will 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.) The minimum useful syntax for pg_dump is: <pre>pg_dump dbname > outfile</pre> However, the backup created from this method has limited usefulness. It can be used to restore a single database in full. A more useful and proper form of PostgreSQL backup syntax looks like this: <pre>pg_dump -U $username --format=c --file=$mydatabase.sqlc $dbname</pre> The options in detail are:<pre> -U, --username=NAME connect as specified database user -F, --format=c|t|p output file format (custom, tar, plain text) -f, --file=FILENAME output file name </pre> The most important of which is --format. By default pg_dump uses the plain text format. The plain text format is useful for very small databases with a minimal number of objects but other than that, it should be avoided. The custom format allows for a wealth of customizability. Using the custom format you are able to restore single objects from a backup. For example to restore only a specified index from a backup file:<pre> pg_restore -U $username --dbname=$dbname --index=$indexname </pre> If you wanted to restore only a single function: <pre> pg_restore -U $username --dbname=$dbname --function=$functionname(args) </pre> If you wanted to restore only a single table: <pre> pg_restore -U $username --dbname=$dbname --table=$tablename </pre> For more information on all the pg_dump options, please see <a href="">the reference page.</a> <strong>Restoring the dump</strong> The command used to restore a backup file is pg_restore. It has similar options to pg_dump. A simple restore: <pre>pg_restore -U$username --dbname=$databasename $filename</pre> Where filename is the name of the backup file. <blockquote>Do not confuse --file with $filename. The --file option is used to turn a custom format backup into a plain text backup. The value of --file will be used as the output file for that transformation.</blockquote> If you make the mistake of creating a plain text backup, pg_restore can not be used as a restoration mechanism. You can use psql to restore it: <pre>psql $dbname < $backupfile</pre> <strong>Backing up every database</strong> The "postgresql" way of backing up every database is to use the command pg_dumpall. Unfortunately pg_dumpall can only create plain text backups and should be considered deprecated. However it is the only way to backup the globals in your cluster. A reasonable backup strategy to backup your globals and produce a flexible backup of every database in the cluster would look like this: <pre> pg_dumpall -g -U$username --file=$globals.sql; psql -AtU postgres -c "SELECT datname FROM pg_database \ WHERE NOT datistemplate"| \ while read f; do pg_dump -Upostgres --format=c --file=$f.sqlc $f; done;</pre> If someone knows of some Windows code that produces a similar result, it would be great if you would share. <blockquote>Remember, pg_dumpall creates a plain text backup. This means you will need to use psql to restore the globals backup file.</blockquote> After restoring a backup, make sure you run ANALYZE to update the statistics. I know this isn't as comprehensive as it could be, but hey, its just a blog. Business OpenSource PostgreSQL SQL Thu, 22 Jul 2010 20:21:51 -0700 Multiple Drupal installations, single login, 10 steps We have several <a href="">Drupal</a> sites, no I am not typing this blog on one. We needed a way to have single sign on with these Drupal sites. One of which is <a href="">PostgreSQL Conference</a>. There are a few modules out there that can do it, some don't work with PostgreSQL, some are usable but not user friendly (HTTP AUTH) and still others use external services such as OAuth. I didn't want any of these. I wanted a simpler, more flexible solution. I found it with a little PostgreSQL know-how and a modification to the Drupal settings.php file. The following is ten steps that assume we have three sites. At the end of the steps we will have single login between the three sites.. <strong>Step 1: Create users</strong><pre> psql -U postgres; create user one with encrypted password 'foo'; create user two with encrypted password 'bar' create user three with encrypted password 'baz'; </pre> <strong>Step 2: Create database and schemas</strong><pre> create database drupal; \c drupal -- (assumes the use of psql) create schema one authorization one; create schema two authorization two; create schema three authorization three; </pre> <strong>Step 3: Sandbox users</strong><pre> alter user one set search_path = 'one'; alter user two set search_path = 'two'; alter user three set search_path = 'three'; </pre> <strong>Step 4: Install Drupal</strong> For the sake of brevity I am going to assume you have unpacked three copies of drupal in the same directory. Perhaps /home/www/one, /home/www/two, /home/www/three . At this point you would use your web browser and set up drupal normally. Just assign your users appropriately to each install and set your database to drupal. <strong>Step 5: Turn off caching (for testing)</strong> Go into the Drupal Administration pages and turn off caching for every install. <strong>Step 6: Alter users and sessions location</strong> This will break your installs initially. Don't fret. It does not really matter which one you pick but for consistency we will assume that the drupal install <strong>one</strong> is the canonical version.<pre> alter table one.users set schema public; alter table one.sessions set schema public; </pre> <strong>Step 7: Fix perms</strong><pre> create role drupal user one,two,three; alter table users owner to drupal; alter table sessions owner to drupal; grant insert,update,delete on users to drupal; grant insert,update,delete on sessions to drupal; </pre> <strong>Step 8: Modify settings.php</strong> Drupal offers the ability to use a single database for multiple installs using an array called db_prefix. Modify the value in each install to:<pre> $db_prefix = array('users' => 'public.', 'sessions' => 'public.',); </pre> <strong>Step 9: Test</strong> At this point you should be able to login to each site using the user/pass from the <strong>one</strong> install. To test it further add a new user to any of the installs and see if you can login on a different one. <strong>Step 10: Marvel (Oh and turn back on caching)</strong> That's right, marvel. No obnoxious plugins. Simple overhead. Works even if the installs aren't on the same machine (although you would need to modify pg_hba.conf and possibly postgresql.conf). Business OpenSource PostgreSQL SQL Wed, 21 Jul 2010 09:27:41 -0700 Let the jokes begin! PostgreSQL Conference West has changed locations. About a week ago I announced <a href="">PostgreSQL Conference West 2010 CFP.</a> In that CFP I also announced the location. A nice place, the Westin at Union Square in San Francisco. We were excited, the hotel was top knotch.<p>Then on Monday I received notice, the hotel acquisitions team (<a href="">EDB</a>) has received an amazing counter offer from a competing hotel.<p> The hotel is still in San Francisco, it is a four star hotel and the rate is much better for attendees (159.00 vs. 199.00). Here is the catch, which if you are reading on Planet you have to wait until after the jump.... Business PostgreSQL SQL Wed, 14 Jul 2010 12:28:20 -0700 PgWest 2010: Call for Papers PostgreSQL Conference West, The PostgreSQL Conference for Decision Makers, End Users and Developers, is being held at the St. Francis, Westin Hotel in San Francisco from November 2nd through 4th 2010. <a href="">Submit your talk.</a> <h3>Time line:</h3><blockquote>July 14th: Talk submission opens Sept 5th: Talk submission closes Sept 10th: Speaker notification </blockquote> This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: <ul><li>General PostgreSQL: <ul><li>Administration <li>Performance <li>High Availability <li>Migration <li>GIS <li>Integration <li>Solutions and White Papers </ul> <li>The Stack: <ul> <li>Python/Django/Pylons/TurboGears/Custom <li>Perl5/Catalyst/Bricolage <li>Ruby/Rails <li>Java (PLJava would be great)/Groovy/Grails <li>Operating System optimization (Linux/FBSD/Solaris/Windows) <li>Solutions and White Papers </ul> <a href="">Submit your talk.</a> Business OpenSource PostgreSQL SQL Mon, 12 Jul 2010 14:25:02 -0700 PostgreSQL High Availability options PostgreSQL is widely accepted as the most scalable and stable Open Source database in the industry. It is also known to hold its own against any of the proprietary databases as well. There are a plethora of High Availability options available for every workload and business requirement. Below is a brief listing of the common High Availability options for PostgreSQL. This is by no means an exhaustive list but it does provide some starting points. (and before anyone yelps, 9.0 isn't out yet) <strong>Log Shipping:</strong> Business OpenSource PostgreSQL Sat, 10 Jul 2010 12:22:01 -0700 Scala... really? I am not writing this to jump all over <a href="">Big Jim's post</a> but after reading it and seeing the syntax of Scala (and Java), I can't help but wonder, why anyone would use either language (based on syntax). Yes I know it is a matter of taste and everyone has an opinion. Let's just say my taste lean toward more succinct code. <pre> #!/usr/bin/python # # Set up initial work # import psycopg2 conn = psycopg2.connect("dbname='postgres' user='postgres'") cur = conn.cursor() cur.execute("SELECT * FROM pg_database") def output(cur): #Run two queries, one for headers, one for data tuples = cur.fetchall() colname = [x[0] for x in cur.description] buff = "\t" + "\t".join(colname[0:4]) + "\n" for row in tuples: # This is a little one liner but could easily be expanded # for readability buff += "\t" + "\t".join([str(i) for i in row[0:4]]) + "\n" return buff print output(cur) </pre> I keep looking back at Java merged/derived/munged/glued languages, <a href="http://">Groovy</a> looks interesting and of course there is <a href="">Jython</a> but I think I will stick with good old fashion CPython just as I am sure that <a href="">MST</a> will stick with Perl. Business OpenSource PostgreSQL Python SQL Thu, 08 Jul 2010 11:06:11 -0700 PostgreSQL 7.4, 8.0 and 8.1 END OF LIFE If you are running any version of PostgreSQL 7.4, 8.0 or 8.1, it is now time to upgrade to 8.3 or 8.4. The versions 7.4 and 8.0 are slated for end of life at the end of this month. The 8.1 version is slated for end of life in November. This is not an item to take lightly. Once a version is end of life you will not be able to get support (easily), there will be no more security updates and no bug fixes even if they are data loss bugs. I often find it disturbing how many people will run older releases. I am not talking about someone running 8.2 when 8.4 is out but we still see the occasional post on the lists about someone running 7.3! Remember folks, at a minimum keep your dot releases updated. The community does not release dot releases on a whim, it is for the protection of your data. Of course, if you need any help with <a href="/contact">upgrading don't hesitate to ask.</a> Business OpenSource PostgreSQL Python SQL Wed, 23 Jun 2010 14:36:52 -0700 WHERE bing = 't' I was on #postgresql today and someone asked an interesting question: (edited for readability)<blockquote>I'm trying to write a constraint for a table. The constraint should check for unique-ness of two columns, one string and one boolean. However I have special logic, I can have only one row with a given string and true attribute. I can have multiple rows with the same string but with false attribute. For example, I can have many {"abc",false}, but only one {"abc",true}. </blockquote> Now why anyone would need this isn't important. This is a great example of PostgreSQL and flexibility. PostgreSQL has the ability to <a href="">create partial indices.</a> The solution I came up with is below: <pre>create table foo(bar text, bing boolean); create unique index baz_index on foobar(bar,bing) where bing = 't'; insert into foobar values('1','t'); insert into foobar values('2','t'); insert into foobar values('1','f'); insert into foobar values('1','f'); insert into foobar values('1','t'); ERROR: duplicate key value violates unique constraint "baz_index" </pre> Exactly as it should be. Excellent. OpenSource PostgreSQL SQL Fri, 04 Jun 2010 13:09:09 -0700 Entering 9 days of PostgreSQL Dimension On the 6th, I leave town, travelling again to an unknown land. A land of mystery, a land of great mobster movies and incredible picture opportunities. Of course, I speak of Chicago where I will be delivering a 5 day training on our illustrious database, PostgreSQL. After the 5 day training, I will be taking a short jaunt to <a href="">South East Linux Fest</a> where I will be speaking on PostgreSQL Performance. The PostgreSQL Performance talk has been getting increasingly popular. I attribute the popularity to a couple of things. One, I don't get mired in the academics of performance. It isn't about finding that final Gentoo inspired 2%. It is about solving the 90% problem. What are the meat and potatoes of PostgreSQL performance and provisioning? It seems especially popular with those who don't want to be a DBA but still want to be confident that their installation is doing a above average job of being configured. A lot of database people at this point are going, "Excuse me? Above Average? It must be exacting in its performance profile!". I say bosh. Give me a great performing database that is above average so I can actually be productive chasing squirrels through New York's central park over agonizing over that 2% any day. Here is a tip. If you want that extra 2% and you want it quickly, buy new hardware. Don't spend weeks of man hours trying to find it. Business OpenSource PostgreSQL SQL Thu, 06 May 2010 11:21:25 -0700 Ubuntu LTS 10.04 is here! Well not really but I thought I would sound excited. I am an avid Ubuntu user. I like their philosophy. I like their code of conduct. I like what they are trying to do with Linux as a whole. I invite everyone to use Ubuntu. Unfortunately I have been unable to upgrade from Karmic. Why? Well, update-manager -d doesn't work because of some error with ubunut-minimal which (yes I researched it) was supposedly fixed, but it isn't. Yay!<p>I figured, no big deal. I will just download the ISO. I have 20Mb here, it only takes a couple of minutes. Download, burn, reboot, hit F8, select CDROM, ooohhhh, prettty purple.<p>Wait, what? What do you mean there are no operating systems on this computer!? (Minor heart attack) Oh, that's right, Ubuntu is still ignorant about Software RAID unless you are using the server ISO.<p>Sigh, I don't have time for this. You know what... My Windows 7 Laptop "just works". Business OpenSource Mon, 19 Apr 2010 13:54:57 -0700 Off to Linux Fest Northwest The great folks of <a href="">Linux Fest Nortwest</a> are hosting a PostgreSQL Track again this year. I will be speaking twice. First on what has ended up being a very popular utility, <a href="">PITRTools</a>. My second talk will be on Dumb Simple PostgreSQL Performance, which has been very popular with user groups. I look forward to seeing everyone again. Business OpenSource PostgreSQL Python SQL Tue, 30 Mar 2010 10:22:40 -0700 PgEast... over and an exciting announcement from the .Org infrastructure team So PgEast is over. You wouldn't know it yet by looking at the website, but it is. We maxed out at ~ 160 people. That is an almost 2x increase over last year at East. It is also a significant increase over West last October. This is an exciting time for this conference series. <p> The trainings were also successful, I had 19 (of a max 20) show for my Performance and Maintenance class, and I know the other classes had similar attendance. <p> Due to the success of East, we are going to be moving West to a hotel as well. There was overwhelming support for not returning to a college. Although most were supportive of the reason we used colleges in the past, they were also firm in their belief that further growth of the conferences will require a step up into the professional realm. That means a hotel. <p> There was also an exciting infrastructure announcement from the .Org sysadmin team. Core team member, Dave Page announced in his talk that .Org will be moving from FreeBSD and jails to Debian Linux and virtualization. This is a long time needed change and I am very glad to be part of the team that will be assisting in this move. I believe that this change will help us entice more people to be part of the sysadmins team and allow for a more diversified and flexible infrastructure. Business OpenSource PostgreSQL Python SQL Thu, 11 Mar 2010 14:24:00 -0700 14 days... and the Hotel is almost full for PostgreSQL Conference East I called the our hotel representative today because I was confused about why we had a deadline of 03/11 on the room discount. I was trying to push them to extend the date because we had met our room quota and I was wondering why they were trying to shut down the discount. Apparently, not only have we met our room quota, but the hotel is reaching capacity!<p>If you have not booked your hotel room for <a href="">PostgreSQL Conference East 2010</a>, now is definitely the time! If you do not book soon, you will be staying at another hotel (of course, you are still welcome to the conference). <p><li><a href="">Hotel Information</a> <li><a href="">Register</a> Business OpenSource PostgreSQL Python SQL Wed, 10 Mar 2010 13:10:16 -0700 15 days... and it all begins, PostgreSQL Conference East As we continue the countdown to the largest community and user conference in PostgreSQL history I am reminded of all the great content we have had in the past. Today while I was reviewing the curriculum for the <a href="">PostgreSQL Performance and Maintenance</a> class, I came across this great talk by Bruce Momjian as a further example of the high quality information you will receive not only from the various trainings but also all the other (over 50!) sessions at <a href="">PostgreSQL Conference East!</a><p> <div align="center">Inside PostgreSQL Shared Memory<br /> <embed src="" type="application/x-shockwave-flash" width="480" allowscriptaccess="always" allowfullscreen="true"></embed> </div> Business OpenSource PostgreSQL Python SQL Tue, 09 Mar 2010 10:22:20 -0700 PostgreSQL Conference East, Hotel Deadline! PostgreSQL Conference East, the largest PostgreSQL Conference for Users, Developers, Decision makers and anyone using PostgreSQL arranged for a hotel discount for attendees from the Radisson Warwick Hotel (the location of the conference).<p>The retail price of a double room is ~ 199.00. The discount rate is 132.00.<p>If you are attending PostgreSQL Conference East and you would like the discount you must register by the 11th of March. For more information:<p><li><a href="">Accommodations</a> <li><a href="">Agenda</a> <li><a href="">List of talks</a> <li><a href="">Register</a><p>Many thanks to our Premiere and Gold Sponsors:<p><li><a href="">Command Prompt, Inc.</a> <li><a href="">EnterpriseDB</a> <li><a href="">OmniTI</a> <li><a href="">OTG</a> <li><a href="">Red Hat</a> Business OpenSource PostgreSQL Python SQL Wed, 03 Mar 2010 10:04:06 -0700 5 Steps to PostgreSQL Performance As we inch closer to <a href="">PostgreSQL Conference East</a>, I have been reviewing some of the great talks given at PostgreSQL Conference West. One of those talks was Josh Berkus's great, "5 Steps to PostgreSQL Performance". Here it is below:<br /> <div align="center"> <embed src="" type="application/x-shockwave-flash" width="480" height="360" allowscriptaccess="always" allowfullscreen="true"></embed> </div><p> If you would like to see more great talks on PostgreSQL, make sure and visit the <a href="">website... and perhaps even attend PostgreSQL Conference East.</a> Business OpenSource PostgreSQL SQL Tue, 23 Feb 2010 11:03:21 -0700 PgEast Talks posted I blogged last week about a small list of content being up for the conference. I am now pleased to announce that 99% of the topics are up. I would say 100% but I keep getting new talks that look good and am trying to accommodate them as the schedule allows. When I review the talks, I see a lot of good content. Of particular interest to me is the talk from the FAA (yes that FAA) as well as Kevin Grittner's talk on Transaction Isolation. I am also keen on seeing Chander's training class on HS/SR/PITR but unfortunately I will also be teaching on Sunday. I won't be giving the keynote this year. Instead Ed Boyajian, President and CEO of EnterpriseDB will be. This is probably a good choice considering my Keynote is always somewhat of an Un-Keynote. I am curious to see where Ed thinks things are moving and how quickly. I will be doing the Conference launch and closing session. This year we will continue the trend of having a raffle at the end of the conference. If you <a href="">haven't registered yet, now is the time.</a> Business OpenSource PostgreSQL Python SQL Wed, 17 Feb 2010 10:54:00 -0700 Content, Content, Content... Oh My! (PgEast) When we moved PostgreSQL Conference East (<a href="">register here</a>)from a three day to a four day conference, I was concerned about our ability to pull it off. Primarily our conferences have been centered about the who's who of PostgreSQL. A nice mix of known contributors and avid users. A lot of the users, we would already knew as they contribute on the lists. <br />The migration to four days caused a need to expand our base. We actively starting soliciting from decision makers, educators, users and community contributors. I also know that several of our sponsors have been doing the same. So far it has paid off, we have more registrations at this point, than we have had at any other point historically for one of the PostgreSQL Conferences. The change seems to have been a true blessing. <br /> The influx of talks has been amazing. We don't have them <a href="">all up yet but you can get a taste here.</a> What I find truly great, is the amount of diversity in the talks. We have case studies from <a href="">Vonage</a>, in depth security talks from Magnus Hagander, Advanced talks on Transaction Isolation; even Core member Dave Page is crossing the pond to talk about the PostgreSQL Infrastructure. There seems no better time to hit <a href="">the PostgreSQL Conference series</a> than now. There is going to be content for everyone. <br> We have also kept up our promise to integrate tertiary communities into the conference with content on PostGIS, PHP, Python, Ruby and Grails. <br /> I was truly skeptical of the conference change. My hat is off to Platinum Sponsor <a href="">EnterpriseDB</a> for convincing me it was a good idea. Business OpenSource PostgreSQL SQL Mon, 25 Jan 2010 11:35:00 -0700 PostgreSQL Conference East: Early Bird registration is open, classes announced As everyone already knows, <a href="">PostgreSQL Conference East</a> is happening on March 25th through March 28th in Philadelphia. However, what is new is, <a href="">early bird registration is now open</a> and we have confirmed the three classes that will be taught on Sunday the 28th. <ul><li><a href="">PostgreSQL Administration</a> by Bruce Momjian. <li><a href="">PostgreSQL Performance and Maintenance</a> by Joshua D. Drake (yes me) <li><a href="">Point and Time Recovery and Hot Standby</a> by Chander Ganesan. </ul>The conference is shaping up to be the largest and most successful U.S. PostgreSQL Conference Yet. I hope you will join us. The <a href="">link to register</a> can be found on the main <a href="">PostgreSQL Conference site.</a> Business OpenSource PostgreSQL Python SQL Wed, 13 Jan 2010 12:14:23 -0700 Is the response to PgEast a sign of things to come? We announced the <a href="">CFP for PostgreSQL Conference East</a> back in December. Since that time, we have been working diligently to make this the best PostgreSQL Conference ever presented in the United States. The signing of Platinum partner <a href="">EnterpriseDB</a> and the subsequent change of venue has been an exciting foray into a new phase of the PostgreSQL Conference series.<p>However, what has really surprised me is the number of individual emails I have received from people. Generally speaking, the Conferences East and West are self populating. The community knows the conferences exist. They know it generates funds for <a href="">PgUS</a> and <a href="">.Org</a>. They also know that it is a chance to meet a lot of the contributors, learn and generally just have a good time.<p>What is new, is the people that are contacting me are not normal members of the community. You are not going to see them on -general or -hackers. These are true End Users. They represent the community, outside the community. What I want to know is where these people are coming from? Are they new to PostgreSQL? Are they exploring new alternatives to legacy databases such as MySQL? Perhaps PostgreSQL is just growing up.<p>Don't get me wrong, PostgreSQL for many years has far surpassed any open source database in overall capability, performance, management and features. However, the community as a whole has only recently realized the importance of cross pollination to other user groups and not being so anal retentive that all we do is turn off potential users. I think we are starting to see actual pay off there.<p>As an example, I know SelenaD and JoshB are both speaking at non-traditional conferences about PostgreSQL. For my part, I have been actively speaking at user groups around the Pacific Northwest, including Django, Python and next week Perl. My topic of course is, PostgreSQL Performance.<p>So, with all the changes in our ecosystem, I invite anyone to contact me about the conference, PgUS or .Org. I would love to just chat, possibly visit your user group or help you find a speaker for your user group.<p>Don't forget to come to <a href="">East. This conference is going to rock!</a> Business OpenSource PostgreSQL Python SQL Tue, 05 Jan 2010 15:24:20 -0700 PostgreSQL Conference East: Change of Venue and Dates East 2010 is taking it up a notch! This year, along with Platinum Partner EnterpriseDB, we will be making an aggressive marketing campaign not only to community but also professionals, and decision makers. With this aggressive marketing campaign we have adjusted the conference to be four days, March 25th - 28th. We have also moved from Drexel University to the Radison Plaza, Warwick Hotel. This is to better allow for business professionals outside of our normal community to attend the conference. It is also to allow for the most exposure to potential exhibitors. Yes, I said exhibitors. This year, PostgreSQL Conference East will have a limited exhibit space (13 (of 15) currently available). The exhibit space is within the main hall, where the Keynote, Social area and Food/Beverages will be provided. Please join Command Prompt and EnterpriseDB in making this the largest, most successful PostgreSQL conference ever! Business OpenSource PostgreSQL SQL Mon, 14 Dec 2009 12:06:39 -0700 PostgreSQL Conference East: 2010 Call for Papers <h1>Call for papers.</h1>December 14th, 2009, the PostgreSQL Conference U.S. team is pleased to announce the East 2010 venue and call for papers. This year the premiere East Coast PostgreSQL Conference will be returning to history Drexel University in Philadelphia. The event this year is being held at Drexel University in Philadelphia from March 26th through 28th. Following previously successful United States PostgreSQL conferences, we will be hosting a series of 3-4 hour tutorials, 90 minute mini-tutorials, 45 minute talks, 5 minute lightning talks and a new 30 minute presentation time slot. <h1>Time line:</h1><ul><li>December 14th: Talk submission opens</li> <li>January 30th: Talk submission closes</li> <li>February 15th: Speaker notification</li> </ul><h2><a href="">Submit Paper (You must be logged in)</h2></a>This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: <ul><li>General PostgreSQL: <ul><li>Administration <li>Performance <li>High Availability <li>Migration <li>GIS <li>Integration <li>Solutions and White Papers </ul><li>The Stack: <ul><li>Python/Django/Pylons/TurboGears/Custom <li>Perl5/Catalyst/Bricolage <li>Ruby/Rails <li>Java (PLJava would be great)/Groovy/Grails <li>Operating System optimization (Linux/FBSD/Solaris/Windows) <li>Solutions and White Papers </ul></ul>If you are using PostgreSQL as your platform, you need to be presenting at this conference! <a href="">Submit Paper</a>. (You must be logged in) Business OpenSource PostgreSQL Python SQL Thu, 19 Nov 2009 09:35:09 -0700 PDXDjango roundup: I finally got my Martini Shout out to Mark Long and Lacey Powers for showing up to support a Pg dude in a foreign land. Preceding my talk on PostgreSQL Performance, Adam Lowry gave and interesting intro to a database connection pooling module he wrote for Django. Essentially they bolted SQLAlchemy's pooler into Django. He then gave some metrics, showing that through the connection pooler they dropped overall request time in half. It was a basic and good indicator of why connection pooling is good, even on smaller applications. Business OpenSource PostgreSQL Python SQL Wed, 18 Nov 2009 09:41:44 -0700 Speaking at PDXDjango Tonight on PostgreSQL Performance I have the unexpected pleasure of speaking at PDXDjango tonight on PostgreSQL performance. Their meetings are 90 minutes with about 60 minutes (in theory) for the main speaker. The talk I am giving is a quick introduction to PostgreSQL Performance. The description I gave to the group was:<ul><li> I am a Django developer not a DBA. <li> I know nothing about PostgreSQL performance. <li> What 10 things (it will be more) can I change to make PostgreSQL faster? </ul>I am being as thorough as possible based on the time constraints and explaining what each option is and how it works. I didn't want to just say, "Change it to 10". If you are in the neighborhood, stop by and let me know all the things I say that are wrong. The meeting starts at 7:00PM. <pre> PIE 1227 NW Davis St Portland, Oregon (At the corner of NW Davis & NW 12th). </pre> OpenSource PostgreSQL Python SQL Thu, 22 Oct 2009 12:51:24 -0700 West wrap up and some videos I am back home and done recovering from Pg West 2009. I can now provide some closing thoughts. I think the conference as a whole went very well. This is the first time we did a zero swag conference and the majority of people didn't seem to mind. SWAG is probably the single largest contributor to time spent organizing a conference (in a single block). By not having swag has allowed the team is able to organize the conference from a single Rubbermaid. We had some scheduling snafus, mostly based around two different versions of the schedule being printed (my bad) and a certain speaker (yeah you Robert Hodges;)) not following the speakers list. In all though these were minor and the attendees took it all in stride. It was great. The Keynote was fun. I wrote the talk late, just finishing right before walking into the room. While writing the talk I was chatting with Big Jim (Jimbo from EDB). He made it a point to request special treatment for the EDB boss from Red Hat. You can get his name <a href="">from the slides.</a> When the video is up, you will see the particular ribbing that was provided. It was all in good fun of course and EDB was a great partner in the festivities. This time around I purchased SDHC based cameras which has enabled me to already start getting videos up. In fact the only thing stopping me from having them all up is that I have a quota on the account. <a href="">You can check here for videos and slides that we currently have up.</a> One thing that didn't happen was a closing session and I did hear some grumbling about that. I want to apologize to the attendees that wanted one. It is a good idea to have that and we didn't. In closing, I am glad it is over. I am glad I muscled through it (I strongly considered not having West this year). I am most glad that I focused on family during the after hours versus going to the various parties. No offense to the speakers or attendees. It is just what I needed this time around. We are already starting to talk about East as well as a Denver or Austin. Stay tuned! Business OpenSource PostgreSQL Python SQL Fri, 16 Oct 2009 15:53:00 -0700 87 people on a Friday What an amazing Friday. Normally on Fridays, PgWest and East are sparsely populated. Usually running in the mid 50s. Today was a great day with 87 people attending and many more slated to attend on Saturday. Even some unexpected contributors showed up such as Robert Bernier. If you are reasonably close, and if you are thinking about coming; now is the time to make the commitment. If you are here by Saturday, there is a great party planned by EnterpriseDB, Saturday night. If there are too many commas in this blog, it is the fault of my daughter. She said it was comma happy day. <a href="">Conference details here.</a> Business OpenSource PostgreSQL Python SQL Thu, 15 Oct 2009 13:10:23 -0700 In Seattle for PostgreSQL Conference West Well I made it to Seattle. It was a bit of disaster to get here with my Wife flatly stating she was suing a well known Mexican restaurant for hot beans and bad signing from a well known pop star. I have a meeting today with the college to verify facilities. I then pick up the programs. Of course the "suites" at Spring Hill Suites are not really suites but hey, the room is clean. Tomorrow I think I will be sitting in on the Howdah talk. It seems odd, but I have been so busy with PostgreSQL that outside of minor high level architecture discussions I am not aware of the supposed coolness that leaks from the pipes of Pylons + Howdah and PostgreSQL. Look forward to seeing everyone! Business OpenSource PostgreSQL Python SQL Mon, 12 Oct 2009 10:47:38 -0700 Loving community, PostgreSQL Conference West gets help I was very happy about the progress of <a href="">PostgreSQL Conference West this year.</a> We secured facilities earlier than we ever had before thanks to heroic efforts by Lisa Sandoval of <a href="">Seattle Central Community College</a>. Communication about the conference has been flowing well. We have a <a href="">great talk line up.</a> <a href="">Registrations are on par with last year, even with the economic downturn.</a> And then I got hit with a heavy six month contract that specifically requires my expertise. I am not complaining. The contract is a good one but it did cause me to be unable to do a lot of things all of a sudden. This is why I am loving community. A vacuum presented itself and the community automatically stepped up to fill the void. I am not organizing an after-party. <a href="">Gabrielle stepped in.</a> For good measure, <a href="">Selena backed her up.</a> Then, EnterpriseDB being the great community member they are asked if they could help. I would also like to thank Kevin Kempter and Brent Friedman for their continued persistence in helping me get things done. Of course let's not forget <a href="">all the speakers who have stepped up to give us such a great round of content.</a> Business OpenSource PostgreSQL SQL Wed, 07 Oct 2009 10:24:36 -0700 Everybody loves parties! (Pg Conference West in Seattle next week) If there is one thing the Open Source community knows how to do, it is party. Well, and drink but those do not necessarily go together at all times. Luckily, the Open Source community is also good at re-inventing the wheel, improvisation and general hackery. With that in mind, it is time to announce the after party info for PostgreSQL Conference West. Business OpenSource PostgreSQL SQL Thu, 24 Sep 2009 10:56:29 -0700 PostgreSQL Conference West 2009 update and Replicator 1.8.1 As mentioned over at the PgUS website, the PostgreSQL Conference West 2009 <a href="">registration is now open.</a>. I will be giving a talk on <a href="">Replicator 1.8.1</a> (which was just updated to 8.3.8). I will also be giving a talk on <a href="">PITRTools</a>. Business OpenSource PostgreSQL Python SQL Wed, 26 Aug 2009 09:43:45 -0700 PostgreSQL Conference West: Talk submission deadline extended until September 5th. In order to make our talk slots available to all who would like to give a presentation at PostgreSQL Conference West 09, we have extended our talk deadline until September 5th. If you have already submitted a talk, you will be notified of your acceptance (or not) by September 1st. For those that submit talks after August 25th, you will be notified by September 7th. As always you can find more information on The PostgreSQL Conference Series here: <a href="">Conference website</a> Business OpenSource PostgreSQL Python SQL Wed, 26 Aug 2009 09:39:50 -0700 PostgreSQL Replicator 8.3.1-1.8 Released From the hackers: <blockquote>Replicator 8.3-1.8.1 It is recommended to update your 8.3-1.8.0 installation. No changes required for replication databases or configuration files. Release notes: - Fixed a bug that lead to occasional crashes of the master's backend when performing updates to a table with dropped columns. - Fixed a couple of minor mcp_stat problems. <li><a href="">Download it here.</a> <li><a href="">Project site</a> </blockquote> Business OpenSource PostgreSQL SQL Wed, 05 Aug 2009 14:21:33 -0700 2nd Call for Papers: PostgreSQL Conference West Reminder: We are in the midst of the <a href="">PostgreSQL Conference West</a> call for papers. The call for papers ends 08/20/09. If you wish to be considered to present you must <a href="">submit a talk.</a> Business OpenSource PostgreSQL SQL Wed, 29 Jul 2009 12:21:54 -0700 Replicator 8.3-1.8 Released! I am pleased to announce the immediate availability of Replicator 8.3-1.8. Everyone jump start <a href="">their engines over here.</a> Business OpenSource PostgreSQL SQL Wed, 24 Jun 2009 12:00:00 -0700 PostgreSQL Conference West 2009 Call for Papers <strong>PostgreSQL Conference West 2009 Call for Papers</strong> June 24th, 2009, the PostgreSQL Conference U.S. team is pleased to announce the West 2009 venue and call for papers. This year the premiere West Coast PostgreSQL Conference will be leaving its roots at Portland State University and moving north to sunny Seattle, Washington. The event this year is being held at Seattle Central Community College from October 16th through 18th. The move to Seattle opens up a larger metropolitan area for continuing to expose databases users, developers, and administrators to the World's Most Advanced Open Source Database. Following previously successful West Coast conferences, we will be hosting a series of 3-4 hour tutorials, 90 minute mini-tutorials, and 45 minute talks. This year we will be continuing our trend of covering the entire PostgreSQL ecosystem. We would like to see talks and tutorials on the following topics: <strong>General PostgreSQL:</strong> <ul><li>Administration <li>Performance <li>High Availability <li>Migration <li>GIS <li>Integration <li>Solutions and White Papers </ul><strong>The Stack:</strong> <ul><li>Python/Django/Pylons/TurboGears/Custom <li>Perl5/Catalyst/Bricolage <li>Potato <li>Ruby/Rails <li>Java (PLJava would be great)/Groovy/Grails <li>Operating System optimization (Linux/FBSD/Solaris/Windows) <li>Solutions and White Papers </ul>If you are using PostgreSQL as your platform, you need to be presenting at this conference! <strong><div align="center"><a href="">Submit your talk</a> (You must be have an account on the site)</div></strong> *** The PostgreSQL Conference U.S. series is an autonomous Educational Project used to educate all comers on the use of The World's Most Advanced Open Source Database. Proceeds from the event are donated directly to United States PostgreSQL; the 501c3 non-profit for PostgreSQL education and advocacy in the United States. Business OpenSource PostgreSQL Python SQL Fri, 12 Jun 2009 10:52:12 -0700 The shortest path between two points Recently I was doing some benchmarking on one of our machines. The benchmarking wasn't going so well due to <a href="">bad batteries on the RAID controller</a>. I had instructed one of our System Administrators to take care of the problem. Long story short, the Administrator went down a very long trail to an obvious solution. The trail was well mapped, thought out and precise. It however missed some important points. When I caught on to the long trail she was taking I asked, "What is the shortest path between two points?". She replied, "On a plane or sphere?". That is when I knew we were in trouble. Now most people would have just said, "Huh?". Luckily I have been blessed with at least a modicum of technical/mathematical knowledge and a general experience with Geeks for 18 years. Let's review. <strong>The System Administrators trail was:</strong><ul> <li>Visit Colo to check cards physically after receiving BIOS message <li>Record all information about cards including BIOS versions <li>Research possible cause of batteries not charging, find that some versions of the BIOS can do that. Thus causing yet another trip to the colo for installation. <li>Go back to colo to update BIOS revisions to see if that resolves the problem<ul> <li>If that doesn't solve the problem, research new batteries for order </ul></ul><strong>My trail:</strong><ul><li>System reports batteries are bad <li>Known fact: Hardware was bought used <li>Buy new batteries <li>Upgrade BIOS during new battery installation </ul>What is the difference? I run into this a lot with technical people. They become hyper focused and they are not able to abstract their problem solving skills to include the entirety of the problem. Now you say, "What the problem is the batteries don't work, fix them." It isn't that simple. Using the System Administrators path, the solution to the problem cost at least 1500.00-2000.00. Using my path, the cost is 550.00. My path is a single trip to the collocation facility with cards drop shipped, an hour to replace and upgrade BIOS. Same resolution, ~ 37% of the cost. <strong>But... But... what if that doesn't fix the problem?</strong> Then you know you have bad cards and it will still cost less to replace them that to perform multiple trips to the colo. Again it isn't that the System Administrators path was incorrect. In fact I would bet a lot of businesses would think it was the absolutely correct direction to take. I would rather just write off the cards and replace them. We can make far more money from the System Administrator if they are not focusing on long and winding roads to the same destination that can be reached by not taking a left at the fork. Business Fri, 22 May 2009 09:38:41 -0700 Hardware problem solved, when you really need some cache. As reported in my last blog, Stefan was having much greater success with his pgbench results than I. In reviewing why, we found a problem with the hardware. What I like about this problem is that the results in the <a href="">previous blog post</a> become more interesting. As a reminder I was running 16 connections over 4 different users at 1M transactions. Below is the results from a single user from that batch: <pre> pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 101.024360 (including connections establishing) tps = 101.024392 (excluding connections establishing) </pre> Over 16 connections we were getting ~ 400 TPS. I verified that this was consistent by running a second test with a single user and 4 connections. The results: <pre> pghost: localhost pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 404.021738 (including connections establishing) tps = 404.022316 (excluding connections establishing) </pre> So, what is it that causes a machine with plenty of resources to perform in such a consistently slow manner?. You can only write data as fast and the spindles turn. That is why they invented cache. The results should look very similar to Stefan's once we replace the battery cache. OpenSource PostgreSQL Thu, 21 May 2009 14:13:54 -0700 Thanks Stefan So while doing the benchmarking of the various parameters, Stefan pointed out the my numbers were ridiculously low. I wasn't really paying attention because I was looking at differences between parameters but then he posted me an example of a single thread pgbench using my same parameters. His machine is a dual core connected to 10 spindles on a NetAPP. In theory my machine should be faster. It is not. His configuration, like mine was all defaults. Stefan's numbers. <pre> scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 1681.615766 (including connections establishing) tps = 1681.622950 (excluding connections establishing) </pre> So now I am trying to figure out what is up with my hardware. Business OpenSource PostgreSQL SQL Thu, 21 May 2009 09:09:29 -0700 8.3.7 TPS and checkpoint segments Continuing my postgresql.conf changes I ran a new test yesterday with checkpoint_segments set to 300. As a reminder the original results and specs of the machine being used in the test are <a href="">here.</a> The results of the new test below: <pre> pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 101.024360 (including connections establishing) tps = 101.024392 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.796885 (including connections establishing) tps = 100.796924 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.801501 (including connections establishing) tps = 100.801534 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.852900 (including connections establishing) tps = 100.852944 (excluding connections establishing) </pre> O.k. so same test, same hardware, 10x more checkpoint_segments gives us ~8 percent improvement. Business OpenSource PostgreSQL SQL Wed, 20 May 2009 10:58:13 -0700 Default TPS performance of 8.3.7 I recently purchased some used hardware for some performance testing of PostgreSQL. I didn't want to interrupt the great work that Mark Wong was doing with the PostgreSQL Performance Lab. The testing I am doing is a bit different than Mark's. Where Mark is testing various filesystem performance via PostgreSQL using DBT2 and FIO and wanted to go up a level. I am testing using the PostgreSQL tool pgbench which is available in contrib. I am also testing in a basically default environment as a way to see how changing different parameters of the postgresql.conf changes overall performance. <strong>Hardware Configuration</strong> <blockquote>Newisys Quad Opteron 846 32GB of memory (2) MSA 30s (RAID 10, 14 drives each) (2) HP 6402 Controllers (one for each MSA) </blockquote> <strong>Operating System Configuration</strong> <blockquote>Ubuntu Hardy LTS x86_64 /array1 , ext3, data=writeback elevator=deadline </blockquote> Outside of the minor operating system changes the system remained in default. The only postgresql.conf parameter I changed was to set checkpoint_segments to 30. <br /> <strong>pgbench configuration</strong> I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was: <pre> /array1/jd/pgsql/bin/pgbench -U bench01 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench01& /array1/jd/pgsql/bin/pgbench -U bench02 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench02& /array1/jd/pgsql/bin/pgbench -U bench03 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench03& /array1/jd/pgsql/bin/pgbench -U bench04 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench04& </pre> <strong>Results</strong> <pre> pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 92.279931 (including connections establishing) tps = 92.279960 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.674708 (including connections establishing) tps = 91.674739 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.583754 (including connections establishing) tps = 91.583782 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.616330 (including connections establishing) tps = 91.616355 (excluding connections establishing) </pre> Each pgbench process executed 4M transactions with an average of ~ 24TPS per client. That is pretty miserable. However remember this is defaults and the defaults will make you checkpoint quite a bit (every 20 seconds or so) with the above stress test. Stay tuned for results as we change specific parameters to see the effect each one has. OpenSource PostgreSQL SQL Mon, 11 May 2009 10:15:47 -0700 Surprising events from a top 20 list In a completely unscientific review of community popularity I was reviewing the active members of various channels on #freenode. For those that don't know, Freenode is the defacto destination for the Open Source community on IRC. This is where you will find official community support channels for such open source luminaries as #gentoo, #ubuntu, #mysql and of course #postgresql. OpenSource PostgreSQL Python SQL Fri, 24 Apr 2009 08:44:01 -0700 My turn on Oracle purchasing Sun. I feel like I am coming late to this topic. All the pundits have already had there say and the <strong>blogosphere</strong> has been rampant. I have been talking with a lot of MySQL folks lately, encouraging them to at least test PostgreSQL as an alternative. MySQL folks are nervous. They don't like the <em>opportunity</em> Oracle brings to the table. This morning I was asked quite bluntly, "From your perspective what is the future of MySQL?". PostgreSQL OpenSource Business Fri, 17 Apr 2009 15:39:30 -0700 The great netbook giveaway At PostgreSQL Conference East, Platinum Sponsor <a href="">EnterpriseDB</a> raffled two Netbooks. This is the video of the raffle (only 5 minutes). Of particular interest is a certain Major Contributors response. PostgreSQL OpenSource Tue, 14 Apr 2009 14:16:02 -0700 Escaping data madness We had an interesting issue crop up this past week. The question was, "How do we properly escape the following string...?". The string was:<pre> You can't have it that way can you? </pre> That seems like a pretty simple string right? On insert you would do one of the following:<pre> (E'You can\'t have it that way can you?'); ($$You can't have it that way can you?$$); ('You can''t have it that way can you?'); </pre> You would think that would be the end of it. However, If you are using ODBC with a pass through query you will receive the error, "The # of binded parameters is < than the # of parameter markers." Yes, that's right. ODBC will parse the ? and interpret it as a parameter. This affects psqlodbc and ODBCng. Apparently it is actually not a bug [1]. I am not sure I agree with that, regardless of what Microsoft says. What is particularly interesting here is that it is specifically the ? that is the problem. Not the single quote. To work around this problem you can execute the query like this:<pre> INSERT INTO foo VALUES ($$You can't have it that way can you?$$); INSERT INTO foo VALUES ('You can''t have it that way can you?'); </pre> Of course neither of those are actually standard (the E'\'' is standard). 1. <a href="">MSDN Data Platform developer center</a> PostgreSQL OpenSource Tue, 31 Mar 2009 09:49:19 -0700 Registration closing for PostgreSQL Conference East As a reminder for all of those in our community that like to register at the last minuted (that means most of us), registration will be closing on Wednesday April first. On line registration is much easier than registering at the door so please bounce on over to <a href=""></a> and get your registration in. PostgreSQL OpenSource Business Thu, 26 Mar 2009 10:50:18 -0700 PostgreSQL Training Command Prompt has offered training since we began as a one man shop back in 1997. It has always been, "by request". Since that time a lot of things have changed and we are regularly receiving training requests from multiple companies. We have decided to finally bring our training into the light and make it a forefront of the services that CMD offers. Interestingly we are going to be providing a lot of ad-hoc, webcast style training (as well as traditional on site or classroom). The number one training we are asked for is something that is half day that covers a specific topic such as backup and restore or configuring Point in Time Recovery. These types of courses will be cost effective for even small establishments and will be held over the Internet. We will also be participating in the open curriculum community ensuring that most if not all of our curriculum is freely available for self starters. What other training provider is going to do that? Here is our current <a href="/services/training">list of pre-defined courses</a>. We will be adding a dozen or so more in the next 90 days. PostgreSQL OpenSource Business Mon, 23 Mar 2009 10:45:31 -0700 PostgreSQL Conference East: Final Schedule announced PostgreSQL Conference East is the Eastern segment of the United States PostgreSQL Conference series. The event is being held at Drexel University. It starts on April 3rd and runs through April 5. We kick the conference off on April 3rd with three sessions. For DBAs we have Mastering PostgreSQL Administration a four hour training, presented by PostgreSQL Core Team member Bruce Momjian. For Developers we have a Database Normalization, a 4 hour workshop. Finally for those who are seeking information on the upcoming 8.4 release we have a 3 hour guide to PostgreSQL 8.4 presented by Major Contributor Robert Treat. April 4th and 5th are a plethora of mini-tutorials and sessions. The talks range from Pylons and Grails development with PostgreSQL to Understanding Column Level privileges, Windowing Functions, the Art of Indexes, four presentations on different Replication technologies and a Performance Round Table. In all we have 35 sessions. There is no question that there is something at this conference for every PostgreSQL user. We will close out the conference with a raffle of two Asus EEEPC 1000 preloaded with Ubuntu Intrepid and Postgres Plus from <a href="">Platinum Sponsor EnterpriseDB.</a> The proceeds of East are being donated directly to the United States PostgreSQL (PgUS) Association. You may <a href="">view the schedule.</a> <a href="">You may register here.</a> PostgreSQL OpenSource Business Thu, 19 Mar 2009 10:49:22 -0700 More PostgreSQL Conference goodness, West 08 Keynote This is the Keynote I gave at West 08 last October. Topics include updates on the various non-profits, reach out to tertiary communities, recognition of the importance of non direct PostgreSQL participation as well as many others (such as 8.4 and replication). Those interested in participating in the <a href="">upcoming East should visit here and register.</a> PostgreSQL OpenSource Business Wed, 18 Mar 2009 10:59:07 -0700 Remembering excellence from past PostgreSQL Conferences Just about 5 months ago we held <a href="">PostgreSQL Conference, West 2008</a> in Portland Oregon. It was a very successful conference with approximate 20% growth over the previous West in 2007. It was also a three day conference over the previous West event which was a single day conference. As we prep to hold <a href="">PostgreSQL Conference, East 2009</a> at Drexel University I wanted to look back at one of my favorite talks from West. Developing a PL for PostgreSQL is a hilarious but technically accurate presentation on creating procedural languages for PostgreSQL, the catch? The presentation uses LOLCode. PostgreSQL OpenSource Tue, 03 Mar 2009 14:38:18 -0700 Pg Conference East 09, Registration open This year is shaping up to be an even larger even than last year with 3 days, four rooms, and multiple tracks. East runs from April 3rd to April 5th at Drexel University. To register please point your <a href="">Open Source web browser to PgUS.</a> Registration is free for Students and Professors and starts at as low as 40.00 for University staff. Once you have registered make sure to <a href="">visit and subscribe to the attendees list.</a> The attendees list is the way to find out about all the goings on of the conference. Here is a sampling of the content to be presented at this years East:<br /> <strong>Web</strong><blockquote> An Introduction to the Pylons Web Application Framework<br /> Architecting Your PostgreSQL Application for the Cloud<br /> Grails In Practice<br /> Building A Collaborative Environment With PostgreSQL To Enhance<br /> The Learning Experience<br /> </blockquote> <strong>Replication/HA</strong><blockquote> PostgreSQL Backup/Recovery and Replication<br /> Replication using PostgreSQL Replicator<br /> Reconciling and comparing databases using schemas, DBI-Link and Slony<br /> Bucardo<br /> Introduction to Golconde<br /> Configuring a Warm Standby, the Easy Way<br /> </blockquote> <strong>Performance</strong><blockquote> Predicting Postgres Performance: Practical Queueing Theory for Postgres DBAs<br /> pgcrypto benchmarking<br /> The Art of Indexes<br /> Effects of Flash and SSDs on PostgreSQL<br /> Using and Abusing pgbench<br /> </blockquote> <strong>8.4</strong><blockquote> Column-Level Privileges, and other changes coming in 8.4<br /> Trees and More in PostgreSQL: Common Table Expressions<br /> Windowing Functions: Putting the TPS in TPS Reports<br /> No More Waiting, A guide to PostgreSQL 8.4<br /> </blockquote> <strong>Usage, Development Newbie and Administration</strong><blockquote> The power of psql<br /> Playing with Playr: The Postgres Application Testing Tool<br /> Postgresql and Java<br /> Converting your database and application from Sybase/MSSQL to PostgreSQL<br /> ERP built by Postgres: We don't need no stinkin toolkit!<br /> PostgreSQL and Temporal Data<br /> Database Development Policies<br /> Monitoring Postgres with<br /> More Than Storage: Intro to PL/pgSQL<br /> Socially Relevant Database Projects in the Undergraduate Classroom<br /> </blockquote> PostgreSQL OpenSource Business Thu, 19 Feb 2009 11:57:24 -0700 Seven things <a href="">Theo Schlossnagle</a> recently wrote a blog post called <a href="">seven things.</a> The idea is, seven things that you "might" want to know about him. Along those lines he listed me as someone he would like to know seven things about. It has taken me a while to get to the post because I have been busy with <a href="">various</a> <a href="">things</a>. So here we go, seven things about me.<blockquote><ul><li>I have crashed a car at 135MPH and walked away. <li>I did not finish High School. <li>I am a <a href="">master gardener.</a> <li>I am an entrepreneur not a computer geek. <li>I think the Open Source community needs to learn when to be quiet. <li>I hate it when people think I should care about <strong>x</strong>. <li>I spent two recent years being someone, I am not. This problem has been resolved. </ul></blockquote> The second part of this blog is I am supposed to list seven people I would like to know seven things about. This has struck me as more difficult than I imagined. Most of the people I want to know about, don't blog and barely email. Many other people don't interest me. Here we go:<blockquote><ul><li><a href="">Michael Stonebraker</a>, because PostgreSQL doesn't interest him. <li><a href="">Magnus Hagander</a>, because he is my counterpart in <a href="">Europe</a>. <li><a href="">Richard Stallman</a>, because I have never seen a bigger hippy that is still living. <li><a href="">W. Somerset Maugham</a>, because he is one of the best authors I have ever read (yes I know he is dead). <li><a href="">Bruce Momjian</a>, because I realized he is a friend. <li><a href="">Robert Treat</a>, because I think he will struggle like I did to come up with this list. <li><a href="">Tom Lane (TGL)</a>, because after all these years he will still graciously answer my email. </ul></blockquote> PostgreSQL OpenSource Tue, 17 Feb 2009 11:19:16 -0700 PostgreSQL Conference: Talk deadline approaching The deadline (Feb, 23rd) is fast approaching for PostgreSQL Conference East talk submissions. <a href="">Get your talk in today! </a> PostgreSQL OpenSource Thu, 12 Feb 2009 11:16:41 -0700 PostgreSQL mininum requirements There has been a lot of talk through the years of what the minimum set of hardware requirements are for a PostgreSQL database. Generally speaking the requirements for PostgreSQL are very low, you can even get by on 256 Megs of memory. However you rarely hear or read about what would be considered the minimum production hardware requirements for PostgreSQL.<p> A primary reason for this is that every business has their own requirements and thus one persons minimum requirements are not another persons minimum requirements. Of course since we are in geek world and many geeks are uniquely pedantic and unable to see outside of their own bubble, my requirements may be <strong>wrong</strong> without any understanding of business requirements as a whole. So what do we do? We voice our opinion anyway. PostgreSQL OpenSource Wed, 11 Feb 2009 14:15:45 -0700 What is that sound ... ... ... that is the sound of JD stomping. After years of listening to Dave Page, Magnus Hagander, Josh Berkus, David Fetter, Stefan Kaltenbrunner, Ads, Gabrielle, JPA and numerous other contributors that I should mention but won't. I have finally submitted to get a passport. PostgreSQL OpenSource Business Fri, 06 Feb 2009 15:36:07 -0700 Configuring Pylons on Ubuntu Hardy I recently configured a complete <a href="">Pylons</a> + <a href="">PostgreSQL</a> environment for a customer. The operating system was (of course) <a href="">Ubuntu Hardy</a>. The system included the use of <a href="">Simpycity</a> and <a href="">WSGI</a>. Although I could never done it without the Pylons documentation, I found that it was unnecessary complicated for those who <strong>just want to get it done</strong>. PostgreSQL OpenSource Wed, 04 Feb 2009 09:34:46 -0700 2nd call for papers: PostgreSQL Conference East! PostgreSQL Conference East is being held at historic Drexel University on April 3rd through 5th 2009 . This is the second call for papers. The call for papers ends Feb 23rd and speakers will be notified on the 27th. You may <a href="">submit your talk here.</a> We are looking for a wide range of topics. Can you speak on any of the below topics? What about a different topic? As long as it is centered around PostgreSQL we want to hear about it. <strong>Hacker topics:</strong> <pre> MVCC C Function development Writing Procedural Languages Creating types The planner Optimization tips Explaining the process model </pre> <strong>DBA topics:</strong> <pre> Backing up PostgreSQL Understanding and Configuring Autovacuum Normalization Trigger Happy (how to use triggers) PITR -- happiness is a shipped transaction log User / Groups / Roles Security </pre> <strong>End User development:</strong> <pre> Web Frameworks with PostgreSQL Pylons Grails Rails Cake Turbo Gears Django </pre> <strong>Solutions:</strong> <pre> Do you have a successful case study to present? How did you solve a problem with PostgreSQL? Do you have an Open Source product that runs on PostgreSQL? </pre> As always we let the presenters drive the feel of the conference. If you have an itch, let's figure out how to scratch it (as long as it is with PostgreSQL). <a href="">Submit your paper today.</a> PostgreSQL OpenSource Business Tue, 03 Feb 2009 08:29:38 -0700 PostgreSQL and Replicator at Northwest Python Day Last Saturday I gave a talk at the Northwest Python Day in Seattle. Unfortunately it was a short talk of only 30 minutes so I didn't get to cover all the topics I wanted but I was able to briefly share on PostgreSQL and on configuring PostgreSQL (Mammoth) Replicator. Just for grins I started the talk off with a question, "Please raise your hand if you are running Ubuntu." There were over 50 people in the room. Over half raised their hand. World domination is coming along nicely. I was asked two questions at the end of the talk. One was about how to have many masters replicate to a single slave. Similar to the salesman problem where they have a database of information that has to sync up to the main hub once a day or something like that. This particular application was doing security polling and the gentlemen wanted to have all the nodes report centrally. Replicator isn't really designed for that. I suggested looking at Slony which is a little more flexible with obscure configurations. The second question was about the mcp server and if the master/slaves would recover should the mcp be unavailable for a period of time. Yes, they will. The trip itself was pretty uneventful but it was nice to get out of town for a couple of days. PostgreSQL OpenSource Tue, 27 Jan 2009 15:47:12 -0700 Reflecting on the great community discussion As I diligently prepare for <a href="http://www.postgresqlconference/">PostgreSQL Conference East 09</a> by trying to ensure that we have enough talks <strong>(hint: talk submission closes Feb 27th)</strong>. We have a suggested hotel and that everyone knows it is going on (including the Groovy, Python, MySQL, PHP and Ruby communities), I take heart in knowing that our community <a href="">PostgreSQL</a> can have a well thought out, pointed and <a href="">productive discussion</a> like no other. I was thinking what other great and historical discussions have we had? <blockquote><li>Vi vs. Emacs? (answer: joe) <li>cvs vs. git vs. svn (answer: svn) <li>python versus perl (answer: Python) <li>C vs C++ (answer: C, but only because I have had patches accepted) <li>fbsd vs linux (answer: my nick is linupoet, you guess) <li>Red versus Blue (answer: They both suck) </blockquote> Any that I missed? PostgreSQL OpenSource Mon, 26 Jan 2009 10:02:37 -0700 East 09: Call for papers! PostgreSQL Conference, U.S., East 09 will be held in Philadelphia at historic Drexel University from April 3rd through 5th. The <a href="">call for papers is now out.</a> <p>As always we let our submissions define our actual tracks. If you have something you want to talk about it. Submit it. As long as it is about PostgreSQL (or doing something with PostgreSQL) we will consider it. <p>We are seeking creative ideas about things we can do at the conference. At West we had a code sprint. The sprint was very successful as it was about all things postgresql and open source. It wasn't just hacking back end code but people worked on all kinds of things.. Is someone up for running a code sprint? <p>There has also been specific interest in having us add (in addition to our advanced topics) a newbie track. Please do not be afraid to submit a talks on items such as:<p><blockquote><li>Backing up PostgreSQL <li>Understanding and Configuring Autovacuum <li>Normalization <li>Trigger Happy (how to use triggers ;) <li>PITR -- happiness is a shipped transaction log </blockquote> <p>Other topics we are interested in beyond the standard PostgreSQL architectural fanfare are:<blockquote><li>Groovy/Grails <li>Django <li>PHP <li>Pylons <li>SQL Alchemy </blockquote> So don't delay, PostgreSQL Conference, U.S. is the premeire PostgreSQL conference series for the United States PostgreSQL community! <a href="">Submit your talk today.</a> PostgreSQL OpenSource Business Wed, 21 Jan 2009 16:05:41 -0700 FK, CHECK, ENUM or DOMAIN. That is the question. We have a customer that recently asked me to comment on which I would use for a particular problem. This is a simple validating lookup. For example, CHECK(VALUE IN ('foo','bar')). Should we use a CHECK constraint, FK, ENUM or DOMAIN? <p>A CHECK constraint is easy to apply and has simple syntax. It is also extremely flexible in solving other types of validating problems. If your valid values change you must DROP CONSTRAINT and ADD CONSTRAINT. You can not add an element to the CHECK. <p>A Foreign Key creates the requirement of a lookup table. It also offers the easiest management of valid values. You just INSERT, UPDATE or DELETE from the lookup table. If you are a smart monkey and using natural keys versus artificial ones, you can avoid the JOIN on SELECT as well. <p>ENUM registers as a type in PostgreSQL. This means if you use an ENUM extensively you are basically locking yourself into the use of the type. In short if you need to modify an ENUM you drop the ENUM and recreate it. You can't drop an ENUM if a relation is using it. There are some interesting functions available with ENUM but I am having a hard time seeing a use case for the type as a whole. An ENUM type in theory lends itself specifically to this type of problem so I have included it. <p>A DOMAIN for this problem suffers from the same problems as ENUM as it registers as a type. However a DOMAIN is more flexible as you can apply complex logic to the validation (just as you can with a CHECK). For example a DOMAIN could contain the regex to validate if a email address is correctly formed. I have used domains many times in the past to create complex validating types. They are useful. <p>So what does all this boil down to? I have listed the pros and cons of managing each method above but what I haven't mentioned is performance. What is the particular performance bottleneck for each method? Read on, to find out for yourself. PostgreSQL OpenSource Tue, 13 Jan 2009 08:47:07 -0700 Replicator meeting log for 01-08-09 is up The PostgreSQL + Replicator meeting logs for 01-08-09 are up. This was a long meeting held on #replicator using the Freenode IRC service. Topics covered were the removal of the Single Point of Failure of the MCP which is partially done. The new in the PostgreSQL backend forwarder works but now the question is how to safely handle failover. <a href="">Take a look, maybe you have an idea.</a> PostgreSQL OpenSource Business Sun, 11 Jan 2009 11:55:51 -0700 PostgreSQL Conference / PgCon.US update In an attempt to ensure the continued positive growth of the community, PostgreSQL Conference, U.S. is going to change its current policy toward the domain PgCon.US. PostgreSQL OpenSource Business Fri, 09 Jan 2009 15:43:18 -0700 PITRTools: Multiple slave support I gave a lightning talk at <a href="">Pg Conference: West 08</a> about a tool that I have developed call PITRTools. PITRTools is a python based log shipping utility. Essentially it wraps itself around standard tools such as rsync, and pg_standby to provide a usable experience. Some of the features is provides are: <blockquote> <li>Auto initialization of environment <li>Simple base backups <li>Monitoring of Master <li>Arbitrary alerts <li>Failover <li>Failover actions <li>etc... </blockquote> In all it is by far the simplest and most effective tool for PostgreSQL standby that I have used (of course I wrote it so...). PostgreSQL OpenSource Business Tue, 06 Jan 2009 12:25:21 -0700 PostgreSQL Replicator Update 01.06.09 I know it appears that it has been pretty quiet since we open sourced Replicator but that isn't the case. We have been actively working on 1.9 and fixing 1.8 Beta as bug reports have come in, including a bug to sequence replication. However, 1.9 is the true milestone release where we have finally moved away from the original architecture. For those that don't know, the original architecture looked like this: <pre> master-->mcp | | ----------------- | | | s0 s1 s2 </pre> The mcp would handle all communication and data transfer between the master and the slaves. The idea behind the architecture was to achieve maximum efficiency for the master, meaning that the number of the slaves never affected the performance of the master. However this architecture came with a cost. A single point of failure. If the mcp were to ever crash, replication would stop. When we started down the path of 1.9 it was made very clear to me by Alvaro and Alexey that this was not acceptable. In return I made it very clear that any architectural change we made must not suffer from the Slony problem (performance degradation based on number of subscribers). Together we were all very clear to each other and Alvaro came up with a new architecture. The new architecture calls for a "Forwarder" process and from a topological view doesn't look much different than the MCP. It does however offer us a great deal more flexibility and stability. Here is the new architecture: <pre> master-->forwarder0 | | ----------------- | | | s0 s1 s2 </pre> How is this different? It is different in a couple of ways. First, the forwarder is now integrated into the PostgreSQL backend. This removes the mcp binary entirely. It also greatly decreases the redundancy of the code. Secondly if the primary forwarder goes down a slave can become the forwarder. This removes the single point of failure. You can read more information on the <a href="">forwarder here.</a> Other items up for idle thoughts on the possibilities of this new architecture is a single monitoring point. With versions of replicator <= 1.8, you can have a clear idea of which replication transactions have been received and transfered to each slave but you must access each slave individually to see what transaction has actually been restored. So what else is coming with Replicator 1.9? In continuing our cleanup of the architecture we are completely rewriting the ROLE and GRANT/REVOKE replication. This is actually the first step of the other half of the <strong>Major Feature</strong> of Replicator 1.9, DDL replication. We are currently investigating the opportunity to have certain DDL operations automatically replicate. The most obvious of these would be: <blockquote><li>CREATE TABLE <li>ALTER TABLE <li>CREATE DOMAIN <li>etc... </blockquote> We decided to pass on replicating CREATE FUNCTION due to complexity in dealing with externally linked libraries as well as various dependency problems. We may look at this again in the future. For more information on this feature please <a href="">visit the thread.</a> If you are interested in testing you can grab the 1.8 Beta or 1.9 from <a href="">SVN</a>. You can also get the 1.8 Beta from <a href="">The pgsqlrpms project.</a> The next developer meeting is on 01.08.09 at 11:00AM PST. We are holding in in the #replicator channel on Freenode. All are welcome. PostgreSQL OpenSource Business Mon, 05 Jan 2009 13:15:29 -0700 PostgreSQL Conference: West 2007, More video up I have gotten a couple more of the West 2007 Videos. <blockquote><li><a href="">PostgreSQL Partitioning</a> - Robert Treat </li> <li><a href="">Babel of PLs</a> - David Fetter</blockquote> PostgreSQL OpenSource Mon, 05 Jan 2009 11:53:04 -0700 PgDay LFNW: Call for Papers! (04/25/09) <a href="">PostgreSQL Conference, U.S.</a> is having a PgDay at <a href="">LinuxFest Northwest</a> in Bellingham Washington on April 25th, 2009. The PgDay (and LinuxFest Northwest) is a free event. We are holding the PgDAY on the first day of the event (a Saturday) parallel with LFNW. There was over 700 attendees to LFNW last year. LFNW is hoping for even more this year! In short, we are looking for some PostgreSQL talks (45 minutes each) to fill out the day. Please <a href="">click here to submit your talk.</a> PostgreSQL OpenSource Business Mon, 15 Dec 2008 13:25:28 -0700 PostgreSQL Conference: West 07, two videos added As I was working on PostgreSQL Conference this weekend I happen to crawl under my desk to pick up a pin I dropped. As I was crawling back out I smacked my head on the top of the desk and in the process jostled an empty (black) computer case that was sitting there. I noticed something on top of the case so I took a long hard look and behold! It was the Western Digital USB drive the West 07 videos were on. Now most of you wouldn't think that was a big deal except that I completely forgot we did video for West 07 and two, I thought that hard drive was long lost to the computer parts gods. I plugged it in and viola! We have videos. Of course the first thing I did was rsync them all off so we have a backup. I then formatted it because it was using HFS and that is just unacceptable. Anyway, the first two videos I pulled from the vault were:<ul> <li>Stupid Solaris tricks : Josh Berkus</li> <li>Ruby on Rails Essentials for PostgreSQL Enthusiasts | David Wheeler</li> </ul> They can both be found at <a href=""></a>. PostgreSQL OpenSource Business Mon, 15 Dec 2008 13:07:08 -0700 PostgreSQL Conference: East 09, when to hold survey (closed) I have closed the survey, "When to hold it" and <a href="">provided the results.</a> Of particular interest to me was the majority wanted Late March/Early April but a significant amount were also interested in having the conference in Early June. I am also glad to see that people were not interested in conflicting with Ottawa as I hear it is a very good conference. PostgreSQL OpenSource Fri, 12 Dec 2008 08:33:22 -0700 PostgreSQL Conference: Perl5 is Alive Although I don't follow the Perl world very much, my respected peer Matt S. Trout emailed me wondering when I would be able to get the <a href="">Perl 5 is Alive!</a> video up. Apparently there are some buffoons spreading FUD about the state of this very much alive, very much supported, very much developed, very much kicking language. So take a look at the link above and see for yourself. If you are a Perl fan you need not worry! PostgreSQL OpenSource Wed, 10 Dec 2008 14:15:13 -0700 PostgreSQL Conference: East 2009, When should we hold it? I have been negotiating (with the help of Bruce and others) with various parties to determine where we are going to hold East 2009. One of the questions that keeps popping up is, "When are we going to hold PostgreSQL Conference East: 2009". Since we now have this nifty new <a href="">Drupal</a> (with PostgreSQL) driven web site, we should take advantage of the ease of use and ask for some feedback. If the community wouldn't mind weighing in that would be helpful. <a href="">Just visit the site and follow the links.</a> PostgreSQL OpenSource Tue, 09 Dec 2008 10:32:05 -0700 Explain: Why do I have to recheck my condition? As with any PostgreSQL question, the first place you should look for answer is the <a href="">PostgreSQL docs.</a> I was recently reviewing the EXPLAIN docs as freshen up on some query tuning fu and I came across this little gem: PostgreSQL OpenSource Tue, 02 Dec 2008 09:40:31 -0700 Pg Conference: Videos up! (well some of them anyway) Last March at PostgreSQL Conference: East we video taped almost all the talks. It has taken some time to get them up and we are still encoding (and learning the tricks of the trade) but we do have a few up now that people may enjoy. PostgreSQL OpenSource Business Tue, 25 Nov 2008 16:29:53 -0700 PostgreSQL Conference: A new platform In my on going efforts to secure a location for the upcoming East (MIT, Penn State, Drexel and even the Marriot are on the list), I am bound and determined to revamp the entire <a href="">Pgcon.US website</a>. I have several problems/goals I wish to solve. <ol> <li>A more community orientated site. Currently the site is 100% manage by me. Although it does use PHP it is essentially flat files. <li>A social site. I want people to use the site. We have a lot of excellent educational content and I want to make sure and utilize that. <li>Promotion. I want to promote our speakers. Most use their own money to help our community. They should be acknowledged. First step, blog aggregation. No I don't want to compete with <a href="">Planet</a> or <a href="">Planet</a> but the more linked a blog is the better. <li>A better sponsors interface. I want sponsors to feel as if they are partners. Possibly even allowing them to create custom content. <li>A push to <a href=""></a> . My thought is that <a href=""></a> will become a portal for all postgresql conference about to happen. For example the <a href="">Canadian International conference.</a> </ol> So <a href="">take a look</a> let me know what you think.</a> Don't complain about a lack of an email. If you can't find my email, you probably shouldn't be emailing me. PostgreSQL OpenSource Business Thu, 20 Nov 2008 08:59:20 -0700 PostgreSQL Certification: JTA results released The PostgreSQL Certification project has released the results of the Job Task Analysis. Thanks to everyone who participated in this important step toward delivering a quality certification. 223 members took the time and effort to fill out the survey. A couple of interesting results. There were 213 members felt we needed a certification. That is a sharp contrast to exposed opinions of some in the community. Of all the results Linux topped the requested operating systems (no surprise) but Windows was number two (I was surprised). Other operating systems that made a decent showing were the various BSDs. If you haven't done so yet I invite you to <a href="">join the community.</a> The results <a href="">can be found here.</a>. PostgreSQL OpenSource Business Sat, 15 Nov 2008 11:38:08 -0700 PostgreSQL Certification: JTA now closed Thanks to everyone that took the time to participate in the PostgreSQL Certification JTA. We received over 223 respondents which is great. We will be posting the results of the JTA shortly and if you would like to participate in the resulting project discussion <a href="">please join us.</a> PostgreSQL OpenSource Business Mon, 10 Nov 2008 13:57:58 -0700 PostgreSQL Certification (JTA closes November 14th) The PostgreSQL certification project is in the closing days of the JTA (Job Task Analysis). In short, what should the PostgreSQL certification project, certify? In hacker terms, "What is the problem we are trying to solve?". It is relatively long but the information is of extreme value to ensure that the project develops a relevant certification to the professional PostgreSQL community. If you haven't done so already <a href="">please create an account</a> and the <a href="">proceed to the JTA.</a> PostgreSQL OpenSource Business Tue, 04 Nov 2008 20:30:25 -0700 You are not good enough (for Open Source) A segment of the talk I gave at NAU on why you should contribute to Open Source and why you think you can't. Original idea credited to Matt S. Trout. <div align="center"> <embed id="VideoPlayback" src="" style="width:400px;height:326px" allowFullScreen="true" allowScriptAccess="always" type="application/x-shockwave-flash"> </embed> <br /> Updated 11/05/08 from Matt Trout: <a href=""> the version he gave at YAPC-EU 2008</a> PostgreSQL OpenSource Business Tue, 28 Oct 2008 22:57:14 -0700 The Open Source job market The following is a segment of the talk I gave at Northern Arizona State University about the Open Source job market. <center> <embed id="VideoPlayback" src="" style="width:400px;height:326px" allowFullScreen="true" allowScriptAccess="always" type="application/x-shockwave-flash"> </embed> </center> PostgreSQL OpenSource Business Mon, 27 Oct 2008 13:34:15 -0700 PostgreSQL Replicator developer meeting 10/28 With the recent open sourcing of Replicator, the team has been trying to come up with ways to ensure an open development process. In that light we have decided to have our first release 1.9 meeting on Freenode. PostgreSQL OpenSource Business Mon, 13 Oct 2008 14:04:31 -0700 Replicator 1.8 Beta released as BSD It took longer than we expected, but Replicator 1.8 for 8.1 and 8.3 of PostgreSQL has been released. It is 100% Open Source and of course, BSD. <a href="">Replicator all your baby elephants here.</a> PostgreSQL OpenSource Business Sun, 12 Oct 2008 20:37:23 -0700 Thanks to all that helped with PostgreSQL Conference West PostgreSQL Conference West was a big hit this weekend. It exceeded expectations of attendance as well as content. I would like to take a moment to thank the people that took time out of their personal lives to help make this conference a success! <ul> <li>Daniel Browning <li>Selena Decklemann <li>Lisa Drake <li>Josh Berkus <li>Richard Broersma Jr. <li>Tim Bruce <li>Rafael de Jess Fernndez Moctezuma <li>Lacey Powers <li>Gabrielle Roth <li>David Wheeler <li>Mark Wong </ul> Thank you all for the time you spent to help make this conference a success! Without you there is no way we could have pulled it off. PostgreSQL OpenSource Business Fri, 10 Oct 2008 06:45:19 -0700 On the morning of West, East 08 appears! In preparing for West I decided we weren't going to go through the hassle we went through at East with recording. We ran out of tapes, had to borrow all the cameras, even used some digital cameras video mode. That combined with a lack of hardware to pull the video off of tapes caused content from East08 to be significantly delayed on getting to the web. PostgreSQL OpenSource Business Tue, 30 Sep 2008 09:59:13 -0700 Pg Conference West: Last call for Lightning talks and tentative schedule released! Lightning talks are an exciting way to get involved in the conference with very little commitment on the speakers end. Assuming you can stand in front of an audience for 5 minutes; you can speak about anything PostgreSQL or Open Source related. <ul> <li><a href="">Submit your lightning talk.</a> <li><a href="">Register for the event.</a> <li><a href="">Tentative talk schedule.</a> </ul> PostgreSQL OpenSource Business Thu, 18 Sep 2008 11:10:00 -0700 Pg Conference West: Lightning talks! While recently seeking feedback on the conference schedule from Josh Berkus and David Fetter I was asked, "Are there going to be lightning talks?". To which I replied, "What?". PostgreSQL OpenSource Business Mon, 15 Sep 2008 09:49:40 -0700 WEST Conference shaping up nicely Once again West this year is running on a truncated calendar. <a href="">Registration is now open!</a>. We had originally planned on announcing and organizing from June till end of conference. Unfortunately that didn't work out as planned and we are back on the 6-8 week time line. Nothing like Just in Time delivery! PostgreSQL OpenSource Business Mon, 18 Aug 2008 20:39:53 -0700 PostgreSQL Conference: West. October 10th-12th Call for papers The second annual PostgreSQL Conference: West is being held on October 10th through October 12th 2008 in the The Native American Student & Community Center at Portland State University. PostgreSQL OpenSource Business Wed, 23 Jul 2008 20:21:26 -0700 PostgreSQL leads OSCON again For the what seems like yet another year (can't we slow these guys down?), OSCON has proven to be the PostgreSQL stomping ground. Per our usual supply of great community members including, Selena, Gabrielle, Michael, Greg, Robert and the other Robert we had what seemed liked an endless supply of quality support and community reaction to all comers. PostgreSQL OpenSource Business Thu, 01 May 2008 12:23:28 -0700 PostgreSQL 04/24/08 thru 05/01/08 As I digg and slashdot my way through the weary set of tubes that connects us all, I have stumbled across a number of interesting (good and bad) posts in the last week. The first of which comes from our friends doing <a href="">Ruby on Rails development</a>. PostgreSQL OpenSource Wed, 23 Apr 2008 10:31:07 -0700 Is that performance I smell? Ext2 vs Ext3 on 50 spindles, testing for PostgreSQL There are few things I like better than when a customer says to the team, "I want the best machine I can buy for XXX dollars". It inspires a certain sense of joy not unlike the feeling an average Slashdot reader gets when they walk into the local gadget store. It is particularly special because you know as much as you <strong>could</strong> make use of such a machine, you <strong>know</strong> you would never justify the expense. PostgreSQL OpenSource Fri, 18 Apr 2008 13:54:25 -0700 In the news again today :)... but for something technical Command Prompt made news today at the grace of our very good partners in the PostgreSQL Community, <a href="">Truviso</a>. We are working with Truviso to implement some very cool vacuum features that will help with long running transactions. PostgreSQL OpenSource Business Fri, 18 Apr 2008 10:27:47 -0700 Additional comments about my talk at MySQLCon Colin Charles blogged about <a href="">my talk at MySQLCon</a>. I wanted to clear a few things up that he mentioned. I noted in my talk: EnterpriseDB is the opposite, theyre closing up more and more. I spoke with Bob Zurek who is the CTO of EnterpriseDB. I was not correct in my EnterpriseDB comment. My reference came <a href="">from this page</a> which is very difficult to tell which is Open Source and which is not. I still think they would benefit more if they would be to just open everything. PostgreSQL OpenSource Business Thu, 17 Apr 2008 20:23:57 -0700 What MySQL (and really, Sun) can learn from PostgreSQL I spent April 17th, 2008 flying to San Jose, Ca only to arrive 30 minutes before my talk, "<a href="/files/mysql_learn.pdf">What MySQL can learn from PostgreSQL</a>", jump in a cab and literally walk into the door of the room I was assigned, "right on time". I think the talk went over fairly well. I opened with the statement, "This is not about MySQL AB, this is about MySQL and the community." I think it help set the tone for the presentation. I didn't want people to feel like I was attacking a profit model or a company. PostgreSQL OpenSource Business Fri, 11 Apr 2008 23:11:41 -0700 And finally here is the keynote Selena and I are a bit behind getting content up on the <a href="">PostgreSQL Conference East</a> site, so I thought I would get my keynote out there. <a href="">Keynote PDF</a> PostgreSQL OpenSource Business Sat, 29 Mar 2008 08:48:25 -0700 At East... keynote went well I delivered the Keynote for <a href="">East</a> this morning. It seemed to go very well, with a full discussion on expanding the community. I spoke on using mentoring contacts as well as organizing the community for free work shops. There is a surprising and well received number of new community members here. PostgreSQL OpenSource Tue, 25 Mar 2008 08:12:26 -0700 Only 3 days left for PostgreSQL Conference: East Online registration ends for PostgreSQL Conference East on March 26th at 5:00pm PST. PostgreSQL Conference: East is being held at the Univerisity of Maryland, College Park in the CSIC building. PostgreSQL OpenSource Fri, 21 Mar 2008 13:14:45 -0700 Read only templates, PDXPUG March 20th, 2008 I was at <a href="">PDXPUG</a> last night. While everyone was introducing themselves, they also mentioned one of their least favorite items about PostgreSQL. It was interesting to hear everyone's experiences. PostgreSQL OpenSource Wed, 19 Mar 2008 14:28:30 -0700 Less than two weeks to go until East! Calling all Elephant herders! On March 29th and 30th 2008, The PostgreSQL Community Conference: East is set to unleash the preeminent source of community interaction, knowledge exchange and learning to ever land on the East Coast! Not since the Founding Fathers sat huddled around candles extolling the virtues of declaring Independence from our taxation without representation overlords has a single more important event be presented to the general populous. PostgreSQL OpenSource Business Thu, 06 Mar 2008 13:08:42 -0700 and 3 weeks left until PostgreSQL Conference: East! Its been a busy community season for me over here. A couple weeks back Greg Smith started down the path of getting a documentation wiki up for PostgreSQL. CMD offered to host and the rest is as they say, history. PostgreSQL OpenSource Thu, 21 Feb 2008 15:30:31 -0700 No, I am not a dirty hippy. In other words, on licensing. I find for many things the BSD License, leaves a bad taste in my mouth. Not all things of course but some. To me it allows theft of work product. I know this is not the legal interpretation, it is a philosophical one and CMD is just as guilty of this theft as others in the PostgreSQL community including EDB and GreenPlum. PostgreSQL OpenSource Business Sun, 10 Feb 2008 10:38:13 -0700 PostgreSQL at SCALE day 2 It's the second day of the Southern California Linux Expo (well third, but second for PostgreSQL) and things are looking good so far. When walking the floor yesterday I was greeted by the pleasant surprise a customer,(<a href="">Randr</a>) has a booth at the show. I am glad to see that their Open Source business (based on PostgreSQL of course) is doing so well. PostgreSQL OpenSource Sat, 09 Feb 2008 11:45:20 -0700 PostgreSQL at Southern California Linux Expo I am currently staffing the PostgreSQL booth at the <a href="">Southern California Linux Expo</a>. Initially the show was a little slow but I as kindly reminded as I reminisced about the high traffic days of OSCON that the Keynote for the show actually takes place mid morning. Once the key note ended traffic increased and with traffic increases comes new and old community members. PostgreSQL OpenSource Fri, 25 Jan 2008 12:03:06 -0700 Thoughts on the PostgreSQL EU non profit. I was talking with a peer about the particular PGEU problem that I am currently arguing on the <a href="">following thread.</a> I wanted to see if I could explain my position outside of the thread to allow the thread to stay productive. OpenSource PostgreSQL Wed, 02 Jan 2008 19:09:08 -0700 Call for Papers: PostgreSQL Conference East The call for papers went out tonight for PostgreSQL Conference East which is being held in College Park Maryland on March 29th and 30th. At West, we only had one day and a series of 9 talks. This time around, we have two days and three rooms... PostgreSQL OpenSource Mon, 17 Dec 2007 13:52:47 -0700 Phew... Handling PostgreSQL Conference East 08 When Selena and I started with the Fall Conference last October we had no idea what a success it was going to be. O.k., Selena swears she knew but I was surprised. After Fall there was zero question that we were going to do an East. The community demand was just too high and we have a volume of not only contributors but also general community on the East coast. PostgreSQL OpenSource Mon, 19 Nov 2007 15:57:45 -0700 PostgreSQL Conference East 08 (updated) It's that time, after a wildly successful conference last October in Portland, Oregon we are now beginning to ramp up for the East Coast 08 conference! The current plan is to host a two day conference of Tutorials (new) and Talks on March 28th and 29th. The currently designated location for the conference is the Univserity of Maryland. This will be confirmed within two weeks. PostgreSQL OpenSource Business Sat, 10 Nov 2007 10:38:08 -0700 PostgreSQL logging, time for a change I have often thought that PostgreSQL logging although very flexible is also unwieldy. PostgreSQL has so many logging options, it is difficult if not impossible to find the "right" logging setting without a lot of trial and error. PostgreSQL OpenSource Wed, 31 Oct 2007 14:42:12 -0700 PostgreSQL Conference Fall 2007: My wrap up We had a great time at the conference and subsequent party. If you are ever in PDX and want some great food and interesting accommodations I strongly suggest the Paramount Hotel and Dragon Fish cafe. They treated all of us very well and had great food! The conference did well above expectations, signing on 10 sponsors, some at the last minute (LinuxFund). Our original goal was only 4. And as has been mentioned in other places already, we had well over the 35 attendees we expected, topping out at 60+. That is a great day for our community! Further because of our sponsors and our attendees, we were in a single day able to raise over 5000.00 dollars for the community. Not bad considering we only had 8 weeks to prepare and it is the first conference that the team had done together. The money of course all went to SPI our affiliated 501c3. The money raised will be used to continue our community growth including sponsoring speakers for other shows, purchasing new shirts and the upcoming live CD for 8.3. Of course the team isn't sleeping until next year, we are already busy on our other upcoming conferences. We are having a PostgreSQL Web Technologies mini-conf in February 08. This will be attached to SCALE and will be similar in format to our PGDay last July. We are also having a PostgreSQL Conference East 08! This show is being held in late Winter, early Spring. We are very excited about this conference as we have a swell of community members in the area. It is going to be held in the D.C./Maryland area, likely be 1.5 days and we are hoping for over 100 community members to attend. Make sure you watch for the call for speakers for both of these upcoming shows. Lastly the conference website: <a href=""></a> has all the audio from the talks up. We also have "some" of the slides (more forthcoming) and the video will be up in a couple of weeks! Thanks to everyone again for their support of this conference series. It is great to see the community growing and supporting each other. PostgreSQL OpenSource Fri, 19 Oct 2007 15:49:46 -0700 PostgreSQL Conference: Good Lord... twice as many as we expected When Selena Deckelmann and I decided to start a PostgreSQL Conference series, we believed that a small, regional, technically specific conference was appropriate. We considered that at most, we would get around 35 people and it would essentially be a large user group meeting that had a series of talks and was good for the community. That was the end of July. PostgreSQL OpenSource Mon, 15 Oct 2007 10:22:25 -0700 plphp 1.3.5 Beta 1 Released Not much to say here but plphp 1.3.5 Beta1 supports named parameters and PostgreSQL 8.3 Beta1. Enjoy :) <a href="">PL/php website</a> PostgreSQL OpenSource Tue, 09 Oct 2007 15:54:35 -0700 PostgreSQL Gotcha: Default timestamps are not exact! If you are compiling PostgreSQL from source you have a configure option called: <pre> --enable-integer-datetimes </pre> Now Debian/Ubuntu wisely turn this option on by default, unfortunately the RPM provided by PostgreSQL.Org and the RPM provided by RedHat/Fedora do not. PostgreSQL OpenSource Mon, 01 Oct 2007 11:35:37 -0700 The easy but harder than I thought road to PostgreSQL Conference Fall 2007 The PostgreSQL Conference Fall 2007 has given me a knew appreciation for the little things. If it wasn't for <a href="">PDXPUG</a> leader Selena Deckelmann the conference would not have the complimentary breakfast/social nor would it have the synopsis of the talks on the <a href="">conference website.</a> The hard work of Selena, myself and the speakers appears to be paying off. We have a strong list of sponsors as well as a very strong list of speakers. See for yourself: PostgreSQL OpenSource Fri, 21 Sep 2007 10:06:21 -0700 Log level correlations There was a <a href="">post recently on using Syslog with PostgreSQL on pgsql-general.</a> As you can see, Tom Lane kindly replied in the thread. I decided that it might be a good idea to submit a <a href="">DOC patch.</a> I only received on comment on the patch, so I don't know if it will be applied or not. The comment, caused me to consider creating a table for the docs that correlated the different log levels between PostgreSQL, Syslog and Eventlog. PostgreSQL OpenSource Thu, 13 Sep 2007 14:32:31 -0700 PostgreSQL Conference Fall 07 When sitting in the booth with the Army of Smurfs (tm) at OSCON 2007, I was struck by the idea that having PostgreSQL Conference(s) (yes plural) could be a great way to insure the growth of our community. Check -- duh! You would think that this is obvious. In fact I would think this is obvious. This is why the community had the Anniversary two years ago and Dan Langille was successful with his PgCon last May. However, I foresaw what I concluded was a fatal flaw in the traditional trade show, in that... only those in the know, actually attend and only those who can expense it, will normally travel for them. PostgreSQL OpenSource Thu, 23 Aug 2007 21:51:17 -0700 PostgreSQL 8.3, it's faster, really it is! I downloaded the latest check out of PostgreSQL 8.3 (not even beta yet) just to see how things are progressing. Of particular interest to me was a recent conversation about sequential scans I had with Jeff Davis. In short, in 8.3 sequential scans should be faster. PostgreSQL Thu, 23 Aug 2007 12:19:39 -0700 How many rows do I have anyway? Have you ever wondered how many tuples you have in a relation? Normally to find out how many tuples you have you would do something like: <pre>SELECT count(1) FROM rows; count ------- 10002</pre> PostgreSQL OpenSource Thu, 09 Aug 2007 10:54:07 -0700 Surrogate versus Natural Primary Keys This is a constant source of argument, flame and general discomfort with any database design. On the PostgreSQL lists it comes up occasionally and it is always a long drawn out thread with people arguing on each side about which one is correct. PostgreSQL Wed, 08 Aug 2007 11:54:43 -0700 LewisC and the PostgreSQL vs EnterpriseDB: Hypocritical crap? Yet another round of EDB PR came out today and of course, some in the community disagreed with it. This time I was not a big objector although I do think their PR was a little misleading, I think it was generally a reasonable release and that the community needs to relax a bit. <a href=""> You can see that thread here.</a> PostgreSQL OpenSource Business Fri, 27 Jul 2007 09:58:55 -0700 OSCON 2007, PostgreSQL army of smurfs! OSCON 2007 was a huge success for the PostgreSQL community. Of course, OSCON is usually a good conference for PostgreSQL but this year was different. So what was different about this year than other years? How did we stand out from other community booths? What about MySQL? PostgreSQL OpenSource Business Mon, 23 Jul 2007 18:59:49 -0700 PgDay Portland, A huge success! On July 22nd, PostgreSQL.Org held a single day conference in Portland Oregon preceding OSCON 2007. This conference, although short notice was a huge success. We had solid attendance from new and old community members. PostgreSQL OpenSource Tue, 17 Jul 2007 10:45:49 -0700 For the record, EnterpriseDB and Command Prompt, Inc. I recently made a post on the pgsql-advocacy list about a press release that EnterpriseDB put out that was less than flattering about PostgreSQL [1]. This thread was long and a little tiring. To make matters worse an Oracle blogger [2] picked up the thread and blogged an incorrect assessment of what happen. I would like to take a moment and set the record straight. PostgreSQL OpenSource Business Wed, 27 Jun 2007 10:09:42 -0700 From the field: On Josh's Rules (of Database Contracting) Josh Berkus wrote an excellent bullet point list of things to do and not do when doing <a href="">Database Contracting.</a> I would like to expand on that list and add some comments to a couple of his points: PostgreSQL Business Mon, 18 Jun 2007 12:57:59 -0700 Training, hot seat style We have been stewing here at CMD for some time over training. Command Prompt actually does quite a bit of PostgreSQL Training. In the 12 months preceding April of 2007 we had taught a dozen classes on PostgreSQL. These classes have always been directed at on-site training, meaning that someone from CMD would travel to the customer site to train their employees. We did this for two reasons. One, because CMD is a widely dispersed corporation without any central location. Two, because we felt that our other training partners (namely <a href="">OTG</a> and <a href="">Big Nerd Ranch</a>) were better suited to classroom style training. We still believe that and thus Command Prompt, will not be offering any classroom style training in the foreseeable future. PostgreSQL Tue, 12 Jun 2007 15:48:37 -0700 PostgreSQL Party July 22nd Command Prompt is working with the PostgreSQL community to have a PostgreSQL party on July 22nd. For those not calendar aware, that is the Sunday before OSCON starts in Portland. For more information please visit <a href=""></a>. PostgreSQL OpenSource