Re: restoring with pg_restore

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.
>