Thread: restoring with pg_restore
Probably a silly question but if I restore with pg_restore to an existing database I get several messages 'primary key violation' ... clearly if I restore to a database I create for the occasion, this does not happen. 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. Thanks for your thoughts Didier
> 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.
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. >
Didier Gasser-Morlay wrote: > - 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 Correct; existing rows within tables will not be updated. > 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) Go ahead. Simply use -c option of pg_dump/pg_dumpall.
Philip, Frank thanks for your input and advice, yes I see now that pobably the way to go in my case, just hesitating with -C but I'll do some testing Didier