Thread: 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

"Heather Johnson" <hjohnson@nypost.com> writes:
> I have been having trouble getting subselect queries to complete on a Sun E=
> -450 running Solaris 7 and PostgreSQL 7.1.3. Just about any subselect query=
>  I try to run will fail to complete execution---Postgres's serverlog indica=
> tes that the process associated with the query gets killed after a few minu=
> tes have gone by.

Could we see the exact text of the log entries?

Is it possible that you have some kernel limit on CPU time, memory
space, etc expended by any one backend process?

            regards, tom lane

"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

"Heather Johnson" <hjohnson@nypost.com> writes:
> Sure, this is what I get:

> 2001-12-11 09:40:24 DEBUG:  StartTransactionCommand
> 2001-12-11 09:40:24 DEBUG:  query: select count(*) from users where id not =
> in (select users_id from users_demographics)
> /opt/postgres/bin/postmaster: reaping dead processes...
> /opt/postgres/bin/postmaster: CleanupProc: pid 26260 exited with status 0

Strange.  That looks like a perfectly normal exit from the backend.
Could you attach to the backend with a debugger and set a breakpoint
at proc_exit, so we can see how it's getting there?

            regards, tom lane

I'm not sure how to go about doing that Tom. If it doesn't inconveniece you
too much, could you let me know how to go about it?

Heather

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Heather Johnson" <hjohnson@nypost.com>
Cc: <pgsql-admin@postgresql.org>; "Harry Ford" <hford@globix.com>; "Serge
Canizares" <serge@enluminaire.com>; "Marie Musacchio" <marie@nypost.com>
Sent: Tuesday, December 11, 2001 10:48 AM
Subject: Re: [ADMIN] problems doing sub-selects on PostgreSQL 7.1.3 and
Solaris 7


> "Heather Johnson" <hjohnson@nypost.com> writes:
> > Sure, this is what I get:
>
> > 2001-12-11 09:40:24 DEBUG:  StartTransactionCommand
> > 2001-12-11 09:40:24 DEBUG:  query: select count(*) from users where id
not =
> > in (select users_id from users_demographics)
> > /opt/postgres/bin/postmaster: reaping dead processes...
> > /opt/postgres/bin/postmaster: CleanupProc: pid 26260 exited with status
0
>
> Strange.  That looks like a perfectly normal exit from the backend.
> Could you attach to the backend with a debugger and set a breakpoint
> at proc_exit, so we can see how it's getting there?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

>     SELECT count(*) FROM users WHERE id NOT IN ( SELECT users_id FROM users_demographics );

I'm not sure about the difference in speed,  but try the following for a
much faster query:

SELECT count(id)
FROM users

EXCEPT

SELECT users_id
FROM  users_demographics

Should be a great deal faster.

- Brandon



----------------------------------------------------------------------------
 c: 646-456-5455                                            h: 201-798-4983
 b. palmer,  bpalmer@crimelabs.net           pgp:crimelabs.net/bpalmer.pgp5

> 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