Re: dump-restore only one table

From: Alexander Kuprijanov <sanya-spb(at)list(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: dump-restore only one table
Date: 2007-05-01 14:11:57
Message-ID: 200705011811.57381.sanya-spb@list.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Tuesday 01 May 2007 14:41:38 Richard Huxton wrote:
> Alexander Kuprijanov wrote:
> > I do:
> > ==============
> > pg_dump -d BaseA --port=54321 --host=localhost --table=TableA --data-only
> > -F c -v | pg_restore -d
> > BaseB --data-only --no-owner --table=TableA --single-transaction -v
> > ==============
> >
> > But <BaseB.TableA> wasn't updated.... why??
>
> I'm not sure you can pipe the custom format between pg_dump and
> pg_restore (it's got compressed sections). Either:
> 1. Use a temporary file
> 2. pipe the standard format to psql

1. with temporary file...
ok I've made the file (it likes good as I can see from size)
====================================
% pg_dump -d
BaseA --port=54321 --host=localhost --table=TableA --data-only -v -F
c -f ./idstation.backup
====================================

but pg_restore dont want make its work...
====================================
% pg_restore --dbname=BaseB --no-owner --table=TableA -v ./idstation.backup
pg_restore: connecting to database for restore
pg_restore: implied data-only restore
====================================
also games with options --single-transactions, --clear, --data-only dont help
me - as result - <BaseB.TableA> has old data...

2. pipe to psql...
I've made the file by pg_dump...
====================================
--
-- PostgreSQL database dump
--

-- Started on 2007-05-01 17:55:41 VOLST

SET client_encoding = 'WIN1251';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = awp_books, pg_catalog;

--
-- TOC entry 2969 (class 0 OID 0)
-- Dependencies: 2293
-- Name: idstation_idstation_seq; Type: SEQUENCE SET; Schema: awp_books;
Owner: pgsql
--

SELECT pg_catalog.setval('idstation_idstation_seq', 1, false);

--
-- TOC entry 2966 (class 0 OID 17400628)
-- Dependencies: 2294
-- Data for Name: idstation; Type: TABLE DATA; Schema: awp_books; Owner: pgsql
--

INSERT INTO idstation VALUES (...);

<...insert, insert, insert...>

-- Completed on 2007-05-01 17:56:19 VOLST

--
-- PostgreSQL database dump complete
--
====================================

I do
% cat ./idstation.backup | psql -d BaseB

and get errors about primary key unique constraint violation...
I cant delete records from <BaseA.TableA> because this table have references
from another tables...

Im sure, that it is not a bug in pg_restore, but in my head... please help me
debug it

--
Kiu glutis tro multe, tiu agas tro stulte

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2007-05-01 14:12:52 Re: Processing a work queue
Previous Message Merlin Moncure 2007-05-01 13:42:45 Re: Processing a work queue