Re: restoring with pg_restore - Mailing list 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.smith@weatherbeeta.com.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.
>