Обсуждение: 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.