> On Sep 25, 2019, at 8:24 AM, Krishnakant Mane <kkmane(at)riseup(dot)net> wrote:
>
>
>> 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
> (Opensource Accounting, Billing and Inventory Management Software)
It would likely be easier to rethink your backup and restore plan. Putting each restore into its own space would be one tack.