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:
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!