PGLoader

It is Postgresql’s data replication tool.
It uses the copy command. The process continues until it has copied all the data (on
error resume next).
It can also be made to stop when it receives an error (on error stop).
Writes bad records to log (reject.log) file.

Properties:
One command migration,
Continuous migration,
Schema discovery,
Partial Migration,
Data/Schema only,
PK/FK ,
Data transfer from HTTP
For details, you can visit pgloader.io page.

Example

Import of comma separated data from team.csv file into Postgresql.

Step 1

mehmed@dataera$ apt-get install pgloader

pgloader is installed. It can also be installed from the source code. Installation files and
installation guide can be obtained from github.com/dimitri/pgloader link.
Note: There is an incompatibility between pgloader 3.6.3 and the postgresql SHA-256
authentication method. The password_ encryption parameter of the user connecting
pgloader to the database need to be changed to md5.

Step 2

PGPASSWORD=”hr” psql -h 192.168.56.61 -p 5434 -d mx -U hr
create table emp
(
no integer,
isim varchar(10),
bölüm varchar(15),
maaş integer
);

The table must be created in advance.

Step 3

mehmed@dataera$ vim /data/emp/ekip.csv
100,Ahmed,Satış,5000
200,Ali,Bilgi işlem,5500
300,Mehmed,Yazılım Gel.,7000
400,Mustafa,Finans,9500
500,Ömer,ArGe,6000
501,Davud,Muhasebe,5400

Sample data is entered into the emp.csv file.

Step 4

LOAD CSV
FROM ‘/data/emp/ekip.csv’
HAVING FIELDS
(
no, isim, bölüm, maaş
)
INTO postgresql://postgres:welcome1@192.168.56.61:5434/mx
TARGET TABLE hr.ekip
TARGET COLUMNS (no, isim, bölüm, maaş)
WITH truncate,
fields terminated by ‘,’
SET work_mem to ‘32 MB’, maintenance_work_mem to ‘64 MB’;

Data source and target database connection information is entered in the emp.load file.

Step 5

mehmed@dataera:/data/emp$ pgloader ekip.load
2022-07-25T05:12:42.007000Z LOG pgloader version “3.6.3~devel”
2022-07-25T05:12:42.149000Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch 0 0 0.003s
----------------------- --------- --------- --------- --------------
“hr”.”ekip” 0 6 0.1 kB 0.025s
----------------------- --------- --------- --------- --------------
Files Processed 0 1 0.011s
COPY Threads Completion 0 2 0.025s
----------------------- --------- --------- --------- --------------
Total import time ✓ 6 0.1 kB 0.036s

The emp.load is triggered by the pgloader command.

pgloader --debug tekip.load

It can be executed in debug mode to troubleshoot error.

One-command migration from MySQL to Postgresql

pgloader mysql://kullanıcı:şifre@mysqlserver_IP/
Vtismi?useSSL=false \ postgresql://kullanıcı:şifre@
postgresserver_IP/VTismi

One-command migration from MSSQL to Postgresql

load database
from mssql://kullanıcı@mssqlserver_IP/dbname
into postgresql://kullanıcı:şifre@postgresserver_IP/
VTismi

Here are some of the options available before copying the data.

BEFORE LOAD DO
$$ create extension if not exists ip4r; $$,
$$ create schema if not exists hr; $$,
$$ create table if not exists hr.ekip
(
no integer,
isim varchar(10),
bölüm varchar(15),
maaş integer
);
$$,