PostgreSQL/PgLoader: Difference between revisions
Jump to navigation
Jump to search
| (9 intermediate revisions by the same user not shown) | |||
| Line 10: | Line 10: | ||
{| | {| | ||
|valign='top'| | |valign='top'| | ||
<syntaxhighlight lang=" | <syntaxhighlight lang="sql"> | ||
cat << DDL | | cat << DDL | mariadb | ||
CREATE DATABASE IF NOT EXISTS academia; | CREATE DATABASE IF NOT EXISTS academia; | ||
CREATE USER 'chorke'@'%' IDENTIFIED BY 'sadaqah!'; | CREATE USER 'chorke'@'%' IDENTIFIED BY 'sadaqah!'; | ||
| Line 20: | Line 20: | ||
|valign='top'| | |valign='top'| | ||
<syntaxhighlight lang="sql"> | |||
cat << DDL | psql | |||
CREATE DATABASE academia; | |||
CREATE USER chorke WITH ENCRYPTED PASSWORD 'sadaqah!'; | |||
GRANT ALL PRIVILEGES ON DATABASE academia TO chorke; | |||
ALTER DATABASE academia OWNER TO chorke; | |||
DDL | |||
</syntaxhighlight> | |||
|- | |||
|colspan='2'| | |||
---- | |||
|- | |||
|colspan='2'| | |||
<syntaxhighlight lang="bash"> | |||
BACKUP_DATE_N_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')" | |||
echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo | |||
# password: sadaqah! | |||
mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia | |||
mariadb-dump -h127.0.0.1 -P3306 -uchorke -Bacademia > ./academia-${BACKUP_DATE_N_TIME}.dump | |||
mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia < ./academia-20241010-T1010-ZP0600.dump | |||
</syntaxhighlight> | |||
|- | |- | ||
|colspan=' | |colspan='2'| | ||
---- | |||
|- | |||
|colspan='2'| | |||
<syntaxhighlight lang="bash"> | |||
cat <<MIG | pgloader -v /dev/stdin | |||
LOAD DATABASE | |||
FROM mysql://chorke:sadaqah!@localhost/academia?useSSL=false | |||
INTO pgsql://chorke:sadaqah!@localhost/academia; | |||
MIG | |||
</syntaxhighlight> | |||
|- | |||
|colspan='2'| | |||
---- | |||
|- | |||
|colspan='2'| | |||
<syntaxhighlight lang="bash"> | |||
BACKUP_DATE_N_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')" | |||
echo -n password: ;read -s PGPASSWORD;export PGPASSWORD;echo | |||
# password: sadaqah! | |||
psql -h127.0.0.1 -p5432 -Uchorke -dacademia | |||
pg_dump -h127.0.0.1 -p5432 -Uchorke -dacademia | gzip > academia-${BACKUP_DATE_N_TIME}.sql.gz | |||
gunzip -c academia-20241010-T1010-ZP0600.sql.gz | psql -h127.0.0.1 -p5432 -Uchorke -dacademia | |||
</syntaxhighlight> | |||
|- | |||
|colspan='2'| | |||
---- | ---- | ||
|- | |- | ||
| Line 33: | Line 84: | ||
==Playground== | ==Playground== | ||
{| | {| | ||
|valign='top' colspan='2'| | |||
<syntaxhighlight lang="bash"> | |||
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -h | |||
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -V | |||
</syntaxhighlight> | |||
|valign='top'| | |valign='top'| | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
| Line 38: | Line 95: | ||
pgloader -V | pgloader -V | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|- | |- | ||
|colspan='3'| | |colspan='3'| | ||
---- | ---- | ||
|- | |- | ||
| | |colspan='3'| | ||
<syntaxhighlight lang="bash"> | |||
| | pgloader ./sqlite/academia.db pgsql:///academia | ||
pgloader ./sqlite/academia.db pgsql://chorke:sadaqah!@localhost/academia | |||
| | </syntaxhighlight> | ||
|- | |||
|colspan='3'| | |||
---- | |||
|- | |||
|colspan='3'| | |||
<syntaxhighlight lang="bash"> | |||
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql:///academia | |||
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql://chorke:sadaqah!@localhost/academia | |||
</syntaxhighlight> | |||
|- | |||
|colspan='3'| | |||
---- | |||
|- | |||
|colspan='3'| | |||
<syntaxhighlight lang="bash"> | |||
cat <<MIG | pgloader -v /dev/stdin | |||
LOAD DATABASE | |||
FROM mysql://chorke:sadaqah!@localhost/academia?useSSL=false | |||
INTO pgsql://chorke:sadaqah!@localhost/academia | |||
WITH include drop, create tables | |||
ALTER SCHEMA 'academia' RENAME TO 'public'; | |||
MIG | |||
</syntaxhighlight> | |||
|} | |} | ||
Latest revision as of 22:07, 3 December 2024
cat << EXE | sudo bash
apt-get update;echo
apt list -a --upgradable
apt-get install -y pgloader
EXE
Migration
cat << DDL | mariadb
CREATE DATABASE IF NOT EXISTS academia;
CREATE USER 'chorke'@'%' IDENTIFIED BY 'sadaqah!';
GRANT ALL PRIVILEGES ON academia.* TO 'chorke'@'%';
FLUSH PRIVILEGES;
DDL
|
cat << DDL | psql
CREATE DATABASE academia;
CREATE USER chorke WITH ENCRYPTED PASSWORD 'sadaqah!';
GRANT ALL PRIVILEGES ON DATABASE academia TO chorke;
ALTER DATABASE academia OWNER TO chorke;
DDL
|
|
| |
BACKUP_DATE_N_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')"
echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo
# password: sadaqah!
mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia
mariadb-dump -h127.0.0.1 -P3306 -uchorke -Bacademia > ./academia-${BACKUP_DATE_N_TIME}.dump
mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia < ./academia-20241010-T1010-ZP0600.dump
| |
|
| |
cat <<MIG | pgloader -v /dev/stdin
LOAD DATABASE
FROM mysql://chorke:sadaqah!@localhost/academia?useSSL=false
INTO pgsql://chorke:sadaqah!@localhost/academia;
MIG
| |
|
| |
BACKUP_DATE_N_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')"
echo -n password: ;read -s PGPASSWORD;export PGPASSWORD;echo
# password: sadaqah!
psql -h127.0.0.1 -p5432 -Uchorke -dacademia
pg_dump -h127.0.0.1 -p5432 -Uchorke -dacademia | gzip > academia-${BACKUP_DATE_N_TIME}.sql.gz
gunzip -c academia-20241010-T1010-ZP0600.sql.gz | psql -h127.0.0.1 -p5432 -Uchorke -dacademia
| |
|
| |
Playground
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -h
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -V
|
pgloader -h
pgloader -V
| |
|
| ||
pgloader ./sqlite/academia.db pgsql:///academia
pgloader ./sqlite/academia.db pgsql://chorke:sadaqah!@localhost/academia
| ||
|
| ||
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql:///academia
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql://chorke:sadaqah!@localhost/academia
| ||
|
| ||
cat <<MIG | pgloader -v /dev/stdin
LOAD DATABASE
FROM mysql://chorke:sadaqah!@localhost/academia?useSSL=false
INTO pgsql://chorke:sadaqah!@localhost/academia
WITH include drop, create tables
ALTER SCHEMA 'academia' RENAME TO 'public';
MIG
| ||
References
|
| ||