Re: [GENERAL] [ADMIN] Backup
Simon Riggs wrote: On Fri, 2008-01-25 at 11:34 +1100, Phillip Smith wrote: We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database. It doesn't appear to. The design of the PITR system allows a product-agnostic backup. Anything that can backup a file can backup PostgreSQL. There is no need for special certifications of hologram logos. You may need to write a few lines of script to do it, but that's not a problem surely? So you can use pg_dump or PITR, as you choose. If you don't mind if you lose some transactions you can also use file system snapshottingwhich would work just as well as pg_dump, and probably have less impact on the running database (assuming you had decent enough hardware). So long as the product you are using can snapshot the file system prior to performing a backup, you can use just about any product (without having to really do much/any work). When you restore a snapshotted file system that contains your postgresql database, postgresql (on restart) will enter auto-recovery mode and recover (as it does in the case of the crash) to the last transaction that was completed successfully prior to the snapshot being created. Note that this would only work if all your tablespaces were on the same file system, and would be unable to roll forward using WAL files generated after the backup. However, you should keep in mind that - like a pg_dump - you won't be able to perform PITR recovery from such a backup. Also, the recovery time may be non-trivial depending on your WAL settings. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com As me about Expert PostgreSQL PostGIS Training delivered worldwide.
Re: [GENERAL] [ADMIN] Backup
On Thu, 2008-01-31 at 07:21 -0500, Chander Ganesan wrote: If you don't mind if you lose some transactions That sentence has no place in any discussion about backup because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. As far as I am concerned, if any Postgres user loses data then we're all responsible. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ADMIN] Backup
On Thu, Jan 31, 2008 at 01:28:48PM +, Simon Riggs wrote: That sentence has no place in any discussion about backup because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. Hmm? I thought the whole point of a filesystem snapshot was that it's the same as if the system crashed. And I was fairly sure we could recover from that... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] [ADMIN] Backup
Simon Riggs wrote: On Thu, 2008-01-31 at 07:21 -0500, Chander Ganesan wrote: If you don't mind if you lose some transactions That sentence has no place in any discussion about backup because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. As far as I am concerned, if any Postgres user loses data then we're all responsible. I understand your point, but indicating that you can't trust a point-in-time snapshot of the database is, IMHO, the same as saying you can't trust PostgreSQL's automatic crash recovery, since the two are essentially the same thing... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Backup
On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: On Thu, Jan 31, 2008 at 01:28:48PM +, Simon Riggs wrote: That sentence has no place in any discussion about backup because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. Hmm? I thought the whole point of a filesystem snapshot was that it's the same as if the system crashed. And I was fairly sure we could recover from that... That was my assumption as well. *Assuming* that the filesystem snapshot is consistent. There are a bunch of solutions that don't do consistent snapshots between different partitions, so if your WAL or one tablespace is on a different partition, you'll get corruption anyway... (seen this in Big Commercial Database, so that's not a pg problem) //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [ADMIN] Backup
Simon Riggs wrote: As far as I am concerned, if any Postgres user loses data then we're all responsible. Remember, our license says this software is given without any warranty whatsoever, implicit or explicit, written or implied, given or sold, alive or deceased. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [ADMIN] Backup
Magnus Hagander wrote: On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: On Thu, Jan 31, 2008 at 01:28:48PM +, Simon Riggs wrote: That sentence has no place in any discussion about backup because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. Hmm? I thought the whole point of a filesystem snapshot was that it's the same as if the system crashed. And I was fairly sure we could recover from that... That was my assumption as well. *Assuming* that the filesystem snapshot is consistent. There are a bunch of solutions that don't do consistent snapshots between different partitions, so if your WAL or one tablespace is on a different partition, you'll get corruption anyway... (seen this in Big Commercial Database, so that's not a pg problem) Agreed. That's why I made it a point to mention that all of your tablespaces should be on the same file system... In hindsight, I should have also stated that your WAL logs should be on the same file system as well... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ADMIN] Backup
On Thu, 2008-01-31 at 10:02 -0500, Chander Ganesan wrote: Magnus Hagander wrote: On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: On Thu, Jan 31, 2008 at 01:28:48PM +, Simon Riggs wrote: That sentence has no place in any discussion about backup because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. Hmm? I thought the whole point of a filesystem snapshot was that it's the same as if the system crashed. And I was fairly sure we could recover from that... That was my assumption as well. *Assuming* that the filesystem snapshot is consistent. There are a bunch of solutions that don't do consistent snapshots between different partitions, so if your WAL or one tablespace is on a different partition, you'll get corruption anyway... (seen this in Big Commercial Database, so that's not a pg problem) Agreed. That's why I made it a point to mention that all of your tablespaces should be on the same file system... In hindsight, I should have also stated that your WAL logs should be on the same file system as well... I think we all understand and agree, I just start twitching when anyone talks about it being OK to lose transactions when backing up. You meant the ones currently in progress, not the ones already committed and on disk. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Backup
On Thu, 2008-01-31 at 12:09 -0300, Alvaro Herrera wrote: Simon Riggs wrote: As far as I am concerned, if any Postgres user loses data then we're all responsible. Remember, our license says this software is given without any warranty whatsoever, implicit or explicit, written or implied, given or sold, alive or deceased. Yes! ...I meant via the free press, not via the courts. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [ADMIN] Backup
PostgreSQL has its own inbuilt mechanism for backing up the database. you can refer to the postgres manual online for more information. http://www.postgresql.org/docs/8.2/interactive/backup.html - Vishal Subject: [ADMIN] BackupDate: Thu, 24 Jan 2008 14:08:26 -0500From: [EMAIL PROTECTED]: pgsql-general@postgresql.org; [EMAIL PROTECTED]: [EMAIL PROTECTED] Hi, We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database. We presently run VERITAS ver9.1 on windows2003 server. What is being used by users out there now. We are thinking of upgrading to the latest Symantec backup exec software but am not sure if this version does backup PostgreSQL. When called for support they told me to go to the Symantec site and search the compatibility list. I didn't find anything dealing with PostgreSQL database. Any answer would help me simplify my backup situation. Thanks, Dominic CarlucciProduction Control Analyst CAE Inc.8585 Cote de LiesseSt. Laurent, Quebec H4T 1G6 (514)341-2000 [EMAIL PROTECTED] _ Tried the new MSN Messenger? It’s cool! Download now. http://messenger.msn.com/Download/Default.aspx?mkt=en-in
Re: [GENERAL] [ADMIN] Backup
AFAIK Dominic needs a plug-in certified by Symantec, and that is not the case. As you may have read prior to this mail, the common way is to pg_dump against a file, picking up that file later with BackupExec as a regular file. We are currently (www.globant.com) using it that way, no problems at all. gb.- On Jan 30, 2008 2:15 AM, Vishal Arora [EMAIL PROTECTED] wrote: PostgreSQL has its own inbuilt mechanism for backing up the database. you can refer to the postgres manual online for more information. http://www.postgresql.org/docs/8.2/interactive/backup.html - Vishal Subject: [ADMIN] Backup Date: Thu, 24 Jan 2008 14:08:26 -0500 From: [EMAIL PROTECTED] To: pgsql-general@postgresql.org; [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Hi, We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database. We presently run VERITAS ver9.1 on windows2003 server. What is being used by users out there now. We are thinking of upgrading to the latest Symantec backup exec software but am not sure if this version does backup PostgreSQL. When called for support they told me to go to the Symantec site and search the compatibility list. I didn't find anything dealing with PostgreSQL database. Any answer would help me simplify my backup situation. Thanks, Dominic Carlucci Production Control Analyst CAE Inc. 8585 Cote de Liesse St. Laurent, Quebec H4T 1G6 (514)341-2000 #2936 [EMAIL PROTECTED] Live the life in style with MSN Lifestyle. Check out! Try it now! -- Guido Barosio --- http://www.globant.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ADMIN] Backup
On Fri, 2008-01-25 at 11:34 +1100, Phillip Smith wrote: We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database. It doesn't appear to. The design of the PITR system allows a product-agnostic backup. Anything that can backup a file can backup PostgreSQL. There is no need for special certifications of hologram logos. You may need to write a few lines of script to do it, but that's not a problem surely? So you can use pg_dump or PITR, as you choose. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ADMIN] Backup
On Jan 24, 2008 1:08 PM, Dominic Carlucci [EMAIL PROTECTED] wrote: Hi, We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database. We presently run VERITAS ver9.1 on windows2003 server. What is being used by users out there now. We are thinking of upgrading to the latest Symantec backup exec software but am not sure if this version does backup PostgreSQL. When called for support they told me to go to the Symantec site and search the compatibility list. I didn't find anything dealing with PostgreSQL database. Any answer would help me simplify my backup situation. Not sure if there's some kind of special plug in for Symantec, but basically, you want to run pg_dump or pg_dumpall and then point symantec to back up the file thus created. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] Backup
We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database. It doesn't appear to. I've just been through the whole rigmarole of BackupExec for some Windows Servers, and I couldn't find anything to deal with PostgreSQL. Just dump Postgres to the File System and backup that dump. Depends on what your Recovery Point requirements are. 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Nov 22, 2007 2:19 PM, Sorin N. Ciolofan [EMAIL PROTECTED] wrote: Hello all! I've a small bash script backup.sh for creating dumps on my Postgre db: #!/bin/bash time=`date '+%d'-'%m'-'%y'` cd /home/swkm/services/test pg_dump mydb mydb_dump_$time.out I've edited crontab and added a line: 00 4 * * * swkm /home/swkm/services/test/backup.sh to execute the backup.sh as user swkm daily at 4 am. The user swkm is the user I use to create backups manually. The script itself is executed fine if run manually but run on cron scheduler I got an mydb_dump_$time.out file empty (of 0 kb) Do you have any idea about what's wrong? Thanks Sorin Hi Sorin, why don't you add a MAILTO=youraddress at the start of your crontab file, so that you can receive a report of the problem? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
Hi Marco! Thank you for the advice. I got: /home/swkm/services/test/backup.sh: line 4: pg_dump: command not found updating: mydb_dump_22-11-07.out (stored 0%) which seems strange -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marco Bizzarri Sent: Thursday, November 22, 2007 3:28 PM To: Sorin N. Ciolofan Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [ADMIN] backup of postgres scheduled with cron On Nov 22, 2007 2:19 PM, Sorin N. Ciolofan [EMAIL PROTECTED] wrote: Hello all! I've a small bash script backup.sh for creating dumps on my Postgre db: #!/bin/bash time=`date '+%d'-'%m'-'%y'` cd /home/swkm/services/test pg_dump mydb mydb_dump_$time.out I've edited crontab and added a line: 00 4 * * * swkm /home/swkm/services/test/backup.sh to execute the backup.sh as user swkm daily at 4 am. The user swkm is the user I use to create backups manually. The script itself is executed fine if run manually but run on cron scheduler I got an mydb_dump_$time.out file empty (of 0 kb) Do you have any idea about what's wrong? Thanks Sorin Hi Sorin, why don't you add a MAILTO=youraddress at the start of your crontab file, so that you can receive a report of the problem? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Nov 22, 2007 2:46 PM, Sorin N. Ciolofan [EMAIL PROTECTED] wrote: Hi Marco! Thank you for the advice. I got: /home/swkm/services/test/backup.sh: line 4: pg_dump: command not found updating: mydb_dump_22-11-07.out (stored 0%) which seems strange Try putting the full path of the pg_dump command in the script. Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Thu, Nov 22, 2007 at 02:28:08PM +0100, Marco Bizzarri wrote: why don't you add a MAILTO=youraddress at the start of your crontab file, so that you can receive a report of the problem? Note: check that your cron accepts such an addition. Many systems now use Vixie's cron, which does accept that, but some don't. It's a nice feature, and good for this purpose. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Nov 22, 2007 2:53 PM, Andrew Sullivan [EMAIL PROTECTED] wrote: On Thu, Nov 22, 2007 at 02:28:08PM +0100, Marco Bizzarri wrote: why don't you add a MAILTO=youraddress at the start of your crontab file, so that you can receive a report of the problem? Note: check that your cron accepts such an addition. Many systems now use Vixie's cron, which does accept that, but some don't. It's a nice feature, and good for this purpose. Andrew, can you confirm the previous statement? I'm checking on a Debian Linux, at it seems to be a Vixie Cron, and that feature is described in the man page... A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Thu, Nov 22, 2007 at 02:59:33PM +0100, Marco Bizzarri wrote: Andrew, can you confirm the previous statement? I'm checking on a Debian Linux, at it seems to be a Vixie Cron, and that feature is described in the man page... If the feature's in your man page, then it works on your system :) I just wanted to warn you that this isn't an original feature of cron, so you have to check your system always to be sure you have it. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Thu, Nov 22, 2007 at 09:14:13AM -0500, Martin Gainty wrote: Good Morning AndrewI noticed the Vixie cron responds to SIGHUP signalsDo you have any suggestions or tutorials on how Postgres would feed these event signals to Vixie cron? Why would Postgres have to tell crond to restart? A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
