PostgreSQL/PgLoader: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
{|class='wikitable' style='width:100%;margin: | {|class='wikitable' style='width:100%;margin:-11px 0 6px 0' | ||
|valign='top' style='width:50%'| | |valign='top' style='width:50%'| | ||
<syntaxhighlight style='margin:3px 0' lang='bash'> | <syntaxhighlight style='margin:3px 0' lang='bash'> | ||
| Line 38: | Line 38: | ||
|valign='top' colspan='2'| | |valign='top' colspan='2'| | ||
<syntaxhighlight style='margin:3px 0' lang='bash'> | <syntaxhighlight style='margin:3px 0' lang='bash'> | ||
BKP_DATE_N_TIME="$(TZ=UTC-8 date +'%Y-%m-%dT%H%M')" | |||
echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo | echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo | ||
# password: sadaqah! | # password: sadaqah! | ||
mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia | mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia | ||
mariadb-dump -h127.0.0.1 -P3306 -uchorke -Bacademia > ./academia-${ | mariadb-dump -h127.0.0.1 -P3306 -uchorke -Bacademia > ./academia-${BKP_DATE_N_TIME}.dump | ||
mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia < ./academia-20241010-T1010-ZP0600.dump | mariadb -h127.0.0.1 -P3306 -uchorke -Dacademia < ./academia-20241010-T1010-ZP0600.dump | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 58: | Line 58: | ||
|valign='top' colspan='2'| | |valign='top' colspan='2'| | ||
<syntaxhighlight style='margin:3px 0' lang='bash'> | <syntaxhighlight style='margin:3px 0' lang='bash'> | ||
BKP_DATE_N_TIME="$(TZ=UTC-8 date +'%Y-%m-%dT%H%M')" | |||
echo -n password: ;read -s PGPASSWORD;export PGPASSWORD;echo | echo -n password: ;read -s PGPASSWORD;export PGPASSWORD;echo | ||
# password: sadaqah! | # password: sadaqah! | ||
psql -h127.0.0.1 -p5432 -Uchorke -dacademia | psql -h127.0.0.1 -p5432 -Uchorke -dacademia | ||
pg_dump -h127.0.0.1 -p5432 -Uchorke -dacademia | gzip > academia-${ | pg_dump -h127.0.0.1 -p5432 -Uchorke -dacademia | gzip > academia-${BKP_DATE_N_TIME}.sql.gz | ||
gunzip -c academia-20241010-T1010-ZP0600.sql.gz | psql -h127.0.0.1 -p5432 -Uchorke -dacademia | gunzip -c academia-20241010-T1010-ZP0600.sql.gz | psql -h127.0.0.1 -p5432 -Uchorke -dacademia | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 121: | Line 121: | ||
* [[Helm/PostgreSQL|PostgreSQL » Helm]] | * [[Helm/PostgreSQL|PostgreSQL » Helm]] | ||
* [[PostgreSQL]] | * [[PostgreSQL]] | ||
|valign='top' style='width:34%'| | |valign='top' style='width:34%'| | ||
| Line 147: | Line 146: | ||
|valign='top'| | |valign='top'| | ||
|} | |} | ||
Latest revision as of 01:11, 16 January 2026
cat << EXE | sudo bash
apt-get update;echo
apt list -a --upgradable
apt-get install -y pgloader
EXE
|
Migration
|
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
|
BKP_DATE_N_TIME="$(TZ=UTC-8 date +'%Y-%m-%dT%H%M')"
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-${BKP_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
| |
BKP_DATE_N_TIME="$(TZ=UTC-8 date +'%Y-%m-%dT%H%M')"
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-${BKP_DATE_N_TIME}.sql.gz
gunzip -c academia-20241010-T1010-ZP0600.sql.gz | psql -h127.0.0.1 -p5432 -Uchorke -dacademia
| |
Playground
|
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
|
References | ||
|---|---|---|