Migration Sqlite to Postgres

Migrating Sqlite to Postgres

Overview

If you started using Yucca with Sqlite and then resources grew, you can migrate your data from a Sqlite database to Postgres. Reverse migration is not available.

Migration

1. Deploy and prepare postgres in advance.

2. stop the Yucca server:

   ``sh sudo systemctl stop yucca ```

3. Run the migration command specifying the path to the source base and destination base:

   /opt/yucca/yucca admin migrate
   

   You can see the list of all parameters in the tooltip:

   /opt/yucca/yucca/yucca admin migrate --help
   Migrate state from SQLite to PostgreSQL
   
   Usage:
   yucca admin migrate [flags].
   
   Flags:
       --config string Path to configuration file (default "/opt/yucca/yucca/yucca.toml")
       --data-dir string The data directory used to store state and other persistent data (default "data")
       --database-busy-timeout int Timeout in seconds for waiting for an SQLite table to become unlocked (default 500)
       --database-ca-cert-path string The path to the CA certificate to use (default "/etc/ssl/certs")
       --database-cache-mode string Shared cache setting used for connecting to the database (private, shared) (default "shared")
       --database-client-cert-path string The path to the client cert
       --database-client-key-path string The path to the client key
       --database-conn-max-lifetime duration Sets the maximum amount of time a connection may be reused (default 0s)
       --database-host string Database host (not applicable for sqlite3) (default "127.0.0.1:5432")
       --database-max-idle-conn int The maximum number of connections in the idle connection pool (default 2)
       --database-max-open-conn int The maximum number of open connections to the database
       --database-name string The name of the Yucca database (default "yucca")
       --database-password string The database user's password (not applicable for sqlite3) (default "postgres")
       --database-path string SQLite database location
       --database-ssl-mode string SSL mode for Postgres (disable, require or verify-full) (default "disable")
       --database-user string The database user (not applicable for sqlite3) (default "postgres")
   -h, --help Help for migrate
       --state-dir string The directory used to store state and other persistent data (default "<data-dir>/state")
   

4. Specify in configuration the new database type postgres and specify the connection details.

5. Start the Yucca server:

   ``sh sudo systemctl start yucca ```

6. Make sure that Yucca now uses postgres, you can see it in the /ui/administration/config configuration section or in the log when the server starts.