Re: [GENERAL] File System Restore
Scot L. Harris wrote: > > I just recently upgraded from 7.2.4 to 7.4.2 postgresql. What I did was > a pg_dumpall to save my databases. Then after the upgrade I did a > pg_restore using the saved dump file. Worked perfectly for me. > > After I installed the new version of postgresql I did a initdb to setup > the new database cluster then a started postgresql. After that I ran a > psql -d template1 -f databasebackupfile. The databasebackupfile was > created from the pg_dumpall. This recreated all my tables and loaded > the data. > > Very easy and clean. > > Not sure you can just copy the file systems around like you describe. This has been a bit of a point of confusion for me. When do you use pg_restore vs. psql -f dumped.sql? Thanks Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] "alter sequence" equivilant in 7.2?
Shea Martin wrote:
I am moving my database from a machine running 7.4 to a machine running
7.2. My sequences are not out of wack, is there a way to set the
current value of a sequence? I though ALTER SEQUENCE would work but it
does not exist in version 7.2.
Thanks,
~S
I honestly did to google before posting the above. But couldn't find anything.
But I just found this, which did exactly what I wanted:
SOLUTION:
select setvalue('post_id_seq', 234134);
~S
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] pg_proc_proname_args_nsp_index error
I've got a standard setup on Debian woody, with postgreSQL backports from http://people.debian.org/~elphick Last night, I started getting mails from the cron daemon that this command: /usr/bin/test -x /usr/lib/postgresql/bin/do.maintenance && /usr/lib/postgresql/bin/do.maintenance -a -F was spitting out this errormessage: ERROR: could not read block 6 of relation "pg_proc_proname_args_nsp_index": Input/output error I'm not really sure what's going on here, and how serious this is. Anyone got idea what this is, and how it can be fixed? Alternatively, links/urls to somewhere online where I can read up on this? Tommy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Limit connections per user
I need to limit the connection number per each user. How can I do this? Stanislaw Tristan Kyiv, Ukraine E-mail: [EMAIL PROTECTED]
[GENERAL] doubt in trigger
dear groups, how to view the trigger definition (i.e create trigger ) which has already been created in the postgres. (iam working in linux terminal). yours sincerely, SathishKumar.S ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] "alter sequence" equivilant in 7.2?
I am moving my database from a machine running 7.4 to a machine running 7.2. My sequences are not out of wack, is there a way to set the current value of a sequence? I though ALTER SEQUENCE would work but it does not exist in version 7.2. Thanks, ~S ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Different runtime on the same query
The first thing I made was vacuum/analyze them. Then I deleted and rebuilt the indicies but the problem remained. So I created a new table (e_catalogo2) with new indicies, I copied all data from the old table to the new one, weirdly the problem was solved. In my opinion there is something wrong (sometimes) in building indicies in Postgres, but of course, I am not sure. I wish to update to the last stable version but I fear the passing will be painful, when I updated from the 7.0 to 7.2 I had to modify some data structure because not compliant with the new version. On 14/06/2004 20.58, Tom Lane <[EMAIL PROTECTED]> wrote: >NMB Webmaster <[EMAIL PROTECTED]> writes: >> I have two database quite similar, one has more tables than the >other. > >Are both of them vacuumed/analyzed recently? The slow plan seems >to be >estimating many more rows out of the e/m join than the other one. > >> My Postgres version is 7.2.3. Please help me , it is very important >to >> me to tune this query. > >I'd recommend an update, myself. 7.2.3 is old... > > regards, tom lane > >---(end of broadcast)--- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that >your > message can get through to the mailing list cleanly > > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Installation problem - mutex_lock/unlock or libpq.so ?
Installation of 7.4.2, even with just ./configure : gmake step ==> _ gmake[4]: Leaving directory `/usr/home/smith/Temp_holding/staging-area/postgresql/postgresql-7.4.2/src/backend/parser' gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations pg_dump.o common.o pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o pg_backup_files.o pg_backup_null.o pg_backup_tar.o dumputils.o ../../../src/backend/parser/keywords.o -L../../../src/interfaces/libpq -lpq -L../../../src/port -L/usr/local/openssl/lib -R/usr/local/pgsql/lib -lpam -lssl -lcrypto -lz -lreadline -lcrypt -lcompat -lm -lutil -lpgport -o pg_dump ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_mutex_unlock' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_mutex_lock' gmake[3]: *** [pg_dump] Error 1 gmake[3]: Leaving directory `/usr/home/smith/Temp_holding/staging-area/postgresql/postgresql-7.4.2/src/bin/pg_dump' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/usr/home/smith/Temp_holding/staging-area/postgresql/postgresql-7.4.2/src/bin' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/home/smith/Temp_holding/staging-area/postgresql/postgresql-7.4.2/src' gmake: *** [all] Error 2 _ Help needed Thanks -- Adam -- ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Relocation error pg_dumpall undefined symbol get_progname in 7.4.3
Regardi I recently compile Postgresql 7.4.3 with gcc 3.4.0 on RH 7.3 . Regression tests work well All passed, but Some binaries report a relocation error: :pg_dumpall undefined symbol get_progname . This error occures in most of the binaries. What is wrong ?
[GENERAL] Help please - completely baffled by quoting
Hi, I generally consider myself competent with the complexities of quoting but I can't figure out postgres at all. I've read the manual and it looks very straightforward but that doesn't accord with my experiences. Here's an example: create table temp (a varchar(50)); insert into temp values 'a\'b'; insert into temp values 'a\\b'; insert into temp values 'a\\\'b'; select * from temp returns a'b a\b a\'b so far so good but what about this: select * from temp where a like 'a\\\'%' this should return all strings starting with a\' but it returns nothing!!! I would expect that these two select * from temp where a like 'a\\\'b' select * from temp where a = 'a\\\'b' should return the same thing. The second one works as expected, but the first one doesn't return anything. I'm sure I'm missing something obvious. Thanks in advance for any help. --- Tim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Help please - completely baffled by quoting
Tim Robinson wrote: > select * from temp where a like 'a\\\'%' > > this should return all strings starting with a\' but it returns > nothing!!! \ is also the quoting character of LIKE, in addition to being a quoting character on the string literal level. If you want all strings starting with a\' then use something like: a LIKE 'a\\\'%' ESCAPE '!' where '!' selects some other escape character for LIKE that doesn't get in the way. The documentation on LIKE contains the details on this mechanism. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pg_proc_proname_args_nsp_index error
Tommy Gildseth <[EMAIL PROTECTED]> writes: > ERROR: could not read block 6 of relation > "pg_proc_proname_args_nsp_index": Input/output error > I'm not really sure what's going on here, and how serious this is. It's not good --- it means your disk has developed an unreadable block. You can get out of this particular problem by REINDEXing pg_proc, but the larger question is whether your disk is on the brink of worse failures. Might be time to buy a new one. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] File System Restore
"Tim Penhey" <[EMAIL PROTECTED]> writes: > This has been a bit of a point of confusion for me. > > When do you use pg_restore vs. psql -f dumped.sql? If you used the 'custom' or 'tar' formats of pg_dump, you need to use pg_restore. If you dumped as straight SQL (the default), just feed the dump to psql. -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Relocation error pg_dumpall undefined symbol get_progname in 7.4.3
"NTPT" <[EMAIL PROTECTED]> writes: > I recently compile Postgresql 7.4.3 with gcc 3.4.0 on RH 7.3 . Regression t= > ests work well All passed, but > Some binaries report a relocation error: :pg_dumpall undefined symbol get_p= > rogname .=20 > This error occures in most of the binaries. What is wrong ? They're linking to an older version of libpq.so. Check your ldconfig library search path. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Relocation error pg_dumpall undefined symbol get_progname
It means you have an old version of the PostgreSQL libraries in your library search path. --- NTPT wrote: > Regardi > > I recently compile Postgresql 7.4.3 with gcc 3.4.0 on RH 7.3 . Regression tests work > well All passed, but > > Some binaries report a relocation error: :pg_dumpall undefined symbol get_progname . > > This error occures in most of the binaries. What is wrong ? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Help please - completely baffled by quoting
> -Original Message- > From: Peter Eisentraut [mailto:[EMAIL PROTECTED] > Sent: 20 June 2004 14:07 > To: Tim Robinson; [EMAIL PROTECTED] > Subject: Re: [GENERAL] Help please - completely baffled by quoting > > > Tim Robinson wrote: > > select * from temp where a like 'a\\\'%' > > > > this should return all strings starting with a\' but it returns > > nothing!!! > > \ is also the quoting character of LIKE, in addition to being > a quoting > character on the string literal level. If you want all strings > starting with a\' then use something like: > > a LIKE 'a\\\'%' ESCAPE '!' > > where '!' selects some other escape character for LIKE that > doesn't get > in the way. The documentation on LIKE contains the details on this > mechanism. > or.. a LIKE 'a\'%' thanks, I get it now :) --- Tim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pivot table sql -- COUNT() not working as expected
hi. I am using a pseudo "pivot table" SQL query to generate a report but I am totally stumped on why the COUNT() function isn't getting me the expected results and was wondering if anyone has some ideas... it is for a statistical report showing which lead source a member followed when creating a profile (i.e. CNN ad, Chicago Tribune ad, etc), grouped by age range. here is the SQL that should total up rows based each specific condition, but doesn't: SELECT ms.display_name AS lead_source, EXTRACT(YEAR FROM AGE(pd.birth_date)) AS age_range, COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN p.profile_id ELSE 0 END) AS "Total 03/07/04", COUNT(CASE WHEN p.added_on BETWEEN '2004-03-14' AND '2004-03-21' THEN p.profile_id ELSE 0 END) AS "Total 03/14/04", COUNT(CASE WHEN p.added_on BETWEEN '2004-03-21' AND '2004-03-28' THEN p.profile_id ELSE 0 END) AS "Total 03/21/04", COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-06-27' THEN p.profile_id ELSE 0 END) AS "Total 03/07/2004 - 06/27/2004", COUNT(p.profile_id) as "Total " FROM ss_profile p, ss_profile_detail pd, ss_profile_multi_select s, ss_multi_select ms WHERE p.profile_id = pd.profile_id AND s.profile_id = p.profile_id AND s.multi_select_id = ms.multi_select_id AND ms.selection_type = 'how_did_you_hear' GROUP BY ms.display_name, age_range ; the output of the query is this: http://farmdev.com/test-report-w-count.txt (you will prob need to paste that into a fix-width font to see it properly) the numbers are all wrong... they are the same for each column for some reason across the board but I don't know why. As a workaround I created a column called "counter", which will always have the value "1" and did a SUM of that to mimic COUNT and it works fine! so what is the problem with COUNT? here is the workaround SQL: SELECT ms.display_name AS lead_source, EXTRACT(YEAR FROM AGE(pd.birth_date)) AS age_range, SUM(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN p.counter ELSE 0 END) AS "Total 03/07/04", SUM(CASE WHEN p.added_on BETWEEN '2004-03-14' AND '2004-03-21' THEN p.counter ELSE 0 END) AS "Total 03/14/04", SUM(CASE WHEN p.added_on BETWEEN '2004-03-21' AND '2004-03-28' THEN p.counter ELSE 0 END) AS "Total 03/21/04", SUM(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-06-27' THEN p.counter ELSE 0 END) AS "Total 03/07/2004 - 06/27/2004", SUM(p.counter) as "Total " FROM ss_profile p, ss_profile_detail pd, ss_profile_multi_select s, ss_multi_select ms WHERE p.profile_id = pd.profile_id AND s.profile_id = p.profile_id AND s.multi_select_id = ms.multi_select_id AND ms.selection_type = 'how_did_you_hear' GROUP BY ms.display_name, age_range ; and here is how that report looks, which shows the correct numbers: http://farmdev.com/test-report-w-sum.txt ... ok... the limited date range and limited lead sources isn't the best for example purposes but this should give an idea of what I'm trying to accomplish. also, to avoid confusion, "age range", actually does get compressed in the PHP script so it looks more like 18-21 ... number. thanks in advance, Kumar
Re: [GENERAL] pivot table sql -- COUNT() not working as expected
Ð ÐÑÐ, 20.06.2004, Ð 17:44, kumar mcmillan ÐÐÑÐÑ: > hi. > I am using a pseudo "pivot table" SQL query to generate a report butI > am totally stumped on why the COUNT() function isn't getting me > theexpected results and was wondering if anyone has some ideas... > > it is for a statistical report showing which lead source a > memberfollowed when creating a profile (i.e. CNN ad, Chicago Tribune > ad,etc), grouped by age range. > > here is the SQL that should total up rows based each > specificcondition, but doesn't: > > SELECT ms.display_name AS lead_source, EXTRACT(YEAR > FROMAGE(pd.birth_date)) AS age_range, > COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' > THENp.profile_id ELSE 0 END) AS "Total 03/07/04", You want COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN p.profile_id ELSE NULL END) AS "Total 03/07/04" or COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN 1 ELSE NULL END) AS "Total 03/07/04" or COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN 42 ELSE NULL END) AS "Total 03/07/04" or COUNT(CASE WHEN p.added_on BETWEEN '2004-03-07' AND '2004-03-14' THEN true ELSE NULL END) AS "Total 03/07/04" which is all the same. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Postgresql installation.
Hi, I am trying install the postgresql-7.4.3 simple installation. I did ./configure command at the postgresql directory source. While the configuring proccess I receiving the follow message: checking for tar... /bin/tarchecking for strip... stripchecking whether it is possible to strip libraries... yeschecking for bison... bison -y*** The installed version of Bison is too old. PostgreSQL needs*** Bison version 1.875 or later. checking for perl... /usr/bin/perlchecking for main in -lbsd... nochecking for setproctitle in -lutil... nochecking for main in -lm... yes But, after this message the install proccess continue like this message. The problem is that the installation never finish. I am thinking that the configure proccess is in loop. Have it anything relation with my hardware configuration? The computer where I did this is: AMD K6-II 200 MHZ; 64 MB memory; I would like why the configure proccess never finish. Regards, Janio
