Thread
-
How to get database schema without pg_dump?
Boban Acimovic <acim@mbl.is> — 2001-12-07T23:22:01Z
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?
Boban Acimovic <acim@mbl.is> — 2001-12-10T18:11:33Z
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
Heather Johnson <hjohnson@nypost.com> — 2001-12-10T21:56:48Z
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
bpalmer <bpalmer@crimelabs.net> — 2001-12-10T22:34:00Z
> 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: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7
Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-10T22:34:11Z
"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?
Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-11T00:24:20Z
"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
Genco Yilmaz <gyilmaz@ilkyar.org> — 2001-12-11T10:27:11Z
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
Heather Johnson <hjohnson@nypost.com> — 2001-12-11T14:45:45Z
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
Heather Johnson <hjohnson@nypost.com> — 2001-12-11T14:47:41Z
Thanks Brandon---that's a very good idea. I'd still like to know why my x86 box can handle the less effecient query better than an E-450, but this is what I'll do until I can get my curiosity satisfied. ;) Heather ----- Original Message ----- From: "bpalmer" <bpalmer@crimelabs.net> To: "Heather Johnson" <hjohnson@nypost.com> Cc: <pgsql-admin@postgresql.org> Sent: Monday, December 10, 2001 5:34 PM Subject: Re: [ADMIN] problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7 > > 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: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7
Tom Lane <tgl@sss.pgh.pa.us> — 2001-12-11T15:48:57Z
"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
Heather Johnson <hjohnson@nypost.com> — 2001-12-11T17:22:19Z
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: How to get database schema without pg_dump?
Boban Acimovic <acim@mbl.is> — 2001-12-12T10:13:43Z
> 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
fcanedo@soneramail.nl — 2001-12-12T17:55:54Z
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
-
Convert money type to int
Rich Ryan <rich@usedcars.com> — 2001-12-13T03:36:30Z
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