Обсуждение: rollback

Hi

I would like to be able to "mark" a point in my postgres database.
After that I want to change a few things and "rollback" to that point.
Does postgres support such a thing?  Is it possible for me to do this?

--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

am  Wed, dem 09.07.2008, um 15:38:52 +0200 mailte Adrian Moisey folgendes:
> Hi
>
> I would like to be able to "mark" a point in my postgres database.
> After that I want to change a few things and "rollback" to that point.
> Does postgres support such a thing?  Is it possible for me to do this?

Is this a joke?

Sure, you can work with transactions, 'begin' and 'rollback' works fine.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Wed, Jul 9, 2008 at 6:38 AM, Adrian Moisey
<adrian@careerjunction.co.za> wrote:
> I would like to be able to "mark" a point in my postgres database. After
> that I want to change a few things and "rollback" to that point. Does
> postgres support such a thing?  Is it possible for me to do this?

Sure.  Check out savepoints.

http://www.postgresql.org/docs/8.3/interactive/sql-savepoint.html


--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Wed, Jul 09, 2008 at 03:38:52PM +0200, Adrian Moisey wrote:
> Hi
>
> I would like to be able to "mark" a point in my postgres database.
> After that I want to change a few things and "rollback" to that point.
> Does postgres support such a thing?  Is it possible for me to do this?

Well, transactions do that. If you want to do this inside a
transaction, the term you're looking for is "savepoints".

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Hi

>> I would like to be able to "mark" a point in my postgres database.
>> After that I want to change a few things and "rollback" to that point.
>> Does postgres support such a thing?  Is it possible for me to do this?
>
> Well, transactions do that. If you want to do this inside a
> transaction, the term you're looking for is "savepoints".

I would like to do this globally over the entire DB, is that possible?

--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

am  Wed, dem 09.07.2008, um 15:59:00 +0200 mailte Adrian Moisey folgendes:
> Hi
>
> >>I would like to be able to "mark" a point in my postgres database.
> >>After that I want to change a few things and "rollback" to that point.
> >>Does postgres support such a thing?  Is it possible for me to do this?
> >
> >Well, transactions do that. If you want to do this inside a
> >transaction, the term you're looking for is "savepoints".
>
> I would like to do this globally over the entire DB, is that possible?

Sure. You can start a transaction, create tables, drop tables, do
inserts and deletes, update some tables, create and delete functions,
triggers, views and schemas. After that, rollback. No problem.



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Hi

>>>> I would like to be able to "mark" a point in my postgres database.
>>>> After that I want to change a few things and "rollback" to that point.
>>>> Does postgres support such a thing?  Is it possible for me to do this?
>>> Well, transactions do that. If you want to do this inside a
>>> transaction, the term you're looking for is "savepoints".
>> I would like to do this globally over the entire DB, is that possible?
>
> Sure. You can start a transaction, create tables, drop tables, do
> inserts and deletes, update some tables, create and delete functions,
> triggers, views and schemas. After that, rollback. No problem.

Can I do this outside of a transaction?

--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

On Wed, Jul 9, 2008 at 10:32 AM, Adrian Moisey
<adrian@careerjunction.co.za> wrote:
> Hi
>
>>>>> I would like to be able to "mark" a point in my postgres database.
>>>>> After that I want to change a few things and "rollback" to that point. Does
>>>>> postgres support such a thing?  Is it possible for me to do this?
>>>>
>>>> Well, transactions do that. If you want to do this inside a
>>>> transaction, the term you're looking for is "savepoints".
>>>
>>> I would like to do this globally over the entire DB, is that possible?
>>
>> Sure. You can start a transaction, create tables, drop tables, do
>> inserts and deletes, update some tables, create and delete functions,
>> triggers, views and schemas. After that, rollback. No problem.
>
> Can I do this outside of a transaction?

yes. you need to check out pitr.

merlin

Adrian Moisey wrote:
> Hi
>
> I would like to be able to "mark" a point in my postgres database. After
> that I want to change a few things and "rollback" to that point. Does
> postgres support such a thing?  Is it possible for me to do this?
>

A crude way of doing it, which I've done in the past on test DBs, is
take periodic dumps of the DB, do some work/development, then drop the
DB and reload the dump to go back in time. Of course, this becomes less
feasible as your DB grows in size.

I've not played with savepoints myself, though if others are
recommending it, it is probably more sane then my method.

Madi

On Jul 9, 2008, at 6:38 AM, Adrian Moisey wrote:
> I would like to be able to "mark" a point in my postgres database.
> After that I want to change a few things and "rollback" to that
> point. Does postgres support such a thing?  Is it possible for me
> to do this?

This seems to be exactly what transactions are designed to do.  Could
you tell us a bit more about the application and why a transaction
isn't the right solution?

On Wed, 2008-07-09 at 15:38 +0200, Adrian Moisey wrote:

> I would like to be able to "mark" a point in my postgres database.
> After that I want to change a few things and "rollback" to that
> point.
> Does postgres support such a thing?  Is it possible for me to do this?

* Transactions ;-)

* PITR

* Build an application-level undo infrastructure using reverse action
triggers.

* Various internal ways not usually attempted.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

Adrian Moisey wrote:
> Hi
>
> >>>> I would like to be able to "mark" a point in my postgres database.
> >>>> After that I want to change a few things and "rollback" to that point.
> >>>> Does postgres support such a thing?  Is it possible for me to do this?
> >>> Well, transactions do that. If you want to do this inside a
> >>> transaction, the term you're looking for is "savepoints".
> >> I would like to do this globally over the entire DB, is that possible?
> >
> > Sure. You can start a transaction, create tables, drop tables, do
> > inserts and deletes, update some tables, create and delete functions,
> > triggers, views and schemas. After that, rollback. No problem.
>
> Can I do this outside of a transaction?
>
>
How about
   create database a_copy template = original_database

To recover at later date
  drop database original_database;
  alter database a_copy rename to original_database;

There will be all sorts of issues so it might be impractical depending
on your situation.
- permission (have to be able to create/drop/rename databases)
- concurrency (noone connected for the drop, everything done by anyone
else will be forgotten as well)
- time / capacity (creating a copy of a large database might take a
while and need extra storage)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

On Thu, Jul 10, 2008 at 6:24 AM, Guido Sagasti
<guidosagasti@yahoo.com.ar> wrote:
> Hi I want to star with postgresql and I want to know if it´s difficult the
> instalation and setting up in a desktop. I have not a IT background but work
> a lot with databases and need a little help.

On a workstation running most linux distros you just install it and go.

Before you go into production, be sure and read ALL of the
administration docs, front to back.

On 10/07/2008 13:24, Guido Sagasti wrote:
> Hi I want to star with postgresql and I want to know if it´s difficult
> the instalation and setting up in a desktop. I have not a IT background
> but work a lot with databases and need a little help.

Hi there,

If you're working on Windows, the installer is pretty good - it will set
up PG, create a user and set permissions, etc, and will also give you
the opportunity to download other stuff that goes with it - I'd
recommend PgAdmin, as it makes life much easier for someone new to PG.

There are also packages for various Linux distributions.

Do read the documentation thoroughly. It's excellent - comprehensive,
well written and well organised.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------