pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Rowell <richard@bowmansystems.com>
To: pgsql-performance@postgresql.org
Cc: Mark Rinaudo <mark@bowmansystems.com>
Subject: Suggestions for a data-warehouse migration routine
Date: Wed, 27 Apr 2005 11:07:14 -0500
Message-ID: <1114618034.7543.36.camel@richard> (raw)

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.


-- 




view thread (3+ messages)  latest in thread

Message-ID: <1114618034.7543.36.camel@richard>
Permalink:  ../1114618034.7543.36.camel@richard/
Also on:    postgresql.org/message-id/1114618034.7543.36.camel@richard

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: richard@bowmansystems.com, mark@bowmansystems.com
  Subject: Re: Suggestions for a data-warehouse migration routine
  In-Reply-To: <1114618034.7543.36.camel@richard>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox