Thread: Copying only incremental records to another DB..
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
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 toupdate 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 databasesat 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.