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 for the Postgres 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
  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>


  4. 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_name> = The DB name. (Example: tomcat)

# 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-#
# Enter \q<return> to exit command line interface.

# If the Postgres DB is not running, start it:

Back to top