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. 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)
https://docs.microsoft.com/en-us/windows/wsl/about

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:

https://sqlitebrowser.org/

https://www.pgadmin.org/

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 …

to this …

 

 

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