Home > Edge > Manufacturing Edge > Guides > Dell Validated Design for Manufacturing Edge with PTC - Design Guide > Configuration tasks
To deploy a VM environment using the created clone, perform the following steps:
Each database type has its own method to back up and restore data. Choose the appropriate method based on the type of database used to store Thingworx persistent database.
Dell Technologies recommends taking a new full backup at set intervals to establish a new differential base for the data. For example, you might take a weekly full backup of the whole database (that is, a full database backup), followed by a regular series of differential database backups during the week. Restoring a differential backup will significantly increase the time required to restore a database if the size becomes large.
To back up a full database, perform the following steps:
BACKUP DATABASE [thingworx]
TO DISK = N'F:\backups\thingworx-248-full-05-06-1.bak'
WITH NOFORMAT
, NOINIT
, NAME = N'thingworx248-Full Database Backup'
, SKIP
, NOREWIND, NOUNLOAD, STATS = 10
GO
ThingWorx persistent database is highly compressible. In our testing, we found that a backup of 50 GB compresses to only 6 GB, which is 88% of space saving. Backup and restore times are also less for a compressed backup, as less data is written to storage. Below is an example of script for creating a compressed backup.
To back up a database with compression, perform the following steps:
BACKUP DATABASE [thingworx]
TO DISK = N'F:\backups\thingworx-248-full-comp-05-09-2.bak' WITH RETAINDAYS = 5
, NOFORMAT, NOINIT
, NAME = N'thingworx-Full Database Backup compressed'
, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
A differential backup captures only the data that has changed since the latest full backup.
Creating a differential backup is much faster than creating a full backup. A differential backup records only the data that has changed since the full backup upon which the differential backup is based.
The following is an example of a script to create a differential backup.
BACKUP DATABASE [thingworx]
TO DISK = N'F:\backups\thingworx-248-diff-05-09-1.bak' WITH DIFFERENTIAL
, NOFORMAT, NOINIT
, NAME = N'thingworx-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
If a database uses either the full or bulk-logged recovery model, back up the transaction log regularly enough to protect your data, and to prevent the transaction log from filling.
The following is an example of a script to create a transaction log backup.
BACKUP LOG [thingworx]
TO DISK = N'E:\backups\thingworx-248-transct-05-09-3.bak'
WITH NOFORMAT, NOINIT, NAME = N'thingworx-transct-log Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
USE [master]
RESTORE DATABASE [thingworx]
FROM DISK = N'F:\backups\thingworx-248-full-05-06-1.bak'
WITH FILE = 1,
MOVE N'thingworx' TO N'D:\data\thingworx-1.mdf',
MOVE N'ThingWorx_log' TO N'E:\logs\thingworx_log-1.ldf',
NOUNLOAD, STATS = 5
GO
There are three fundamentally different approaches to backing up PostgreSQL data:
SQL dump method generates a file with SQL commands that, when fed back to the server, will recreate the database in the same state as the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. The output file format can be custom, directory, tar, or plain text (default). The output file can be compressed for space efficiency. pg_dump is a regular PostgreSQL client application, which means that you can perform this backup procedure from any remote host that has access to the database. All you need to do is specify the hostname and username information for the database on which the backup is to be performed. The same applies to a pg_restore command.
See the examples below for backup and restore commands.
# /usr/bin/pg_dump thingworx -F t --file "/backup/postgres/thingworx-04May-15-47.tar" --host "172.19.111.246" --username "twadmin" --verbose
# pg_dump thingworx --username "twadmin" | gzip > thingworx-04May-17-28.gz
Regular unattended backups can be taken by running cron job on Linux installation of postgres.
To create a backup using cron job, perform the following steps.
$ crontab -e
0 0 * * * pg_dump thingworx > /backup/postgres/cron1.bak 2>> ~/backup/logs/cron1.bak.log
The restore process using pg_restore does not create the database, so an empty database needs be created before restoring. Before restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions.
Other important things to do before restoring a database are:
psql -h 192.168.153.245 -U postgres -c "UPDATE pg_database SET datallowconn=false WHERE datname='thingworx';"
psql -h 192.168.153.245 -U postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'thingworx AND pid <> pg_backend_pid();"
psql -h 192.168.153.245 -U postgres -c "DROP DATABASE thingworx;"
/opt/ThingWorxFoundation/schema/thingworxPostgresDBSetup.sh
to create the database for ThingWorx. ON_ERROR_STOP
variable set to alter that behavior and have psql exit with an exit status of 3 if an SQL error occurs. Restore from plain text compressed backup:
# /usr/bin/pg_restore -d thingworx "/backup/postgres/thingworx-04May-17-28.gz" --username "twadmin"
Restore from plain text tar backup:
# /usr/bin/pg_restore -d thingworx "/backup/postgres/thingworx245-03-16-1218.tar" --username "twadmin"
pg_dump dbname | gzip > filename.gz
createdb dbname
gunzip -c filename.gz | psql dbname
or
cat filename.gz | gunzip | psql dbname
The Influxd backup command generates an InfluxDB Enterprise-compatible format with filtering options to constrain the range of data points exported to the backup. backup creates and stores the following in a specified directory (filenames include UTC timestamp when created):
<UTC timestamp>.<shard_id>.tar.gz
: <UTC timestamp>.manifest
Use the following command examples for backup:
# influxd backup -portable -database thingworx /root/backup/influx1
# influxd backup -portable -database thingworx -start 2022-03-18T12:00:00Z thingworx-20220318T1200UTC
An online restore process is initiated by using the restore command with either the portable argument (indicating the new Enterprise-compatible backup format) or online flag (indicating the legacy backup format).
To restore data to existing database, perform the following steps:
Restore thingworx database into a temporary database thingworxMay03:
# influxd restore -portable -db thingworx -newdb thingworxMay03 /root/backup/influx-2022-05-03-15-34
Sideload into the target database and drop the temporary database:
> USE thingworxMay03
> SELECT * INTO thingworx..:MEASUREMENT FROM /.*/ GROUP BY *
> DROP DATABASE thingworxMay03
It is possible to copy contents of a table into a CSV file. The following is an example for copying table Factory2_Device99 to a CSV file:
# influx -precision 'rfc3339' -database 'thingworx' -execute 'SELECT * FROM Factory2_Device99' -format csv > factory2_device99.csv
Kepware Server has an MQTT Client Driver which can connect and subscribe to MQTT brokers. This allows Kepware Server to receive data from MQTT brokers running on devices such as the Monnit Advanced Gateway.
On Kepware, set up an MQTT communication channel by connecting to the MQTT server by following these steps.