Penelope Data Return: Data Retrieval

Attention: Social Solutions is now Bonterra. Please bear with us as we update our screenshots to match our new name.

***IMPORTANT: This process will NOT work if you are running an Internet Explorer version below 11. Please see https://aws.amazon.com/premiumsupport/knowledge-center/browsers-management-console/ for an up-to-date list of supported browsers. Bonterra Support personally recommends using Google Chrome.***

Note for the above text: Internet Explorer 11 reached End of Life (EOL) in June 2022. We highly recommend switching to either the new Microsoft Edge or Google Chrome browsers as soon as you can.

If you are retrieving your data from Penelope to migrate to a new system, a backup file of your database (postgresql .gz format) and your attachments can be provided to you through Penelope itself. This is only available upon request and typically you will not see the option on the System Admin side until it has been provided.

System Administrator Access

If you do not have access to a System Admin account but do have access to a front end Worker account, that account may have the ability to either unlock an existing account or set up a new one. The following guides provide further information --

Add a Worker using a Super User Account

Managing Worker Accounts

• Login to Penelope as a System Administrator and navigate to the "File Attachment" Setup area.
  ​
  ​

  

• In the "File Transfer" box, click on “Go to Penelope Files”.
  ​
  ​

  

• This will take you to your S3 bucket where you can download the files.

• Click on "archives/"

• Click on "database/"

• Select the file and click "Actions" then "Download"

NOTE: once the support agent provides the link for your S3 bucket, you will have 14 days to download, after which time it will expire and need to be re-uploaded, which is a billable service

This folder will contain a .gz file, which is a copy of your database. The .gz file is compressed postgresql database file. It is not a zipped file (so do not attempt to unzip it) and is not a CSV file.

NOTE: If the .gz file is opened or otherwise unzipped, it will corrupt the file. See "Loading Your Database Backup" below for information on accessing the file.

Any changes made to the database copy may render it unusable by your IT team. Please note, that these files are hosted on an AWS console. As a result, the ability to download multiple files may not work due to limitations imposed directly by Amazon. Therefore, it is important to schedule a back up zip file containing all your data to be placed in the bucket at the moment you wish to download your files.

NOTE: If you cannot see the "Go to Penelope Files" button as pictured above - Please contact our support team immediately.

Attachments

Your attachments will be in a zip file ending in "_attachments.zip" in the "Go to File Transfer" link just above the "Go to Penelope Files" link that you will use to get to your database backup.

AWS obfuscates all attachment names by default, so to help identify which attachments are there will be a .csv file labelled "dtattachmod.csv". to assist with identifying which attachments are which. It is an extract of the dtattachmod table (where all attachment information for Penelope is stored) into a CSV format.

NOTE: dtattachmod.csv will be located in the _attachments.zip folder

NOTE: The dtattachmod table includes both existing/current attachments as well as historical (deleted) attachment records in the list.

A '1' for the kobjectstoragetransmitstatus column indicates it is a current attachment. These files will appear in the zip file.

A '4' or '6' indicate that it is a previously uploaded (and subsequently deleted) attachment. These files will NOT appear in the zip file as they had been deleted from Penelope.

The following image is an example of what the .csv file will look like --

Loading Your Database Backup

The database backup file will require that you have Postgres installed in order to work with it. We also recommend using PGAdmin, as it offers an easier to use user interface for Postgres.

https://www.pgadmin.org/

Postgres is an opensource database software. It can be downloaded here:

https://www.postgresql.org/download/

Postgres has a number of support materials found here:

https://www.postgresql.org/docs/current/

These materials will be linked with below information/steps.

Once you have installed Postgres you will need to configure it. Postgres has a number of support materials found here:

https://www.postgresql.org/docs/current/runtime.html

https://www.postgresql.org/docs/current/runtime-config.html

Now that you have the server configured and running, you can start on getting the database loaded into Postgres.

This is a multi-step process including:

1) creating a blank database instance (https://www.postgresql.org/docs/current/manage-ag-createdb.html)

2) restoring the database into the blank database instance (https://www.postgresql.org/docs/current/backup-dump.html#BACKUP-DUMP-RESTORE)

At this point you now have a running Postgres instance with a copy of your database loaded into it.

From here you can now work with the database. There are a number of tools that will allow you to query from and work with your database including psql shell and PGAdmin (both come with the Postgres server installation). You can also connect to the database via an ODBC driver connection (similar to how you may have done so before).

You may find the following articles useful if you wish to use an ODBC data connection.

Connecting to your Penelope database via ODBC

Creating an ODBC data connection

Please note that any references to contacting support for a database account would not apply. You would make use of the Postgres account you configured on your Postgres server (or any other account you setup on that Postgres server for this purpose).