airControl2 - How to Back Up and Restore a Database Manually

Overview


The instructions in this article are mainly for clients using versions previous to the official public release, or if interested in backing up and restoring manually. Otherwise, in version 2.0, you will find a Database Backup feature in the Control Panel of the airControl client, which allows to easily back up and restore databases. No other action is necessary.The location of these backups is: /opt/Ubiquiti/AirControl2/lib/db/dbbackups/

Note: When restoring a backup using the Control Panel, you must manually restart the airControl2Server service. With ssh access use:

sudo service airControl2Server restart

Table of Contents


  1. How to Back up a Database
  2. How to Restore a Backup
  3. Other Useful "postgresql" Commands
  4. Related Articles

How to Back Up a Database


Back to Top

1. Backup Command 

Issue this command to back up the database. In this example we will use the username postgres, localhost as the host, ac2 as the database name, and ac2backup.sql as the backup file name.

Sudo pg_dump -n <schemaName> -F c -T ac2.*statistics -f
/opt/Ubiquiti/AirControl2/lib/db/dbbackups/<backup file name>.pgdmp
postgresql://<pg_user_name>:<pg_user_password>@<pg_server>:<port>/<db_name>

Example:

sudo pg_dump -n ac2 -F c -T ac2.*statistics -f
/opt/Ubiquiti/AirControl2/lib/db/dbbackups/ac2backup.pgdmp
postgresql://postgres:testpass123@localhost:5432/ac2

2. View Files (Optional)

This will place the backup file in the directory. To view, issue the following ls command: 

user@ubuntuserver:~$ ls /opt/Ubiquiti/AirControl2/lib/db/dbbackups/
ac2backup.pgdmp

3. Copy File (Optional)

If desired, you can copy this to another location. There are many ways to do this. For example, using SCP to copy to another computer:

user@ubuntuserver:~$ sudo scp /opt/Ubiquiti/AirControl2/lib/db/dbbackups/ac2backup.pgdmp
user@192.168.1.15:/Users/admin/Desktop

How to Restore a Backup


Back to Top

1. Restore Backup File

sudo pg_restore -c -n schemaName -F c -d
postgresql://<pg_user_name>:<pg_user_password>@<pg_server>:<port>/<db_name>
<backup_file_name_to_restore>

2. Restart airControl2Server Service

sudo service airControl2Server restart

Example:

user@ubuntuserver:~$ sudo pg_restore -c -n ac2 -F c -d
postgresql://postgres:testpass123@localhost:5432/ac2
/opt/Ubiquiti/AirControl2/lib/db/dbbackups/v2.0.RC2.2028_5_ac2_20160805_134400_9119.pgdmp

jordan@ubuntuserver:~$ sudo service airControl2Server restart

Other Useful "postgresql" Commands


Back to Top

Connect first, using the default postgres user, by following this command:

psql -h localhost -U postgres

Then issue these commands as needed:

\? -- Shows help

\du -- Lists users

\l -- Lists databases

\dn -- Lists schemas

\conninfo -- Displays information about current connection

\q -- Quit/Exit


Related Articles


Back to Top