PostgreSQL/PgLoader: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older editNewer edit →
VisualWikitext
No edit summary
Line 1: Line 1:
<syntaxhighlight lang="bash">
{|class='wikitable' style='width:100%;margin:3px 0'
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' lang='bash'>
cat << EXE | sudo bash
cat << EXE | sudo bash
apt-get update;echo
apt-get update;echo
Line 6: Line 8:
EXE
EXE
</syntaxhighlight>
</syntaxhighlight>
|valign='top' style='width:50%'|
|}


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


|valign='top'|
|valign='top' style='width:50%'|
<syntaxhighlight lang="sql">
<syntaxhighlight style='margin:3px 0' lang='sql'>
cat << DDL | psql
cat << DDL | psql
CREATE DATABASE academia;
CREATE DATABASE academia;
Line 28: Line 35:
DDL
DDL
</syntaxhighlight>
</syntaxhighlight>
|-
|-
|colspan='2'|
|valign='top' colspan='2'|
----
<syntaxhighlight style='margin:3px 0' lang='bash'>
|-
|colspan='2'|
<syntaxhighlight lang="bash">
BACKUP_DATE_N_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')"
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
echo -n password: ;read -s MYSQL_PWD;export MYSQL_PWD; echo
Line 43: Line 46:
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>
|-
|-
|colspan='2'|
|valign='top' colspan='2'|
----
<syntaxhighlight style='margin:3px 0' lang='bash'>
|-
|colspan='2'|
<syntaxhighlight lang="bash">
cat <<MIG | pgloader -v /dev/stdin
cat <<MIG | pgloader -v /dev/stdin
LOAD DATABASE
LOAD DATABASE
Line 56: Line 55:
MIG
MIG
</syntaxhighlight>
</syntaxhighlight>
|-
|-
|colspan='2'|
|valign='top' colspan='2'|
----
<syntaxhighlight style='margin:3px 0' lang='bash'>
|-
|colspan='2'|
<syntaxhighlight lang="bash">
BACKUP_DATE_N_TIME="$(date +'%Y%m%d-T%H%M')-Z$(date +'%z'|tr '+-' 'PM')"
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
echo -n password: ;read -s PGPASSWORD;export PGPASSWORD;echo
Line 71: Line 66:
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>
|-
|colspan='2'|
----
|-
|valign='top'|
|valign='top'|
|}
|}


==Playground==
==Playground==
{|
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
|valign='top' colspan='2'|
!scope='col' style='text-align:left' colspan='2'|
<syntaxhighlight lang="bash">
Playground
|-
|valign='top' style='width:50%'|
<syntaxhighlight style='margin:3px 0' 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
</syntaxhighlight>
</syntaxhighlight>


|valign='top'|
|valign='top' style='width:50%'|
<syntaxhighlight lang="bash">
<syntaxhighlight style='margin:3px 0' lang='bash'>
pgloader -h
pgloader -h
pgloader -V
pgloader -V
</syntaxhighlight>
</syntaxhighlight>
|-
|-
|colspan='3'|
|valign='top' colspan='2'|
----
<syntaxhighlight style='margin:3px 0' lang='bash'>
|-
|colspan='3'|
<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
</syntaxhighlight>
</syntaxhighlight>
|-
|-
|colspan='3'|
|valign='top' colspan='2'|
----
<syntaxhighlight style='margin:3px 0' lang='bash'>
|-
|colspan='3'|
<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
</syntaxhighlight>
</syntaxhighlight>
|-
|-
|colspan='3'|
|valign='top'|
----
<syntaxhighlight style='margin:3px 0' lang='bash'>
|-
|colspan='3'|
<syntaxhighlight lang="bash">
cat <<MIG | pgloader -v /dev/stdin
cat <<MIG | pgloader -v /dev/stdin
LOAD DATABASE
LOAD DATABASE
Line 127: Line 107:
MIG
MIG
</syntaxhighlight>
</syntaxhighlight>
 
|valign='top'|
|}
|}


== References==
==References==
{|
{|class='wikitable mw-collapsible' style='width:100%;margin:3px 0'
|valign="top"|
!scope='col' style='text-align:left' colspan='3'|
References
|-
|valign='top' style='width:33%'|
* [https://www.digitalocean.com/community/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader PostgreSQL » PgLoader » Migrate From MySQL]
* [https://www.digitalocean.com/community/tutorials/how-to-migrate-mysql-database-to-postgres-using-pgloader PostgreSQL » PgLoader » Migrate From MySQL]
* [[PostgreSQL/PgBouncer|PostgreSQL » PgBouncer]]
* [[PostgreSQL/PgBouncer|PostgreSQL » PgBouncer]]
Line 139: Line 122:
* [[PostgreSQL]]
* [[PostgreSQL]]


|valign="top"|


|valign="top"|
|valign='top' style='width:34%'|
 
|valign='top' style='width:33%'|


|-
|-
|colspan="3"|
|valign='top'|
----
|-
|valign="top"|
* [[Academia JavaEE Workspace in Ubuntu]]
* [[Academia JavaEE Workspace in Ubuntu]]
* [[IntelliJ IDEA]]
* [[IntelliJ IDEA]]
Line 159: Line 140:
* [[K8s]]
* [[K8s]]


|valign="top"|
|valign='top'|
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
* [[K8s/Ingress|K8s » Ingress]]
* [[K8s/Ingress|K8s » Ingress]]
Line 165: Line 146:
* [[K8s/Run|K8s » Run]]
* [[K8s/Run|K8s » Run]]


|valign="top"|
|valign='top'|


|}
|}

Revision as of 00:32, 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

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

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=16116"