Skip to main content

Exporting Postgres DB to SQLite DB

This is a step-by-step procedure for exporting all tables and data from a Postgres database to a new SQLite database:

This procedure assumes that there is a fully populated Postgres database running on a specific port and that you are logged into the server as a user that has "super user" permissions to the Postgres database. This procedure is using code and functions stored in the GitHub tomcat repository - https://github.com/ml4ai/tomcat/tree/master/human_experiments/datasette_interface
It is recommended that after you ssh into the server where the Postgres DB lives, you run git pull to update to the latest code in the repository, check to make you have enough available disk space where the SQLite DB is going to be created, and enter a tmux session before running any of the datasette interface commands.

Examples used in this procedure are for a Postgres database running on gauss and that the SQLite database will be created on gauss.

  1. SSH to the server where the Postgres DB lives using a user account that has "super user" permissions DB:

    Example:

    ssh gauss

  2. CD to the directory where the local repository is cloned and run a "git pull" to get the latest code:

    Example:

    cd ~/tomcat/human\_experiments/datasette\_interface git pull
Example of installing "tmux" on the server:

details

3\. \*\*Start or attach to a "\*tmux"\* session\*\* (You may have to install "\*\*\*tmux\*\*\*" on the server if it is not already installed): It is recommended to use an app like "\*\*\*tmux\*\*\*" so that if you get disconnect from the server while running a "long-running \*\*\*datasette interface\*\*\* command", the command will continue to run in a "\*\*\*tmux session\*\*\*" and you will be able to reconnect to that "\*\*\*tmux session\*\*\*" later. Example of installing "\*\*\*tmux\*\*\*" on the server: ``` 
# Install tmux if not already installed on server:

apt install tmux

Start a new tmux session:

tmux

List current tmux sessions running:

tmux ls

Attach to a running tmux session by session_number:

tmux attach -t <session_number>

  1. Verify that the Postgres DB is running on your specified <port> (if it is not, start it):

    ### <port> = Port the DB is running on. (Example: 5436)
### <run_path> = Path to DB run directory. (Example: /fast/rchamplin/postgres/run)
### <db_path> = Path to the database directory. (Example: /fast/rchamplin/postgres/data/11/rchamplin)
### <db_name> = The DB name. (Example: tomcat)
### <pg_ctl_path> = Path to where pg_ctl is installed on server. (Example: /usr/lib/postgresql/11/bin)

    Check to see if you can connect to the running Postgres DB by command line:

    psql -p <port> -h <run_path> -d <db_name>

    Example output >

    ###> psql (11.20 (Debian 11.20-0+deb10u1)) ###> Type "help" for help. ###> tomcat2-#

    \t<return> to list tables.

    \q<return> to exit command line interface.

    If the Postgres DB is not running, start it:

    <pg_ctl_path>/pg_ctl -D <db_path> -l logfile start

  2. Using "minicondo3", activate the tomcat-database environment (if "minicondo3" not installed, install and create env):

    ### <datasette_interface_clone_path> = Path to where "datasette_interface" github code is cloned.
### Example: ~/tomcat/human_experiments/datasette_interface

    If not already installed, install "minicondo3" on server:

    mkdir -p ~/miniconda3 wget https://repo.anaconda.com/miniconda/Miniconda3-latest-Linux-x86_64.sh -O ~/miniconda3/miniconda.sh bash ~/miniconda3/miniconda.sh -b -u -p ~/miniconda3 rm ~/miniconda3/miniconda.sh

    After installing minicondo3, tell Linux to source ~/.bashrc

    so that it will have PATH to condo commands:

    source ~/.bashrc cd <datasette_interface_clone_path>

    condo will now change prompt to show what environment is active (defalt is base)

    Example: (base) rchamplin@gauss:~/tomcat/human_experiments/datasette_interface$

    If not already created, create a "conda" python=3.9 environment named "tomcat-database":

    cd <datasette_interface_clone_path> conda create --name tomcat-database python=3.9

    Example output:

    ###> Retrieving notices: ...working... done ###> WARNING: A conda environment already exists at '/work/rchamplin/miniconda3/envs/tomcat-database' ###> Remove existing environment (y/[n])? n

    *** To remove a conda environment: conda remove -n tomcat-database --all

    Activate the conda "tomcat-database" environment:

    source ~/.bashrc cd <datasette_interface_clone_path> conda activate tomcat-database

    Example output:

    ###> (tomcat-database) rchamplin@gauss:~/tomcat/human_experiments/datasette_interface$

Back to top