3 Migration from SQLite to PostgreSQL

Pierre-Alain TORET edited this page 2023-01-11 17:41:47 +01:00

Table of Contents

• Backup SQLite
• Dump your data
• Configure PostgreSQL
• Configure EteSync for PostgreSQL
• Initialize the PostgreSQL database
• Import your data
• (optional) Check your data
• Restart EteSync
• Enjoy EteSync !

SQLite is the default storage backend setup.

It's configured both in the default etesync-server.ini file with the following block

[database]
engine = django.db.backends.sqlite3
name = db.sqlite3

and using the Django style in etesync_server/settings.py with the block :

DATABASES = {  
    'default': {    
        'ENGINE': 'django.db.backends.sqlite3',  
        'NAME': os.environ.get('ETESYNC_DB_PATH',  
                               `os.path.join(BASE_DIR, 'db.sqlite3')),  
    }
}

As the app is based on Django, it can be used with PostgreSQL as a storage backend easily. And if you have started to store some data in EteSync, you will have to migrate your date from SQLite to PostgreSQL.

Here is how :

First make sure the server is stopped.

Backup SQLite

Just copy the SQLite database somewhere safe, juste in case :)

cp db.sqlite3 /path/to/somwhere/safe/

Dump your data

./manage.py dumpdata > /path/to/writable/etesync.json

You will end up with a file of several megabytes if you already have some data stored in EteSync.

Configure PostgreSQL

You will need to setup a database to hold your data.

sudo su - postgres
createuser -P etesync # that will ask you to enter a password
createdb -O etesync
# depending on your existing configuration you might have to edit pg_hba.conf to allow the connection from etesync server
exit

Configure EteSync for PostgreSQL

One need to add the psycopg2 requirement to what's already installed. Either load your venv and pip install psycopg2 or add psycopg2 to your requirement.txt and pip install it again.

Either using the .ini configuration file

[database]
engine = django.db.backends.postgresql
name = etesync
user = etesync
password = etesync
host = 127.0.0.1
port = 5432

Or using Django style configuration.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'etesync',
        'USER': 'etesync',
        'PASSWORD': 'etesync', # you have to put the password you chose earlier during the user creation
        'HOST': '127.0.0.1',   # that's if you PostgreSQL database is on the same host as your EteSync server
        'PORT': '5432',        
    }
}

Initialize the PostgreSQL database

This is done with the simple command ./manage.py migrate

Import your data

./manage.py loaddata /path/to/writable/etesync.json

This should end up running with no errors.

(optional) Check your data

sudo su - postgres
psql
(in psql) \c etesync # or the name you have given to your database earlier
(in psql) \d # will display the tables available
(in psql) select * from auth_user; # will give you the user added to EteSync for example

Restart EteSync

./manage.py runserver 127.0.0.1:8000 if you are behind a reverse-proxy for example

Enjoy EteSync !

• Home
• Setting up an Etebase Server (EteSync v2)
  • Basic Setup Etebase
  • Production setup using Nginx
  • Run uvicorn at boot
  • Setup HTTPS for Etebase
• Migration from SQLite to PostgreSQL
• Backups