Thread: Re: [HACKERS] Problems with >2GB tables on Linux 2.0

I am still not able to pg_dump my data after recovering from this disaster.
My files are now segmented at 1GB, vacuuming is fine, but pg_dump has a
problem "locating the template1 database".

I sure hope someone can help me come up with a way to safely backup this
data. Someone sent me a patch for the Linux Kernel that will allow it to
handle files > 2GB, but that won't help me with my backup problems.

Thanks,

Tim Perdue
geocrawler.com




-----Original Message-----
From: Tatsuo Ishii <t-ishii@sra.co.jp>
To: Peter Mount <petermount@it.maidstone.gov.uk>
Cc: t-ishii@sra.co.jp <t-ishii@sra.co.jp>; Tom Lane <tgl@sss.pgh.pa.us>;
pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>
Date: Tuesday, March 16, 1999 10:45 PM
Subject: Re: [HACKERS] Problems with >2GB tables on Linux 2.0


>>Just a question. Does your patch let vacuum handle segmented tables?
>>--
>>Tatsuo Ishii
>
>>It simply reduces the size of each segment from 2Gb to 1Gb. The problem
>>was that some OS's (Linux in my case) don't like files exactly 2Gb in
>>size. I don't know how vacuum interacts with the storage manager, but in
>>theory it should be transparent.
>
>Ok. So we still have following problem:
>
>test=> vacuum smallcat;
>NOTICE:  Can't truncate multi-segments relation smallcat
>VACUUM
>
>Maybe this should be added to TODO if it's not already there.
>--
>Tatsuo Ishii

Thus spake Tim Perdue
> I am still not able to pg_dump my data after recovering from this disaster.
> My files are now segmented at 1GB, vacuuming is fine, but pg_dump has a
> problem "locating the template1 database".

I recall once creating a shell script that dumped a table.  I can't
remember why I didn't use pg_dump but the shell script was pretty simple.
If you can read the data, you should be able to make this work.  Worst
case you may have to handcraft each table dump but beyond that it should
work pretty good.

Here's how I would do it in Python.  Requires the PostgreSQL module
found at http://www.druid.net/pygresql/.


#! /usr/bin/env python
from pg import DB
db = DB()  # opens default database on local machine - adjust as needed

for t in db.query("SELECT * FROM mytable").dictresult():   print """INSERT INTO mytable (id, desc, val)       VALUES
(%(id),'%(desc)', %(val));""" % t
 

Then feed that back into a new database.  I assume you have the schema
backed up somewhere.  You may have to get fancy with formatting special
characters and stuff.  If you can still read the schema you may be able
to automate this more.  It would depend on the amount of tables in your
database and the complexity.  See the get_tables() and get_attnames()
methods in PyGreSQL.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.