Thread: Online Backups PostGre
Hi everyone, I am new to PostGreSql but I have to use it at work. I have to take backups of the database every 10 mins so that work isn't lost in case of an accident. I use PostGreSql v8.1 on Windows XP. I have already set up a system that automatically takes the backup of the database every 10 mins. I did this using a batch file script set up in scheduled tasks to run every 10 mins. The command I used inside the batch file is: pg_dump dbname -U postgres -o > backup_file_name I tested the system and it works correctly but I know that in the live server the pg_dump command will take a long time to execute. I know that I should use a 'point in time recovery backup' but I am confused how to exactly go about it as I am new to PostGre. I searched the web and I know I should get a snapshot of the filesystem and then backup the WAL archive logs but this is the part where I am confused. I do not know of any open source backup utilities that can take snapshots of the filesystem. I get the overall concept of online backups but I am still unclear EXACTLY how the system works. I would be grateful if anyone could explain it to me. I have already gone through the postgresql document on online backups. I know that taking the backup of the whole database every 10 minutes is a very bad way to go about it but until I find a better way to do it, it will have to do for now. Any suggestions/tips/articles on how to do the backup would be appreciated very much. Thanks in advance, PostGre Newbie.
On Fri, 1 May 2009, PostGre Newbie wrote: > I know that taking the backup of the whole database every 10 minutes > is a very bad way to go about it but until I find a better way to do > it, it will have to do for now. Any suggestions/tips/articles on how > to do the backup would be appreciated very much. The postgres manual tends to be a great source of information. See, for example: http://www.postgresql.org/docs/8.1/interactive/backup.html
On Fri, 2009-05-01 at 09:22 -0700, PostGre Newbie wrote: > Hi everyone, > I searched the web and I know I should get a snapshot of the > filesystem and then backup the WAL archive logs but this is the part > where I am confused. I do not know of any open source backup utilities > that can take snapshots of the filesystem. I get the overall concept > of online backups but I am still unclear EXACTLY how the system works. > I would be grateful if anyone could explain it to me. I have already > gone through the postgresql document on online backups. Well that's just it. Out of the box it doesn't actually work. PostgreSQL only gives you the facilities to roll your own PITR solution. You can look at PITR Tools: https://projects.commandprompt.com/public/pitrtools It doesn't quite work on Windows due to lack of rsync and signaling differences but could give you an idea of how to move forward with your own implementation. I would also note that Win32 8.1 is deprecated and unsupported. You need to update to at least 8.2. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Fri, 2009-05-01 at 09:47 -0700, Ben Chobot wrote: > On Fri, 1 May 2009, PostGre Newbie wrote: > > > I know that taking the backup of the whole database every 10 minutes > > is a very bad way to go about it but until I find a better way to do > > it, it will have to do for now. Any suggestions/tips/articles on how > > to do the backup would be appreciated very much. > > The postgres manual tends to be a great source of information. See, for > example: > > http://www.postgresql.org/docs/8.1/interactive/backup.html Not for Windows it isn't. Joshua D. Drake -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Friday 01 May 2009, PostGre Newbie <garbagegigo@gmail.com> wrote: >I do not know of any open source backup utilities > that can take snapshots of the filesystem. I get the overall concept > of online backups but I am still unclear EXACTLY how the system works. > I would be grateful if anyone could explain it to me. I have already > gone through the postgresql document on online backups. rsync or tar. One of the really nice things about PITR is that the base backup source can change while it's being backed up and it still works. PITR is really just as simple as it sounds. Call start_backup(). Copy the PostgreSQL data directory. Call stop_backup(). Keep all the WAL logs generated during and after the base backup. -- Even a sixth-grader can figure out that you can’t borrow money to pay off your debt
Joshua D. Drake wrote: > Well that's just it. Out of the box it doesn't actually work. PostgreSQL > only gives you the facilities to roll your own PITR solution. You can > look at PITR Tools: > > https://projects.commandprompt.com/public/pitrtools > > It doesn't quite work on Windows due to lack of rsync and signaling > differences but could give you an idea of how to move forward with your > own implementation. > Quite possibly 'robocopy' from Microsoft somewhere (doesn't come with windows, but was part of an admin kit or something) would be a workable replacement for the rsync part.
----- "Joshua D. Drake" <jd@commandprompt.com> wrote: > On Fri, 2009-05-01 at 09:22 -0700, PostGre Newbie wrote: > > Hi everyone, > > > I searched the web and I know I should get a snapshot of the > > filesystem and then backup the WAL archive logs but this is the > part > > where I am confused. I do not know of any open source backup > utilities > > that can take snapshots of the filesystem. I get the overall > concept > > of online backups but I am still unclear EXACTLY how the system > works. > > I would be grateful if anyone could explain it to me. I have > already > > gone through the postgresql document on online backups. > > Well that's just it. Out of the box it doesn't actually work. > PostgreSQL > only gives you the facilities to roll your own PITR solution. You can > look at PITR Tools: > > https://projects.commandprompt.com/public/pitrtools > > It doesn't quite work on Windows due to lack of rsync and signaling > differences but could give you an idea of how to move forward with > your > own implementation. > > I would also note that Win32 8.1 is deprecated and unsupported. You > need > to update to at least 8.2. > > Sincerely, > > Joshua D. Drake For the rsync requirement you want to take a look at: DeltaCopy http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp Adrian Klaver aklaver@comcast.net
On Fri, May 1, 2009 at 12:06 PM, John R Pierce <pierce@hogranch.com> wrote: > Joshua D. Drake wrote: >> >> Well that's just it. Out of the box it doesn't actually work. PostgreSQL >> only gives you the facilities to roll your own PITR solution. You can >> look at PITR Tools: >> >> https://projects.commandprompt.com/public/pitrtools >> >> It doesn't quite work on Windows due to lack of rsync and signaling >> differences but could give you an idea of how to move forward with your >> own implementation. >> > > Quite possibly 'robocopy' from Microsoft somewhere (doesn't come with > windows, but was part of an admin kit or something) would be a workable > replacement for the rsync part. There is an rsync for windows, called delta copy: http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp
Cygwin comes with rsync on Windows. On 02/05/2009, at 4:06 AM, John R Pierce wrote: > Joshua D. Drake wrote: >> Well that's just it. Out of the box it doesn't actually work. >> PostgreSQL >> only gives you the facilities to roll your own PITR solution. You can >> look at PITR Tools: >> >> https://projects.commandprompt.com/public/pitrtools >> >> It doesn't quite work on Windows due to lack of rsync and signaling >> differences but could give you an idea of how to move forward with >> your >> own implementation. >> > > Quite possibly 'robocopy' from Microsoft somewhere (doesn't come > with windows, but was part of an admin kit or something) would be a > workable replacement for the rsync part. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Thanks everyone for replying. I will definitely try out the methods outlined. Thanks once again :)
Thanks everyone for replying. I will definitely try out the methods outlined. Thanks once again :)