Copying only incremental records to another DB..

On Jun 29, 1:10 pm, phoenix.ki...@gmail.com (Phoenix Kiula) wrote:

Hi

We're trying PG on a new machine, so we copied our current (live)
database to that server. Tested the code and it's all working. Now, to
make that second server the main live server, we will need to copy the
db again including the new records since we copied for testing. Is
there any way to copy only the incremental records in all the tables?

Thanks

--
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Hi Phoenix,

you could try using the tool "rubyrep". With rubyrep you can compare
one or multiple tables between databases and synchronize all changes.
http://www.rubyrep.org

Best Regards,
Arndt

Shut down the postmasters and rsync. (Assuming same architecture & build
options...)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On Tue, Jun 30, 2009 at 11:21 PM, Scott Ribe<scott_ribe@killerbytes.com> wrote:

Shut down the postmasters and rsync. (Assuming same architecture & build
options...)

You mean rsync the "data" folder, or the entire PG folder?

Architecture may be the same (same processor) but the setup is a touch
different: SCSI hard disks instead of SATA, and RAID10 instead of
RAID1. Plus, the version of PG is different: this is 8.2.9, that is
8.4.

Will this be a challenge?

You mean rsync the "data" folder, or the entire PG folder?

I meant the data folder.

Will this be a challenge?

Yes, if you're using different major PG releases, then the data files are
not binary compatible.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On 29 Jun 2009, at 6:10, Phoenix Kiula wrote:

Hi

We're trying PG on a new machine, so we copied our current (live)
database to that server. Tested the code and it's all working. Now, to
make that second server the main live server, we will need to copy the
db again including the new records since we copied for testing. Is
there any way to copy only the incremental records in all the tables?

It would be nice if there were a tool that could do a diff between two
dumps resulting in a new dump with just the statements necessary to
apply the differences. I don't think there is such a tool yet though
(some light Googling does bring up such a tool for sqllite).
Implementing it does have a few challenges, changes to records with
foreign keys for example.

Barring the availability of such a tool, there are some tools out
there that can 'diff' two XML files and, seeing that the xml module
can export tables to a pre-defined XML format, you may be able to do
something using that. What to do with the resulting XML file is
another story, some XSLT could probably turn it back into SQL again.

If the changes aren't many you could probably also work from the
results of a normal diff from two text-dumps and glue them back
together into a usable dump file. Or just apply the changes by hand...

All of these methods involve a bit of work and none is foolproof
(unless the dump-diff tool does exist), but if approached well it
could result in a rather useful tool.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a6995d910131993413858!

Alban Hertroys, 24.07.2009 13:07:

It would be nice if there were a tool that could do a diff between two
dumps resulting in a new dump with just the statements necessary to
apply the differences. I don't think there is such a tool yet though
(some light Googling does bring up such a tool for sqllite).
Implementing it does have a few challenges, changes to records with
foreign keys for example.

You might want to have a look at my SQL Workbench/J

It has a command that can compare the data of two databases for differences and can write the necessary DML statements to update the target database to match the data from the source.

Details can be found here:
http://www.sql-workbench.net/manual/wb-commands.html#command-data-diff

As it does not compare two dumps, but the databases directly, tt requires that connections can be made to both databases at the same time (so it's not possible to do an "offline-diff")

Feel free to contact me if you have any questions (support email address is on the homepage).

Regards
Thomas

Scott Ribe wrote:

You mean rsync the "data" folder, or the entire PG folder?

I meant the data folder.

To be clearer: Do you mean that the folder you backed up is the folder
with the file "PG_VERSION" in it, and all its contents?

--
Craig Ringer

Craig Ringer <craig@postnewspapers.com.au> writes:

To be clearer: Do you mean that the folder you backed up is the folder
with the file "PG_VERSION" in it, and all its contents?

Careful --- there are multiple PG_VERSION files scattered around in a
Postgres data directory tree. Your comment is correct with respect
to the topmost one, but I'm not certain it's a foolproof definition
of "the data directory".

regards, tom lane

To be clearer: Do you mean that the folder you backed up is the folder
with the file "PG_VERSION" in it, and all its contents?

In my case, yes, because I can copy the config files as well. In general, I
was thinking of the folder passed to postmaster via -D. But of course if you
don't want to copy some config files, or you have some table spaces off on
another volume, you may need to do something a little more involved.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe escribi�:

To be clearer: Do you mean that the folder you backed up is the folder
with the file "PG_VERSION" in it, and all its contents?

In my case, yes, because I can copy the config files as well. In general, I
was thinking of the folder passed to postmaster via -D. But of course if you
don't want to copy some config files, or you have some table spaces off on
another volume, you may need to do something a little more involved.

Actually it might be more complicated even if you don't have any
tablespace, if your config file specifies a data_directory other than
the default one.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.