Re: table move across databases - Mailing list pgsql-novice

From Ron Johnson
Subject Re: table move across databases
Date
Msg-id 1027398638.7578.228.camel@rebel
Whole thread Raw
In response to table move across databases  (Duncan Sargeant <dunc-postgres@rcpt.to>)
List pgsql-novice
On Mon, 2002-07-22 at 22:59, Duncan Sargeant wrote:
> Hi,
>
> Is there a way to move a table between databases?
>
> The reason I ask is that I have a growing 'log' table which is only
> ever inserted to and no rows are ever deleted, updated and replace.  It
> is indexed by the time of insert.  I'd like to archive off this table to
> another database so that vacuum and pg_dump don't take so long on the
> 'live' database.  I'm taking a nightly backup, and this data never
> changes so it doesn't need to be archived as often.  If there is a way
> to do all this or something similar without rotating the table to a
> different database then I would like to hear it (the only other way I
> can think of is to use the -t option of pg_dump for each of the other
> tables, but that's too ugly)

1. Create 2 views, one defining the data you want to keep, and
   one defining that which you want to archive off.
2. COPY off each view into separate files.
3. Drop the original table, then recreate it, sans index.
4. COPY the data you want to keep back into the newly emptied
   table.
5. Re-index the table.
6. Now that you've shrunk the table, you can a on-line procedure
   (possibly pg/sql) to archive off old data on a periodic basis
   will run much faster.

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "The greatest dangers to liberty lurk in insidious encroachment |
|  by men of zeal, well-meaning, but without understanding."      |
|   Justice Louis Brandeis, dissenting, Olmstead v US (1928)      |
+-----------------------------------------------------------------+


pgsql-novice by date:

Previous
From: Duncan Sargeant
Date:
Subject: table move across databases
Next
From: Andrew McMillan
Date:
Subject: Re: table move across databases