Message-ID: <426FCAAC.4040000@arbash-meinel.com>
Date: Wed, 27 Apr 2005 12:23:56 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324)
MIME-Version: 1.0
To: Richard Rowell <richard@bowmansystems.com>
Cc: pgsql-performance@postgresql.org,
	Mark Rinaudo <mark@bowmansystems.com>
Subject: Re: Suggestions for a data-warehouse migration routine
References: <1114618034.7543.36.camel@richard>
In-Reply-To: <1114618034.7543.36.camel@richard>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigE92AF43FF0FBA3A0130D3112"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigE92AF43FF0FBA3A0130D3112
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Richard Rowell wrote:
> I've ported enough of my companies database to Postgres to make
> warehousing on PG a real possibility.  I thought I would toss my data
> migration architecture ideas out for the list to shoot apart..
>
> 1.  Script on production server dumps the production database (MSSQL) to
> a set of delimited text files.
> 2.  Script on production server moves files via FTP to a Postgres
> database server.
> 3.  File Alteration Monitor trigger on PG server executes script when
> last file is transferred.
> 4.  Script on PG server drops the target database (by issuing a "dropdb"
> command).
> 5.  Script on PG server re-creates target database. (createdb command)
> 6.  Script on PG server re-creates the tables.
> 7.  Script on PG server issues COPY commands to import data.
> 8.  Script on PG server indexes tables.
> 9.  Script on PG server builds de-normalized reporting tables.
> 10. Script on PG server indexes the reporting tables.
> 11. Script on PG server creates needed reporting functions.
> 12. Vacuum analyze?
>
> My question revolves around the drop/create for the database.  Is their
> significant downside to this approach?  I'm taking this approach because
> it is simpler from a scripting point of view to simply start from
> scratch on each warehouse update.  If I do not drop the database I would
> need to delete the contents of each table and drop all indexes prior to
> the COPY/data import.  My assumption is all the table deletes and index
> drops would be more expensive then just droping/re-creating the entire
> database.

I believe you are correct. If you are going to completely wipe the
database, just drop it and re-create. Deleting is much slower than
dropping. (One of the uses of partitioning is so that you can just drop
one of the tables, rather than deleting the entries). Dropping the whole
db skips any Foreign Key checks, etc.

>
> Also, is the Vacuum analyze step needed on a freshly minted database
> where the indexes have all been newly created?
>
> Thanks in advance for all feedback.

ANALYZE is needed, since you haven't updated any of your statistics yet.
So the planner doesn't really know how many rows there are.

VACUUM probably isn't since everything should be pretty well aligned.

John
=:->

--------------enigE92AF43FF0FBA3A0130D3112
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFCb8qsJdeBCYSNAAMRAgHkAJ9HegqkzKckhY1rcxCaeZDiKELMOACgn1K+
rOyUTalvLHuoOo4XhAghey0=
=N9bL
-----END PGP SIGNATURE-----

--------------enigE92AF43FF0FBA3A0130D3112--