PostgreSQL/PgLoader: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 9: | Line 9: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
|valign='top' style='width:50%'| | |valign='top' style='width:50%'| | ||
|- | |||
|valign='top' colspan='2'| | |||
{|class='wikitable mw-collapsible mw-collapsed' | |||
!scope='col' style='text-align:left'| | |||
Diagram | |||
|- | |||
|valign='top'| | |||
<kroki lang='plantuml'> | |||
@startuml | |||
!theme plain | |||
' Styling for Transparency and Layout | |||
skinparam backgroundColor transparent | |||
skinparam DefaultFontName Helvetica | |||
skinparam componentStyle uml2 | |||
skinparam packageStyle rectangle | |||
skinparam actorStyle awesome | |||
skinparam shadowing false | |||
skinparam databaseBackgroundColor #LightBlue | |||
skinparam arrowColor #DarkSlateGray | |||
package "Source Databases" { | |||
database "MariaDB / MySQL" as MariaDB #LightYellow | |||
database "Oracle Database" as Oracle #DarkSalmon | |||
database "SQLite / MSSQL / CSV" as Others #White | |||
} | |||
node "Migration Engine" { | |||
component [pgloader] as PGL #DeepSkyBlue | |||
} | |||
database "PostgreSQL" as Postgres #SteelBlue | |||
' Connections | |||
MariaDB -down-> PGL : " Reads Schema & Data " | |||
Oracle -down-> PGL : " Data Casting " | |||
Others -down-> PGL : " ETL Transformation " | |||
PGL -down-> Postgres : " LOAD DATA (Copy Protocol) " | |||
note right of PGL | |||
**Key Features:** | |||
* Fully Automated Schema Discovery | |||
* On-the-fly Data Type Casting | |||
* Parallel Data Loading | |||
* Continuous Migration (CDC) | |||
end note | |||
@enduml | |||
</kroki> | |||
|} | |||
|} | |} | ||
Revision as of 14:49, 25 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 | ||
|---|---|---|