Restoring a back-up

MSmith

Hi All

This link shows a great backup script for OC written by Montri.

https://docs.openclinica.com/3.1/technical-documents/system-administration/backup-and-restore-openclinica#content-title-5156

 

I’ve tested it and it works a treat. However there’s no restoration explanation. I’ve tried using PGadmin to try and restore the .db file the backup creates without success.
Please can anyone advise on how to do the restoration.

Thanks in advance.

Maisy

GerbenRienk

Hi Maisy,

I did not succeed with PGAdmin when restoring linux backups to a windows machine, so I do it all from the prompt:

cd c:\program files\postgresql\8.4\bin\ 
psql -U postgres openclinica32 < c:\pg_backups\pg_dump_openclinica_20140407

 It is also described on http://www.trialdatasolutions.com/tds/howto/backuprestore.jsp

Kind regards,

Gerben Rienk

Syntax

Hi Maisy,

Sorry that I did not cover how to restore in the script I wrote (as it's just for create the backup), btw you can restore the database by following steps (note that you can adjust it according to your need):

Syntax

Ahh, seems my previous post via my e-mail screw up, let try again directly on forum and hope it works this time.

- Stop "Tomcat service" on the server<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

- Save “OC database dump file (.db)” from recent OC backup (or from the backup that you need to restore) into “C:\TEMP” on the server

- Open “pgAdmin III” by click Start -> All Programs -> PostgreSQL 8.4 -> pgAdmin III

- Connect to “PostgreSQL 8.4 server” by right click at “PostgreSQL 8.4 (localhost:5432)” icon, and click “Connect”

- Enter [postgres password], and click OK

- Delete “openclinica” database by click “+” in front of “Databases(2)” icon, right click “openclinica” icon, and click “Delete/Drop”

- Click “Yes” on the “Drop Database?” confirmation dialog

- Open “SQL Query” by click at “postgres” icon, and click “SQL Query” icon on the toolbar

- Clear all text inside the “SQL Editor”

- Enter SQL command “CREATE DATABASE  openclinica WITH ENCODING='UTF8' OWNER=clinica;” (type without “”), and press F5 to execute the query *change the database name 'openclinica' to your designate database name in case you did not use openclinica for database name

- Close “SQL Editor”, and click “No” when confirmation dialog appeared

- Open “Command Prompt” by click Start -> All Programs -> Accessories -> Command Prompt

- Change current path to “C:\Program Files (x86)\PostgreSQL\8.4\bin”

- Type the following command “psql -U postgres -d openclinica < c:\temp\[OC database dump file name]” (type without “” and [], parameters are case sensitive)

Example:

psql -U postgres -d openclinica < c:\temp\openclinica-2012-02-29-Wed--01-49.db

- Press Enter

- Enter [postgres password], and press Enter

- Wait until database restore is completed, it will take a while

- Close “Command Prompt”

- Switch back to “pgAdmin III”, refresh database list by right click at “Databases(1)” icon, and click “Refresh”

- You will see “openclinica” database

*or the database name you have specified, in case you did not use openclinica for the database name

- click “+” in front of “openclinica” icon, click “+” in front of “Schemas(1)” icon, and click “+” in front of “public” icon

- Verify the number after “Tables” icon, you should see more than 100 tables (the exactly number of tables may differ in different OC version)

- Close “pgAdmin III”

- Start “Tomcat service” on the server

Hope it helps ^^

Best regards,

Montri

Syntax

Note

- This is for Windows only

- Normally I will drop the database and recreate the blank database before perform database restore (to make it's clean as possible)

- Tomcat service needs to be stopped before restore the database to make sure there is no connection made to the database during restore operations

- Please test the steps in non-production environment to make sure everything is working fine before do it in production environment