Re: [ADMIN] Statistics Buffer is full
Pallav Kalva wrote: Hi Everybody, I got this logged in my log file last night on my production database while doing vacuumdb with analyze option , is this normal ? does it affect the system anyway ? is there anyway to get rid of these messages ? 2005-05-06 00:31:03 EDT%%% LOG: statistics buffer is full 2005-05-06 00:31:15 EDT%%% LOG: statistics buffer is full 2005-05-06 00:31:15 EDT%%% LOG: statistics buffer is full 2005-05-06 00:31:15 EDT%%% LOG: statistics buffer is full 2005-05-06 00:31:15 EDT%%% LOG: statistics buffer is full Thanks! Pallav I frequently see the same message in my logs when doing a bulk load (6-8 million rows). I'm interested in knowing what the message is as well. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] catastrophic error
Joel Fradkin wrote: Hi, I have been live for 4 days (vacuums run each night and backups done each night). Today around 2:30 PM EST my web app returned a catastrophic error. Both web servers appeared to have the issue. I could go on them and get data via pgadmin. I could log on the server (IIS servers are win2k and pg is Redhat AS4 running 8.0.2) and see it was not using much memory or cpu. Neither the web or database servers seemed stressed? Any ideas what I should look at? I re-booted the IIS servers and it did not fix the issue. I rebooted the database server and the web servers are back to connecting. Being new to postgres I am not sure what to look at for the cause and hopefully permanent fix to this. Thanks in advance to any ideas (I did search the archive, but only saw a mention of pre 8 versions and oid numbering wraparound). Joel Fradkin Any messages in syslog on db server? Any web error logs that you can check? -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] select * and save into a text file failed
Lee Wu wrote: > > How can I save PG data into text file without using pg_dump? > did you try select * from table \o '/var/tmp/textfile.txt' ; -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] sleep?
Don Drake wrote: Is there a sleep function in plpgsql? I need to wait a period time (60 seconds) in a while loop. I didn't see anything in the docs. Thanks. -Don create or replace function sleep(integer) returns void as $$ return sleep(shift) $$ language plperlu immutable strict; select sleep(5); --(sleeps for 5 seconds) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Moving pg_xlog problem
Joost Kraaijeveld wrote: > lrwxrwxrwx 1 root root pg_xlog -> /opt/pg_xlog Maybe this one here? Try chown'ing it to postgres:postgres and see what happens -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] upgrade database to 8.1 - 2GB file limit (pg_dump)
Tomeh, Husam wrote: I'm upgrading from 8.0 to 8.1 on RedHat 2.6 kernel. I'm hitting the file max limit of 2 GB file when I pg_dump my database (even if I compress the dump file as it gets generated using a pipe). pg_dump is the recommendation stated in the INSTALL doc to upgrade; what would be the next recommendation to upgrade the database? Can I use the -Fc option in pg_dump instead of the plain-text (default) ? Thanks in advance. --- Husam Tomeh How about using "split" to break up your file sizes? http://www.postgresql.org/docs/8.1/interactive/backup.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Backing up views, functions
Benjamin Arai wrote: I have a database of several million records and we are currently developing pl functions and views. How do you dump only the code for views and functions? Benjamin This has come up before, if you search the archives, you'll find various ways of accomplishing this. Here's a start (one way of getting function source) http://archives.postgresql.org/pgsql-general/2005-10/msg01633.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Backing up views, functions
Benjamin Arai wrote: I have a database of several million records and we are currently developing pl functions and views. How do you dump only the code for views and functions? Benjamin Also, one way of recreating views: select 'drop view '||viewname||'; CREATE OR REPLACE view '||viewname||' as '||definition||'\n' from pg_views where schemaname='public'; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] the time for [EMAIL PROTECTED] has come?
Guido Barosio wrote: All, (boring mail ahead) Writing here to get in contact with major people. Recently, with the new Windows port, and a great massive reception of the new release, I've noticed that too many threads are being sent to pgsql-admin regarding: "How do I install" "How do I backup" "What is initdb" etc. The point is that I am wondering about the need of a new mailing list, to isolate these cases, and give them a better support, before "upgrading" into a list like the admin one. A big distance between some threads and the ones that I mention already exists, and I wonder about a few orphan threads, without any kind of reply, because they are "silly" or the help was not properly requested. pgsql-starting seems to be a good way to isolate this threads and get a better idea of the common problems on getting started with postgresql. Recording / analyzing these threads could lead after into a better experience in the future, or the chance to understand who are these starters (profiles,background,etc). And I would kindly put some time there. My 5 cents (more than 2 ;) ) Best regards, g.- -- "Adopting the position that you are smarter than an automaticoptimization algorithm is generally a good way to achieve lessperformance, not more" - Tom Lane. Might be more useful to sift the various questions and answers and add them to the FAQ. More obvious pointers of where to go to find the FAQ might also help reduce the volume of those questions. ---(end of broadcast)--- TIP 1: 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
Re: [ADMIN] IsDate function in plpgsql
Sistemas C.M.P. wrote: A few days ago, someone in this list gives me a function (plpgsql) that evaluates if a string is a valid date or not. It returns 1 or 0 (valid/Not Valid) The problem is that if the argument is a NULL field the function returns 1. I don't have experience with plpgsql language. This is the function begin perform $1::date; return 1; exception when others then return 0; end create or replace function datetest (text) returns integer as $$ begin if ($1 is null) then return 0; end if; perform $1::date; return 1; exception when others then return 0; end; $$ language plpgsql; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Copying data from table to table (cloned tables)
Fourat Zouari wrote: Hello all, Any one could suggest the best way to copy data from table to table in the same db, the reason why am seeking for this is that the first table is becoming very big, and old data has no reason why to stay there, so i created a cloned table but without indexes and constraints (lighter) and now i would like to copy 'some' data from first to second table (copied data is data older than 3 months, i have a timestamp column). In other way, i have a table called 'hotqueues' where i store fresh messages queued, once messages are treated, they stay in 'hotqueues' but with a flag indicating that their arent queued for treatment.. so in this way, data will rest there forever, slowing down any searches in that table, the solution was to copy old messages to another table called 'coldqueues' that has the same structure as 'hotqueues' but lighter (without constraints and indexes). How to copy these data with 100% data-loose free. Thanks for any help you can provide. If you just want to copy the data across to the other table: begin; insert into table2 select * from table1 where ; commit; if you also want to remove that same data from table1: begin; insert into table2 select * from table1 where ; delete from table1 where ; commit; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Missing Earth Distance Functions Under Debian
Duncan McDonald wrote: Hi Tom, Thanks for the reply. No I didn't run the earthdistance.sql script on the backup database, is this included with the standard PostgreSQL package? If not, would you mind letting me know how/where to obtain it? I'm relatively new to PostgreSQL administration so I apologise in advance if this is an obvious question. Regards, -Duncan I think that it is in the postgresql-contrib-8.1 deb package available via aptitude (or whichever package manager you are using). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Simple Unload
Naomi Walker wrote: From time to time, I need to unload rows to a delimited file, specifically with a "where" clause. I've cobbled a script together to do this, but it seems like a reasonable utility to support. Sort of a pg_dump on steroids.. Have I missed the simple way to do this? Would someone consider adding such a utility or adding this to pg_dump? Naomi 8.2 COPY appears to be able to output in csv format, using a WHERE clause, though I've not tried it. http://www.postgresql.org/docs/8.2/static/sql-copy.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Dump and Query
Andy Shellam (Mailing Lists) wrote: Hi Enrico, The following command will get you a text file of your result-set: # echo "SELECT customer_id, first_name, sur_name FROM users;"|/usr/local/pgsql/bin/psql -U [username] -d [database] > myfile.txt # cat myfile.txt Alternative version: psql -d -c "SELECT customer_id, first_name, sur_name FROM users" -o myfile.txt ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Performances
Cedric BUSCHINI wrote: Hello everyone, A quick question: - how long does it take you to perform that query : "SELECT COUNT(*) FROM A_TABLE;" Not long :) dev5=# SELECT COUNT(*) FROM A_TABLE; ERROR: relation "a_table" does not exist ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] pg_dump inquiry
Karthikeyan Sundaram wrote: Hi, I have to dump only 10 tables out of 100 tables. In the pg_dump utility given by postgres there is an option called -t followed by table name. In that option, if I give more than 1 table, it's not accepting. How can I get the dump in one stroke for all the 10 tables? Please advise. Regards skarthi If you are using postgresql 8.2, you can specify multiple tables http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html Don't think that that was possible before 8.2, though. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] sequences
Alexander B. wrote: Hi, I tried to find, but I didn't, I would like to know what's the view to list all sequences! Thank you \ds or select * from pg_class where relkind = 'S'; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Who's attached to the database?
Carol Walter wrote: Thanks, guys, This told me that the user that has it open is the interface user called db_user. Is there a command to disconnect this user? Carol select pg_cancel_backend(); -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
