Thread: Reliability of Windows versions 8.3 or 8.4

Can anyone advise me if either PostgreSQL 8.3 or 8.4 is ready for
special case of production use?

I'm considering using the windows version PostgreSQL in the following
conditions:
at least 10 years of up time (with periodic power failures <= 1 a year)
single table with less-than 50 record inserts a day
reporting at most once a month by a single connection
no future patches applied
no DBA for routine maintenance
only CRON-like maintenance (windows scheduler)

I have a project to integrate a solution to log daily production
numbers for historical reporting.  The daily production numbers are
calculated values produced by a programmable logic controller (PLC).
These values will be persisted to a data stored (I'm proposing Pg) on
an industrial computer having a windows O/S.  Here is the problem that
concerns me,  because the PLC and industrial computer will be located
in a control panel in an industrial area no DBA will have access to
provide routine maintenance to the database.

Other than the occasional loss of power, does any one feel confident
that the windows version of postgres as it is today can run without
crashes for an up-time of at least 10 years?


I my case, I used the windows version of PostgreSQL daily for 4 years.
 I've only experienced one crash with version 8.4.0.  Since the
update, I've yet to see any additional crashes.


--
Regards,
Richard Broersma Jr.

On 5/12/2010 11:45 AM, Richard Broersma wrote:
> Can anyone advise me if either PostgreSQL 8.3 or 8.4 is ready for
> special case of production use?
>
> I'm considering using the windows version PostgreSQL in the following
> conditions:
> at least 10 years of up time (with periodic power failures<= 1 a year)
> single table with less-than 50 record inserts a day
> reporting at most once a month by a single connection
>
I question any database on the market that will guarantee such a thing.
Power in industrial plants is some of the dirtiest.  brown outs, spikes,
surges, harmonics, and the list keeps going.  That is not the best
environment for computers, even industrial ones.

Given how few records are being inserted a day a full database like
Postgresql is over kill.

I would do a plain text file something like XML.  Given this is for
industrial use 10 years is a good number for warranty and support, but
this stuff will hang around years later, think 20 to 30 years.  How
many people understand FLAT ISAM tables from the 1980's today, let alone
tools to read/modify the records.

I suggest storing the records in manner that is human readable



All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary quotation system. Quotations received via any other form of communication will not be honored. 

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other
informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it
addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.

On Wed, May 12, 2010 at 9:18 AM, Justin Graf <justin@magwerks.com> wrote:

> I would do a plain text file something like XML.  Given this is for
> industrial use 10 years is a good number for warranty and support, but
> this stuff will hang around years later, think 20 to 30 years.  How
> many people understand FLAT ISAM tables from the 1980's today, let alone
> tools to read/modify the records.
>
> I suggest storing the records in manner that is human readable

These are all good points.  There is one concern that I do have, this
information will be used to audit the billing system.  Is there any
concern for loss of data if a file rewrite is interrupted by a power
failure?   When using postgres there are some protections provided to
reduce this kind of data loss.

However, I do agree that tabular/xml data would stand the test of time.
--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On 5/12/2010 12:33 PM, Richard Broersma wrote:
> On Wed, May 12, 2010 at 9:18 AM, Justin Graf<justin@magwerks.com>  wrote:
>
>
>> I would do a plain text file something like XML.  Given this is for
>> industrial use 10 years is a good number for warranty and support, but
>> this stuff will hang around years later, think 20 to 30 years.  How
>> many people understand FLAT ISAM tables from the 1980's today, let alone
>> tools to read/modify the records.
>>
>> I suggest storing the records in manner that is human readable
>>
> These are all good points.  There is one concern that I do have, this
> information will be used to audit the billing system.  Is there any
> concern for loss of data if a file rewrite is interrupted by a power
> failure?   When using postgres there are some protections provided to
> reduce this kind of data loss.
>
> However, I do agree that tabular/xml data would stand the test of time.
>
Text files are a little hard to corrupt to the point nobody can read them.

Obviously if the system is in the middle of writing  the XML node  and
looses power the XML layout is toasted.
To limit data lose  create new XML file each day or month or what ever
time period makes sense.  If you end up with a screwed XML file kiss it
off or note it so a person can manually fix the entries and get most of
the data back.


50 records * 365 * 10 = 182500 records.  Not allot of data for 10 years
of collecting,  but very big for a single XML file


All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by
ourproprietary quotation system. Quotations received via any other form of communication will not be honored. 

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other
informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it
addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified
thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have
receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this
e-mailimmediately. 
Thank you.

I would suggest "sqlite" (specifically, version 3).  It is well tested to survive system crashes.  It is an embedded database engine (runs in same address space as your process, you use it just like any other C library).  If your program is not running, then neither is it and the database file(s) are not "open" on the disk.  It also works great with perl and can even be used from a CMD script (by spawning its shell, 'sqlite.exe').  I use sqlite in a variety of projects when Postgresql would be "overkill" and I've been vary happy with it.

http://www.sqlite.org/sqlite.html

On Wed, 2010-05-12 at 13:45 -0400, Rob Richardson wrote:
> I use both PostgreSQL and SQLite in my job.  I have mixed feelings
> about SQLite.  If you play by its rules, it works very well, but I
> think you have to understand its rules well.  If you do not set up
> your indexes correctly and do not use transactions correctly,
> performance can be horrible, but if you do, its performance is
> excellent.  For some reason I doubt I will ever understand, its
> developer thinks that it is a good thing not to require a column to
> hold any particular type of information.  One record can have an
> integer in a field, while the next record in the table can have a
> string in the same field.  I would much rather have my database
> enforce type consistency, and tell me when I'm screwing up.  There is
> no fixed date format.  I suddenly found that one program that used
> SQLite began writing dates as human-readable text strings for no
> reason I could understand, after it had been writing them as Julian
> dates (a floating-point number representing the number of days since a
> given date) happily for years.  I had to rewrite the corresponding
> program that reads the data to be able to handle either strings or
> Julian dates correctly and transparently, which was not easy.
>
> But it is nice to have SQLite available for use in programs that will
> be installed at multiple customer sites where we can't be sure if the
> main database will be PostgreSQL, SQL Server, Oracle, or something
> else.
>

I think it is also important to remember to use the right tool for each
job. SQLite is used for RPM as well as bookmarks for firefox. It makes
sense there. PostgreSQL would not.

SQLite is a small, cool, fast, embedded database.

Joshua D. Drake


> RobR
>


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Richard Broersma wrote on 12.05.2010 17:45:

> I'm considering using the windows version PostgreSQL in the following
> conditions:
> at least 10 years of up time (with periodic power failures<= 1 a year)

I don't think you can get 10 years of up time on a Windows Server.

Most of the security patches will need a reboot, and that means probably one reboot every month.

Thomas

On Wed, May 12, 2010 at 10:55 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Richard Broersma wrote on 12.05.2010 17:45:
>
>> I'm considering using the windows version PostgreSQL in the following
>> conditions:
>> at least 10 years of up time (with periodic power failures<= 1 a year)
>
> I don't think you can get 10 years of up time on a Windows Server.
>
> Most of the security patches will need a reboot, and that means probably one
> reboot every month.

:)  Hopefully this isn't the real reason for the catastrophe in the
Gulf of Mexico.

Actually my observation with several clients in the industry that I
work, I've noticed that systems like this never get patches applied.
If the system breaks or is known to be unreliable, then the whole
system is replaced.

To yank this thread in yet another direction:

The question of 8.3 vs 8.4 brings up a very irritating issue we have
with one client.  Originally, we had installed PostgreSQL 8.3 on his
system.  There is a program we occasionally have to use that requires us
to use "mstsc /console" to connect to his computer.  When we logged off
after connecting that way, PostgreSQL would crash.  The log file would
report error 128, which does not help us very much.

A search of the Internet found one person who had wrestled with the same
problem.  We were able to contact him.  He said that the problem went
away when he upgraded to 8.4.  So, we upgraded our customer to 8.4 (and
ourselves, while we were at it).  The problem still exists.

Has anyone else seen this problem?  Does anyone have any ideas on how to
avoid it?

Thank you very much!

RobR

On Wed, 2010-05-12 at 13:45 -0400, Rob Richardson wrote:
> I use both PostgreSQL and SQLite in my job.  I have mixed feelings
> about SQLite.  If you play by its rules, it works very well, but I
> think you have to understand its rules well.  If you do not set up
> your indexes correctly and do not use transactions correctly,
> performance can be horrible, but if you do, its performance is
> excellent.  For some reason I doubt I will ever understand, its
> developer thinks that it is a good thing not to require a column to
> hold any particular type of information.  One record can have an
> integer in a field, while the next record in the table can have a
> string in the same field.  I would much rather have my database
> enforce type consistency, and tell me when I'm screwing up.  There is
> no fixed date format.  I suddenly found that one program that used
> SQLite began writing dates as human-readable text strings for no
> reason I could understand, after it had been writing them as Julian
> dates (a floating-point number representing the number of days since a
> given date) happily for years.  I had to rewrite the corresponding
> program that reads the data to be able to handle either strings or
> Julian dates correctly and transparently, which was not easy.
>
> But it is nice to have SQLite available for use in programs that will
> be installed at multiple customer sites where we can't be sure if the
> main database will be PostgreSQL, SQL Server, Oracle, or something
> else.
>

I think it is also important to remember to use the right tool for each
job. SQLite is used for RPM as well as bookmarks for firefox. It makes
sense there. PostgreSQL would not.

SQLite is a small, cool, fast, embedded database.

Joshua D. Drake


> RobR
>


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering