Backup and Recovery for PostgreSQL Databases

[#1]

Database backups are vital when it comes to ensure data integrity and data safety. PostgreSQL offers simple solutions to generate backups of the existing ThingWorx database instance and recover them when needed.

Please note that this does not replace a proper and well-defined disaster recovery plan.

Export and Import are part of this strategy, but are not reflecting the complete strategy.

The commands used in this post are for Windows, but can be adjusted to work on Linux-based systems as well.

Backup

To create a Backup, the export / dump functionality of PostgreSQL can be used.

pg_dump -U postgres -C thingworx > thingworxDump.sql

The -C option will include the statement to create the database in the .sql file and map it to the existing tablespace and user (e.g. 'thingworx' and 'twadmin'). The tablespace and user can be seen in the .sql file in the line with "ALTER DATABASE <dbname> OWNER TO <user>;"

In the above example, we're backing up the thingworx database schema and dump it into the thingworxDump.sql file

Tablespace, username & password are also included in the platform_settings.json

Restore

To restore the database, we just assume an empty PostgreSQL installation. We need to create the DB schema user first via the following commandline:

psql -U postgres -c "CREATE USER twadmin WITH PASSWORD 'ts';"

With the user created, we can now re-generate the tablespace and grant permissions to the twadmin user:

psql -U postgres -c "CREATE TABLESPACE thingworx OWNER twadmin LOCATION 'C:\ThingWorx\ThingworxPostgresqlStorage';"
psql -U postgres -c "GRANT ALL PRIVILEGES ON TABLESPACE thingworx TO twadmin;"

Finally the database itself can be restored by using the following commandline:

psql -U postgres < thingworxDump.sql

This will create the database and populate it with tables, functions and sequences and will also restore the data from the .sql file.

It is important to have database, username and tablespace match with the original system - otherwise granting permissions and re-creating data might fail on recovery. User and tablespace can also be reused, so only the database has to be deleted before restoring it.

Part of the strategy

Part of the backup and recovery strategy should be to actually test the backup as well as the recovery part of the procedure. It should be well tested on a test-environment before deployed to any production environments. Take backups on a regular basis and test for disaster recovery once or twice a year, to ensure the procedure is still valid.

Data is the most important source in your application - protect it!