Re: upgrading to 9.3

On 11/06/2013 03:08 PM, zach cruise wrote:
> moving from 8.1 to 9.3, and redesigning at the same time (using navicat and psql).
> have access to both 8.1 and 9.3. and by redesigning i mean, going from multiple databases to multiple schemas.
> so what's the best approach?

Having just done that, I can offer a little bit.

1) my web site (in php) required very little changes.  I have a OpenDB($client) function that used to connect to the
clientdatabase, I switched it to connect to the new webdb database and then "set search_path = $client".  95% of the
restof the php code worked as-is.  The rest is just adding the schema name before the tablename. 

2) I have a bunch of batch processing to update the databases.  The website itself is all read-only.  I get updated
datain batches, run it through a few program, generate some reports, and dump it into the webdb.  This part required
quitea few more changes.  I have 15'ish (give or take a few) different programs that could run, depending on the update
type. Each program needed updated.  Most of the time it was the connect string and then updating the sql statements to
includethe schema in front.  (Some programs I used the set search_path thing, some not.  I choose what seemed to be
simpler).

I have a test box setup, so I can run the batch processing and the websites all on the test box.  I made sure
everythingwas right (or at least mostly right), then waited for a weekend, and set it all live. 

I wrote myself a script (perl, of course), to backup the original database, then script it back into the new database
insidea schema. 

All of the databases where owned my user mcp.  so first:

psql -h webserv -l -q -tA| awk -F \| ' /mcp/ { print "./run.sh", $1 }' > all

the file all now looks like:
./run.sh adairia
./run.sh adairmo
./run.sh adamsia
./run.sh appanooseia
./run.sh blueearthmn
etc..

run.sh looks like:

#!/bin/bash
set -e
C=$1
echo "Running: $C"
rm -f $C.sql
pg_dump -h webserv -f $C.sql --no-owner --no-privileges $C
psql -U mcp -d web -c "drop schema if exists $C cascade"
./reschema.pl $C  < $C.sql | psql -U mcp -X -q -v ON_ERROR_STOP=1 --pset pager=off -d webdb



I run this on the test box, the live box is named webserv.  reschema.pl looks like:
#!/usr/bin/perl

use strict;
use warnings;

my $county = pop;
if (! $county)
{
         die "requires county name\n";
}

while (<>)
{
         if (/SET search_path = public, pg_catalog;/)
         {
                 print "create schema if not exists $county;\n";
                 print "set search_path = $county, pg_catalog;\n";
         }
         else {
                 print;
         }
}

Fire off "sh all" and voila, backup all databases off live box into one big database with lots of schemas on the text
box. Going the other way was simpler.  I could just backup the db from test and restore it on the live.  I had to bump
upmax_locks_per_transaction (or whatever its called) to about 2300,  but it ran fine. 

These are not the exact script I ran, so there may be a few syntax errors.  I removed some of the extra details that
youwouldn't care about, and might only confuse the main points. 

All in all, I dont see a lot of difference in the code, or my daily work.  The webserv box, on the other hand, along
withpgbouncer, has noticed quite a big difference.  My load average before what 0.5 or 0.7... it has dropped quite a
bit(0.15 for the last three days).  I didn't really notice a difference just looking at it, but my graphite graphs tell
amuch different story. 

Have to give props to pgbouncer here.  I did some load tests, on my original multi-database setup, I could hammer the
box,have load averages above 5, get error messages (too many clients, OOM, etc), really slow responses, etc.  After I
setupthe new multi-schema database and pointed php at pgbouncer, that same test had no problems.  Load average was
between1 and 1.5, no errors, no slowdowns.  It was beautiful. 


-Andy