Thread: Copying table to another database.
Hello, Anyone knows how to copy a table to another database? Cheers! Wim.
On Tue, 2002-09-17 at 11:32, Wim wrote: > Hello, > > > Anyone knows how to copy a table to another database? perhaps pg_dump the table and then restore it in another database (you could pipe the output of pg_dump to the input of a psql so you don't have to store the dump anywhere. Good luck, Tycho -- Tycho Fruru tycho@fruru.com "Prediction is extremely difficult. Especially about the future." - Niels Bohr
Attachment
On Tue, 17 Sep 2002, Wim wrote:
> Anyone knows how to copy a table to another database?
Use the the COPY command:
Name
COPY -- copy data between files and tables
Synopsis
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
Or, bearing in mind your problem,
pg_dump -t <tablename> ...
For your real problem, it sounds like it's the system tables causing you
problems. You say you tried REINDEX, that was REINDEX DATABASE <dbname> FORCE
presumably.
It is somewhat worrying that the same problem has reoccured. You checked your
hard disk but what about memory?
pg_dumpall fails but what about just pg_dump on the individual DBs?
Is it a production system? If it continues to cause problems what about
considering bringing someone in to investigate?
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants
You can try to use pg_dump and pg_restore to backup & restore table(s). Best regards Andy ----- Original Message ----- From: "Wim" <wdh@belbone.be> To: <pgsql-general@postgresql.org> Sent: Tuesday, September 17, 2002 4:32 PM Subject: [GENERAL] Copying table to another database. > Hello, > > > Anyone knows how to copy a table to another database? > > > Cheers! > > Wim. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Hi Nigel,
Thanx for the response...
Nigel J. Andrews wrote:
>On Tue, 17 Sep 2002, Wim wrote:
>
>
>
>>Anyone knows how to copy a table to another database?
>>
>>
>
>
>Use the the COPY command:
>
>Name
>COPY -- copy data between files and tables
>
>Synopsis
>
>COPY [ BINARY ] table [ WITH OIDS ]
> FROM { 'filename' | stdin }
> [ [USING] DELIMITERS 'delimiter' ]
> [ WITH NULL AS 'null string' ]
>
>COPY [ BINARY ] table [ WITH OIDS ]
> TO { 'filename' | stdout }
> [ [USING] DELIMITERS 'delimiter' ]
> [ WITH NULL AS 'null string' ]
>
>
>Or, bearing in mind your problem,
>
> pg_dump -t <tablename> ...
>
>
>For your real problem, it sounds like it's the system tables causing you
>problems. You say you tried REINDEX, that was REINDEX DATABASE <dbname> FORCE
>presumably.
>
Yep, that's right...
>
>It is somewhat worrying that the same problem has reoccured. You checked your
>hard disk but what about memory?
>
Checked with vmstat:
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us
sy id
0 0 0 658872 156016 0 0 0 0 0 0 0 0 0 0 0 402 9 18
0 0 100
0 0 0 658872 155216 321 0 2849 10 10 0 0 0 0 0 0 416 353 30
79 3 18
0 0 0 657392 159440 317 58 2207 0 0 0 0 0 0 0 0 457 1781 93
92 7 1
0 0 0 657880 166720 342 60 2871 40 40 0 0 0 0 0 0 438 1073 73
89 7 4
0 0 0 658872 167392 376 0 3200 2 2 0 0 0 0 0 0 408 438 35
94 2 3
0 0 0 658872 169096 369 0 2534 0 0 0 0 0 0 0 0 412 401 43
91 5 4
0 0 0 658872 168984 361 0 2968 2 2 0 0 0 0 0 0 414 386 47
90 3 6
0 0 0 658872 169432 81 0 378 0 0 0 0 0 0 0 0 406 94 26
21 1 78
0 0 0 658872 169424 0 0 0 0 0 0 0 0 0 0 0 406 12 23
0 0 100
0 0 0 658872 169296 15 55 0 2 2 0 0 0 0 0 0 409 88 25
1 1 98
0 0 0 658872 168808 0 0 0 0 0 0 0 0 0 0 0 402 9 18
0 0 100
>
>pg_dumpall fails but what about just pg_dump on the individual DBs?
>
pg_dump fails on one database... other DB's are dumped...
>
>Is it a production system? If it continues to cause problems what about
>considering bringing someone in to investigate?
>
>
Indeed, it's a production system.
What do you mean by bringing someone in to investigate? Someone from
Postgres?
PS: I have some debugging output...
>
>
>
Thnx for your help!
Wim
On Tue, 17 Sep 2002, Wim wrote: > > > >It is somewhat worrying that the same problem has reoccured. You checked your > >hard disk but what about memory? > > > Checked with vmstat: > kthr memory page disk faults cpu > r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us > sy id Not Intel architecture then. Is there a way of testing the memory modules, like memtest86, although that sort of thing is obviously a very distruptive task on a production system. > > > >pg_dumpall fails but what about just pg_dump on the individual DBs? > > > pg_dump fails on one database... other DB's are dumped... Same DB as the previous failure? > > > >Is it a production system? If it continues to cause problems what about > >considering bringing someone in to investigate? > > > > > Indeed, it's a production system. > What do you mean by bringing someone in to investigate? Someone from > Postgres? Yes, that's what I was thinking you might need. Someone with expert knowledge poking around the data and system. > > PS: I have some debugging output... I probably wouldn't know what to make of it. Maybe someone will have better suggestions but all I can suggest for now is to see if pg_dump -s can dump the schema and also to run a parallel installation, after solving the problem of course, and waiting to see if the problem triggers on both systems at the same point. If pg_dump -s works and selecting from all the tables in the 'broken' DB works then there must be some sort of problem in the combination of schema and data. -- Nigel J. Andrews
Nigel J. Andrews wrote:
>On Tue, 17 Sep 2002, Wim wrote:
>
>
>>>The next simple but dumb test, which I don't know if you have already tried, is
>>>to check that you can SELECT * FROM pg_* without error.
>>>
>>>
>>>
>>"SELECT * FROM pg_*" gives:
>>ERROR: Relation "pg_" does not exist
>>
>>
>
>Sorry, I wasn't clear. I meant the pg_* to represent all of the pg_ tables
>taken in turn. To get a list try:
>
>SELECT relname FROM pg_class WHERE relname like 'pg_%' AND relkind = 'r'
>
SELECT relname, relkind from pg_class;
works, but:
SELECT relname FROM pg_class WHERE relname like 'pg_%' AND relkind = 'r';
belbonedb_v2-# ;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
>I see in the meantime there's been more messages suggesting the hardware is at
>fault. I still wouldn't rule that out, especially if you haven't been able to
>properly test these things. Running a second box in parallel once you are
>up and will help show if this is the problem. However, in this regard I'd be
>more inclined to use a replacement system for production, you can extract your
>data alright and should be able to recreate the schema for earlier dumps, and
>to then test the Sparc machine thoroughly. Perhaps even calling Sun to handle
>or help with this.
>
>
>
>
Maybe a bug in the version you are using as it works fine on the boxes we
use as shown below
SELECT relname FROM pg_class WHERE relname like 'pg_%' AND relkind = 'r';
Works fine on the box we use
oss=> SELECT relname FROM pg_class WHERE relname like 'pg_%' AND relkind =
'r';
relname
----------------
pg_aggregate
pg_am
pg_amop
pg_amproc
pg_attrdef
pg_attribute
pg_class
pg_database
pg_description
pg_group
pg_index
pg_inherits
pg_language
pg_largeobject
pg_listener
pg_opclass
pg_operator
pg_proc
pg_relcheck
pg_rewrite
pg_shadow
pg_statistic
pg_trigger
pg_type
(24 rows)
oss=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
oss=>
On Tue, 17 Sep 2002, Wim wrote:
>
>
> Nigel J. Andrews wrote:
>
> >On Tue, 17 Sep 2002, Wim wrote:
> >
> >
> >>>The next simple but dumb test, which I don't know if you have already tried, is
> >>>to check that you can SELECT * FROM pg_* without error.
> >>>
> >>>
> >>>
> >>"SELECT * FROM pg_*" gives:
> >>ERROR: Relation "pg_" does not exist
> >>
> >>
> >
> >Sorry, I wasn't clear. I meant the pg_* to represent all of the pg_ tables
> >taken in turn. To get a list try:
> >
> >SELECT relname FROM pg_class WHERE relname like 'pg_%' AND relkind = 'r'
> >
> SELECT relname, relkind from pg_class;
>
> works, but:
>
> SELECT relname FROM pg_class WHERE relname like 'pg_%' AND relkind = 'r';
> belbonedb_v2-# ;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> >I see in the meantime there's been more messages suggesting the hardware is at
> >fault. I still wouldn't rule that out, especially if you haven't been able to
> >properly test these things. Running a second box in parallel once you are
> >up and will help show if this is the problem. However, in this regard I'd be
> >more inclined to use a replacement system for production, you can extract your
> >data alright and should be able to recreate the schema for earlier dumps, and
> >to then test the Sparc machine thoroughly. Perhaps even calling Sun to handle
> >or help with this.
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Darren Ferguson