SFPUG Beta Test Day

From PostgreSQL wiki
Jump to navigationJump to search


The San Francisco PostgreSQL User's Group is planning a PostgreSQL 9 beta-test day on April 3, 2010. This page is to help coordinate this event.


  • Noisebridge, San Francisco
    • 2169 Mission Street
    • Plenty of power, other people to draw in. Need to ask them.
    • Kitchen
    • Near BART
    • *Parking is scarce*, plan to take public transportation. If you have to drop off equipment, coordinate before drop-off so that someone can receive it.


Will be served. Thanks, EnterpriseDB for sponsoring this.

Help We Need

  • Set Up Crew: 2 people to help set up the venue starting at 10am.
  • Clean Up Crew: 2 people to help clean up after us after 6pm.
  • Packaged Database Applications with Test Suites: if you know an application like Rails, phpBB, LegerSMB, OpenStreetMap etc. well, please bring packages and sample databases to distribute so we can test it with version 9.

Computer Setup(s)

Add standard setup(s) here. To avoid connection confusion, we may want to assign hard-coded IPs to the machines instead of using DHCP. If so, we can start a list of IP addresses and associated machine descriptions. If you can volunteer a machine, please list its info in the section below.

Testbed Computer List

Test Machines
Machine Owner Architecture OS IP assigned
David Fetter x86_64 Mac OS/X 10.6 t.b.d.d00d
David Fetter x86_64 (VM) Windows XP SP2 t.b.d.d00d
David Fetter x86 Linux t.b.d.d00d
Josh Berkus x86 Ubuntu 8.04
Josh Berkus x86 Mac OSX 10.5
Josh Berkus x86 Ubuntu 9.10 remote access


These are things you need to bring to make the test day successful.

  • One or more computers.
    • ... with all of the tools required to compile PostgreSQL and any supporting modules you plan to use. Ask on-list if you have questions.
    • ... For Windows users, have the beta packages installed (and please do bring Windows machines!)
    • Also, consider remote access servers you can reach over the internet.
  • One or more personal/public domain databases which have already been ported to a previous version of PostgreSQL.
  • One or more popular open source applications which work on PostgreSQL. Emphasis on those which have regression test suites.

Shell variables that may help

export PG_PREFIX=$HOME/alpha5 export PGDATA=$PG_PREFIX/data export PATH=$PG_PREFIX/bin:$PATH export PGPORT=5485

Building after tarball

cd postgresql-9.0alpha5 ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT --enable-cassert --enable-debug make check make install

INSTALL Alpha5 on your computers

Alpha5 has just been rolled and you can download source here: [1]

For Windows and other users, one-click installs are here: [2] Dave Page warns that this binary is untested, and particularly pgAdmin may break.

INSTALL Alpha4 on your computers.

Since Alpha5 was not tested and may have build issues, you may try installing Alpha4 as well:

You can download it here: [3]

windows: [4]

INSTALL 8.4 on your computers

IF you can, please also install a copy of version 8.4.3 so that we can do comparison testing.

If anyone needs help compiling PostgreSQL, please ask on the list.

Also, I would suggest installing your favorite PostgreSQL applications to try out on 9.0. If the applications are relatively portable, please bring copies on a USB key.

Support Equipment

Quantities and specifics to be determined. Put your name by it (and quantity) if you can bring something.

Support Equipment
Item Quantity Provided by Comments
Heavy duty extension cords 2 or more, 12+ feet Steve Crawford Power supplied by the venue.
Probably not needed
Power strips 3 or more Josh Berkus (3), Steve Crawford (2)
Network cables 10 or more, 6-12 feet Steve Crawford I have a box of 7 and 10 foot cables
8-16 port fast ethernet switch 1 Steve Crawford has a 5-port 100MB switch
10MB ethernet hub 1 Steve Crawford Old 8-port Linksys
100MB or gig ethernet switch 1
Projector/Screen 1 Josh Berkus Also supplied by the venue
Electric fan 1 Steve Crawford Oscillating

Steve Crawford comment: If the date does not work for me I'll give the equipment to someone else to take to the event. I also have a 100MB hub if needed.

Specific Tests to be Done

We need to do a number of beta tests in order to make the day worthwhile. Please put your name next to any test which you can prepare for and run.

  • Compile Tests
    • with options
    • contrib modules
    • external modules
    • regressions
  • Head-to-Head pgBench Tests
    • 8.4 vs. 9.0
  • Feature Tests
    • Hot Standby
    • Streaming Replication
    • Exclusion Constraints
    • DO() (all languages)
    • Deferrable UNIQUE
    • Others??
  • Database Porting Test -- bring your own database!
  • Application Porting Test
    • MediaWiki
    • Openstreetmap data?? I contacted them to see about getting a standard PG data dump.
    • Slony
    • Bucardo
    • pgsnmpd
    • Others??
  • Driver Regression Tests
    • Java
    • Python
      • psycopg2
    • Perl
      • DBD::Pg
    • PHP
      • pgsql -- Eric T
    • R -- Eric T
    • Ruby -- Eric T
    • Others?
  • ORM Regression Tests
    • SQLAlchemy
    • Django
    • Doctrine (PHP) -- Eric T (maybe)
    • Others?

Hot Standby / Streaming Replication

  1. Set up replication between two machines.
  2. Clone
  3. Start Replication
  4. Run pgbench on the master.
  5. Run pgbench in select-only mode on the slave
  6. Test with different delay settings.
  7. Test failover to the slave.
  8. Set up with pgPool and try.

Bonus: set up a master with 3 replicas


  • Write a simple, non-data-writing plpgsql script and run it.
  • Use DO() to run a data-modifying script
  • Use DO() to run pl/perl or pl/python
  • Use DO() to run the stats-modifying script here: [5]


  1. Load one of your databases with a complex schema
  2. do grants and revokes on some individual tables
  5. do some more individual grants and revokes


  1. Set up a NOTIFY on one or more pgbench tables, with payloads
    1. an ID
    2. a serialized row
  2. LISTEN to that notification
  3. LISTEN from a remote client

Exclusion Constraints

  1. Install Temporal and Intarray
  2. Set up tables with PERIOD and INT[] columns
  3. Set up exclusion indexes on these using overlaps or contains
  4. Test for conflicts

Deferrable Unique

  • Create a Celko Tree and move the nodes
  • Do an id = id + 5 on a large table


  1. Run a bunch of pgbench tests
  2. VACUUM FULL the whole database
  3. Attempt to query tables while vacuum full is running

Bonus: do this on a database which is being replicated by HA/SR