DB Import & Export

Commands

PostgreSQL

🎯 Purpose	⌨️ Command
Backup (container)	source .env sudo docker exec -e PGPASSWORD="$OWNERS_PASSWORD" -it container_name pg_dump -U user -d dbname -a > backup.sql
Backup (container)	source .env sudo docker exec \ -e PGPASSWORD="$OWNERS_PASSWORD" \ -it container_name \ pg_dump \ --data-only \ --column-inserts \ --exclude-table=schema_migrations \ --exclude-table=spatial_ref_sys \ -U user \ -h 192.168.0.253 \ -p 65432 \ -d dbname

MariaDB

MariaDB
Export database	mariadb-dump -p my_wiki mariadb-dump -p --compatible=postgresql my_wiki mariadb-dump -p --no-data --compatible=postgresql my_wiki mariadb-dump -p --no-create_info --compatible=postgresql my_wiki mariadb-dump -p --no-create_info --extended-insert=false --compatible=postgresql my_wiki
Restore database	echo 'create database my_wiki;' | mariadb -p mariadb -p < data-to-import.sql

SQLite3

SQLite 3
Export database	sqlite3 my_wiki.sqlite '.dump' sqlite3 my_wiki.sqlite '.dump user' sqlite3 -header my_wiki.sqlite '.mode insert' 'select * from user;'
Restore database

MariaDB

Script to sync db

DATESN=`date +'%m%d'`
DBNAME=mydb
DBFILE=~/Downloads/${DBNAME}-${DATESN}.sql
SSHTUN=13306
SSHCTRL=tun.ctrl
SSHCONF=myconf
RPASS=********
LPASS=********

if [ -e $SSHCTRL ]; then
  echo "* Close existed tunnel."
  ssh -S $SSHCTRL -O exit $SSHCONF 2> /dev/null
fi

echo "* Load data from remote and save as $DBFILE"
ssh -M -S $SSHCTRL -NCfL $SSHTUN:127.0.0.1:3306 $SSHCONF
mysqldump -h 127.0.0.1 -P $SSHTUN -u root -p$RPASS $DBNAME > $DBFILE
ssh -S $SSHCTRL -O exit $SSHCONF 2> /dev/null

echo '* Import data into local'
echo "DROP DATABASE IF EXISTS $DBNAME" | mysql -u root -p$LPASS test
echo "CREATE DATABASE IF NOT EXISTS $DBNAME" | mysql -u root -p$LPASS test
echo "source $DBFILE" | mysql -u root -p$LPASS $DBNAME

echo 'Done.'