What are the options for saving data locally upon a failed Postgres SQL connection

I'm building a C# application that uses a PostgresSQL database (version 9.3) in the cloud for storage. While my office connection is fiber and extremely stable to the db provider that might not be the case for my client.

I'm using Devart Entity Developer edition 5.5.164 and of course the dot connect PostgreSQL provider.

The question is if the connection to the database fails while the application is in use, what are the easy to implement options for saving the current status locally to the PC (if any) and secondly when the database comes back on line what synchronization options are available to ensure that the data on the local PC is written to the database successfully.

I have considered a master slave PostgreSQL type setup where a local machine is setup to serve up the DB and then replicate each change to the Cloud. But the client does not want to manage another system. So this is not an option.

So... any ideas?

Answers


I think a local sqlite database could be a solution.

  • by far superior to a self made file storage solution
  • no extra admin costs, it is one file on disk
  • library easily integratable into your program
  • SQL interface will lead to little code duplication

Then you could follow a strategy like this:

  • local commit first, usually very fast
  • then remote commit
  • if committed remove local data
  • on program startup store local data into cloud

and so on...

Edit: just checked, sqlite is supported by Devart Entity Developer :-)


While all the suggestions for solving this problem have been very well thought out, I've decided to serialize the data and save it upon a detected failed connection. This works in my case because there is only a few tables (six) that are modified by the system while the client is creating/updating records. I retry the connection 3 times before doing a final "save the data" operation and then lock down the system so that the end user is notified and can't make further changes until the data connection is stable. The process of serializing the data is very straight forward and is easily accomplished.

Here is a small sample for those of you interested:

 var backupfile = String.Format("ModifiedGridTotals_Backup_{0}.xml", DateTime.Now.ToString("d-MMM-yyyy-HHmmss"));
 var serializer = new XmlSerializer(typeof(List<GridTotal>));
 serializer.Serialize(new XmlTextWriter(backupfile, Encoding.UTF8), ModifiedGridTotals);

Upon database connection being restored, I read these files back from disk and submit the changes.


Need Your Help

Incorrect size in setMeasuredDimension() or incorrect size calculation?

java android android-layout

I need something like the Grid Layout, but with the own logic of the elements positioning. I expanded Frame Layout, and wrote the following logic:

Simple arithmetic expression doesn't compute in PL/pgSQL 9.3.5

postgresql plpgsql parentheses integer-division

I'm writing a user defined function and have had a lot of trouble doing simple arithmetic: