Postgres Pro
Downloads
Home   >   mailing lists

Thread: Synchronizing databases

Synchronizing databases

From
Rob
Date:
Hi,

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.

Thanks

Rob

--
He who dies with the most toys ...

                    ... still dies

Re: Synchronizing databases

From
"Josh Berkus"
Date:
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?

SQL-Dump and refertial Integrity

From
Thomas Woehlke
Date:
Hi @All!

I have a problem on dumping a database:

If I export the Structure via pg_dump
the table are dumped in alphabetical order, but the order violates
the refenrential integrity of my db.

At inserting it via psql db < db_structure.sql it throws errors and the
database is not not correctly build on the other server. Tables are
missing.

Are there any known solutions or a workaround?

I would greatly appreciate receiving an helping answer even if this
question is very newbie-like.

Thank You

Thomas

Re: SQL-Dump and refertial Integrity

From
Tom Lane
Date:
Thomas Woehlke <Thomas.Woehlke@ESC-Electronics.de> writes:
> If I export the Structure via pg_dump
> the table are dumped in alphabetical order, but the order violates
> the refenrential integrity of my db.

> At inserting it via psql db < db_structure.sql it throws errors and the
> database is not not correctly build on the other server. Tables are
> missing.

How old is your PG version?  This problem was fixed ages ago.

If you cannot find any other way out, you'll have to edit the dump file
by hand to put the table declarations in a valid order.  (It might be
best to dump schema and data separately, if the combined dump file is
too large to edit conveniently.)

            regards, tom lane

Re: SQL-Dump and refertial Integrity

From
"Joshua b. Jore"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Oh sure, look in the archives a few days ago for some functions I posted:
EnableTriggers(), DisableTriggers().

Joshua b. Jore
http://www.greentechnologist.org

On Mon, 15 Apr 2002, Thomas Woehlke wrote:

> Hi @All!
>
> I have a problem on dumping a database:
>
> If I export the Structure via pg_dump
> the table are dumped in alphabetical order, but the order violates
> the refenrential integrity of my db.
>
> At inserting it via psql db < db_structure.sql it throws errors and the
> database is not not correctly build on the other server. Tables are
> missing.
>
> Are there any known solutions or a workaround?
>
> I would greatly appreciate receiving an helping answer even if this
> question is very newbie-like.
>
> Thank You
>
> Thomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8uyo3fexLsowstzcRAgBUAJ0ds4bgDaY8tnMcDBsIl+E/F1nFGQCglaPL
KdAj1m9gwJnpKx0VHicRjOQ=
=AfQt
-----END PGP SIGNATURE-----