FOSSExperts, day 2
Posted Wednesday Jul 28th, 2010 09:35am
by Joshua Drake | Permalink



I expected feedback from the community on FOSSExperts. 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.

How do deal with disagreement about the deliverable:

This is an interesting one. I wanted to keep FOSSExperts simple. That is why the deliverable on the ALTER TABLE project is simple, committed to PostgreSQL Core.

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.

The second one is to take a vote. It would work something like this.

• Developer states project is complete and demonstrates completeness based on the deliverable.
• Every person who funded the project votes on whether or not the project is complete.
  • If 66% vote the project complete, developer gets their money.
  • If less than 66% vote project incomplete, developer doesn't get their money.

  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).

  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.

  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?

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. If you have ideas, please share them. We have setup a flame page just for this.

Remember a lot of your questions can be answered in the FAQ as well.

---

blog comments powered by Disqus

FOSSExperts, a new way to fund Open Source (Beta)
Posted Tuesday Jul 27th, 2010 03:37pm
by Joshua Drake | Permalink



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? FOSSExperts of course.

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 Kickstarter. With our focus obviously being on a different kind of creative.

This is long overdue in the FOSS Community. There are a great deal of communities out there (LedgerSMB 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.

What FOSSExperts is not, is a place to send money to global projects such as Debian or PostgreSQL.org. It is for specific, well defined proposals and has a specific and defined delivery as well as refund policies etc.

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. So take a look, and let the rage begin! If you like, you can review one of the larger proposals already on the site as well.

---

blog comments powered by Disqus

A better backup with PostgreSQL using pg_dump
Posted Friday Jul 23rd, 2010 11:23am
by Joshua Drake | Permalink

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.

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:

pg_dump dbname > outfile

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:

pg_dump -U $username --format=c --file=$mydatabase.sqlc $dbname

The options in detail are:

 -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

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:

pg_restore -U $username --dbname=$dbname --index=$indexname

If you wanted to restore only a single function:

pg_restore -U $username --dbname=$dbname --function=$functionname(args)

If you wanted to restore only a single table:

pg_restore -U $username --dbname=$dbname --table=$tablename

For more information on all the pg_dump options, please see the reference page. Restoring the dump The command used to restore a backup file is pg_restore. It has similar options to pg_dump. A simple restore:

pg_restore -U$username --dbname=$databasename $filename

Where filename is the name of the backup file.

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.

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:

psql $dbname < $backupfile

Backing up every database 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:

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;

If someone knows of some Windows code that produces a similar result, it would be great if you would share.

Remember, pg_dumpall creates a plain text backup. This means you will need to use psql to restore the globals backup file.

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.

---

blog comments powered by Disqus

Multiple Drupal installations, single login, 10 steps
Posted Thursday Jul 22nd, 2010 08:21pm
by Joshua Drake | Permalink



We have several Drupal 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 PostgreSQL Conference. 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.. Step 1: Create users

psql -U postgres;
create user one with encrypted password 'foo';
create user two with encrypted password 'bar'
create user three with encrypted password 'baz';

Step 2: Create database and schemas

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;

Step 3: Sandbox users

alter user one set search_path = 'one';
alter user two set search_path = 'two';
alter user three set search_path = 'three';

Step 4: Install Drupal 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. Step 5: Turn off caching (for testing) Go into the Drupal Administration pages and turn off caching for every install. Step 6: Alter users and sessions location 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 one is the canonical version.

alter table one.users set schema public;
alter table one.sessions set schema public;

Step 7: Fix perms

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;

Step 8: Modify settings.php 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:

$db_prefix = array('users' => 'public.',
                 'sessions' => 'public.',);

Step 9: Test At this point you should be able to login to each site using the user/pass from the one install. To test it further add a new user to any of the installs and see if you can login on a different one. Step 10: Marvel (Oh and turn back on caching) 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).

---

blog comments powered by Disqus

Let the jokes begin! PostgreSQL Conference West has changed locations.
Posted Wednesday Jul 21st, 2010 09:27am
by Joshua Drake | Permalink



About a week ago I announced PostgreSQL Conference West 2010 CFP. 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.

Then on Monday I received notice, the hotel acquisitions team (EDB) has received an amazing counter offer from a competing hotel.

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.... Read more...

---

blog comments powered by Disqus

PgWest 2010: Call for Papers
Posted Wednesday Jul 14th, 2010 12:28pm
by Joshua Drake | Permalink



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. Submit your talk.

Time line:

July 14th: Talk submission opens Sept 5th: Talk submission closes Sept 10th: Speaker notification

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:

• 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
  Submit your talk.
  

  ---

  Categories: Business, OpenSource, PostgreSQL, SQL
  
  
  blog comments powered by Disqus

PostgreSQL High Availability options
Posted Monday Jul 12th, 2010 02:25pm
by Joshua Drake | Permalink



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) Log Shipping: Read more...

---

blog comments powered by Disqus

Scala... really?
Posted Saturday Jul 10th, 2010 12:22pm
by Joshua Drake | Permalink



I am not writing this to jump all over Big Jim's post 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.

#!/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)

I keep looking back at Java merged/derived/munged/glued languages, Groovy looks interesting and of course there is Jython but I think I will stick with good old fashion CPython just as I am sure that MST will stick with Perl.

---

blog comments powered by Disqus

PostgreSQL 7.4, 8.0 and 8.1 END OF LIFE
Posted Thursday Jul 8th, 2010 11:06am
by Joshua Drake | Permalink



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 upgrading don't hesitate to ask.

---

blog comments powered by Disqus