Convert database from SQLite to Postgres

Let’s discuss how we can move from a file based database (SQLite) to a server based database. Using a Postgres database is recommended for VFX Studios.

Why?
To increase stability and performance when you store the library data on a server and share it with multiple artists.

1 1. Install requirements
- 1.1 pgloader
  - 1.1.1 Windows
- 1.2 database tools (optional)
2 2. Create new database
3 3. Import data into database
4 4. Update library config file (optional)

1. Install requirements

pgloader

Tool to move data from SQLite to Postgres

# Linux
sudo apt-get install pgloader

# MacOS
brew install pgloader

Windows

You can use Windows Subsystem for Linux (WSL)
What is Windows Subsystem for Linux

Open Command Prompt (cmd.exe) as administrator (elevated):

# Windows
# first install the Linux distribution
wsl --install Ubuntu

# restart your workstation after install

# now install pgloader
sudo apt update
sudo apt install pgloader -y

database tools (optional)

To see what is going on in your databases you can use tools like:

DB Browser for SQLite

pgAdmin - PostgreSQL Tools

2. Create new database

Use the 'das element' UI to create a new library with the database type: Postgres

Please choose a different file name for the library config file. For example:
/mnt/server/elements_library/.config/das-element_postgres.lib

3. Import data into database

Use the pgloader tool to copy the data from the old database to the newly created one.

# Linux / MacOS
pgloader --with "data only" --with "quote identifiers" /path/to/library/.config/das-element.db postgresql://username:password@my-database-server/das_element

# Windows
pgloader --with "data only" --with "quote identifiers" /mnt/c/users/my-user/Desktop/das-element.db postgresql://username:password@my-database-server/das_element

Windows using Windows Subsystem for Linux (WSL)

For WSL the C:\ drive can be access like this: /mnt/c

Copy the database file locally, maybe on the Desktop. The file path will now look like this:
/mnt/c/users/admin/Desktop/das-element.db

4. Update library config file (optional)

If you want to keep your existing library configuration like transcoding templates, simply copy over the database information to the previous library config file.

Open the library config and replace this part …

    "database": {
        "type": "sqlite",
        "value": {
            "lin": "/mnt/server/my_library/.config/das-element.db",
            "mac": "",
            "win": ""
        }
    },

to this …

"database": {
    "type": "postgres",
    "value": {
        "name": "das_element",
        "password": "password",
        "port": 5432,
        "sslmode": "disable",
        "uri": "my-database-server",
        "user": "username"
    }
},

After all steps are done, please make sure to restart the software! That’s it!