Skip to main content

Moving from SQLite to PostgreSQL

If you installed Knocknoc Server before version 7.7, your instance is likely using a local SQLite database. Starting with version 7.7, the Knocknoc Server now uses PostgreSQL as its primary database. This guide explains how to use the "knocker convertdb" tool, which is bundled with all recent Knocknoc Server releases, to seamlessly migrate an existing SQLite database to PostgreSQL.

Before progressing, ensure you have the latest version of the Knocknoc Server installed. More information on how to do this is available here.

 

Converting your Database with Local PostreSQL (Recommended)

Using "knocker convertdb" on your system will install and configure PostgreSQL for the Knocknoc Server:

$ sudo /opt/knocknoc/knocker/knocker convertdb

You will now be prompted to decide between using a local instance of Postgres, or an external preconfigured Postgres database. Enter "1" and hit enter:

This process migrates your Knocknoc instance from SQLite to PostgreSQL.
** On success, Knocknoc will be restarted to apply the migration, after taking a complete backup of your existing SQLite database.

How would you like to configure PostgreSQL?
  1) Use a local PostgreSQL installation (default)
  2) Use an external or preconfigured PostgreSQL database

Option 1 or 2? (default is 1) 1

...

__        __   _                            _
\ \      / /__| | ___ ___  _ __ ___   ___  | |_ ___
 \ \ /\ / / _ \ |/ __/ _ \| '_ ` _ \ / _ \ | __/ _ \
  \ V  V /  __/ | (_| (_) | | | | | |  __/ | || (_) |
 __\_/\_/ \___|_|\___\___/|_| |_| |_|\___|  \__\___/
|  _ \ ___  ___| |_ __ _ _ __ ___  ___
| |_) / _ \/ __| __/ _` | '__/ _ \/ __|
|  __/ (_) \__ \ || (_| | | |  __/\__ \
|_|   \___/|___/\__\__, |_|  \___||___/
                   |___/

✅ Knocknoc running on PostgreSQL
   - Knocknoc status:                 active
   - PostgreSQL connection string:    postgres:///knocknoc
   - SQLite database backup:          /opt/knocknoc/var/sqlite/<timestamp>/
   - Former SQLite database:          /opt/knocknoc/var/.deleted/<timestamp>/

 

You're done! Your server data will now be available within Postgres, and the Knocknoc Server will be running with the increased speed and reliability offered by the new configuration.

If anything appears incorrect in your new setup, you can always revert back to your previous SQLite database (See troubleshooting below), and contact support@knocknoc.io for further support. 

Converting your Database with External PostgreSQL (For Advanced Users)

Start by running "knocker convertdb":

$ sudo /opt/knocknoc/knocker/knocker convertdb

You will now be prompted to decide between using a local instance of Postgres, or an external preconfigured Postgres database. Enter "2" and hit enter:

This process migrates your Knocknoc instance from SQLite to PostgreSQL.
** On success, Knocknoc will be restarted to apply the migration, after taking a complete backup of your existing SQLite database.

How would you like to configure PostgreSQL?
  1) Use a local PostgreSQL installation (default)
  2) Use an external or preconfigured PostgreSQL database

Option 1 or 2? (default is 1) 2

You will next be prompted for a PostgreSQL connection string. For more information, checkout out the BYO Postgres page. Once the connection string is provided, the database conversion will proceed and finish normally. 

If anything appears incorrect in your new setup, you can always revert back to your previous SQLite database (See troubleshooting below), and contact support@knocknoc.io for further support. 

Troubleshooting

Restoring your Previous Database

To restore your previous SQLite database, start by finding the location of the former database in the "knocker convertdb" summary output:

✅ Knocknoc running on PostgreSQL
   - Knocknoc status:                 active
   - PostgreSQL connection string:    postgres:///knocknoc
   - SQLite database backup:          /opt/knocknoc/var/sqlite/<timestamp>/
   - Former SQLite database:          /opt/knocknoc/var/.deleted/<timestamp>/

Or, if you have lost this output, it will be located under "/opt/knocknoc/var/.deleted/<timestamp>/", where <timestamp> is the time the backup was taken, so you most likely would want to restore from the most recent timestamp (largest number).

Once you have located the database backup, move it into "/opt/knocknoc/var/":
$ cp /opt/knocknoc/var/.deleted/<timestamp>/knocknoc.db /opt/knocknoc/var/knocknoc.db

Finally, revert the "DBURL" value to "DBFile" in your knocknoc.conf:
$ nano /opt/knocknoc/etc/knocknoc.conf

...
## Reverse proxy / trusted forwarders
TrustedForwarders = [ "127.0.0.1/32" ]
#
## Choose how to prioritise HTTP forwarded headers.
## For more information, see https://docs.knocknoc.io/books/admin-guide/page/server-installation-on-premise
#HttpForwardedHeaders = [ "X-Forwarded-For", "Forwarded" ]
#
## LogLevels can be set as "error" "warn" "info" "debug" "trace", increasing in verbosity.
LogLevel = "info"
#
FrontendDir = "/opt/knocknoc/var/frontend"
SSLCert = "/opt/knocknoc/var/knocknoc.crt"
SSLKey = "/opt/knocknoc/var/knocknoc.key"


# MAKE AND SAVE THE CHANGES BELOW

# DBURL = "postgres:///knocknoc"
DBFile = "/opt/knocknoc/var/knocknoc.db"

Finally, restart the Knocknoc Server:
$ systemctl restart knocknoc

I Received the Error Message: "Failed to copy data from SQLite to Postgres"

If you received this error message:

Failed to copy data from SQLite to Postgres.
There is already data present in the postgres database pointed at by postgres:///knocknoc.
If you'd like force the migration, and disregard/delete any data in the existing postgres database, please run `/opt/knocknoc/knocker/knocker convertdb force`.
If this still doesn't work, please consider contacting support@knocknoc.io.

This error message will occur when the provided custom database contains data that conflicts with the Knocknoc Server database configuration. If you are certain you'd like to overwrite data in the database (potentially deleting data), then run "/opt/knocknoc/knocker/knocker convertdb force" as suggested.

If you are not sure, please contact support@knocknoc.io for a deeper look at the cause, and potential alternatives.

Back to top