Hosting DashBox database on external server

[ben]

We've been working on a version of the DashBox that allows the database to be stored on a local server (starting at V4.2.4a1 at http://www.brultech.com/software).

An SD card is still needed for the DashBox to operate, but that SD card will no longer be written to. It will be used in case of emergency (for example if your server IP changes and the DashBox can no longer access the database). If you ever need to restore the original settings in order to access the DashBox, there's a script located in the /system/ section to restore them:

http://<your dashbox address>/system/db.php

Requirements:
- PostgreSQL installed (version shouldn't matter as we aren't doing anything too advanced).
- Dump of your database.

To dump your database, use our backup tool. Instructions are available here:
http://brultech.com/community/viewtopic.php?f=41&t=1816

Run the backup with the "Exclude System Tables" option unchecked.

- Restore the contents of the SQL file with pg_restore.

pg_restore -d brultech_dash <backup name>

More information on pg_restore can be found at: https://www.postgresql.org/docs/9.2/sta ... store.html

Once your backup is up and running on your DashBox, access your DashBoxes settings section. Under Network->Database Connection you can alter the database connection settings.

Input your IP Address, Port, Username, and Password then click the Test Connection button. If it says Connection Successful, save the connection. Your DashBox should now be running off your local server.

[dbreiser]

Will this firmware version help with all the errors I got attempting pg_restore (to increase the size of my SD card)? https://www.brultech.com/community/view ... =32&t=1847

At the time (last December) it looked to me like I wasn't able to avoid permission conflicts between ddbackup and the system information.

Dave

[ben]

Will this firmware version help with all the errors I got attempting pg_restore (to increase the size of my SD card)? https://www.brultech.com/community/view ... =32&t=1847

At the time (last December) it looked to me like I wasn't able to avoid permission conflicts between ddbackup and the system information.

Dave

I haven't tested it but dbbackup is granted INSERT, CREATE, and SELECT privileges on all tables in the database.

It may be quicker however (depending on how much data you have) to take a disk-level backup, restore it to the larger SD card, then run the sd.php script.

[dbreiser]

I haven't tested it but dbbackup is granted INSERT, CREATE, and SELECT privileges on all tables in the database.

It may be quicker however (depending on how much data you have) to take a disk-level backup, restore it to the larger SD card, then run the sd.php script.

I like the idea of a disk-level backup, but when I put the SD in a USB adapter and plug it into my Mac, the Mac says it can't be read, and do I want to format it? I even tried to tell my Windows VM to try to intercept the USB mount, but macOS still interferes.

What format does the SD use?

Dave

[ben]

I haven't tested it but dbbackup is granted INSERT, CREATE, and SELECT privileges on all tables in the database.

It may be quicker however (depending on how much data you have) to take a disk-level backup, restore it to the larger SD card, then run the sd.php script.

I like the idea of a disk-level backup, but when I put the SD in a USB adapter and plug it into my Mac, the Mac says it can't be read, and do I want to format it? I even tried to tell my Windows VM to try to intercept the USB mount, but macOS still interferes.

What format does the SD use?

Dave

Surprised the Mac can't read it, it's an EXT4 filesystem.

You shouldn't need to read the filesystem partitions as long as the SD card is listed under /dev/. If it's under /dev/, you should be able to use dd to make an image of the entire disk.

dd if=/path-to-backup/backup-20151026.img of=/dev/disk#
(where # is the disk number assigned by the Mac).

[scottmichaelj]

This is amazing. However what would really be nice is putting this on either a standalone VM or Docker image without the SD card. Since most machines could have multiple drives the SD card wouldn’t be needed. Any thoughts on this? Great work. It would be amazing not to have yet another “box” if you already have a NAS etc.

[dbreiser]

Surprised the Mac can't read it, it's an EXT4 filesystem.

You shouldn't need to read the filesystem partitions as long as the SD card is listed under /dev/. If it's under /dev/, you should be able to use dd to make an image of the entire disk.

dd if=/path-to-backup/backup-20151026.img of=/dev/disk#
(where # is the disk number assigned by the Mac).

I wish I had understood your words...
One might contend that between HFS+ and APFS, Apple doesn't support it's own file systems as well as they should. In any event, they provide no support for EXT4. But that doesn't matter for image handling, as you were trying to communicate to me.

dd works but is agonizingly slow. It created a backup image of the 16GB SD card (have never gotten the backup routine to work) in 35 minutes, but it took 4.5 hours to write it to the 32 GB card.

Next time I'll plug both cards into the Mac and dd from the source to the new card. Should save me about an hour starting from a 32GB card.

One other wrench in my understanding is that Apple's newest SDXC card readers are attached to the PCIe bus. VMWare can't access unmounted devices on the PCIe bus. Again, a red herring since I could use the Mac directly as long as I didn't care about reading the file system. This limitation shows up most notably when I expected to be able to see the dashbox files in my iMac's sd card slot from an Ubuntu VM. Oh well.

It was definitely nice to have the "Expand card" button in dashbox's sd.php utility. Thank you for adding that.

It should be close to 3 years before I need to do this operation again.

For Mac folk not used to manipulating unix in the terminal, the command to see the /dev/ list is:

diskutil list

and the command to copy the image of one disk to another (both attached to the mac via usb reader or the sd card reader), but not mounted in the file system:

dd if=/dev/diskN1 of=/dev/diskN2

with N1 and N2 replaced by the device identifier from 'diskutil list' as in "disk3" or "disk5" or similar.

And because unix is happy to do significant damage if you aren't careful, I'd advocate that the source sd card be write protected before connecting it to the mac, since if you make a source vs. target mistake in the dd command, you can wipe out your data.

Dave

[covvie]

When using dd, you should specify a block size as part of the command to improve the performance. For example: dd if=/dev/source of=/dev/dest bs=1m.

If you don't specify a block size, it reads & writes in 512 byte blocks. As you saw that is very, very slow, particularly on writes. You can adjust the block size to whatever you prefer, but making it larger than 1m won't make a much of a difference. Personally, I usually use either 64k or 1m.

-Chad

[dbreiser]

When using dd, you should specify a block size as part of the command to improve the performance. For example: dd if=/dev/source of=/dev/dest bs=1m.

If you don't specify a block size, it reads & writes in 512 byte blocks. As you saw that is very, very slow, particularly on writes. You can adjust the block size to whatever you prefer, but making it larger than 1m won't make a much of a difference. Personally, I usually use either 64k or 1m.

-Chad

Thanks for the tip. I went digging, and a comment this year from an ancient thread
https://superuser.com/questions/631592/ ... n-mac-os-x

suggests that using /dev/rdiskN instead of /dev/diskN may get me an additional 5x speedup, even after using bs=1m.

I may have to get another microSD just to test...

[jgreene]

I've been successful at installing Postsql on my linux server and dump the database and creating and restoring it on the linux server. So far so good. However, when I try to get the dashbox to connect to it all I get is "connection failed" when I test the connection. Not very helpful in determining why. I've made all the modifications to the configuration files to allow remote access and my server had no trouble grabbing the database from the dashbox, so I know they are communicating.