Re: [GENERAL] Segmentation Fault
Hi All, A silly question how do I get install external symbols for postgresql, if compiled it myself previously? Do I recompile it with --enable-debug option? Cheers, Benson - Original Message - From: "Craig Ringer" To: "Benson Jin" Cc: pgsql-general@postgresql.org Sent: Monday, June 11, 2012 3:49:41 AM Subject: Re: [GENERAL] Segmentation Fault On 06/11/2012 11:34 AM, Benson Jin wrote: Hi All, We are having a problem with our streaming replication read only node. It has crashed a few times with a couple of different reasons, mostly "segmentation fault". Any chance of examining a core dump and getting a stack trace? http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD -- Craig Ringer
Re: [GENERAL] Segmentation Fault
I will try produce one and submit to here. - Original Message - From: "Craig Ringer" To: "Benson Jin" Cc: pgsql-general@postgresql.org Sent: Monday, June 11, 2012 3:49:41 AM Subject: Re: [GENERAL] Segmentation Fault On 06/11/2012 11:34 AM, Benson Jin wrote: Hi All, We are having a problem with our streaming replication read only node. It has crashed a few times with a couple of different reasons, mostly "segmentation fault". Any chance of examining a core dump and getting a stack trace? http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD -- Craig Ringer
[GENERAL] Segmentation Fault
Hi All, We are having a problem with our streaming replication read only node. It has crashed a few times with a couple of different reasons, mostly "segmentation fault". The latest log are listed below: 2012-05-30 23:56:37.385 UTC::: LOG: server process (PID 19476) was terminated by signal 11: Segmentation fault 2012-05-30 23:56:37.385 UTC::: LOG: terminating any other active server processes 2012-05-30 23:56:37.385 UTC:10.43.6.61:webmaster:panorama WARNING: terminating connection because of crash of another server process 2012-05-30 23:56:37.385 UTC:10.43.6.61:webmaster:panorama DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-30 23:56:37.385 UTC:10.43.6.61:webmaster:panorama HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama WARNING: terminating connection because of crash of another server process 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama WARNING: terminating connection because of crash of another server process 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2012-05-30 23:56:37.385 UTC:10.43.6.81:webmaster:panorama HINT: In a moment you should be able to reconnect to the database and repeat your command. 2012-05-30 23:56:37.575 UTC:10.43.6.81:webmaster:panorama FATAL: the database system is in recovery mode Our setup: 2x physical server - Dell PE R815, 64GB ECC RAM, 2 CPUs (12 cores each), storing pgsql data on SAN backed volumes. CentOS 5.6 PostgreSQL 9.0.8, compiled *without* int64 datetime. Both servers are identically configured (or at least as much as we could ensure) One is master, another is streaming read-only node. The master runs two instances of postgreSQL, where the slave runs 5 instances of postgreSQL. 2 out of 5 are streaming replication from the master, rest 3 are streaming replication from other DB nodes. Those 2 instances serves clients as Read Only. The master node never had any crash so far. However, the 2 instances on slave have crashed 3 times by now, 1 time on one readonly instance, twice on another readonly instance. Above log was generated from one of the instances. All three crashes happened when the database was doing vacuuming. we automatically purge some data every night, and run vacuum analyze right after that... Our the CPU load is generally on 40%-60% mark. I have run a complete set of hardware diagnostics on the slave , with no faulty hardware detected. Can someone kindly shed some lights on me? I am not sure where to look into at this point Cheers, Bo Jin Operating/IT Manager Troo Corporation [ www.troo.com ] 43 Auriga Drive, Suite 102, Ottawa, ON K2E 7Y8 Ph: +1 877.702.8766 x156 Fax: +1 855.726.8766
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
Hi Josh, Thanks for your prompt reply! Yes, you were right about the parameter. We have upgraded a few times in the past. It was not so painful, as our data as rather small. As our business grows, it becomes harder and harder to do any upgrade that requires downtime. There is never a good time to do this type of changes. However, a better time would be earlier rather than later, as our data size is growing steadily. The early we do it, the less downtime we will have to bear. I guess my question is how to do the change without downtime or with the least downtime? Is there a recommended steps we should take? Also, is there an installer for Windows 64bit with --disable-integer-datetimes available? Cheers, Benson - Original Message - From: "Josh Kupershmidt" To: "Benson Jin" Cc: pgsql-general@postgresql.org Sent: Wednesday, June 6, 2012 8:01:35 PM Subject: Re: [GENERAL] Need help in transferring FP to Int64 DateTime On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin wrote: > I am sure this question has been asked before, however, I failed to find any > related topics in the internet. We have a database about 100GB in size. It > was started back in 7.x days and has been upgraded along the way to 9.0. > Because of the historical reason, all timestamps are stored in > FloatingPoint. To use pg9, we had to compile it with FP support instead of > the default Int64. I take it you had to compile 9.0 with --disable-integer-datetimes because you wanted to use pg_upgrade to perform one of your database upgrades, yes? Otherwise you would have been able to just dump-and-restore into a 9.0 database with integer timestamps. At any rate, you must have performed a dump-and-restore at some point since your "7.x days", since pg_upgrade can handle databases only back to 8.3. > Some recent research shows that PG team will eventually > dump FP support in favor of int64 in future, we figured we need to make the > move to Int64 before the database gets even larger. FWIW, I don't think there's any urgent push to get rid of float timestamps as a compile-time option, even though [1] claims the option is "deprecated". Float timestamps were the default through 8.3, which isn't quite ancient history yet, and it seems likely there are many users in the same boat who would be upset about not being able to use pg_upgrade if we removed that option. Josh [1] http://www.postgresql.org/docs/current/static/datatype-datetime.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
Hi Adrian, Thanks for the suggestion! We thought about that too, but concerned about the possible performance penalty trigger based replication would bring. If there is no other alternative, we will give your suggestion a try... Cheers, Benson - Original Message - From: "Adrian Klaver" To: "Benson Jin" Cc: pgsql-general@postgresql.org Sent: Wednesday, June 6, 2012 10:02:02 PM Subject: Re: [GENERAL] Need help in transferring FP to Int64 DateTime On 06/06/2012 09:20 AM, Benson Jin wrote: > Hi All, > > I am sure this question has been asked before, however, I failed to find > any related topics in the internet. We have a database about 100GB in > size. It was started back in 7.x days and has been upgraded along the > way to 9.0. Because of the historical reason, all timestamps are stored > in FloatingPoint. To use pg9, we had to compile it with FP support > instead of the default Int64. Some recent research shows that PG team > will eventually dump FP support in favor of int64 in future, we figured > we need to make the move to Int64 before the database gets even larger. > The challenge we now face is how to "convert" the database fast enough, > so that required down time is minimized. Searching through the internet > for 2 days yields little fruit so far... Can someone please provide > enlighten us? A thought. Dependent on sufficient disk space. Use one of the trigger based(non-binary) replication tools i.e. Slony, Bucardo, etc. Compile a Postgres 9.0 instance using the default of integer timestamps. Set up replication PG 9.0 FP --> PG 9.0 integer Once the standby is close to the primary, shut off access to the primary and let the standby completely catch up. Shut down the primary and promote the standby as the new primary. I have not actually done this, which is why I propose it as a thought. Hopefully, others will weigh in on the advisability of the above. > > Cheers, > > > Bo Jin > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help in transferring FP to Int64 DateTime
Hi All, I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. The challenge we now face is how to "convert" the database fast enough, so that required down time is minimized. Searching through the internet for 2 days yields little fruit so far... Can someone please provide enlighten us? Cheers, Bo Jin Operating/IT Manager Troo Corporation [ www.troo.com ] 43 Auriga Drive, Suite 102, Ottawa, ON K2E 7Y8 Ph: +1 877.702.8766 x156 Fax: +1 855.726.8766
