Moving my business to PostgreSQL - Mailing list pgsql-general

This post is long, if you'd like to offer quick point advice, I specificly
am looking for live replication/fallover for PostgreSQL, daily
synchronization with Access, and data import/restructuring from the D3
nested relational database to PostgreSQL.

I also am looking for more general sage like advice on the varried
challanges I will likely face in the next 6 months. Some of these are not
Postgre specific, but since I hope to make Postgre the core of this
business, I hope you'll indulge me and offer any advice you might have.

I'm the new manager of operations of a mail order retailer. I was hired by
new ownership, which purchased the business assets from the previous owners
in Nov. We went to the application vendor, and had them build us a system
preconfigured with the same version of their software the previous owners
used. For a variety of reasons, this system _barely_ works at the moment,
and most likely will not support our future plans for the business.

There is, quite simply, no budget to buy a more capable off the shelf
system.

So, where that brings me is trying to engineer a better system myself. I've
worked as a CRM implimenter, and done a variety of web dev. Over the last
year, I've become quite sold on postgre's capabilities. Unless your needs
are esoteric, it's Oracle at a FreeBSD price point :).

The basic process of our business is we handle orders through a call center.
Orders are exported from their system by flat file and imported by our
system several times daily. Payment is processed in batch by modem daily. We
authorize before shipping, then capture deposits after shipping.

Orders are sent to our shipping facility 1 day ahead of each shipping day.
They're exported as a flat file from our system, moved over *cough* PC
Anywhere to the shipping facility, and I execute some DTS scripts on the
remote machine via PC Anywhere that verify and insert the flat file into
their Access database.

At the shipping facility, a bar code system from ADSI manages picking
products, generating shipping invoices, etc.

The day after each shipping data, a flat file is built on that system, and
again by PC Anywhere, moved back to our system. The order id's and FedEx
tracking #'s are used to capture payments, again batch processed by modem.

All of these process steps are manually initiated.

The existing system uses the D3 nested relational database.

The general outline of what I'm planning is to move most everything to 2
hosted servers. A web application will be used by the call center agents to
enter orders directly to our system. Payment processing will be done over
ssl by a schedualed cron job. Postgre will of course, be the database.

Some of the bigger problems I'm facing:

1.) Exporting 12 years worth of data from a nested relational database,
restructuring that to a sql database to get it into Postgre. The current
database is ~500meg. I'm not that familiar with D3, but a large portion of
that seems to be history table data. I'd estimate the business has done
50,000 orders. There's information relating to 10 warehouses, and probibly a
few thousand SKU's over the years. It's vital that the new system have this
data, since forcasting and modeling are the life or death of a mail order
business.

2.) Providing live fallover between the servers. As we internalize what used
to be a web application hosted at the call center to our own system, we take
on the responsibility of providing 24/7/365 uptime on that system.

3.) Integrating my new Posgre based system with the barcode/shipping system.
This comes down to 2 way sync of a few tables between Postgre and Access.

Smaller stuff:

1.) Reporting tool: right now I'm thinking of having my web application just
generate simple spreadsheet files. Anything more snazzy will need to be
accessable to business persons who uses mac and pc systems. We also use
linux and irix desktops, but I'm fine with ignoring those for reporting.

2.) Monitoring and backup schemes for Postgre. I plan to host the servers at
someplace like rackspace. I've found that co-location facilities don't offer
the backup frequency I'd like, so I'd like to get updates to backups daily.
These can just be generated on the remote servers and ftp'd by my job to our
location where I can put them on tape and in the fire safe. I also need some
way of monitoring the whole system, and quick recovery/reinstallation in the
face of a failure.

Stuff not related to Postgre (off list response please):
1.) Payment processing: we currently use Paymentech. They say they're
working on an SSL gateway, but so far they only offer a one item at a time
SOAP-like interface. Our products are requested for delivery on particular
days, and often far in advance of when an authorization done on the day of
ordering would expire. That means while it would be *very* nice to do AVS on
order entry, we have to have a schedualed job that looks for orders soon
shipping that need authorization, or orders just shipped that need to
capture deposit. I've found getting real answers from payment processors a
real PITA, so any anicdotal advice here I'd really welcome.
2.) Anything that speeds developing the web application. This will be done
entirely by me, with about 20hrs/week to dedicate to it. I need to have the
system tested, running and be 100% confident of it by Nov 1 to handle the
xmas rush. I know, I know, but there simply isn't any other option available
to me... I'm workin' with what I got. I imagine there are a few eCom systems
compatable with Postgre that might be worth looking at. However, you can see
these will need signifigant customization, and the ones I've looked at so
far were to inflexible to handle what I need.
3.) At this point, I'm not ready to consider changing the shipping system.
However, once the front half of the business is moved to Postgre, I would
like to entertain thoughts about moving those pieces to open source as well.
Any info on barcode hardware and FedEx software interfaces would be helpful
when I come around to that.

A lot of what I'm facing only bears partly on Postgre, but, assuming I find
this can be done, I want to do a detailed write up (*cough* "case study" for
you MBA's). I think Postgre could really benifit if people can read how a
small company like us was able to move our entire business to open source
infrastructure, with Postgre at the core. I'd love to do whatever I can to
show people that this is a viable option to build your business is around. I
appreciate any advice or anicdotes you might offer. If not PostgreSQL
specific, or you feel they'd not be of interest to the list as a whole,
please respond directly.

jason watkins