From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Krishnakant Mane <kkmane(at)riseup(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: managing primary key conflicts while restoring data to table with existing data |
Date: | 2019-09-25 14:20:41 |
Message-ID: | 920a0616-125c-6b6a-fd51-abd250a6ee9c@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)//
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ekaterina Amez | 2019-09-25 14:21:34 | Upgrading old server |
Previous Message | PegoraroF10 | 2019-09-25 13:38:50 | Re: Operator is not unique |