Skip to content
Snippets Groups Projects

[Docs] Migrating from SQLite to PostgreSQL

    • Closed Issue created by f00wl @f00wl

    Migrate from SQLite to PostgreSQL with pgloader

    SQLite is the default database engine, mainly for development purpose but it should be enough for small instance. If you want to migrate to PostgreSQL afterward you can follow theses steps (Debian based):

    1. Install and configure PostgreSQL, create a new database (ex... hiboo) with the right owner if you don't use postgres: help

    2. install pgloader: https://pgloader.readthedocs.io

    apt install pgloader
    1. by default pgloader convert datetime to timestamptz and it raise error on hiboo so create pgloader command-file /tmp/pgloader-hiboo.conf:
    LOAD database
	FROM sqlite:///tmp/hiboo.db
	INTO postgresql:///hiboo

CAST	type datetime to timestamp using sqlite-timestamp-to-timestamp;
    1. shutdown hiboo to avoid data loss during migration
    service hiboo stop # if you created systemd file
    1. backup your sqlite file
    cp hiboo.db hiboo.db.bak
    1. make sure postgres user can read the SQlite db and the pgloader config file:
    chown postgres:postgres /tmp/hiboo.db
chown postgres:postgres /tmp/pgloader-hiboo.conf
    1. import
    sudo -u postgresql pgloader /tmp/pgloader-hiboo.conf
    1. If you created specific user you will probably need to set the right owner:
    su - postgres psql <dbname>

    then inside psql:

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>;

    <CTRL-D> to exit

    1. don't forget to setup SQLAlchemy with the right URI
    SQLALCHEMY_DATABASE_URI='postgres://<dbuser>:<password>@localhost:5432/<dbname>'
    1. Restart hiboo, check if it works and clean the mess!
      If it's fail restore the SQLite backup file and database URI configuration.
    Edited
    To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information

    Child items ...

    • Activity

    Please register or sign in to reply