On 25/09/19 7:50 PM, Adrian Klaver wrote: > On 9/25/19 12:15 AM, Krishnakant Mane wrote: >> Hello all, >> >> I have been using postgresql for an enterprise quality account's >> automation and inventory management software called GNUKhata >> <https://gnukhata.in> >> >> Our team is planning to add backup and restore function in the software. >> >> But we don't want to dump the entire database and then restore the same. >> >> What we are trying to do is to copy data specific to an organization. >> >> The challenge here is that I might copy all data (account heads, >> bills, vouchers etc ) for one organization from an instance on one >> machine. >> >> I take the archive in what ever format to another machine and now >> attempt to restore. >> >> The risk here is for example if the primary key value for orgcode in >> the organization table is 5, it might conflict with the data where I >> am attempting it to be restored. >> >> Same holds true for bills, invoices etc. >> >> A certain account head with accountcode 1 might be already present on >> the second machine. >> >> I am not expecting the users to empty all data from the destination >> machine before restoring a backup. >> >> The reason is that an auditor may have many client's data and one >> can't predict what primary key values are going to come from a backup. >> >> Basically I can even say this is a copy paste instead of a pure >> backup and restore. >> >> Can any one suggest how to handle such conflicts? > > Hard to say. If the data is held in common tables(bills, vouchers, > etc)then the only thing I see happening is changing the PK values to > an unused value. That could turn into a nightmare though. Not only > that you lose the connection to the original data source. If the data > can be broken out into separate tables then I could see placing them > in their own schema. > >> >> >> -- >> Regards, >> Krishnakant Mane, >> Project Founder and Leader, >> GNUKhata <https://gnukhata.in/> >> //(Opensource Accounting, Billing and Inventory Management Software)// >
Hi Adrian, Even I am thinnking to do some kind of upsert with this situation. And I would have to set the pkey to an unassigned value when there is conflict. I may also choose to revamp the serial by timestamps but don't know if the target customers would like it. -- Regards, Krishnakant Mane, Project Founder and Leader, GNUKhata <https://gnukhata.in/> //(Opensource Accounting, Billing and Inventory Management Software)//