How to get database schema without pg_dump? - Mailing list pgsql-general

From Boban Acimovic
Subject How to get database schema without pg_dump?
Date
Msg-id 003201c17f75$fa05f480$8b00000a@acim
Whole thread Raw
List pgsql-general
I have serious problem to take backup of some databases. I suspect that
something is wrong with locale, because all queries including regular
expressions fails, including \d commands and pg_dump and pg_dumpall.

I initialized database several monts ago with locale is_IS.ISO8859-1.
Postgres is compiles just with --enable-locale.
My configuration is Solaris 8 and PostgreSQL 7.1.3. The frontend is Apache
and Mason, using DBD::Pg 1.01. All software is the latest release except
Apache which is in version 1.3.19.

Several databases in the begining were created by user postgres which has
correct environment, but some users created databases with iso-8859-1 locale
latter. Now, I can some databases, but few of them fails crashing the
backend. I tried to compile another daemon without locale, but it didn't
start because database is initialized with locale. I also tried to change
environment of postgres user to use C locale, but it didn't help, the same
error appears again. Finally, I tried Perl module DBIx::DBSchema, but it
failed also. COPY command works correctly, at least with few tables that I
tried, but problem is how to get database schema out. We can maybe
reconstruct some databases, but it would take lot of time and effort.

getIndices(): SELECT failed.  Explanation from backend: 'pqReadData() --
backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.

DEBUG:  query: SELECT i.oid, t1.oid as indoid, t1.relname as indexrelname,
t2.relname as indrelname, i.indproc, i.
indkey, i.indclass, a.amname as indamname, i.indisunique, i.indisprimary
from pg_index i, pg_class t1, pg_class t2
, pg_am a WHERE t1.oid = i.indexrelid and t2.oid = i.indrelid and t1.relam =
a.oid and i.indexrelid > '18539'::oid
 and t2.relname !~ '^pg_'

I tried to specify just single table dump, but it failed again because there
are still some queries using regular expressions. Is there any other way to
get database schema out? In that case I just have to make script to COPY all
tables to files. Please help because we (my colleague and me) had difficult
time to convince our management to try PostgreSQL while using Informix as
standard database in our company. Everyone was very happy with PostgreSQL
until this problem arrived. We are thinking to switch completely to
PostgreSQL in one year. Is there any hope to resolve this? Thank you in
advance.

Best regards,
Boban Acimovic
www.mbl.is


pgsql-general by date:

Previous
From: Marc Munro
Date:
Subject: Virtual Private Database
Next
From: Brook Milligan
Date:
Subject: papers on datatype design