Skip site navigation (1) Skip section navigation (2)

Re: Synchronizing databases

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Rob <rob(at)obsidian(dot)co(dot)za>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Synchronizing databases
Date: 2002-03-27 16:43:36
Message-ID: web-840309@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?

In response to

Responses

pgsql-novice by date

Next:From: RobDate: 2002-03-27 18:09:16
Subject: Synchronizing databases
Previous:From: denisDate: 2002-03-27 09:52:14
Subject: Re: Visio ODBC winnt Linux question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group