postgres pgload pgloader - ghdrako/doc_snipets GitHub Wiki

• https://pgloader.io/
• https://pgloader.readthedocs.io/en/latest/

pgLoader is a highly efficient and flexible data migration tool that simplifies the process of transferring data into PostgreSQL from a wide range of sources, including other databases (such as MySQL or SQLite), CSV files, and other structured formats. One of pgLoader's strengths is its ability to automate tasks such as schema discovery, data type conversion, and even index creation, making it an indispensable tool for database administrators and developers handling migrations.

Unlike manual migration methods, pgLoader can perform migrations in a single step, converting data and loading it into PostgreSQL with minimal user intervention. Additionally, it supports both full and incremental data loads, allowing for more streamlined database replication or updates.

load csv 
  from /tmp/geonames/allCountries.txt 
  into pgsql://appdev@/appdev 
  target table raw.geonames 
  with fields terminated by '\t', 
       fields optionally enclosed by '§', 
       fields escaped by '%', 
       truncate;

LOAD CSV
  FROM 'path/to/data.csv'
  INTO postgresql://username:password@localhost:5432/database_name
  WITH
    csv header,
    fields terminated by ','
  SET work_mem to '16MB', maintenance_work_mem to '64MB';

After creating the command file, running pgLoader is as simple as executing the following command:

pgloader command_file.load

pgLoader automatically handles schema detection, converts incompatible data types, and loads the data efficiently. It also provides options for optimizing performance, such as parallelism and bulk data loading, ensuring a smooth and fast migration process even for large datasets.