Thread: dump-restore only one table
Hello
I have a stooped trable... I have equal bases <BaseA> and <BaseB>, and next
table <TableA> on each of them
CREATE TABLE <TableA>
(
idstation int4 NOT NULL DEFAULT serial,
ru_name varchar,
en_name varchar,
address varchar,
<...other fields...>
CONSTRAINT idstation_pkey PRIMARY KEY (idstation),
CONSTRAINT idcurrency_fkey FOREIGN KEY (idcurrency)
REFERENCES awp_books.idcurrency (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
<...other constrations...>
)
WITHOUT OIDS;
other tables have references on <TableA>,
but I need update my table <BaseB.TableA> with data from <BaseA.TableA>
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
==============
==== output =====
pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "idstation"
pg_dump: finding default expressions of table "idstation"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "idstation"
pg_dump: reading constraints
pg_dump: reading foreign key constraints for table "idstation"
pg_dump: reading triggers
pg_dump: reading triggers for table "idstation"
pg_dump: reading dependency data
pg_dump: saving encoding = WIN1251
pg_dump: saving standard_conforming_strings = off
pg_restore: connecting to database for restore
=============
But <BaseB.TableA> wasn't updated.... why??
--
Kiu nenion valoras, plej multe sin gloras
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 -- Richard Huxton Archonet Ltd
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
Alexander Kuprijanov <sanya-spb@list.ru> writes:
> 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
> ====================================
Works for me. Perhaps your table name is mixed-case and you didn't
quote it correctly? Have you tried 'pg_restore -l' to verify what
is in the dump file?
regards, tom lane
On Tuesday 01 May 2007 18:43:37 Tom Lane wrote: > Alexander Kuprijanov <sanya-spb@list.ru> writes: > > 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 > > ==================================== > > Works for me. Perhaps your table name is mixed-case and you didn't > quote it correctly? Have you tried 'pg_restore -l' to verify what > is in the dump file? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings no, tablenames not mixed, all small-case letter % pg_restore -l ./idstation.backup ; ; Archive created at Tue May 1 18:56:55 2007 ; dbname: ArisPSQLBase ; TOC Entries: 4 ; Compression: -1 ; Dump Version: 1.10-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 8.1.9 ; Dumped by pg_dump version: 8.2.4 ; ; ; Selected TOC Entries: ; 2969; 0 0 SEQUENCE SET awp_books idstation_idstation_seq pgsql 2966; 0 17400628 TABLE DATA awp_books idstation pgsql -- Marĉandado aĉeti ne devigas
Alexander Kuprijanov <sanya-spb@list.ru> writes:
> On Tuesday 01 May 2007 18:43:37 Tom Lane wrote:
>> Works for me. Perhaps your table name is mixed-case and you didn't
>> quote it correctly? Have you tried 'pg_restore -l' to verify what
>> is in the dump file?
> no, tablenames not mixed, all small-case letter
Hmph. I tried to reproduce your problem here (even to the extent of
using 8.2 pg_dump to dump from an 8.1 database) but it works fine as
far as I can tell. If you leave off the --table option to pg_restore
does it restore the data? Can you put together a complete test-case
to make it fail for someone else?
regards, tom lane
On Tuesday 01 May 2007 20:02:55 Tom Lane wrote: > Alexander Kuprijanov <sanya-spb@list.ru> writes: > > On Tuesday 01 May 2007 18:43:37 Tom Lane wrote: > >> Works for me. Perhaps your table name is mixed-case and you didn't > >> quote it correctly? Have you tried 'pg_restore -l' to verify what > >> is in the dump file? > > > > no, tablenames not mixed, all small-case letter > > Hmph. I tried to reproduce your problem here (even to the extent of > using 8.2 pg_dump to dump from an 8.1 database) but it works fine as > far as I can tell. If you leave off the --table option to pg_restore > does it restore the data? Can you put together a complete test-case > to make it fail for someone else? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ I make a test for simulation... (files test_a.sql.gz and test_b.sql.gz you can find in att.) I do: % createdb test_a; zcat test_a.sql.gz | psql -d test_a % createdb test_b; zcat test_b.sql.gz | psql -d test_b % echo 'select * from b' | psql test_a i | n ----+--- 1 | 3 2 | 3 3 | 3 4 | 3 5 | 3 6 | 3 7 | 3 8 | 3 9 | 3 10 | 3 (10 rows) % echo 'select * from b' | psql test_b i | n ----+--- 1 | 9 2 | 9 3 | 9 4 | 9 5 | 9 6 | 9 7 | 9 8 | 9 9 | 9 10 | 9 (10 rows) % pg_dump -d test_a --table=b -F c -v -f b.backup ok, at now I want to update table "test_b.b" from table "test_a.b" What command I need for to be happy? It must be very simple, but I'm creazy... please help -- Li ĵus elrampis el la ova ŝelo
Attachment
Alexander Kuprijanov <sanya-spb@list.ru> writes:
> % pg_dump -d test_a --table=b -F c -v -f b.backup
That syntax doesn't actually work in any reasonably modern version
of pg_dump ... what are you really typing? (-d is not a switch for
specifying the database name.)
But at any rate, I still can't duplicate the behavior you show of
pg_restore not trying to restore the data. What I get with this
test case (after fixing the command syntax) is
pg_restore: connecting to database for restore
pg_restore: restoring data for table "b"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1612; 0 67454 TABLE DATA b sanya
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "b_pkey"
CONTEXT: COPY b, line 1: "1 3"
WARNING: errors ignored on restore: 1
which is what I'd expect given that there's conflicting data
already in the destination table. Are you looking for an option to
delete the destination's existing data? There isn't one in the
--data-only mode, AFAIR, but you could instead use the -c option
to drop and recreate the table.
regards, tom lane