Architecting, installing and maintaining your SAS environment

Recovery Database Failed, how do I fix this?

Reply
New Contributor
Posts: 4

Recovery Database Failed, how do I fix this?

I am a new Admin for SAS.

I am working with SAS 9.4 on a Linux back end.

I am trying to "validate" our recovery process to one, be able to have it documented for my admin group and two to be able to apply hotfixes and have the ability to "rollback" if the hotfixes don't install correctly.

 

When attempting to recover from a backup, when I the process gets to the database I get the following error.

 

sourceType: database
status: failed
serverSideReturnCode: 312
hosts
hostName: localhost
status: failed
size: 520260364
logFilePath: /opt/sasinside/sas/Lev1/Backup/Logs/2017-03-15T09_07_50/recover/Database
backupLocation: /opt/sasinside/sas/Lev1/Backup/Vault/2017-03-15T08_39_53/db

 

When I look into the Database Logs I get:

 

2017-03-15 09:09:19,367 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: [archiver (db)] Error while PROCESSING TOC:
2017-03-15 09:09:19,367 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public dbmsowner
2017-03-15 09:09:19,367 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema public because other objects depend on it

2017-03-15 09:09:19,368 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
2017-03-15 09:09:19,368 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - Command was: CREATE SCHEMA public;

2017-03-15 09:09:24,884 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - jmdbadmin /opt/sasinside/sashome/SASWebInfrastructurePlatformDataServer/9.4/bin /opt/sasinside/sas/Lev1/Backup/Vault/2017-03-15T08_39_53/db/105363afdaade2f159e8f9731c6951467910af93 null /opt/sasinside/sashome/SASWebInfrastructurePlatformDataServer/9.4/lib
2017-03-15 09:09:24,886 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: connecting to database for restore
2017-03-15 09:09:24,886 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: [archiver (db)] connection to database "postgres" failed: could not connect to server: Connection refused
2017-03-15 09:09:24,886 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - Is the server running on host "localhost"  and accepting
2017-03-15 09:09:24,886 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - TCP/IP connections on port 9452?
2017-03-15 09:09:24,887 [main] INFO com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - pg_restore: *** aborted because of error
2017-03-15 09:09:24,888 [main] ERROR com.sas.svcs.backup.server.handler.PostgresRecoveryHandler - The recovery encountered exception(s): Error Code: 313
2017-03-15 09:09:24,888 [main] ERROR com.sas.svcs.backup.server.BackupServerSession - An exception while running the command: Error Code: 313
2017-03-15 09:09:24,888 [main] INFO com.sas.svcs.backup.server.BackupServerSession - Calling post processor : com.sas.svcs.backup.server.handler.PostgresRecoveryHandler
2017-03-15 09:09:24,901 [main] INFO com.sas.svcs.backup.server.BackupServerSession - Found session result properties : 27
2017-03-15 09:09:24,916 [main] ERROR com.sas.svcs.backup.server.BackupDriver - An exception while running the BackupServer executable, Exiting..... : Error Code: 313
Error Code: 312, Stack Trace: com.sas.svcs.backup.server.handler.PostgresRecoveryHandler.recover(PostgresRecoveryHandler.java:204) com.sas.svcs.backup.server.handler.PostgresRecoveryHandler.execute(PostgresRecoveryHandler.java:394)... Nested BackupException : Error code - 313
at com.sas.svcs.backup.server.handler.PostgresRecoveryHandler.recover(PostgresRecoveryHandler.java:215)
at com.sas.svcs.backup.server.handler.PostgresRecoveryHandler.execute(PostgresRecoveryHandler.java:394)
at com.sas.svcs.backup.server.handler.PostgresRecoveryHandler.execute(PostgresRecoveryHandler.java:35)
at com.sas.svcs.backup.server.BackupServerSession.execute(BackupServerSession.java:107)
at com.sas.svcs.backup.server.BackupDriver.main(BackupDriver.java:131)
Caused by: Error Code: 313
at com.sas.svcs.backup.server.handler.PostgresRecoveryHandler.recover(PostgresRecoveryHandler.java:204)
... 4 more
2017-03-15 09:09:24,916 [main] ERROR com.sas.svcs.backup.server.BackupDriver - [Ljava.lang.StackTraceElement;@6e67a769
2017-03-15 09:09:24,916 [main] INFO com.sas.svcs.backup.server.BackupDriver - Exiting with status code: 312

 

I have gotten this error before (Is the server running on host "my localhost"  and accepting TCP/IP connections on port 9452?) and have tried to fix it by checking to see what runs on it normally before I go offline. I found it to be my postgres server and on my next attempt to do a recovery used the   /opt/sasinside/sas/Lev1/DataRemediationDataServer/dmrmdtdsvrc.sh start command to start the postgres server. The only other logs that I find that show something is wrong is located at /opt/sasinside/sas/Lev1/WebInfrastructurePlatformDataServer/Logs saying that an unexpected EOF on client connection followed by sending cancel to blocking autovacuum.  I think this might be the smoking gun, but I can't find this issue in documentation and don't have enough knowledge of the system to know where to go to fix this.

 

Any help would be appreciated.

 

Thanks,

 

Wayne

 

Trusted Advisor
Posts: 1,312

Re: Recovery Database Failed, how do I fix this?

Hello @wjsnyder,

 

this is a great question. And it is a known issue within PostreSQL DBAs.

Your main problem is this kind of message:

could not execute query: ERROR:  schema "public" already exists

 

My question to you would be: what maintance of SAS and OS are you running?  Do you have installed all the latest hotfixes?


Here is the thing: whilst this question can be answered and solved by PSQL dbas, I would strongly advise you to align the solution with SAS Technical Support, to ensure that is supported and probably you will get additional useful information and maybe even a hotfix (they will ask you for the DeploymentRegistry.xml of your SAS platform).

 

This link can explain to you the original problem, the solutions, considerations about the version of PSQL and drawbacks:

 

http://dba.stackexchange.com/questions/90258/pg-restore-archiver-db-could-not-execute-query-error-sc...

 

I hope this can help you a bit, Wayne.

 

Juan

New Contributor
Posts: 4

Re: Recovery Database Failed, how do I fix this?

Posted in reply to JuanS_OCS

Juan,

 

Thank you for for your help.

 

To answer your questions.

 

What maintenance of SAS and OS are you running? 

SAS 9.4_M3 on RHEL's 6 (Linux x64)

Have you installed all the latest hotfixes?

Coincidently I am trying to perform a restore in order to assure my group that we have a safety net. Just in case we install a hotfix and it breaks, either by applying something that we don't have or us not performing the post/pre-installation instructions correctly, our system.

 

Ultimately you are saying that I should escalate this to SAS Tech Support?

 

Thanks again,

 

Wayne

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 230 views
  • 1 like
  • 2 in conversation