DB Import & Export: Difference between revisions
Jump to navigation
Jump to search
| (26 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
= Commands = | = Commands = | ||
== PostgreSQL == | |||
{| class="wikitable" | |||
! 🎯 Purpose || ⌨️ Command | |||
|- | |||
| Perform a backup of the container’s database inside the container. || | |||
<syntaxhighlight lang="bash"> | |||
source .env | |||
sudo docker exec \ | |||
-e PGPASSWORD="$OWNERS_PASSWORD" \ | |||
-it container_name \ | |||
pg_dump \ | |||
-U user \ | |||
-d dbname \ | |||
-a > backup.sql | |||
</syntaxhighlight> | |||
|- | |||
| Perform a remote database backup inside the container. || | |||
<syntaxhighlight lang="bash"> | |||
source .env | |||
sudo docker exec \ | |||
-e PGPASSWORD="$OWNERS_PASSWORD" \ | |||
-i 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 > backup.sql | |||
</syntaxhighlight> | |||
|- | |||
| Truncate tables || | |||
<syntaxhighlight lang="bash"> | |||
TRUNCATE TABLE | |||
roles, | |||
users | |||
RESTART IDENTITY; | |||
</syntaxhighlight> | |||
|- | |||
| Restore from SQL into local database. || | |||
<syntaxhighlight lang="bash"> | |||
source .env | |||
sudo docker exec \ | |||
-e PGPASSWORD="$OWNERS_PASSWORD" \ | |||
-i container_name \ | |||
psql \ | |||
-U user \ | |||
-d dbname < backup.sql | |||
</syntaxhighlight> | |||
|} | |||
== MariaDB == | |||
{| class="wikitable" | {| class="wikitable" | ||
! colspan="2" | MariaDB | ! colspan="2" | MariaDB | ||
| Line 8: | Line 63: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
mariadb-dump -p my_wiki | mariadb-dump -p my_wiki | ||
mariadb-dump -p --no-data my_wiki | mariadb-dump -p --compatible=postgresql my_wiki | ||
mariadb-dump -p --no- | mariadb-dump -p --no-data --compatible=postgresql my_wiki | ||
mariadb-dump -p | mariadb-dump -p --no-create_info --compatible=postgresql my_wiki | ||
mariadb-dump -p --no-create_info --extended-insert=false --compatible=postgresql my_wiki | |||
</syntaxhighlight> | </syntaxhighlight> | ||
|- | |- | ||
| Line 18: | Line 74: | ||
echo 'create database my_wiki;' | mariadb -p | echo 'create database my_wiki;' | mariadb -p | ||
mariadb -p < data-to-import.sql | mariadb -p < data-to-import.sql | ||
</syntaxhighlight> | |||
|} | |||
== SQLite3 == | |||
{| class="wikitable" | |||
! colspan="2" | SQLite 3 | |||
|- | |||
| Export database | |||
| | |||
<syntaxhighlight lang="bash"> | |||
sqlite3 my_wiki.sqlite '.dump' | |||
sqlite3 my_wiki.sqlite '.dump user' | |||
sqlite3 -header my_wiki.sqlite '.mode insert' 'select * from user;' | |||
</syntaxhighlight> | |||
|- | |||
| Restore database | |||
| | |||
<syntaxhighlight lang="bash"> | |||
</syntaxhighlight> | </syntaxhighlight> | ||
|} | |} | ||
| Line 41: | Line 117: | ||
</quickgv> | </quickgv> | ||
< | <syntaxhighlight lang="bash"> | ||
DATESN=`date +'%m%d'` | DATESN=`date +'%m%d'` | ||
DBNAME=mydb | DBNAME=mydb | ||
| Line 67: | Line 143: | ||
echo 'Done.' | echo 'Done.' | ||
</ | </syntaxhighlight> | ||
Latest revision as of 06:33, 22 October 2026
Commands
PostgreSQL
| 🎯 Purpose | ⌨️ Command |
|---|---|
| Perform a backup of the container’s database inside the container. |
source .env
sudo docker exec \
-e PGPASSWORD="$OWNERS_PASSWORD" \
-it container_name \
pg_dump \
-U user \
-d dbname \
-a > backup.sql
|
| Perform a remote database backup inside the container. |
source .env
sudo docker exec \
-e PGPASSWORD="$OWNERS_PASSWORD" \
-i 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 > backup.sql
|
| Truncate tables |
TRUNCATE TABLE
roles,
users
RESTART IDENTITY;
|
| Restore from SQL into local database. |
source .env
sudo docker exec \
-e PGPASSWORD="$OWNERS_PASSWORD" \
-i container_name \
psql \
-U user \
-d dbname < backup.sql
|
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.'