Recovery Database Failed, how do I fix this?

[wjsnyder]

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

[JuanS_OCS]

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

[wjsnyder]

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

[sathya66]

is this resolved?
I am getting the below error as databese backup failed.


2021-10-19 16:05:49,030 [main] INFO com.sas.svcs.backup.server.handler.PostgresBackupHandler - Exit code : 0
2021-10-19 16:05:49,030 [main] INFO com.sas.svcs.backup.server.handler.PostgresBackupHandler - The backup successfully completed!!!
2021-10-19 16:05:49,032 [main] ERROR com.sas.svcs.backup.server.BackupServerSession - An exception while running the command: One of the database failed to backup see Database.log for details
2021-10-19 16:05:49,033 [main] INFO com.sas.svcs.backup.server.BackupServerSession - Calling post processor : com.sas.svcs.backup.server.handler.PostgresBackupHandler
2021-10-19 16:05:49,050 [main] INFO com.sas.svcs.backup.server.BackupServerSession - Found session result properties : 40
2021-10-19 16:05:49,072 [main] ERROR com.sas.svcs.backup.server.BackupDriver - An exception while running the BackupServer executable, Exiting..... : One of the database failed to backup see Database.log for details
Error Code: 310, Stack Trace: One of the database failed to backup see Database.log for details
at com.sas.svcs.backup.server.handler.PostgresBackupHandler.execute(PostgresBackupHandler.java:512)
at com.sas.svcs.backup.server.handler.PostgresBackupHandler.execute(PostgresBackupHandler.java:45)
at com.sas.svcs.backup.server.BackupServerSession.execute(BackupServerSession.java:118)
at com.sas.svcs.backup.server.BackupDriver.main(BackupDriver.java:131)
2021-10-19 16:05:49,073 [main] ERROR com.sas.svcs.backup.server.BackupDriver - [Ljava.lang.StackTraceElement;@ef9296d
2021-10-19 16:05:49,073 [main] INFO com.sas.svcs.backup.server.BackupDriver - Exiting with status code: 310

[gwootton]

Have you checked the log for the Web Infrastructure Platform Data Server to see if there are any corresponding errors there?

[michele_sas]

Job Monitoring Database Server uses 9452.  This is one of the data servers that the Backup and Recovery Tool documentation refers to as "solution data servers".    

Step 4. Stop all servers and services except the SAS Metadata Server, SAS Web Infrastructure Platform Data Server (all instances, including all solution data servers, as well), and SAS Deployment Agent (all instances).

SAS Help Center: Performing a Recovery Using the Deployment Backup and Recovery Tool

So make sure all * Data Server and * Database Server instances are started. 

[sathya66]

Backup is success after all * Data Server and * Database Server instances are up and running(restarted these servcies )
Thanks for your help