Re: [Treebase-devel] pg_dump how best to run?

[Jon A. <jon...@du...>]

Rutger,

I've got a cron job that dumps the database every night. I keep a  
weeks worth of backups on the server, and a months worth on our backup  
server. You can find the backups in the '/backups' directory.

The backup script is:
# backup all the roles with password hashes
pg_dumpall -U postgres -w -r -f /backups/roles.`date +%a`.sql

# backup the treebase database in custom (binary) format
pg_dump -U postgres -F c -f /backups/treebasedev`date +%a`.custom.dump  
treebasedev

I could have used pg_dumpall just as easily, but I prefer to have  
granularity in my restores.

You've got three choices for dump format: custom, tar, or plain text

The reason I no longer do plain txt is that I've had issues restoring  
some of the plain text dumps. Sometimes postgres will try to restore  
the sequences before the corresponding tables and the whole restore  
fails.

With that being said, we should run a restore before you run your  
script, just to make sure it works. When I get in today, I'll try  
restoring it on another server, since the treebasedb-dev server is  
rather bogged down at the moment.

-Jon

On Dec 2, 2009, at 6:36 AM, Rutger Vos wrote:

> Hi,
>
> I am about to run a script that deletes orphaned taxonlabels from the
> database. These labels were created during an intermediate step of
> data bulk loading. They don't interfere with the integrity of genuine
> records, but may have some effect on performance (though probably not
> spectacular) so I want to get rid of them. Just to be on the safe
> side, I want to dump the database first. Also, this might be a good
> practice run if we want to release periodical database dumps to the
> public (the way itis and ncbi do). So what would be the best way to do
> this? Anyone have any experience running pg_dump?
>
> Rutger
>
> -- 
> Dr. Rutger A. Vos
> School of Biological Sciences
> Philip Lyle Building, Level 4
> University of Reading
> Reading
> RG6 6BX
> United Kingdom
> Tel: +44 (0) 118 378 7535
> http://www.nexml.org
> http://rutgervos.blogspot.com
>
> ------------------------------------------------------------------------------
> Join us December 9, 2009 for the Red Hat Virtual Experience,
> a free event focused on virtualization and cloud computing.
> Attend in-depth sessions from your desk. Your couch. Anywhere.
> http://p.sf.net/sfu/redhat-sfdev2dev
> _______________________________________________
> Treebase-devel mailing list
> Tre...@li...
> https://lists.sourceforge.net/lists/listinfo/treebase-devel

-------------------------------------------------------
  Jon Auman
  Systems Administrator
  National Evolutionary Synthesis Center
  Duke University
  http:www.nescent.org
  jon...@ne...
------------------------------------------------------