Connection marked as broken because of SQLSTATE(08003)

Problem Statement: Customer is restarting PostgreSQL database every night. Long running jobs are failing from after DB restart.

https://github.com/brettwooldridge/HikariCP/issues/198

Address this:
https://github.com/brettwooldridge/HikariCP/issues/1056
Just to save the trouble of scanning through the PostgreSQL driver release notes, the Connection.setNetworkTimeout() was released in version 42.2.0 of the driver (https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0)

Available properties
https://stackoverflow.com/questions/26490967/how-do-i-configure-hikaricp-in-my-spring-boot-app-in-my-application-properties-f/51079239#51079239

Check when database was restarted
https://yongitz.wordpress.com/2013/12/05/getting-postgresql-servers-start-time-and-uptime/
To get the start time, execute the query below:
psql -c “SELECT pg_postmaster_start_time();”

To get the uptime, execute the query below:
psql -c “SELECT now() – pg_postmaster_start_time();”

Exception Handling for guaranteed write:

boolean writeStatus = false;
while(!writeStatus)
{
try{
write to database;
writeStatus = true;
}catch (Exception ex)
{
print exception;
sleep for 30 seconds;
//Hope for db to recover.
}
}

Advertisements

PostgreSQL

Step 1: Download
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#macosx

Step 2: use pgadmin to connect to PostgreSQL
https://www.pgadmin.org/

Other UI Tools: https://postgresapp.com/documentation/gui-tools.html

Step 3: Oracle vs PostgreSQl data types.
http://www.sqlines.com/oracle-to-postgresql