Skip site navigation (1) Skip section navigation (2)

Re: restoring with pg_restore

From: "Didier Gasser-Morlay" <didiergm(at)gmail(dot)com>
To: "Phillip Smith" <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: restoring with pg_restore
Date: 2008-04-24 08:21:55
Message-ID: 608b66ce0804240121k6d52f07fk2ae1e92bd6ebf4ee@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I realise I was not clear, that what happens when English is not your
1st language .  :)

- considering that I need to run every day a number of heavy reports
using data up to the yesterday close of play, I thought it would be a
good idea to have a backup restored every morning on a separate
'Report server' last night's backup from the 'live' server.

- I believe that restoring a backup onto an existing, non empty
database generates 'primary key violations' and I am not sure about
what happens to exsiting records, I do not believe they are updated so
I can get a restored database with data inconsistent with the 'live'
server

so, as I understand it, I would on the report server need to drop the
database ; create a new bank database and restore onto this new
database.

If I am correct so far, my question is : is it a good practice to drop
and create a database every day on a postgreSQL server or do I risk
any corruption of the database cluster (that's how I call the
directory and files created with initdb)

I hope I made myself clearer

Didier



On Thu, Apr 24, 2008 at 1:34 AM, Phillip Smith
<phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
> > Is it a good practice in order to have a reasonable level of security to
>  on a server drop and recreate a database every day ? would there be any side
>  > > > effects things that could get corrupted in the long term. We want to
>  restore because we are using a separate server for the heavy reporting we
>  have so we > are planning to backup and restore at least every night.
>
>  I'm a little lost - how would dropping and restoring your database each
>  night increase security? Do you mean security as in your data will be (more)
>  secure against data loss, as opposed to unauthorized access etc?
>
>  The only benefits I can see is:
>  A) You will always know your backup is working (or not, as the case may be)
>  B) Your physical 'size on disk' will always be minimum possible as the
>  backend files on disk will be re-written and optimized every time you do it.
>
>
>  THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
>
>  *******************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
>  e-mail.
>

In response to

Responses

pgsql-novice by date

Next:From: Didier Gasser-MorlayDate: 2008-04-24 09:08:12
Subject: Changing a trigger function
Previous:From: Phillip SmithDate: 2008-04-23 23:34:45
Subject: Re: restoring with pg_restore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group