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 client database, I switched it to connect to the new webdb database and then "set search_path = $client". 95% of the rest of 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 data in batches, run it through a few program, generate some reports, and dump it into the webdb. This part required quite a 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 include the 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 everything was 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 inside a 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 up max_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 you wouldn'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 with pgbouncer, 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 a much 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 setup the new multi-schema database and pointed php at pgbouncer, that same test had no problems. Load average was between 1 and 1.5, no errors, no slowdowns. It was beautiful.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
