Thread: How to get database schema without pg_dump?

How to get database schema without pg_dump?

From
"Boban Acimovic"
Date:
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


Re: [GENERAL] How to get database schema without pg_dump?

From
"Boban Acimovic"
Date:
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


problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

From
"Heather Johnson"
Date:
Hello--
 
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 indicates that the process associated with the query gets killed after a few minutes have gone by. I've tried running subselects from an interface to Postgres which uses a socket connection, and I've tried running them directly from the command line, always with the same result. The E-450 has two 400 MHz processors and 1.5 GB of RAM. Here's an example of the kind of query I've tried:
 
    SELECT count(*) FROM users WHERE id NOT IN ( SELECT users_id FROM users_demographics );
 
What's weird is that I can successfully run the same queries on a Solaris x86 box with a single 233MHz Pentium chip, and 96 MB of RAM, also running PostgreSQL 7.1.3 but with Solaris 8. The database contains about 650,000 records in the users table, and only slightly less than that in the users_demographics table. I dumped the database from the E-450 and re-imported it into the x86 box to be sure that I was working with the same volume of data. The x86 box completes the query in about 10 minutes, but the E-450 can't complete the query at all.
 
The resources of the E-450 don't seem to be overly taxed by postgres (see results of top over a 15-20 minutes period included below), and yet the comparative performance of the x86 box makes it sound like there is some kind of system problem responsible. Is anyone aware of any problems with doing subselects in PostgreSQL with Solaris 7? Might there be other system-related issues or Postgres-related issues responsible for this? I would appreciate any suggestions you might have!
 
Thank you!
Heather Johnson
 
 
load averages:  0.31,  0.11,  0.10
49 processes:  47 sleeping, 2 on cpu
CPU states: 25.5% idle, 25.2% user,  4.0% kernel, 45.2% iowait,  0.0% swap
Memory: 1536M real, 24M free, 1987M swap in use, 693M swap free
 
  PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
24283 postgres   1  21    0   14M 6768K cpu/1    0:37 23.01% postgres
24352 postgres   1   0    0 2168K 1432K cpu/3    0:00  0.78% top
 4846 nobody     1  60    4 1782M 1006M sleep  380:20  0.21% impress
12047 nobody     1  54    0  148M  145M sleep    0:04  0.08% httpd
  239 root       1  58    0    0K    0K sleep   14:54  0.01% sysedge.sol27-s
  258 root       6  58    0   14M 3192K sleep   91:41  0.00% mysqld
    1 root       1  58    0  752K  152K sleep    9:00  0.00% init
  201 root      15  59    0 3376K  808K sleep    8:08  0.00% syslogd
28712 postgres   1  58    0   13M  664K sleep    0:30  0.00% postgres
  210 root       1  58    0 1784K  400K sleep    0:21  0.00% cron
  192 root       5  58    0 3408K  840K sleep    0:17  0.00% automountd
  221 root       1  58    0 1016K  240K sleep    0:08  0.00% utmpd
 3220 root       1  58    0 2528K  448K sleep    0:05  0.00% sshd
  233 root       1  59  -12 2080K  456K sleep    0:05  0.00% xntpd
  225 root       1  58    0 2376K  504K sleep    0:04  0.00% sendmail
 
 
load averages:  0.41,  0.23,  0.15
49 processes:  47 sleeping, 2 on cpu
CPU states:  1.7% idle, 49.3% user,  2.0% kernel, 47.1% iowait,  0.0% swap
Memory: 1536M real, 22M free, 1987M swap in use, 693M swap free
 
  PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
24283 postgres   1   0    0   14M 6728K cpu/1    1:59 32.91% postgres
24358 postgres   1   0    0 2168K 1424K cpu/3    0:02  1.00% top
 4846 nobody     1  60    4 1782M 1005M sleep  380:21  0.18% impress
24337 root       1  58    0 2488K 1688K sleep    0:00  0.02% sendmail
  258 root       6  58    0   14M 3824K sleep   91:41  0.00% mysqld
  239 root       1  58    0    0K    0K sleep   14:54  0.00% sysedge.sol27-s
    1 root       1  58    0  752K  144K sleep    9:00  0.00% init
  201 root      15  59    0 3376K  832K sleep    8:08  0.00% syslogd
28712 postgres   1  58    0   13M  720K sleep    0:30  0.00% postgres
  210 root       1  58    0 1784K  400K sleep    0:21  0.00% cron
  192 root       5  58    0 3408K  816K sleep    0:17  0.00% automountd
  221 root       1  58    0 1016K  264K sleep    0:08  0.00% utmpd
 3220 root       1  58    0 2528K  448K sleep    0:05  0.00% sshd
  233 root       1  59  -12 2080K  488K sleep    0:05  0.00% xntpd
12047 nobody     1  58    0  148M  145M sleep    0:04  0.00% httpd
 
 
load averages:  0.47,  0.26,  0.16
49 processes:  47 sleeping, 2 on cpu
CPU states: 20.1% idle, 24.1% user,  4.0% kernel, 51.8% iowait,  0.0% swap
Memory: 1536M real, 23M free, 1987M swap in use, 693M swap free
 
  PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
24283 postgres   1  30    0   14M 6568K cpu/3    2:17 32.27% postgres
24358 postgres   1   0    0 2168K 1424K cpu/1    0:02  0.99% top
 4846 nobody     1  60    4 1782M 1004M sleep  380:21  0.20% impress
  239 root       1  58    0    0K    0K sleep   14:54  0.01% sysedge.sol27-s
24337 root       1  58    0 2488K 1688K sleep    0:00  0.00% sendmail
  258 root       6  58    0   14M 3816K sleep   91:41  0.00% mysqld
    1 root       1  58    0  752K  136K sleep    9:00  0.00% init
  201 root      15  59    0 3376K  832K sleep    8:08  0.00% syslogd
28712 postgres   1  58    0   13M  696K sleep    0:30  0.00% postgres
  210 root       1  58    0 1784K  400K sleep    0:21  0.00% cron
  192 root       5  58    0 3408K  840K sleep    0:17  0.00% automountd
  221 root       1  58    0 1016K  264K sleep    0:08  0.00% utmpd
 3220 root       1  58    0 2528K  448K sleep    0:05  0.00% sshd
  233 root       1  59  -12 2080K  480K sleep    0:05  0.00% xntpd
12047 nobody     1  58    0  148M  145M sleep    0:04  0.00% httpd
 
load averages:  0.79,  0.48,  0.28
56 processes:  52 sleeping, 1 running, 1 zombie, 2 on cpu
CPU states: 18.7% idle, 23.5% user, 17.5% kernel, 40.3% iowait,  0.0% swap
Memory: 1536M real, 24M free, 1990M swap in use, 690M swap free
 
  PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
24283 postgres   1  20    0   14M 6408K cpu/1    4:24 17.16% postgres
  258 root       7  58    0   14M 5472K sleep   91:48  1.35% mysqld
24358 postgres   1   0    0 2168K 1424K cpu/3    0:07  1.04% top
24383 nobody     1  34    0 6664K 4400K sleep    0:01  0.55% press.cgi
24525 nobody     1   0    0 2648K 1912K run      0:00  0.20% ssh
 4846 nobody     1  60    4 1782M 1012M sleep  380:22  0.15% impress
24337 root       1  58    0 2512K 1704K sleep    0:00  0.06% sendmail
24386 nobody     1  46    0 1088K  808K sleep    0:00  0.03% push.sh
24524 nobody     1  46    0 1760K 1120K sleep    0:00  0.02% rsync
  201 root      15  59    0 3376K  880K sleep    8:08  0.02% syslogd
  221 root       1  58    0 1016K  264K sleep    0:08  0.01% utmpd
13305 nobody     1  58    0  148M  145M sleep    0:02  0.01% httpd
  239 root       1  58    0    0K    0K sleep   14:54  0.01% sysedge.sol27-s
12048 nobody     1  58    0  148M  145M sleep    0:04  0.01% httpd
24385 nobody     1  24    0 1040K  744K sleep    0:00  0.01% reload.sh
 
load averages:  0.55,  0.60,  0.45
49 processes:  47 sleeping, 2 on cpu
CPU states: 15.4% idle, 24.4% user,  4.6% kernel, 55.6% iowait,  0.0% swap
Memory: 1536M real, 23M free, 1987M swap in use, 693M swap free
 
  PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
24283 postgres   1  10    0   14M 6136K cpu/1    8:45 17.21% postgres
24358 postgres   1   0    0 2168K 1424K cpu/3    0:19  0.96% top
 4846 nobody     1  60    4 1782M 1011M sleep  380:24  0.21% impress
  239 root       1  58    0    0K    0K sleep   14:54  0.02% sysedge.sol27-s
24271 root       1  58    0 2560K  872K sleep    0:00  0.01% sshd
  258 root       6  58    0   14M 5352K sleep   91:48  0.00% mysqld
    1 root       1  58    0  752K  152K sleep    9:00  0.00% init
  201 root      15  59    0 3376K  880K sleep    8:08  0.00% syslogd
28712 postgres   1  58    0   13M  720K sleep    0:30  0.00% postgres
  210 root       1  58    0 1784K  400K sleep    0:21  0.00% cron
  192 root       5  58    0 3408K  840K sleep    0:17  0.00% automountd
  221 root       1  58    0 1016K  264K sleep    0:08  0.00% utmpd
 3220 root       1  58    0 2528K  440K sleep    0:05  0.00% sshd
  233 root       1  59  -12 2080K  472K sleep    0:05  0.00% xntpd
12053 nobody     1  58    0  148M  145M sleep    0:04  0.00% httpd
 
 
 

Re: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

From
Tom Lane
Date:
"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

Re: [GENERAL] How to get database schema without pg_dump?

From
Tom Lane
Date:
"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

shared library

From
"Genco Yilmaz"
Date:
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



Re: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

From
"Heather Johnson"
Date:
Hi Tom--
 
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
 
The E-450 box is managed by someone other than myself, but I've asked the person primarily responsible for managing it about quotas and other externally imposed resource limits. He doesn't think that there are any.
 
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: Monday, December 10, 2001 5:34 PM
Subject: Re: [ADMIN] problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

> "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

Re: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

From
Tom Lane
Date:
"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

Re: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

From
"Heather Johnson"
Date:
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


Re: problems doing sub-selects on PostgreSQL 7.1.3 and

From
bpalmer
Date:
>     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


Re: How to get database schema without pg_dump?

From
Boban Acimovic
Date:
> 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


Re: [GENERAL] shared library

From
Date:

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