X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D583C53748 for ; Wed, 27 Apr 2005 13:07:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03469-03 for ; Wed, 27 Apr 2005 16:07:15 +0000 (GMT) Received: from www.bowmansystems.com (ns1.bowmansystems.com [65.166.193.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7226D53716 for ; Wed, 27 Apr 2005 13:07:14 -0300 (ADT) Received: from ads2.ucomics.com (unknown [65.166.193.11]) by www.bowmansystems.com (Postfix) with ESMTP id 8B956CB60; Wed, 27 Apr 2005 11:06:07 -0500 (CDT) Subject: Suggestions for a data-warehouse migration routine From: Richard Rowell To: pgsql-performance@postgresql.org Cc: Mark Rinaudo Content-Type: text/plain Date: Wed, 27 Apr 2005 11:07:14 -0500 Message-Id: <1114618034.7543.36.camel@richard> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.414 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200504/728 X-Sequence-Number: 12125 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. 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. --