PostgreSQL/PgLoader: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older edit
VisualWikitext
No edit summary
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
{|class='wikitable' style='width:100%;margin:3px 0'
{|class='wikitable'
|valign='top' style='width:50%'|
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
cat << EXE | sudo bash
cat << EXE | sudo bash
apt-get update;echo
apt-get update;echo
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>
|}
|}
|}


==Migration==
==Migration==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
!scope='col' style='text-align:left' colspan='2'|
Migration
Migration
|-
|-
|valign='top' style='width:50%'|
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
cat << DDL | mariadb
cat << DDL | mariadb
CREATE DATABASE IF NOT EXISTS academia;
CREATE DATABASE IF NOT EXISTS academia;
Line 27: Line 79:


|valign='top' style='width:50%'|
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='sql'>
<syntaxhighlight lang='sql'>
cat << DDL | psql
cat << DDL | psql
CREATE DATABASE academia;
CREATE DATABASE academia;
Line 37: Line 89:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
BKP_DATE_N_TIME="$(TZ=UTC-8 date +'%Y-%m-%dT%H%M')"
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
Line 48: Line 100:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
cat <<MIG | pgloader -v /dev/stdin
cat <<MIG | pgloader -v /dev/stdin
LOAD DATABASE
LOAD DATABASE
Line 57: Line 109:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
BKP_DATE_N_TIME="$(TZ=UTC-8 date +'%Y-%m-%dT%H%M')"
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
Line 69: Line 121:


==Playground==
==Playground==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='2'|
!scope='col' style='text-align:left' colspan='2'|
Playground
Playground
|-
|-
|valign='top' style='width:50%'|
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -h
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -h
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -V
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader -V
Line 80: Line 132:


|valign='top' style='width:50%'|
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
pgloader -h
pgloader -h
pgloader -V
pgloader -V
Line 86: Line 138:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
pgloader ./sqlite/academia.db pgsql:///academia
pgloader ./sqlite/academia.db pgsql:///academia
pgloader ./sqlite/academia.db pgsql://chorke:sadaqah!@localhost/academia
pgloader ./sqlite/academia.db pgsql://chorke:sadaqah!@localhost/academia
Line 92: Line 144:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql:///academia
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql:///academia
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql://chorke:sadaqah!@localhost/academia
pgloader mysql://chorke:sadaqah!@localhost/academia pgsql://chorke:sadaqah!@localhost/academia
Line 98: Line 150:
|-
|-
|valign='top'|
|valign='top'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
<syntaxhighlight lang='bash'>
cat <<MIG | pgloader -v /dev/stdin
cat <<MIG | pgloader -v /dev/stdin
LOAD DATABASE
LOAD DATABASE
Line 111: Line 163:


==References==
==References==
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
{|class='wikitable mw-collapsible'
!scope='col' style='text-align:left' colspan='3'|
!scope='col' style='text-align:left' colspan='3'|
References
References
Line 121: Line 173:
* [[Helm/PostgreSQL|PostgreSQL » Helm]]
* [[Helm/PostgreSQL|PostgreSQL » Helm]]
* [[PostgreSQL]]
* [[PostgreSQL]]


|valign='top' style='width:34%'|
|valign='top' style='width:34%'|
Line 147: Line 198:


|valign='top'|
|valign='top'|
|}
|}

Latest revision as of 14:55, 25 January 2026

cat << EXE | sudo bash
apt-get update;echo
apt list -a --upgradable
apt-get install -y pgloader
EXE

Diagram

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

Retrieved from "https://wiki.chorke.org/index.php?title=PostgreSQL/PgLoader&oldid=16448"