Postgres Pro
Downloads
Home   >   mailing lists

Thread: Backup/Recovery (BR) question

Backup/Recovery (BR) question

From
Oli Sennhauser
Date:
Hello dba's

Im playing around with the BR features of PostgreSQL 7.4 for a
PostgreSQL-BR course.

Q1: Let's imagine, we have a database cluster containing DB1, DB2 and
DB3. System (disk) crashes and we are only able to recover DB2 (was on a
local disk not on the external like DB1 and DB3). Bring it up again,
everything is fine...
But then I want to clean up a little and drop the missing DB's (e.g. to
install them from scratch):

DB2=# \l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 DB1       | postgres | SQL_ASCII
 DB2       | postgres | SQL_ASCII
 DB3       | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(5 rows)

DB2=# drop database DB1;
ERROR:  database "db1" does not exist

* How can I clean up the database cluster after this scenario?
* Is the database cluster so fare so well ok now for further production
or do we have to dump?

Q2: New situation: Why is it not a good idea to backup the database
files of a cluster incl. all c_log and x_log (log files last) to get a
"physicaly hot backup".
In principle it is the same situation like a server which is crashing
(not a once but during some time). After restoring, it should do a redo
and rollback automatically like after a crash. This methode (physical
hot backup) would increas backup and restore times dramatically.

Thanks for clearing up
Oli

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import
Attachment

Re: Backup/Recovery (BR) question

From
Robert Treat
Date:
On Thu, 2003-11-20 at 11:09, Oli Sennhauser wrote:
> Hello dba's
>
> Im playing around with the BR features of PostgreSQL 7.4 for a
> PostgreSQL-BR course.
>
> Q1: Let's imagine, we have a database cluster containing DB1, DB2 and
> DB3. System (disk) crashes and we are only able to recover DB2 (was on a
>
> local disk not on the external like DB1 and DB3). Bring it up again,
> everything is fine...
> But then I want to clean up a little and drop the missing DB's (e.g. to
> install them from scratch):
>
> DB2=# \l
>         List of databases
>    Name    |  Owner   | Encoding
> -----------+----------+-----------
>  DB1       | postgres | SQL_ASCII
>  DB2       | postgres | SQL_ASCII
>  DB3       | postgres | SQL_ASCII
>  template0 | postgres | SQL_ASCII
>  template1 | postgres | SQL_ASCII
> (5 rows)
>
> DB2=# drop database DB1;
> ERROR:  database "db1" does not exist
>

There is an error in your example that I can't determine if it is
intentional or not.  In your above example, your problem is not that
your database doesn't exist, it is that you are not quoting the database
name properly. Note the name of the database is DB1 (uppercase) but the
error message is talking about db1 (lowercase)

If you do  // drop database "DB1" // the first database will drop,
though you might want to verify if it is really damaged.


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: Backup/Recovery (BR) question

From
Robert Treat
Date:
On Thursday 20 November 2003 18:00, Oli Sennhauser wrote:
> Hello Robert
>
> >If you do  // drop database "DB1" // the first database will drop,
> >though you might want to verify if it is really damaged.
>
> Oh sh....! It worked. Thanks. I am used to do the other way around (from
> oracle).
>
> Do you have any idea to my second question?
>
> Q2: New situation: Why is it not a good idea to backup the database
> files of a cluster incl. all c_log and x_log (log files last) to get a
> "physicaly hot backup".
> In principle it is the same situation like a server which is crashing
> (not a once but during some time). After restoring, it should do a redo
> and rollback automatically like after a crash. This methode (physical
> hot backup) would increas backup and restore times dramatically.
>

Essentially I think you're right, it should behave much like a crashing
server.  The main reason why people don't recommend it is that (depending on
your os setup) there is the potential to lose data that has been commited but
not actually written to disk.  Note that you shouldn't get corrupted data
from this, but in many cases losing data is just as bad so we don't recomend
it.  If you really want to do this, you should really either shut down the
database  or get LVM going.


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: Backup/Recovery (BR) question

From
"Jean Huveneers"
Date:
Hi,
Hi,

We do a back-up of our postgresql server by stopping it, takning a LVM
snapshot starting te server again (< 10 seconds), than we write the snapshot
to a tape.
Works fine, and with your theory it should be able to take the snapshot
without stopping postgresql. (to make the log replay work you at least need
the log & db files from the exact same moment in time).

See attached batch file (used with Redhat 9, LVM 1.0.7 and taper)

Regards,

Jean Huveneers

Xillion ICT Solutions B.V.
Marktplein 8
6243 BR  Geulle
The Netherlands

tel: +31 (0)43 3659244
fax: +31 (0)43 3659249
gsm: +31 (0)6 456 44 357
www: www.xillion.nl

> -----Original Message-----
> From: Robert Treat [mailto:xzilla@users.sourceforge.net]
> Sent: vrijdag 21 november 2003 7:55
> To: Oli Sennhauser
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Backup/Recovery (BR) question
>
>
> On Thursday 20 November 2003 18:00, Oli Sennhauser wrote:
> > Hello Robert
> >
> > >If you do  // drop database "DB1" // the first database will drop,
> > >though you might want to verify if it is really damaged.
> >
> > Oh sh....! It worked. Thanks. I am used to do the other way
> around (from
> > oracle).
> >
> > Do you have any idea to my second question?
> >
> > Q2: New situation: Why is it not a good idea to backup the database
> > files of a cluster incl. all c_log and x_log (log files
> last) to get a
> > "physicaly hot backup".
> > In principle it is the same situation like a server which
> is crashing
> > (not a once but during some time). After restoring, it
> should do a redo
> > and rollback automatically like after a crash. This methode
> (physical
> > hot backup) would increas backup and restore times dramatically.
> >
>
> Essentially I think you're right, it should behave much like
> a crashing
> server.  The main reason why people don't recommend it is
> that (depending on
> your os setup) there is the potential to lose data that has
> been commited but
> not actually written to disk.  Note that you shouldn't get
> corrupted data
> from this, but in many cases losing data is just as bad so we
> don't recomend
> it.  If you really want to do this, you should really either
> shut down the
> database  or get LVM going.
>
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
>       joining column's datatypes do not match
>
>
Attachment