pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCopying data from table to table (cloned tables)
13+ messages / 10 participants
[nested] [flat]
* Copying data from table to table (cloned tables)
@ 2006-10-09 23:51 Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
2006-10-10 09:22 ` Re: Copying data from table to table (cloned tables) daq <daq@ugyvitelszolgaltato.hu>
2006-10-10 09:35 ` Re: [NOVICE] Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
0 siblings, 3 replies; 13+ messages in thread
From: Fourat Zouari @ 2006-10-09 23:51 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-admin
Hello all,
Any one could suggest the best way to copy data from table to table in the
same db, the reason why am seeking for this is that the first table is
becoming very big, and old data has no reason why to stay there, so i
created a cloned table but without indexes and constraints (lighter) and now
i would like to copy 'some' data from first to second table (copied data is
data older than 3 months, i have a timestamp column).
In other way, i have a table called 'hotqueues' where i store fresh messages
queued, once messages are treated, they stay in 'hotqueues' but with a flag
indicating that their arent queued for treatment..
so in this way, data will rest there forever, slowing down any searches in
that table, the solution was to copy old messages to another table called
'coldqueues' that has the same structure as 'hotqueues' but lighter (without
constraints and indexes).
How to copy these data with 100% data-loose free.
Thanks for any help you can provide.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
@ 2006-10-10 00:02 ` Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2 siblings, 1 reply; 13+ messages in thread
From: Bricklen Anderson @ 2006-10-10 00:02 UTC (permalink / raw)
To: Fourat Zouari <fourat@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-admin
Fourat Zouari wrote:
> Hello all,
> Any one could suggest the best way to copy data from table to table in
> the same db, the reason why am seeking for this is that the first table
> is becoming very big, and old data has no reason why to stay there, so i
> created a cloned table but without indexes and constraints (lighter) and
> now i would like to copy 'some' data from first to second table (copied
> data is data older than 3 months, i have a timestamp column).
>
> In other way, i have a table called 'hotqueues' where i store fresh
> messages queued, once messages are treated, they stay in 'hotqueues' but
> with a flag indicating that their arent queued for treatment..
> so in this way, data will rest there forever, slowing down any searches
> in that table, the solution was to copy old messages to another table
> called 'coldqueues' that has the same structure as 'hotqueues' but
> lighter (without constraints and indexes).
> How to copy these data with 100% data-loose free.
>
> Thanks for any help you can provide.
If you just want to copy the data across to the other table:
begin;
insert into table2 select * from table1 where <some criteria>;
commit;
if you also want to remove that same data from table1:
begin;
insert into table2 select * from table1 where <some criteria>;
delete from table1 where <same criteria as above>;
commit;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
@ 2006-10-10 15:24 ` Jim C. Nasby <jim@nasby.net>
2006-10-10 15:37 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2006-10-13 21:45 ` Re: Copying data from table to table (cloned tables) Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 13+ messages in thread
From: Jim C. Nasby @ 2006-10-10 15:24 UTC (permalink / raw)
To: Bricklen Anderson <banderson@presinet.com>; +Cc: Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org; pgsql-admin
On Mon, Oct 09, 2006 at 05:02:07PM -0700, Bricklen Anderson wrote:
> Fourat Zouari wrote:
> >Hello all,
> >Any one could suggest the best way to copy data from table to table in
> >the same db, the reason why am seeking for this is that the first table
> >is becoming very big, and old data has no reason why to stay there, so i
> >created a cloned table but without indexes and constraints (lighter) and
> >now i would like to copy 'some' data from first to second table (copied
> >data is data older than 3 months, i have a timestamp column).
> >
> >In other way, i have a table called 'hotqueues' where i store fresh
> >messages queued, once messages are treated, they stay in 'hotqueues' but
> >with a flag indicating that their arent queued for treatment..
> >so in this way, data will rest there forever, slowing down any searches
> >in that table, the solution was to copy old messages to another table
> >called 'coldqueues' that has the same structure as 'hotqueues' but
> >lighter (without constraints and indexes).
> >How to copy these data with 100% data-loose free.
> >
> >Thanks for any help you can provide.
>
> If you just want to copy the data across to the other table:
> begin;
> insert into table2 select * from table1 where <some criteria>;
> commit;
>
> if you also want to remove that same data from table1:
> begin;
> insert into table2 select * from table1 where <some criteria>;
> delete from table1 where <same criteria as above>;
> commit;
You need to be careful with this method. For what the OP wants to do it
would probably work, but not always. The problem is that in some
scenarios, <same criteria as above> won't necessarily return the same
set of rows.
Starting in 8.2 you'll be able to do something like
INSERT INTO table2 DELET FROM table1 WHERE ... RETURNING *;
The RETURNING * will return all the data that the command deleted. In
older versions, your best bet is to store the data you're moving in a
temporary table, and then use that to delete the exact rows.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
@ 2006-10-10 15:37 ` Jim C. Nasby <jim@nasby.net>
2006-10-11 11:30 ` Re: Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Jim C. Nasby @ 2006-10-10 15:37 UTC (permalink / raw)
To: Bricklen Anderson <banderson@presinet.com>; +Cc: Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org; pgsql-admin
On Tue, Oct 10, 2006 at 10:24:57AM -0500, Jim C. Nasby wrote:
> > If you just want to copy the data across to the other table:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > commit;
> >
> > if you also want to remove that same data from table1:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > delete from table1 where <same criteria as above>;
> > commit;
I forgot to mention you could also use a serializable transaction with
your method...
BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT ...
DELETE ...
COMMIT;
Just remember you'll need to deal with the possibility of a 'could not
serialize' error.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2006-10-10 15:37 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
@ 2006-10-11 11:30 ` Fourat Zouari <fourat@gmail.com>
2006-10-11 12:56 ` Re: [ADMIN] Copying data from table to table (cloned tables) Brandon Aiken <BAiken@winemantech.com>
2006-10-11 15:14 ` Re: Copying data from table to table (cloned tables) Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 13+ messages in thread
From: Fourat Zouari @ 2006-10-11 11:30 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Bricklen Anderson <banderson@presinet.com>; pgsql-novice@postgresql.org; pgsql-admin
what's the benefits using a serialized trans ?
On 10/10/06, Jim C. Nasby <jim@nasby.net> wrote:
>
> On Tue, Oct 10, 2006 at 10:24:57AM -0500, Jim C. Nasby wrote:
> > > If you just want to copy the data across to the other table:
> > > begin;
> > > insert into table2 select * from table1 where <some criteria>;
> > > commit;
> > >
> > > if you also want to remove that same data from table1:
> > > begin;
> > > insert into table2 select * from table1 where <some criteria>;
> > > delete from table1 where <same criteria as above>;
> > > commit;
>
> I forgot to mention you could also use a serializable transaction with
> your method...
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
> INSERT ...
> DELETE ...
> COMMIT;
>
> Just remember you'll need to deal with the possibility of a 'could not
> serialize' error.
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [ADMIN] Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2006-10-10 15:37 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2006-10-11 11:30 ` Re: Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
@ 2006-10-11 12:56 ` Brandon Aiken <BAiken@winemantech.com>
1 sibling, 0 replies; 13+ messages in thread
From: Brandon Aiken @ 2006-10-11 12:56 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-novice@postgresql.org
It makes it so that a transaction is highly isolated.
http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
--
Brandon Aiken
CS/IT Systems Engineer
________________________________
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Fourat Zouari
Sent: Wednesday, October 11, 2006 7:30 AM
To: Jim C. Nasby
Cc: Bricklen Anderson; pgsql-novice@postgresql.org;
pgsql-admin@postgresql.org
Subject: Re: [NOVICE] [ADMIN] Copying data from table to table (cloned
tables)
what's the benefits using a serialized trans ?
On 10/10/06, Jim C. Nasby <jim@nasby.net> wrote:
On Tue, Oct 10, 2006 at 10:24:57AM -0500, Jim C. Nasby wrote:
> > If you just want to copy the data across to the other table:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > commit;
> >
> > if you also want to remove that same data from table1:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > delete from table1 where <same criteria as above>;
> > commit;
I forgot to mention you could also use a serializable transaction with
your method...
BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT ...
DELETE ...
COMMIT;
Just remember you'll need to deal with the possibility of a 'could not
serialize' error.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2006-10-10 15:37 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
2006-10-11 11:30 ` Re: Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
@ 2006-10-11 15:14 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 13+ messages in thread
From: Bruno Wolff III @ 2006-10-11 15:14 UTC (permalink / raw)
To: Fourat Zouari <fourat@gmail.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Bricklen Anderson <banderson@presinet.com>; pgsql-novice@postgresql.org; pgsql-admin
On Wed, Oct 11, 2006 at 13:30:22 +0200,
Fourat Zouari <fourat@gmail.com> wrote:
> what's the benefits using a serialized trans ?
All of the queries in the transaction see a consistant view of the database.
So that two identical select queries will return the same information
within a serialized transaction. (As long as the current transaction doesn't
make any changes between the two selects that would affect their output.)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Re: Copying data from table to table (cloned tables) Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <jim@nasby.net>
@ 2006-10-13 21:45 ` Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 13+ messages in thread
From: Aaron Bono @ 2006-10-13 21:45 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Bricklen Anderson <banderson@presinet.com>; Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org; pgsql-admin
On 10/10/06, Jim C. Nasby <jim@nasby.net> wrote:
>
> On Mon, Oct 09, 2006 at 05:02:07PM -0700, Bricklen Anderson wrote:
> > Fourat Zouari wrote:
> > >Hello all,
> > >Any one could suggest the best way to copy data from table to table in
> > >the same db, the reason why am seeking for this is that the first table
> > >is becoming very big, and old data has no reason why to stay there, so
> i
> > >created a cloned table but without indexes and constraints (lighter)
> and
> > >now i would like to copy 'some' data from first to second table (copied
> > >data is data older than 3 months, i have a timestamp column).
> > >
> > >In other way, i have a table called 'hotqueues' where i store fresh
> > >messages queued, once messages are treated, they stay in 'hotqueues'
> but
> > >with a flag indicating that their arent queued for treatment..
> > >so in this way, data will rest there forever, slowing down any searches
> > >in that table, the solution was to copy old messages to another table
> > >called 'coldqueues' that has the same structure as 'hotqueues' but
> > >lighter (without constraints and indexes).
> > >How to copy these data with 100% data-loose free.
> > >
> > >Thanks for any help you can provide.
> >
> > If you just want to copy the data across to the other table:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > commit;
> >
> > if you also want to remove that same data from table1:
> > begin;
> > insert into table2 select * from table1 where <some criteria>;
> > delete from table1 where <same criteria as above>;
> > commit;
>
> You need to be careful with this method. For what the OP wants to do it
> would probably work, but not always. The problem is that in some
> scenarios, <same criteria as above> won't necessarily return the same
> set of rows.
>
> Starting in 8.2 you'll be able to do something like
>
> INSERT INTO table2 DELET FROM table1 WHERE ... RETURNING *;
>
> The RETURNING * will return all the data that the command deleted. In
> older versions, your best bet is to store the data you're moving in a
> temporary table, and then use that to delete the exact rows.
You may want to lock the table before doing the copy/delete - that would
take care of having a record added between the copy and the delete.
If you are doing this on a regular basis, you may want to consider adding an
insert/update trigger on the original table to put a copy into the secondary
table and then all you have to do is do a simple delete from the originating
table every so often.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
@ 2006-10-10 09:22 ` daq <daq@ugyvitelszolgaltato.hu>
2 siblings, 0 replies; 13+ messages in thread
From: daq @ 2006-10-10 09:22 UTC (permalink / raw)
To: Fourat Zouari <fourat@gmail.com>; +Cc: pgsql-novice@postgresql.org
FZ> Hello all,
FZ> Any one could suggest the best way to copy data from table to table in the
FZ> same db, the reason why am seeking for this is that the first table is
FZ> becoming very big, and old data has no reason why to stay there, so i
FZ> created a cloned table but without indexes and constraints (lighter) and now
FZ> i would like to copy 'some' data from first to second table (copied data is
FZ> data older than 3 months, i have a timestamp column).
FZ> In other way, i have a table called 'hotqueues' where i store fresh messages
FZ> queued, once messages are treated, they stay in 'hotqueues' but with a flag
FZ> indicating that their arent queued for treatment..
FZ> so in this way, data will rest there forever, slowing down any searches in
FZ> that table, the solution was to copy old messages to another table called
FZ> 'coldqueues' that has the same structure as 'hotqueues' but lighter (without
FZ> constraints and indexes).
FZ> How to copy these data with 100% data-loose free.
FZ> Thanks for any help you can provide.
A simple solution:
INSERT INTO coldqueues SELECT * FROM hotqueues where ...;
See documentation page: http://www.postgresql.org/docs/8.1/static/sql-insert.html
DAQ
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [NOVICE] Copying data from table to table (cloned tables)
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
@ 2006-10-10 09:35 ` Fourat Zouari <fourat@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: Fourat Zouari @ 2006-10-10 09:35 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-novice@postgresql.org; pgsql-admin
thanks for sharing, was very helpfull
On 10/10/06, Phillip Smith <phillip.smith@weatherbeeta.com.au> wrote:
>
> Use the INTO keyword on a SELECT statement:
>
> SELECT *
>
> INTO coldqueues
>
> FROM hotqueues
>
> WHERE treatment_flag = 'DONE';
>
>
>
> DELETE FROM hotqueues WHERE treatment_flag = 'DONE';
>
>
>
> You will need to drop the coldqueues table first if you've already created
> it as this will create it for you. I don't know if indexes and constraints
> are copied from the source table, but you can always drop them afterwards.
> Don't forget to re-apply permissions etc to the new table if required.
>
>
>
> Another way if you don't want to drop the table would be to add the
> keyword TEMP, copy to temporary table to a file, then copy that file back to
> the proper coldqueues table:
>
> SELECT *
>
> INTO *TEMP* coldqueues_temp
>
> FROM hotqueues
>
> WHERE treatment_flag = 'DONE';
>
>
>
> COPY coldqueues_temp TO '/tmp/coldqueues.sql';
>
> COPY coldqueues FROM '/tmp/coldqueues.sql';
>
>
>
> DELETE FROM hotqueues WHERE treatment_flag = 'DONE';
>
>
>
> This will prevent you having to delete the table in the first place –
> which you won't want to do on future archives. Once you close the session
> you have open, the "coldqueues_temp" table will automatically be dropped for
> you.
>
>
>
> Hope this helps,
>
> -p
>
>
>
> -----Original Message-----
> *From:* pgsql-novice-owner@postgresql.org [mailto:
> pgsql-novice-owner@postgresql.org] *On Behalf Of *Fourat Zouari
> *Sent:* Tuesday, 10 October 2006 09:52
> *To:* pgsql-novice@postgresql.org; pgsql-admin@postgresql.org
> *Subject:* [NOVICE] Copying data from table to table (cloned tables)
>
>
>
> Hello all,
> Any one could suggest the best way to copy data from table to table in the
> same db, the reason why am seeking for this is that the first table is
> becoming very big, and old data has no reason why to stay there, so i
> created a cloned table but without indexes and constraints (lighter) and now
> i would like to copy 'some' data from first to second table (copied data is
> data older than 3 months, i have a timestamp column).
>
> In other way, i have a table called 'hotqueues' where i store fresh
> messages queued, once messages are treated, they stay in 'hotqueues' but
> with a flag indicating that their arent queued for treatment..
> so in this way, data will rest there forever, slowing down any searches in
> that table, the solution was to copy old messages to another table called
> 'coldqueues' that has the same structure as 'hotqueues' but lighter (without
> constraints and indexes).
> How to copy these data with 100% data-loose free.
>
> Thanks for any help you can provide.
>
> ********************Confidentiality and Privilege
> Notice********************
>
> The material contained in this message is privileged and confidential to
> the addressee. If you are not the addressee indicated in this message or
> responsible for delivery of the message to such person, you may not copy or
> deliver this message to anyone, and you should destroy it and kindly notify
> the sender by reply email.
>
> Information in this message that does not relate to the official business
> of Weatherbeeta must be treated as neither given nor endorsed by
> Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall
> not be liable for direct, indirect or consequential loss arising from
> transmission of this message or any attachments
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
@ 2006-10-27 12:38 shaik mastan dilshad <dilshad_sm@rediffmail.com>
2006-10-30 12:41 ` Re: [ADMIN] Copying data from table to table (cloned tables) Richard Broersma Jr <rabroersma@yahoo.com>
2006-10-30 15:25 ` Re: Copying data from table to table (cloned tables) Jim C. Nasby <decibel@decibel.org>
0 siblings, 2 replies; 13+ messages in thread
From: shaik mastan dilshad @ 2006-10-27 12:38 UTC (permalink / raw)
To: Jim C.Nasby <jim@nasby.net>; +Cc: Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org, pgsql-admin@postgresql.org, "Bricklen Anderson" <banderson@presinet.com>
Hi all,
Can any one tell me,how to retirve the data which had deleted for an instance ,i mean how to rollback the previous.
Look forward to hearing from all of you.
Regards,
S.M.Dilshad.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [ADMIN] Copying data from table to table (cloned tables)
2006-10-27 12:38 Re: Copying data from table to table (cloned tables) shaik mastan dilshad <dilshad_sm@rediffmail.com>
@ 2006-10-30 12:41 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 13+ messages in thread
From: Richard Broersma Jr @ 2006-10-30 12:41 UTC (permalink / raw)
To: shaik mastan dilshad <dilshad_sm@rediffmail.com>; +Cc: pgsql-novice@postgresql.org
> Can any one tell me,how to retirve the data which had deleted for an instance ,i mean how to
> rollback the previous.
To get the functionality of a rollback you next to begin with a transaction:
begin transaction;
delete from foo;
--oops I deleted to much;
rollback;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Copying data from table to table (cloned tables)
2006-10-27 12:38 Re: Copying data from table to table (cloned tables) shaik mastan dilshad <dilshad_sm@rediffmail.com>
@ 2006-10-30 15:25 ` Jim C. Nasby <decibel@decibel.org>
1 sibling, 0 replies; 13+ messages in thread
From: Jim C. Nasby @ 2006-10-30 15:25 UTC (permalink / raw)
To: shaik mastan dilshad <dilshad_sm@rediffmail.com>; +Cc: Jim C.Nasby <jim@nasby.net>; Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org, pgsql-admin@postgresql.org, Bricklen Anderson <banderson@presinet.com>
On Fri, Oct 27, 2006 at 12:38:51PM -0000, shaik mastan dilshad wrote:
> Hi all,
>
> Can any one tell me,how to retirve the data which had deleted for an instance ,i mean how to rollback the previous.
>
> Look forward to hearing from all of you.
Please start a new thread instead of replying to an existing one.
You can only rollback an uncommitted transaction. Once you commit, the
changes are permanent.
Your only other option is to setup Point In Time Recovery (PITR), and
use it to restore a copy of your database to just before committing a
the transaction you want to back out. Of course, everything that's
happened in the database since then will also be gone.
--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-10-30 15:25 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24 ` Jim C. Nasby <jim@nasby.net>
2006-10-10 15:37 ` Jim C. Nasby <jim@nasby.net>
2006-10-11 11:30 ` Fourat Zouari <fourat@gmail.com>
2006-10-11 12:56 ` Brandon Aiken <BAiken@winemantech.com>
2006-10-11 15:14 ` Bruno Wolff III <bruno@wolff.to>
2006-10-13 21:45 ` Aaron Bono <postgresql@aranya.com>
2006-10-10 09:22 ` daq <daq@ugyvitelszolgaltato.hu>
2006-10-10 09:35 ` Fourat Zouari <fourat@gmail.com>
2006-10-27 12:38 Re: Copying data from table to table (cloned tables) shaik mastan dilshad <dilshad_sm@rediffmail.com>
2006-10-30 12:41 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-10-30 15:25 ` Jim C. Nasby <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox