Обсуждение: postgres pg_restore append data

Поиск
Список
Период
Сортировка

postgres pg_restore append data

От
dhanuj hippie
Дата:
Hi, 
I have a pg backup created using pg_dump custom format. I'm trying to restore into a DB which already has some data, using pg_restore data-only. This fails in scenarios where same data (some rows) is present in DB as well as dump. Is there a way to ignore such rows and proceed with restoring the rest of data ? I don't want to lose the present data in DB. I need to append the backup onto current content in DB.

Thanks,
Dhanuj

Re: postgres pg_restore append data

От
Melvin Davidson
Дата:


On Mon, Dec 5, 2016 at 4:36 AM, dhanuj hippie <dhanuj.hippie@gmail.com> wrote:
Hi, 
I have a pg backup created using pg_dump custom format. I'm trying to restore into a DB which already has some data, using pg_restore data-only. This fails in scenarios where same data (some rows) is present in DB as well as dump. Is there a way to ignore such rows and proceed with restoring the rest of data ? I don't want to lose the present data in DB. I need to append the backup onto current content in DB.

Thanks,
Dhanuj

The best I can advise you is this.
1. Create a new database x.
2. Restore the backup to the new database.
3. Rename the table to something like table_old.
4. Rename the primary index and any other index
   so that they will not be the same as ones in the original table.
5. Dump table_old only -> pg_dump -t table_old x > table_old.sql
6. Restore table_old to your database with your original table (table_orig).
Then you can:
INSERT INTO table_orig
  SELECT * FROM table_old
   WHERE your_primary_key NOT IN  (SELECT your_primary_key FROM table_orig );

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.