Re: [Celsius-devel] [Nelsonlab-main] axis.genome.ucla.edu (celsius database) offline

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hey,

Thanks for taking point on this.  You'll want to make sure you
TRUNCATE on both plier and plier_byq, there is a trigger that sends
data from one table to the other to keep them synced.

I don't really have a good undertanding of pgdump, generally.  Maybe
you should do a VACUUM FULL on the database first though after making
such a large change by dropping those tables.

-Allen

On Wed, May 14, 2008 at 7:07 PM, Bret Harry <ja...@ga...> wrote:
> The celsius postgres database is ~2T. I have been wrestling with this behemoth over the last month or so, trying to get a complete database dump as a prerequisite for doing backups. My goal is to make a clean standalone installation that is documented and can be replicated at other sites. Here is a brief narrative of what has happened so far:
>
>  Following the postgres user manual, I initially did a pg_dumpall which wrote ~500 GB data in ~20 hours before throwing the following error:
>
>    bash-3.00$ pg_dumpall -U postgres --oids | split --suffix-length=3 --bytes=1073741824 - `date "+axis.pg_dumpall.%Y.%m.%d.%H.%M."`
>
>    pg_dump: ERROR:  invalid page header in block 2325630 of relation "plier"
>    pg_dump: SQL command to dump the contents of table "plier" failed: PQendcopy() failed.
>    pg_dump: Error message from server: ERROR:  invalid page header in block 2325630 of relation "plier"
>    pg_dump: The command was: COPY part_elementresult.plier (elementresult_id, element_id, quantification_id, signal) TO stdout;
>    pg_dumpall: pg_dump failed on database "chado-celsius", exiting
>
>  After researching the error a bit, speaking with Allen, and trying a unsuccessful VACUUM (same error as above). I tried to truncate part_elementresult.plier, which  caused the following error:
>
>    chado-celsius=> truncate part_elementresult.plier;
>    WARNING:  could not remove relation 1663/16387/1945642: Read-only file system
>    TRUNCATE TABLE
>    chado-celsius=> select * from part_elementresult.plier;
>    ERROR:  could not open relation 1663/16387/4822791: Read-only file system
>
>    May  9 15:11:41 axis kernel: EXT3-fs error (device md0): ext3_free_blocks_sb: bit already cleared for block 973843591
>    May  9 15:11:41 axis kernel: Aborting journal on device md0.
>    May  9 15:11:41 axis kernel: ext3_abort called.
>    May  9 15:11:41 axis kernel: EXT3-fs error (device md0): ext3_journal_start_sb: Detected aborted journal
>    May  9 15:11:41 axis kernel: Remounting filesystem read-only
>    May  9 15:11:41 axis kernel: EXT3-fs error (device md0) in ext3_free_blocks_sb: Journal has aborted
>    May  9 15:11:41 axis kernel: EXT3-fs error (device md0) in ext3_reserve_inode_write: Journal has aborted
>    May  9 15:11:41 axis kernel: __journal_remove_journal_head: freeing b_committed_data
>
>  Scary stuff. I did a shutdown -r on axis, and mercifully, postgres appeared to come back online. The part_elementresult.plier table was empty. I restarted the dump as before, however this time around the dump is extremely slow, I estimate it will take ~40 days to complete...
>
>  I wanted to see if anyone has any ideas on how I might be able to speed up the dump.
>
>  Regards,
>
>  -bret
>
>
>  On Wed, May 14, 2008 at 07:55:54PM -0600, Bret Harry wrote:
>  > I am moving this over to celsius-devel.
>  > -bret
>  >
>  > On Fri, May 09, 2008 at 06:27:17PM -0700, Allen Day wrote:
>  > > you should send this to the celsius-users list as well.
>  > >
>  > > On Fri, May 9, 2008 at 4:27 PM, Bret Harry <ja...@ga...> wrote:
>  > > > Hello Folks,
>  > > >
>  > > > While trying to backup celsius' postgres database, there was a filesystem error on axis.genome.ucla.edu which caused the main data volume to become read-only. Unfortunately, this machine will have to be taken offline in order to run a filesystem check. I will update the list when it finishes.
>  > > >
>  > > > -bret
>  > > >
>  > > > -------------------------------------------------------------------------
>  > > > This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>  > > > Don't miss this year's exciting event. There's still time to save $100.
>  > > > Use priority code J8TL2D2.
>  > > > http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>  > > > _______________________________________________
>  > > > Nelsonlab-main mailing list
>  > > > Nel...@li...
>  > > > https://lists.sourceforge.net/lists/listinfo/nelsonlab-main
>  > > >
>  > >
>  > >
>  > >
>  > > --
>  > > allenday.skype
>  > > +1 (415) 335-4654 (office)
>  > > +1 (310) 804-5304 (mobile)
>  > > +1 (515) 474-9337 (fax)
>  >
>  > -------------------------------------------------------------------------
>
> > This SF.net email is sponsored by: Microsoft
>  > Defy all challenges. Microsoft(R) Visual Studio 2008.
>  > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
>  > _______________________________________________
>  > Celsius-devel mailing list
>  > Cel...@li...
>  > https://lists.sourceforge.net/lists/listinfo/celsius-devel
>
>
>  -------------------------------------------------------------------------
>  This SF.net email is sponsored by: Microsoft
>  Defy all challenges. Microsoft(R) Visual Studio 2008.
>  http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
>  _______________________________________________
>  Celsius-devel mailing list
>  Cel...@li...
>  https://lists.sourceforge.net/lists/listinfo/celsius-devel
>

-- 
allenday.skype
+1 (415) 335-4654 (office)
+1 (310) 804-5304 (mobile)
+1 (515) 474-9337 (fax)