Re: [GENERAL] Question regarding DEALLOCATE pdo_stmt_00000001
On Wed, May 21, 2014 at 7:50 PM, David G Johnston david.g.johns...@gmail.com wrote: Laurentius Purba wrote Hello all, I've been seeing lots of this processes in my database DEALLOCATE pdo_stmt_0001 with idle state. Executing *select * from pg_stat_activity where query like 'DEALLOCATE%';*I can see some of them are idle for +/- 30 minutes, and there is one idle for 4 days. When I checked one of the pid by executing *select * from pg_stat_activity where pid = 63882;* (for example), after few minutes (can be longer), it became *DISCARD ALL* and after that it was removed from pg_stat_activity. I was wondering if this is the normal behavior, since some of them are idle for +/- 30 minutes. I am using PostgreSQL 9.3.2 and PgBouncer. Any help is appreciated. Regards, -Laurentius Yes. 9.3 - On an idle connection the value of query is the last executed query - which in this case is some form session cleanup command before returning the connection to the pool. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-regarding-DEALLOCATE-pdo-stmt-0001-tp5804667p5804672.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Thanks David. So, it is a normal behavior in Postgres. One more thing that bothers me, why this idle connection can be stayed idle for 3 days. Is this a zombie process? -Laurentius
[GENERAL] Question regarding DEALLOCATE pdo_stmt_00000001
Hello all, I've been seeing lots of this processes in my database DEALLOCATE pdo_stmt_0001 with idle state. Executing *select * from pg_stat_activity where query like 'DEALLOCATE%';*I can see some of them are idle for +/- 30 minutes, and there is one idle for 4 days. When I checked one of the pid by executing *select * from pg_stat_activity where pid = 63882;* (for example), after few minutes (can be longer), it became *DISCARD ALL* and after that it was removed from pg_stat_activity. I was wondering if this is the normal behavior, since some of them are idle for +/- 30 minutes. I am using PostgreSQL 9.3.2 and PgBouncer. Any help is appreciated. Regards, -Laurentius
[GENERAL] Database compare tools
Hello all, I was wondering if anyone of you know any database compare tools. I've been using PgComparer, and it has been working great until we upgraded Postgres from 9.0 to 9.3, and PgComparer stops to work. Any help is appreciated. Thanks! -Laurent
Re: [GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?
Hi Michael, Sorry about that. Anyway, my colleague just sent out a similar question to this mailing list right after I sent mine. The subject title is pg_upgrade tablespaces. If you can help us from my colleague's email, I really appreciate. Thanks! -Laurent On Fri, Dec 20, 2013 at 1:05 AM, Michael Paquier michael.paqu...@gmail.comwrote: On Thu, Dec 19, 2013 at 10:07 PM, Laurentius Purba lpu...@sproutloud.com wrote: Hi Greg, I just wanted to know if you were able successfully upgrading from 9.0 to 9.3. I have been doing this upgrading this past week, but always ended up with unsuccessful upgrade. It will be great if you can share you knowledge on this. It would be even better if you use this thread to report the problems you are seeing, such as someone could help you going through this upgrade process. Regards, -- Michael
Re: [GENERAL] Is it advisable to pg_upgrade directly from 9.0 to 9.3?
Hi Greg, I just wanted to know if you were able successfully upgrading from 9.0 to 9.3. I have been doing this upgrading this past week, but always ended up with unsuccessful upgrade. It will be great if you can share you knowledge on this. Thanks! -Laurent On Thu, Nov 7, 2013 at 2:07 PM, Greg Burek g...@heroku.com wrote: On Wed, Nov 6, 2013 at 4:36 AM, Leonardo Carneiro chesterma...@gmail.comwrote: I don't think that there will be too much trouble, as long as you follow every changelog tip (9.0-9.1, 9.1-9.2 and 9.2-9.3) What if we don't follow the changelog tip? In this case, we have only the 9.0 and 9.3 binaries installed and pg_upgrade directly to 9.3. Does that cause fear?
Re: [GENERAL] Clone database using rsync?
Just want to give you some idea for your reference. If you are using zfs, you will be gaining lots of advantages. A simple clone and send command from zfs will help you big time. Check it out: http://docs.oracle.com/cd/E18752_01/html/819-5461/docinfo.html On Tue, Nov 5, 2013 at 6:11 PM, sparikh spar...@ecotality.com wrote: I need to clone production database to development server ? What is the best and simplest way to achieve that? Both my production and development postgres versions are same. Will copy over data folder using rsync work? Thanks in advance. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Clone-database-using-rsync-tp5777141.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - *Laurentius Purba* - SproutLoud Media Networks, LLC. - lpu...@sproutloud.com - www.sproutloud.com - • - [image: Facebook] http://www.facebook.com/SproutLoud [image: Twitter]http://twitter.com/sproutloud [image: Google+] http://plus.google.com/100508252462681981365 [image: LinkedIn] http://www.linkedin.com/company/440456?trk=tyah [image: YouTube] http://www.youtube.com/user/sproutcms [image: Pinterest]http://pinterest.com/sproutloud/ [image: Blog RSS] http://blog.sproutloud.com/ [image: SproutLoud Newsletter] http://market.sproutloud.com/Newsletter-signup.html -
Re: [GENERAL] Postgresql 9.0.13 core dump
Kevin, Thanks for the response. I will look into it based on your suggestion. -Laurent On Mon, Oct 14, 2013 at 8:29 PM, Kevin Grittner kgri...@ymail.com wrote: Laurentius Purba lpu...@sproutloud.com wrote: I did google this error message, ...signal 10: Bus error.. and found the issue with hardware problem, memory. Do you have any other pointers or clues that I can look into? Well, the very first thing I would do is to make sure that the OS and jail software was up-to-date, in case it is a bug which has been fixed. I might take a look at BIOS and firmware levels, too. Tomas makes a good point about the XML library tending to be problematic, so I would make sure that was current. If I still had the problem after that, I would try to distil it down to the smallest reproducible test case, and see whether it ran OK outside the jail. Another approach would be to run hardware tests, although that tends to require a longer maintenance window than the other things. Basically, there are a lot of layers the problem could be in, and not a lot of reason to suspect any particular layer, so you need to start ruling things out. In a situation like that I tend to start with the fastest, easiest layers to check first. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [GENERAL] Postgresql 9.0.13 core dump
Hi Tomas, Thanks for your response. Regarding using BYTEA instead of TEXT for binary content, I did a google search prior sending my first email. Also, in my first email, I mentioned that I am not convinced this query, updating a field with pdf content in a table, causing this core dump. The reason is, out of few crashes, 3 or 4 crashes, this is the only PID that it's tied to that update query. The other crashes have their own PIDs that were not tied to any query(ies). Regarding libxml, I am using libxml2-2.8.0_2 XML parser library for GNOME. Is this reproducible? Unfortunately it is not. Regarding an update PDF, I am not sure if this update causing core dump, as I mentioned in my second paragraph. Anyway, thanks for your response, Tomas. -Laurent On Mon, Oct 14, 2013 at 4:37 PM, Tomas Vondra t...@fuzzy.cz wrote: On 14.10.2013 22:18, Laurentius Purba wrote: Hello all, I am having core dump on Postgres 9.0.13 with the message ...was terminated by signal 10: Bus error So, I set a PID on the log file to capture specific PID that causing this crash. After, several crashes, I finally got the PID that causing this crash. But I am still not convinced that this process that causing this crash. That is the reason why I sent out this email, hoping anybody can help me or have had this experience before. Based on the PID on the log file, the crash happened while the application was trying to update a table's field with binary (PDF) content. The datatype of this field is TEXT. Hi Laurentius, wouldn't it be better to use BYTEA columns for binary content, not TEXT? I'm not sure if that's a problem with PDF, but generally TEXT does not allow some octet values (e.g. '\0'). The backtrace you've posted however lists a bunch of libxml functions at the top, and in my experience libxml is not the best coded piece of software. So I'd guess the problem is somewhere within libxml. What version of libxml are you using? Signal 10 usually means hardware error (but if the other jails are running fine, it's unlikely) or about the same as SEGFAULT (i.e. accessing invalid memory etc.). What I don't understand is why the call ended in libxml when you're dealing with PDF? Is this reproducible? Does that happen with a particular PDF, or with random PDF documents? Can you prepare a small self-contained test case? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 9.0.13 core dump
Hello all, I am having core dump on Postgres 9.0.13 with the message ...was terminated by signal 10: Bus error So, I set a PID on the log file to capture specific PID that causing this crash. After, several crashes, I finally got the PID that causing this crash. But I am still not convinced that this process that causing this crash. That is the reason why I sent out this email, hoping anybody can help me or have had this experience before. Based on the PID on the log file, the crash happened while the application was trying to update a table's field with binary (PDF) content. The datatype of this field is TEXT. I am using FreeBSD 9.1 host that has 3 database jails. One of them is this database that causing core dump. Other databases are working fine. Below is the information about the core dump. Any help is appreciated. Thanks! -Laurent [pgsql@MY-BOX ~]$ gdb postgres data/postgres.core GNU gdb 6.1.1 [FreeBSD] Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as amd64-marcel-freebsd...(no debugging symbols found)... Core was generated by `postgres'. Program terminated with signal 10, Bus error. Reading symbols from /usr/local/lib/libintl.so.9...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libintl.so.9 Reading symbols from /usr/local/lib/libxml2.so.5...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libxml2.so.5 Reading symbols from /usr/lib/libssl.so.6...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libssl.so.6 Reading symbols from /lib/libcrypto.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libcrypto.so.6 Reading symbols from /lib/libm.so.5...(no debugging symbols found)...done. Loaded symbols for /lib/libm.so.5 Reading symbols from /lib/libc.so.7...(no debugging symbols found)...done. Loaded symbols for /lib/libc.so.7 Reading symbols from /usr/local/lib/libiconv.so.3...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libiconv.so.3 Reading symbols from /lib/libz.so.6...(no debugging symbols found)...done. Loaded symbols for /lib/libz.so.6 Reading symbols from /usr/lib/liblzma.so.5...(no debugging symbols found)...done. Loaded symbols for /usr/lib/liblzma.so.5 Reading symbols from /usr/local/lib/nss_ldap.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/nss_ldap.so.1 Reading symbols from /usr/local/lib/libldap-2.4.so.8...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/libldap-2.4.so.8 Reading symbols from /usr/local/lib/liblber-2.4.so.8...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/liblber-2.4.so.8 Reading symbols from /usr/lib/libkrb5.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libkrb5.so.10 Reading symbols from /usr/lib/libcom_err.so.5...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libcom_err.so.5 Reading symbols from /usr/lib/libgssapi_krb5.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libgssapi_krb5.so.10 Reading symbols from /usr/lib/libasn1.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libasn1.so.10 Reading symbols from /lib/libcrypt.so.5...(no debugging symbols found)...done. Loaded symbols for /lib/libcrypt.so.5 Reading symbols from /usr/lib/libhx509.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libhx509.so.10 Reading symbols from /usr/lib/libroken.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libroken.so.10 Reading symbols from /usr/lib/libgssapi.so.10...(no debugging symbols found)...done. Loaded symbols for /usr/lib/libgssapi.so.10 Reading symbols from /usr/local/lib/postgresql/plpgsql.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/plpgsql.so Reading symbols from /usr/local/lib/postgresql/dict_snowball.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/dict_snowball.so Reading symbols from /usr/local/lib/postgresql/plperl.so...(no debugging symbols found)...done. Loaded symbols for /usr/local/lib/postgresql/plperl.so Reading symbols from /usr/local/lib/perl5/5.14.2/mach/CORE/libperl.so...(no debugging symbols found)...done. [29/1378] Loaded symbols for /usr/local/lib/perl5/5.14.2/mach/CORE/libperl.so Reading symbols from /lib/libthr.so.3...(no debugging symbols found)...done. Loaded symbols for /lib/libthr.so.3 Reading symbols from /lib/libutil.so.9...(no debugging symbols found)...done. Loaded symbols for /lib/libutil.so.9 Reading symbols from /libexec/ld-elf.so.1...(no debugging symbols found)...done. Loaded symbols for
Re: [GENERAL] Postgresql 9.0.13 core dump
Hi Kevin, Thanks for your response. I did google this error message, ...signal 10: Bus error.. and found the issue with hardware problem, memory. Do you have any other pointers or clues that I can look into? -Laurent On Mon, Oct 14, 2013 at 4:33 PM, Kevin Grittner kgri...@ymail.com wrote: Laurentius Purba lpu...@sproutloud.com wrote: I am having core dump on Postgres 9.0.13 with the message ...was terminated by signal 10: Bus error Every time I have seen this it has been a bug in VM or jail code; although a hardware problem could also cause it. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
