Re: Synchronizing databases

Rob,

> I've written a database that goes with the product we sell. All
> changes
> and developments/testing are done on my machine. What I need to know
> is
> if there is an easy way to replicate the changes I make to the
> database on
> my machine to all my clients' machines.

I do this all the time using pg_dump and script files.

For example, frequently I'll modify functions and views to fix bugs,
but not touch the data. Most of the time, is simply save these
changes in script files, with names like "staffos_update.03.22".
Then, when the fix is tested on my system, I will remotely log into
the client's system using: "psql -h 10.1.1.4 staffos" and then "\i
staffos_update.03.22"

This works great.

Sometimes, however, I have to make a large number of changes or modify
dependencies. For this, a script file doesn't work. Instead, I use
pg_dump. Assuming that you are only changing the database *structure*
and not updating the client's *data*:
1. dump the schema of the development database: pg_dump -s staffos >
latest_dev.pgdump.
2. dump the data on the clients database (after remote login) pg_dump
-a staffos > client_data.pgdump.
3. Drop and re-create the database on the client machine: DROP
DATABASE staffos; CREATE DATABASE staffos;
4. Load your schema and their data: (from psql) \i latest_dev.pgdump
\i client_data.pgdump

And you're good to go!

-Josh Berkus

P.S. if your product is commercial, mind sharing it with us so that we
can know what else runs on postgresql?