From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Dawid Kuroczko <qnex42(at)gmail(dot)com> |
Cc: | "Kevin Kempter" <kevin(at)kevinkempterllc(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Diff ? |
Date: | 2007-08-27 02:34:30 |
Message-ID: | F370BEA6-D9E4-4B03-99E7-16A042A8B820@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Aug 26, 2007, at 9:02 AM, Dawid Kuroczko wrote:
> On 8/26/07, Kevin Kempter <kevin(at)kevinkempterllc(dot)com> wrote:
>> On Saturday 25 August 2007 21:10:19 Ron Johnson wrote:
>>> On 08/25/07 21:51, Kevin Kempter wrote:
>>>> Hi List;
>>>>
>>>> I have a very large table (52million rows) - I'm creating a copy
>>>> of it to
>>>> rid it of 35G worth of dead space, then I'll do a sync, drop the
>>>> original
>>>> table and rename table2.
>>>
>>> What is your definition of "dead space"?
>>>
>>> Bad rows, duplicate rows, old rows? Something else?
>>
>> deleted rows that should have been cleaned up with vacuum, problem
>> is the
>> client let it go so long that now I cant get a vacuum to finish
>> cause it
>> impacts the day2day operations too much. Long story, see my
>> recent questions
>> on the performance list for more info.
>
> In your place I would do something like Slony-I does, when
> it replicates the tables. Create on insert/update/delete triggers
> on table1 which will log operations on table1 to some table1_log
> table. Then copy table1 to table2. Then replay table1_log on
> table2, then BEGIN;LOCK tablel1;finish replaying the lock;DROP table1;
> alter table rename...;commit;
>
> Or perhaps actually use Slony-I for the above steps? Should work
> quite nicely... Or perhaps use SkyTools for it (I've never used it)?
>
Yeah, for trigger based replication it'd be simpler to just use Slony-
I or Skytools. However, if you're on 8.2, with row-wise comparisons,
you could do something like:
begin;
lock table1;
insert into table2
select *
from table1
where id not in (select id from test2);
drop table1;
alter table2 rename to table1;
commit;
Here id is your primary key. Note that if your ids are generated by
a sequence you'll need to use setval on the sequence to get it
"caught up" before that commit or you'll get duplicate key errors
immediately.
Erik Jones
Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-08-27 03:43:05 | Re: Restore v. Running COPY/INDEX seperatly |
Previous Message | Ow Mun Heng | 2007-08-27 01:52:32 | psql \copy command runs as a transcation? |