Horton
Menu

Thread

  1. Hot Backup

    Sandeep Chadha <sandeep@newnetco.com> — 2002-10-07T17:21:06Z

    Hello to all the Doers of Postgres!!!

Last time I went through forums, people spoke highly about 7.3 and its capability to do hot backups. My problem is if the database goes down and I lose my main data store, then I will lose all transactions back to the time I did the pg_dump.

Other databases (i e Oracle) solves this by retaining their archive logs in some physically separate storage. So, when you lose your data, you can restore the data from back-up, and then apply your archive log, and avoid losing any committed transactions. 

Postgresql has been lacking this all along. I've installed postgres 7.3b2 and still don't see any archive's flushed to any other place. Please let me know how is hot backup procedure implemented in current 7.3 beta(2) release.


Thanks.

  2. Re: [HACKERS] Hot Backup

    Neil Conway <neilc@samurai.com> — 2002-10-07T17:48:00Z

    "Sandeep Chadha" <sandeep@newnetco.com> writes:
> Postgresql has been lacking this all along. I've installed postgres
> 7.3b2 and still don't see any archive's flushed to any other
> place. Please let me know how is hot backup procedure implemented in
> current 7.3 beta(2) release.

AFAIK no such hot backup feature has been implemented for 7.3 -- you
appear to have been misinformed.

That said, I agree that would be a good feature to have.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

  3. Re: [HACKERS] Hot Backup

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-08T05:47:18Z

    On 7 Oct 2002 at 13:48, Neil Conway wrote:

> "Sandeep Chadha" <sandeep@newnetco.com> writes:
> > Postgresql has been lacking this all along. I've installed postgres
> > 7.3b2 and still don't see any archive's flushed to any other
> > place. Please let me know how is hot backup procedure implemented in
> > current 7.3 beta(2) release.
> AFAIK no such hot backup feature has been implemented for 7.3 -- you
> appear to have been misinformed.

Is replication an answer to hot backup?

Bye
 Shridhar

--
ink, n.:	A villainous compound of tannogallate of iron, gum-arabic,	and water, 
chiefly used to facilitate the infection of	idiocy and promote intellectual 
crime.		-- H.L. Mencken

  4. Re: [HACKERS] Hot Backup

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-09T00:26:57Z

    Shridhar Daithankar wrote:
> On 7 Oct 2002 at 13:48, Neil Conway wrote:
> 
> > "Sandeep Chadha" <sandeep@newnetco.com> writes:
> > > Postgresql has been lacking this all along. I've installed postgres
> > > 7.3b2 and still don't see any archive's flushed to any other
> > > place. Please let me know how is hot backup procedure implemented in
> > > current 7.3 beta(2) release.
> > AFAIK no such hot backup feature has been implemented for 7.3 -- you
> > appear to have been misinformed.
> 
> Is replication an answer to hot backup?

We already allow hot backups using pg_dump.  If you mean point-in-time
recovery, we have a patch for that ready for 7.4.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  5. Point in Time Recovery WAS: Hot Backup

    scott.marlowe <scott.marlowe@ihs.com> — 2002-10-09T16:19:26Z

    Hi Sandeep.  What you were calling Hot Backup is really called Point in 
Time Recovery (PITR).  Hot Backup means making a complete backup of the 
database while it is running, something Postgresql has supported for a 
very long time.

On Mon, 7 Oct 2002, Sandeep Chadha wrote:

> Hello to all the Doers of Postgres!!!
> 
> Last time I went through forums, people spoke highly about 7.3 and its 
> capability to do hot backups. My problem is if the database goes down 
> and I lose my main data store, then I will lose all transactions back 
> to the time I did the pg_dump.

Let's make it clear that this kind of failure is EXTREMELY rare on real 
database servers since they almost ALL run their data sets on RAID arrays.  
While it is possible to lost >1 drive at the same time and all your 
database, it is probably more likely to have a bad memory chip corrupt 
your data silently, or a bad query delete data it shouldn't.

That said, there IS work ongoing to provide this facility for Postgresql, 
but I would much rather have work done on making large complex queries run 
faster, or fix the little issues with foreign keys cause deadlocks.

> Other databases (i e Oracle) solves this by retaining their archive 
> logs in some physically separate storage. So, when you lose your data, 
> you can restore the data from back-up, and then apply your archive log, 
> and avoid losing any committed transactions. 
> 
> > Postgresql has been lacking this all along. I've installed postgres 
> 7.3b2 and still don't see any archive's flushed to any other place. 
> Please let me know how is hot backup procedure implemented in current 
> 7.3 beta(2) release.

Again, you'll get better response to your questions if you call it "point 
in time recovery" or pitr.  Hot backup is the wrong word, and something 
Postgresql DOES have.

It also supports WALs, which stands for Write ahead logs.  These files 
store what the database is about to do before it does it.  Should the 
database crash with transactions pending, the server will come back up and 
process the pending transactions that are in the WAL files, ensuring the 
integrity of your database.

Point in Time recovery is very nice, but it's the last step in many to 
ensure a stable, coherent database, and will probably be in 7.4 or 
somewhere around there.  If you're running in a RAID array, then the loss 
of your datastore should be a very remote possibility.

  6. Question about DEADLOCK

    savita <savita@india.hp.com> — 2002-11-22T06:24:00Z

    Hi All,

I have a question related to postgres.I have an application which is transferring data from MSSQL to Postgres at some regular interval.

I had around 20,000 data in MSSQL which should be transferred to postgres.
After transferring around 12000 data I got a error message
ERROR: deadlock detected
And after that data transfer has stopped.

How to avoid this error and what is the reason for this error.
--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

  7. Re: Question about DEADLOCK

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-11-22T07:04:52Z

    On Fri, 22 Nov 2002, Savita wrote:

> Hi All,
>
> I have a question related to postgres.I have an application which is
> transferring data from MSSQL to Postgres at some regular interval.
>
> I had around 20,000 data in MSSQL which should be transferred to postgres.
> After transferring around 12000 data I got a error message
> ERROR: deadlock detected
> And after that data transfer has stopped.
>
> How to avoid this error and what is the reason for this error.

Most likely cause would be a concurrent transaction doing something that
caused a foreign key to get into a dead lock situation (the current
implementation grabs overly strong locks).  In any case, we'll need more
information about what else was going on probably.

  8. Re: Question about DEADLOCK

    savita <savita@india.hp.com> — 2002-11-22T07:10:09Z

    Thanks Stephan,

I wold like to know where to get LOCK manual/reference.

And what is ther error number it will return when this error will come.Is there
any work around this.HOw do I avoid this situation.

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------


Stephan Szabo wrote:

> On Fri, 22 Nov 2002, Savita wrote:
>
> > Hi All,
> >
> > I have a question related to postgres.I have an application which is
> > transferring data from MSSQL to Postgres at some regular interval.
> >
> > I had around 20,000 data in MSSQL which should be transferred to postgres.
> > After transferring around 12000 data I got a error message
> > ERROR: deadlock detected
> > And after that data transfer has stopped.
> >
> > How to avoid this error and what is the reason for this error.
>
> Most likely cause would be a concurrent transaction doing something that
> caused a foreign key to get into a dead lock situation (the current
> implementation grabs overly strong locks).  In any case, we'll need more
> information about what else was going on probably.

  9. Re: Question about DEADLOCK

    Hans-Juergen Schoenig <hs@cybertec.at> — 2002-11-22T08:16:16Z

    I recommend reading Tom Lane's talk "Concurrency Issues" at OSCON2002.
This will answer all questions and some more.

    Best regards,

            Hans



Savita wrote:

>--------------880B7BB5722DEBFDA92AF2C3
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>Hi All,
>
>I have a question related to postgres.I have an application which is transferring data from MSSQL to Postgres at some regular interval.
>
>I had around 20,000 data in MSSQL which should be transferred to postgres.
>After transferring around 12000 data I got a error message
>ERROR: deadlock detected
>And after that data transfer has stopped.
>
>How to avoid this error and what is the reason for this error.
>--
>Best Regards
>- Savita
>----------------------------------------------------
>Hewlett Packard (India)
>+91 80 2051288 (Phone)
>847 1288 (HP Telnet)
>----------------------------------------------------
>
>
>--------------880B7BB5722DEBFDA92AF2C3
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
><html>
>Hi All,
><p>I have a question related to postgres.I have an application which is
>transferring data from MSSQL to Postgres at some regular interval.
><p>I had around 20,000 data in MSSQL which should be transferred to postgres.
><br>After transferring around 12000 data I got a error message
><br><b><font color="#3333FF">ERROR: deadlock detected</font></b>
><br><font color="#000000">And after that data transfer has stopped.</font><font color="#000000"></font>
><p><font color="#000000">How to avoid this error and what is the reason
>for this error.</font>
><br>--
><br>Best Regards
><br>- Savita
><br>----------------------------------------------------
><br>Hewlett Packard (India)
><br>+91 80 2051288 (Phone)
><br>847 1288 (HP Telnet)
><br>----------------------------------------------------
><br>&nbsp;</html>
>
>--------------880B7BB5722DEBFDA92AF2C3--
>
>  
>


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>

  10. Re: Question about DEADLOCK

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-11-22T15:35:47Z

    On Fri, 22 Nov 2002, Savita wrote:

> I wold like to know where to get LOCK manual/reference.

Well, there's the entry for the LOCK command, but I don't think
that's what you want, what exactly are you looking for?


> And what is ther error number it will return when this error will come.Is there
> any work around this.HOw do I avoid this situation.

I don't know of an error number, but the string will will have that
deadlock detected in it. :) As for a workaround, since we don't know
what's causing it, we can't really give you a workaround.

What's the schema of the table you're transferring to and what else
was going on in the server at the time you got the deadlock?  I'd
guess it was a foreign key, but that only applies if you've got
foreign keys, for example.

  11. Re: Question about DEADLOCK

    Haris Peco <snpe@snpe.co.yu> — 2002-11-22T17:30:35Z

    Hello,
You try load data first and then referential constraints

regards
Haris Peco
On Friday 22 November 2002 07:10 am, Savita wrote:
> Thanks Stephan,
>
> I wold like to know where to get LOCK manual/reference.
>
> And what is ther error number it will return when this error will come.Is
> there any work around this.HOw do I avoid this situation.
>
> --
> Best Regards
> - Savita
> ----------------------------------------------------
> Hewlett Packard (India)
> +91 80 2051288 (Phone)
> 847 1288 (HP Telnet)
> ----------------------------------------------------
>
> Stephan Szabo wrote:
> > On Fri, 22 Nov 2002, Savita wrote:
> > > Hi All,
> > >
> > > I have a question related to postgres.I have an application which is
> > > transferring data from MSSQL to Postgres at some regular interval.
> > >
> > > I had around 20,000 data in MSSQL which should be transferred to
> > > postgres. After transferring around 12000 data I got a error message
> > > ERROR: deadlock detected
> > > And after that data transfer has stopped.
> > >
> > > How to avoid this error and what is the reason for this error.
> >
> > Most likely cause would be a concurrent transaction doing something that
> > caused a foreign key to get into a dead lock situation (the current
> > implementation grabs overly strong locks).  In any case, we'll need more
> > information about what else was going on probably.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

  12. Re: Question about DEADLOCK

    Thomas F.O'Connell <tfo@monsterlabs.com> — 2003-01-09T19:09:47Z

    I haven't seen this before, and I can't find it on techdocs. Can you 
post a link to it?

-tfo

In article <3DDDE7D0.5040803@cybertec.at>,
 Hans-Jurgen Schonig <hs@cybertec.at> wrote:

> I recommend reading Tom Lane's talk "Concurrency Issues" at OSCON2002.
> This will answer all questions and some more.
> 
>     Best regards,
> 
>             Hans

  13. Re: Question about DEADLOCK

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-01-09T21:04:58Z

    "Thomas O'Connell" <tfo@monsterlabs.com> writes:
>  Hans-Jurgen Schonig <hs@cybertec.at> wrote:
>> I recommend reading Tom Lane's talk "Concurrency Issues" at OSCON2002.

> I haven't seen this before, and I can't find it on techdocs. Can you 
> post a link to it?

You can find a PDF at O'Reilly's conferences archive,

http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681

I recall asking Vince to put a copy on the Postgres website, but I don't
think he ever got around to it.

			regards, tom lane