Database settings

The application uses SQLite database by default, with a possibility to change it to your own PostgreSQL database.

Reset database - format local database
Create a backup - create a backup of the current state of the local database
Export database - download selected database file
Import database - upload database file to list
Restore database - restore database from selected backup
Delete backup - delete selected backup

JDBC URL - full JDBC url for postgres database (You can specify schema by adding currentSchema to url)
db.selectzero.io:5432/postgres?currentSchema=my_schema
Schema used for SelectZero must not contain any tables, as these will be automatically created
Username - postgres database user
Password - postgres database password

PostgreSQL user DB requirements

We recommend creating a new separate database for SelectZero alongside with separate user and schema. The user must have all rights in selected schema

Sample creation scripts:

-- Create new empty database
CREATE DATABASE selectzero;
       
-- Create application user
CREATE USER selectzero_user WITH PASSWORD 'your_secure_password';

-- Make sure next commands are run in selected database context
       
-- Create empty schema for application user
CREATE SCHEMA selectzero AUTHORIZATION selectzero_user;
       
-- Set created schema as default path (by default it will be 'public')
ALTER ROLE selectzero_user SET search_path TO selectzero;

Migrating SQLite to PostgreSQL

It is possible to migrate existing SQLite database to a PostgreSQL database using pgloader (installing pgloader). Before migration, make sure you have created the needed DB user following instructions

Create backup of SQLite database and download the database file
Set up Postgres connection in Database selector
Restart the application (the application will be deployed on empty schema, creating necessary tables automatically. This means the database will initially be empty)
Create file selectzero.load with content:
LOAD DATABASE FROM sqlite:///path_to_your_backup/backup_file_name.db INTO postgres://username:password@postgres_url:5432/postgres WITH truncate, data only INCLUDING ONLY TABLE NAMES LIKE 'tt_%';
Use pgloader to export data from SQLite to PostgreSQL using command and script:
pgloader selectzero.load
As license validation will fail, you can manually restart platform once after data is loaded (or insert license manually again)

23 May 2025