Обсуждение: How to get database schema without pg_dump?

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

After lot of tries, it seems I resolved this problem.

First thing what I did is to hack pg_dump not to use SQL queries with
regular expressions. I tried LIKE clause, but it seems it is internally
interpreted as regular expression, so I replaced it with SUBSTR function.
This pg_dump was able to dump all databases with no problem.

Then I compiled PostgreSQL with multibyte support and created databases with
encoding LATIN1. After I filled the data back, everything looks fine. It is
working for several hours and I can't see any problems. Hopefully this is
the resolution for my problem.

Thank you Stephan for helping.

Regards,
Boban Acimovic
UNIX SysAdmin
www.mbl.is

"Boban Acimovic" <acim@mbl.is> writes:
> After lot of tries, it seems I resolved this problem.

So the characterization of the bug seems to be: regular expressions
crash if you build with locale support but not multibyte support?

Seems odd that we'd not have heard about that before.  Can you build it
that way again, adding --enable-debug this time, and provide a stack
trace from the point of the crash?

            regards, tom lane

Hi ,

I am a new comer to this list.My problem is related to installing of
postgres 7.1.3 to Slackware 8.0 Linux.  2.2.19 Kernel

I had not any problems in my first time install but I tried to install
postgres in to another machine having almost the same configuration.
But new installation step failed in shared library.(I installed postgres
may times)
   I know that I should tell the OS where to find shared libraries (either
with /etc/ld.so.conf or environmental variables LD_LIBRARY_PATH which is
not preferrable.)
    But when I try to run psql program it says that it cannot find shared
object files.. even if I tell the OS where it can find it..
    in archives of the list, most answers are related to LD_LIBRARY_PATH
but it is not a solution for me...
     Could any one suggest me a solution ?
any help will be appreciated..
many thanks in advance...





---------------------------
"Orada bir koy var uzakta"
http://www.ilkyar.org.tr

> So the characterization of the bug seems to be: regular expressions
> crash if you build with locale support but not multibyte support?
>
> Seems odd that we'd not have heard about that before.  Can you build it
> that way again, adding --enable-debug this time, and provide a stack
> trace from the point of the crash?

This is a bit strange. Before few days I recreated all databases again
using LATIN1 locale and all of them were OK after. I could use all \d
commands and pg_dump worked correctly. The day after one database showed
the some problem again and then one more day after, another database has
the same problem. There is still one database (the biggest one) working
completely OK. Just to mention, I am doing vacuum every night on all
databases before I take backups.

When I try \d commands or pg_dump, backend crash completelly.

So, I haven't resolved this problem and I am currently installing gdb
5.1 and I will recompile Pg with --enable-debug. After I have logs, I
will post them here if someone can help me what to do.

Just to repeat, this is Solaris 8 on Sparc and PosgtreSQL 7.1.3.

Regards,
Boban Acimovic
www.mbl.is

On Tue, 11 Dec 2001, Genco Yilmaz wrote:
...
> I am a new comer to this list.My problem is related to installing of
> postgres 7.1.3 to Slackware 8.0 Linux.  2.2.19 Kernel
...
>     But when I try to run psql program it says that it cannot find shared
> object files.. even if I tell the OS where it can find it..


Did you try running ldconfig? You need to run this as root after making
changes to /etc/ld.so.conf. If you add the '-v' option it will  tell you
what libraries it's using.

ldd may be usefull as well, it tells you what libraries a binary uses and
complains when it can't find one or more. That way you'll know what to
look for if indeed you're missing libraries!

For example:

$ ldd /usr/local/pgsql
    libpq.so.2 => /usr/local/pgsql/lib/libpq.so.2 (0x40017000)
...
    libc.so.6 => /lib/libc.so.6 (0x40259000)
    /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)


Good luck,

Francisco

There are some instances where I want to display a column I defined as type
money, without the $ sign and truncate the cents. Is there an easy way to do
this? Some kind of built-in function? I tried searching the mailing list
archives on the web site, but it was broken.
Thanks,
Rich