### Migrate DBF from HTTP to PostgreSQL Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst This example illustrates migrating data from a DBF file fetched from an HTTP location. pgloader downloads the file, unarchives it if necessary, discovers the schema, and loads the data into PostgreSQL, requiring the --type switch to specify the source format. ```shell createdb foo pgloader --type dbf http://www.insee.fr/fr/methodes/nomenclatures/cog/telechargement/2013/dbf/historiq2013.zip postgresql:///foo ``` -------------------------------- ### Example PostgreSQL Table Schema Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst Provides the SQL CREATE TABLE statement for the 'districts_longlat' table used in the pgloader CSV loading examples. Defines columns with appropriate data types. ```sql create table districts_longlat ( usps text, geoid text, aland bigint, awater bigint, aland_sqmi double precision, awater_sqmi double precision, intptlat double precision, intptlong double precision ); ``` -------------------------------- ### Migrate SQLite to PostgreSQL Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst This example demonstrates migrating a SQLite database to PostgreSQL using pgloader. It first creates a new PostgreSQL database and then uses pgloader to discover SQLite table definitions, cast data types, and migrate the data. ```shell createdb newdb pgloader ./test/sqlite/sqlite.db postgresql:///newdb ``` -------------------------------- ### PGLoader Basic Usage Examples Source: https://github.com/dimitri/pgloader/blob/master/README.md Demonstrates how to use pgloader for database migrations. Examples include migrating from SQLite to PostgreSQL and from MySQL to PostgreSQL, showcasing schema definition and parallel data loading. ```shell createdb newdb pgloader ./test/sqlite/sqlite.db postgresql:///newdb ``` ```shell createdb pagila pgloader mysql://user@localhost/sakila postgresql:///pagila ``` -------------------------------- ### Build pgloader RPM from Source Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Provides steps to build a pgloader RPM package from source on RedHat/CentOS systems. This involves setting up EPEL, installing build dependencies, and using rpmbuild. ```shell # 1. Install EPEL repo (e.g., sudo yum install epel-release) # 2. Install rpmbuild dependencies: # sudo yum -y install yum-utils rpmdevtools @"Development Tools" # 3. Install pgloader build dependencies: # sudo yum-builddep pgloader.spec # 4. Download pgloader source: # spectool -g -R pgloader.spec # 5. Build the source and binary RPMs: # rpmbuild -ba pgloader.spec ``` -------------------------------- ### Build pgloader from Source on Debian Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Installs build dependencies and then builds pgloader from source on Debian-based systems. Requires installing SBCL, unzip, and development libraries. ```shell $ apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev $ cd /path/to/pgloader $ make save $ ./build/bin/pgloader --help ``` -------------------------------- ### Build pgloader from Source on macOS Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Installs necessary tools like SBCL or CCL and Freetds via Homebrew, then builds pgloader from source. Ensures universal binaries for Freetds are installed. ```shell $ brew install sbcl $ brew install clozure-cl $ brew install freetds --universal --build-from-source $ make save $ ./build/bin/pgloader --version ``` -------------------------------- ### pgloader IXF Load File Configuration Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/ixf.rst Provides an example of the content for a pgloader load command file, specifying the IXF source, PostgreSQL target, table name, and various loading options. ```pgloader LOAD IXF FROM data/nsitra.test1.ixf INTO postgresql:///pgloader TARGET TABLE nsitra.test1 WITH truncate, create table, timezone UTC BEFORE LOAD DO $$ create schema if not exists nsitra; $$, $$ drop table if exists nsitra.test1; $$; ``` -------------------------------- ### PGLoader Installation via apt Source: https://github.com/dimitri/pgloader/blob/master/README.md Shows how to install pgloader on Debian-based systems using the apt package manager. ```shell apt-get install pgloader ``` -------------------------------- ### pgloader COPY Load Command Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/copy.rst An example of a pgloader load command file (`.load`) demonstrating how to use the COPY command to load data from a specified file into a PostgreSQL table. ```pgloader LOAD COPY FROM copy://./data/track.copy ( trackid, track, album, media, genre, composer, milliseconds, bytes, unitprice ) INTO postgresql:///pgloader TARGET TABLE track_full WITH truncate SET work_mem to '14MB', standard_conforming_strings to 'on' BEFORE LOAD DO $$ drop table if exists track_full; $$, $$ create table track_full ( trackid bigserial, track text, album text, media text, genre text, composer text, milliseconds bigint, bytes bigint, unitprice numeric ); $$; ``` -------------------------------- ### MySQL Database Setup Source: https://github.com/dimitri/pgloader/blob/master/docs/tutorial/mysql.rst Commands to create a MySQL database and load data into it, typically as a prerequisite for migration. ```bash $ mysql -u root > create database f1db; > source f1db.sql ``` -------------------------------- ### pgloader CSV Loading Example Source: https://github.com/dimitri/pgloader/blob/master/ISSUE_TEMPLATE.md Demonstrates a pgloader command file for loading CSV data into a PostgreSQL table. It specifies data encoding, delimiters, table truncation, and includes SQL commands for table creation and dropping. ```pgloader LOAD CSV FROM INLINE with encoding 'ascii' INTO postgresql:///pgloader TARGET TABLE jordane WITH truncate, fields terminated by '|', fields not enclosed, fields escaped by backslash-quote SET work_mem to '128MB', standard_conforming_strings to 'on' BEFORE LOAD DO $$ drop table if exists jordane; $$, $$ CREATE TABLE jordane ( "NOM" character(20), "PRENOM" character(20) ) $$; BORDET|Jordane BORDET|Audrey LASTNAME|"opening quote BONNIER|testprenombe~aucouptroplong JOURDAIN|héhé¶ ``` -------------------------------- ### Build pgloader with Default Settings Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Builds the pgloader application using the `make` command. This process compiles the Common Lisp code and creates the executable binary. ```Shell make ``` -------------------------------- ### Install pgloader via apt Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Installs pgloader using the apt package manager on Debian-based systems. This is a straightforward method for users who prefer pre-compiled packages. ```shell $ apt-get install pgloader ``` -------------------------------- ### Execute pgloader Command Source: https://github.com/dimitri/pgloader/blob/master/docs/tutorial/geolite.rst Example of how to run a pgloader command file from the terminal and the initial log output indicating the start of the process, parsing, and fetching the archive. ```bash $ pgloader archive.load ... LOG Starting pgloader, log system is ready. ... LOG Parsing commands from file "/Users/dim/dev/pgloader/test/archive.load" ... LOG Fetching 'http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip' ... LOG Extracting files from archive '//private/var/folders/w7/9n8v8pw54t1gngfff0lj16040000gn/T/pgloader//GeoLiteCity-latest.zip' table name read imported errors time ----------------- --------- --------- --------- -------------- download 0 0 0 11.592s extract 0 0 0 1.012s ``` -------------------------------- ### PGLoader Installation via Docker Source: https://github.com/dimitri/pgloader/blob/master/README.md Demonstrates how to pull and run the latest pgloader version using Docker. This is useful for users who want to use the most recent build from the CI. ```shell docker pull ghcr.io/dimitri/pgloader:latest docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version ``` -------------------------------- ### pgloader CSV Load Example with Inline Data Source: https://github.com/dimitri/pgloader/blob/master/docs/bugreport.rst Demonstrates loading CSV data directly from an inline source into a PostgreSQL table using pgloader. Includes setup for table creation and data transformation rules. ```pgloader LOAD CSV FROM INLINE INTO postgresql://dim@localhost/pgloader?public."HS" WITH truncate, fields terminated by '\t', fields not enclosed, fields escaped by backslash-quote, quote identifiers SET work_mem to '128MB', standard_conforming_strings to 'on', application_name to 'my app name' BEFORE LOAD DO $$ create extension if not exists hstore; $$, $$ drop table if exists "HS"; $$, $$ CREATE TABLE "HS" ( id serial primary key, kv hstore ) $$; 1 email=>foo@example.com,a=>b 2 test=>value 3 a=>b,c=>"quoted hstore value",d=>other 4 baddata ``` -------------------------------- ### pgloader DBF Load File Configuration Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/dbf.rst An example pgloader configuration file for loading data from a DBF file located at an HTTP URL. It specifies the source, target database, and custom casting rules for specific columns. ```APIDOC LOAD DBF FROM http://www.insee.fr/fr/methodes/nomenclatures/cog/telechargement/2013/dbf/reg2013.dbf INTO postgresql://user@localhost/dbname WITH truncate, create table CAST column reg2013.region to integer, column reg2013.tncc to smallint; ``` -------------------------------- ### Migrate MySQL to PostgreSQL Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst This snippet shows how to migrate data from a MySQL database to PostgreSQL. It involves creating a target PostgreSQL database and then executing a single pgloader command to handle the migration of definitions and data. ```shell createdb pagila pgloader mysql://user@localhost/sakila postgresql:///pagila ``` -------------------------------- ### Run pgloader Docker Image (DockerHub) Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Executes the pgloader command using a Docker image from DockerHub. This allows running pgloader in an isolated environment without local installation. ```shell $ docker run --rm -it dimitri/pgloader:latest pgloader --version ``` -------------------------------- ### pgloader Load Command File Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/pgsql.rst An example content for a pgloader command file (`pg.load`). It specifies the source and target database connections and includes clauses for filtering tables based on names and schemas. ```pgloader load database from pgsql://localhost/pgloader into pgsql://localhost/copy including only table names matching 'bits', ~/utilisateur/ in schema 'mysql' including only table names matching ~/geolocations/ in schema 'public' ; ``` -------------------------------- ### Create Self-Contained Binary Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Uses the Makefile to create a self-contained binary file for pgloader. The resulting executable will be located at ./build/bin/pgloader. ```makefile make save ``` -------------------------------- ### Download pgloader Source RPM Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Downloads the source RPM for pgloader using `spectool`. This command fetches the necessary source files and patches to build the pgloader package. ```Shell spectool -g -R pgloader.spec ``` -------------------------------- ### pgloader CSV Load Script Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/csv.rst An example pgloader script demonstrating advanced CSV loading configurations. It specifies the source file, encoding, field mapping, target table, and loading options like truncation and header skipping. ```pgloader LOAD CSV FROM 'GeoLiteCity-Blocks.csv' WITH ENCODING iso-646-us HAVING FIELDS ( startIpNum, endIpNum, locId ) INTO postgresql://user@localhost:54393/dbname TARGET TABLE geolite.blocks TARGET COLUMNS ( iprange ip4r using (ip-range startIpNum endIpNum), locId ) WITH truncate, skip header = 2, fields optionally enclosed by '"', fields escaped by backslash-quote, fields terminated by '\t' SET work_mem to '32 MB', maintenance_work_mem to '64 MB'; ``` -------------------------------- ### pgloader Fixed Load Command Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/fixed.rst An example of a pgloader command file (`.load`) for loading data from a fixed-width file. It specifies the data source, target table, data transformation, and pre-load SQL commands. ```pgloader LOAD FIXED FROM inline ( a from 0 for 10, b from 10 for 8, c from 18 for 8, d from 26 for 17 [null if blanks, trim right whitespace] ) INTO postgresql:///pgloader TARGET TABLE fixed ( a, b, c time using (time-with-no-separator c), d ) WITH truncate SET work_mem to '14MB', standard_conforming_strings to 'on' BEFORE LOAD DO $$ drop table if exists fixed; $$, $$ create table fixed ( a integer, b date, c time, d text ); $$; ``` -------------------------------- ### Build pgloader from Source (Legacy Makefile) Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Builds pgloader using the legacy build system, which relies on Buildapp. This command is an alternative to 'make save' for building the executable. ```shell $ make pgloader ``` -------------------------------- ### Stream Compressed CSV from HTTP to PostgreSQL Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst Streams compressed CSV data from an HTTP location through gunzip to pgloader, enabling efficient loading without intermediate file storage. Uses standard input for pgloader. ```bash curl http://pgsql.tapoueh.org/temp/2013_Gaz_113CDs_national.txt.gz \ | gunzip -c \ | pgloader --type csv \ --field "usps,geoid,aland,awater,aland_sqmi,awater_sqmi,intptlat,intptlong" \ --with "skip header = 1" \ --with "fields terminated by '\t'" \ - \ postgresql:///pgloader?districts_longlat ``` -------------------------------- ### Build Docker Image with SBCL Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Builds a Docker image for pgloader using SBCL as the default Common Lisp compiler. This command assumes you are in the pgloader source directory. ```shell $ docker build . ``` -------------------------------- ### pgloader IXF Load Command Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/ixf.rst Demonstrates the basic command structure for invoking pgloader to process an IXF file using a specified load command file. ```pgloader $ pgloader ixf.load ``` -------------------------------- ### Build pgloader from Source (Makefile) Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Builds pgloader from its source code using the provided Makefile. This method is suitable for developers or users who need the latest features or custom builds. ```shell $ make save $ ./build/bin/pgloader --help ``` -------------------------------- ### Install Universal Freetds on Mac OS X via Homebrew Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Installs the freetds library with universal binaries on Mac OS X using Homebrew. This ensures compatibility for pgloader's database connectivity. ```Shell brew install freetds --universal --build-from-source ``` -------------------------------- ### pgloader Migration Log Output Source: https://github.com/dimitri/pgloader/blob/master/docs/tutorial/mysql.rst Example output log from a pgloader migration process, showing progress, table statistics, and completion times. ```text $ pgloader mysql://root@localhost/f1db pgsql:///f1db 2017-06-16T08:56:14.064000+02:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log' 2017-06-16T08:56:14.068000+02:00 LOG Data errors in '/private/tmp/pgloader/' 2017-06-16T08:56:19.542000+02:00 LOG report summary reset table name read imported errors total time ------------------------- --------- --------- --------- -------------- fetch meta data 33 33 0 0.365s Create Schemas 0 0 0 0.007s Create SQL Types 0 0 0 0.006s Create tables 26 26 0 0.068s Set Table OIDs 13 13 0 0.012s ------------------------- --------- --------- --------- -------------- f1db.constructorresults 11011 11011 0 0.205s f1db.circuits 73 73 0 0.150s f1db.constructors 208 208 0 0.059s f1db.constructorstandings 11766 11766 0 0.365s f1db.drivers 841 841 0 0.268s f1db.laptimes 413578 413578 0 2.892s f1db.driverstandings 31420 31420 0 0.583s f1db.pitstops 5796 5796 0 2.154s f1db.races 976 976 0 0.227s f1db.qualifying 7257 7257 0 0.228s f1db.seasons 68 68 0 0.527s f1db.results 23514 23514 0 0.658s f1db.status 133 133 0 0.130s ------------------------- --------- --------- --------- -------------- COPY Threads Completion 39 39 0 4.303s Create Indexes 20 20 0 1.497s Index Build Completion 20 20 0 0.214s Reset Sequences 0 10 0 0.058s Primary Keys 13 13 0 0.012s Create Foreign Keys 0 0 0 0.000s Create Triggers 0 0 0 0.001s Install Comments 0 0 0 0.000s ------------------------- --------- --------- --------- -------------- Total import time 506641 506641 0 5.547s ``` -------------------------------- ### Install Debian Dependencies for pgloader Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Installs essential packages for pgloader on Debian-based systems, including SBCL, git, curl, and development libraries. These are required for building the Common Lisp application. ```Shell sudo apt-get install -y sbcl \ git curl patch unzip \ devscripts pandoc \ libsqlite3-dev \ freetds-dev ``` -------------------------------- ### Run pgloader Docker Image (GitHub CR) Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Pulls and runs the pgloader Docker image from GitHub Container Registry, typically built from the main branch. This provides access to the latest development versions. ```shell $ docker pull ghcr.io/dimitri/pgloader:latest $ docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version $ docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --help ``` -------------------------------- ### Build pgloader RPMs Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Builds both source and binary RPM packages for pgloader using `rpmbuild`. This command compiles the Common Lisp code and packages it for distribution. ```Shell rpmbuild -ba pgloader.spec ``` -------------------------------- ### Install RPM Build Dependencies on Redhat/CentOS Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Installs tools required for building RPM packages on Red Hat or CentOS systems, including yum-utils and development tools. This is a prerequisite for building pgloader RPMs. ```Shell sudo yum -y install yum-utils rpmdevtools @"Development Tools" ``` -------------------------------- ### Install pgloader Build Dependencies via yum Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Uses `yum-builddep` to install dependencies required specifically for building pgloader from its spec file on Red Hat/CentOS. This command reads the spec file to determine necessary packages. ```Shell sudo yum-builddep pgloader.spec ``` -------------------------------- ### Install SBCL and Clojure CL on Mac OS X via Homebrew Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Installs the Steel Bank Common Lisp (SBCL) and Clojure CL compilers on Mac OS X using the Homebrew package manager. These are required for building pgloader. ```Shell brew install sbcl brew install clozure-cl ``` -------------------------------- ### pgloader Load Command File Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/sqlite.rst An example content for a pgloader load command file. It specifies the source (SQLite) and target (PostgreSQL) databases, along with various 'WITH' options to control schema creation, data loading, and index management. ```pgloader load database from sqlite:///Users/dim/Downloads/lastfm_tags.db into postgresql:///tags with include drop, create tables, create indexes, reset sequences set work_mem to '16MB', maintenance_work_mem to '512 MB'; ``` -------------------------------- ### Run pgloader from Docker Container Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Runs a pgloader Docker container and executes a command within it to check the pgloader version. The container is removed after execution. ```Shell docker run --rm --name pgloader pgloader:debian bash -c "pgloader --version" ``` -------------------------------- ### Load CSV to PostgreSQL via Command Line Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst Loads data from a CSV file into a pre-existing PostgreSQL table using command-line arguments. Specifies CSV type, fields, termination, and target PostgreSQL URI. ```bash pgloader --type csv \ --field id --field field \ --with truncate \ --with "fields terminated by ','" \ ./test/data/matching-1.csv \ postgres:///pgloader?tablename=matching ``` -------------------------------- ### Build Binary with Custom Memory Size Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Builds a pgloader binary with a custom dynamic memory size allocation. This option is supported by the legacy build system and allows specifying memory in megabytes. ```makefile make DYNSIZE=8192 pgloader ``` -------------------------------- ### Compile SBCL from Source Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Compiles the Steel Bank Common Lisp (SBCL) compiler from its source code. This command includes options for core compression and threading support. ```Shell sh make.sh --with-sb-core-compression --with-sb-thread ``` -------------------------------- ### Build Docker Image with CCL Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Builds a Docker image for pgloader using Clozure CL (CCL) as the Common Lisp compiler. This requires specifying a custom Dockerfile. ```shell $ docker build -f Dockerfile.ccl . ``` -------------------------------- ### pgloader Archive Load Command Example Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/archive.rst Demonstrates a complete pgloader command file for loading data from a ZIP archive. It specifies the archive source, database target, pre-load SQL execution, and detailed CSV loading configurations for multiple files within the archive. ```pgloader LOAD ARCHIVE FROM /Users/dim/Downloads/GeoLiteCity-latest.zip INTO postgresql:///ip4r BEFORE LOAD DO $$ create extension if not exists ip4r; $$, $$ create schema if not exists geolite; $$, EXECUTE 'geolite.sql' LOAD CSV FROM FILENAME MATCHING ~/GeoLiteCity-Location.csv/ WITH ENCODING iso-8859-1 ( locId, country, region null if blanks, city null if blanks, postalCode null if blanks, latitude, longitude, metroCode null if blanks, areaCode null if blanks ) INTO postgresql:///ip4r?geolite.location ( locid,country,region,city,postalCode, location point using (format nil "(~a,~a)" longitude latitude), metroCode,areaCode ) WITH skip header = 2, fields optionally enclosed by '"', fields escaped by double-quote, fields terminated by ',' AND LOAD CSV FROM FILENAME MATCHING ~/GeoLiteCity-Blocks.csv/ WITH ENCODING iso-8859-1 ( startIpNum, endIpNum, locId ) INTO postgresql:///ip4r?geolite.blocks ( iprange ip4r using (ip-range startIpNum endIpNum), locId ) WITH skip header = 2, fields optionally enclosed by '"', fields escaped by double-quote, fields terminated by ',' FINALLY DO $$ create index blocks_ip4r_idx on geolite.blocks using gist(iprange); $$; ``` -------------------------------- ### pgloader Pre/Post SQL Execution Source: https://github.com/dimitri/pgloader/blob/master/docs/index.rst pgloader allows the execution of custom SQL commands before or after the migration process, or specifically after the schema has been created. This is useful for setup or cleanup tasks. ```APIDOC Pre/Post SQL commands, Post-Schema SQL commands: While pgloader takes care of rewriting the schema to PostgreSQL expectations, and even provides user-defined data type casting rules support to that end, sometimes it is necessary to add some specific SQL commands around the migration. It's of course supported right from pgloader itself, without having to script around it. ``` -------------------------------- ### Basic pgloader Command Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/mysql.rst Example of a simple pgloader command to migrate data from a MySQL database to a PostgreSQL database using connection URLs. ```shell $ pgloader mysql://myuser@myhost/dbname pgsql://pguser@pghost/dbname ``` -------------------------------- ### Build Binary with CCL Source: https://github.com/dimitri/pgloader/blob/master/docs/install.rst Compiles a self-contained binary for pgloader using Clozure CL (CCL) instead of the default SBCL. This requires specifying the CL variable in the make command. ```makefile make CL=ccl64 save ``` -------------------------------- ### Load CSV from STDIN to PostgreSQL Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst Loads CSV data from standard input to PostgreSQL, useful for piping data. Includes options to specify fields, skip headers, and set field terminators. ```bash pgloader --type csv \ --field "usps,geoid,aland,awater,aland_sqmi,awater_sqmi,intptlat,intptlong" \ --with "skip header = 1" \ --with "fields terminated by '\t'" \ - \ postgresql:///pgloader?districts_longlat \ < test/data/2013_Gaz_113CDs_national.txt ``` -------------------------------- ### Load CSV from HTTP to PostgreSQL Source: https://github.com/dimitri/pgloader/blob/master/docs/quickstart.rst Loads CSV data directly from an HTTP URL into PostgreSQL. Supports specifying fields, skipping headers, and handling different field terminators for remote files. ```bash pgloader --type csv \ --field "usps,geoid,aland,awater,aland_sqmi,awater_sqmi,intptlat,intptlong" \ --with "skip header = 1" \ --with "fields terminated by '\t'" \ http://pgsql.tapoueh.org/temp/2013_Gaz_113CDs_national.txt \ postgresql:///pgloader?districts_longlat ``` -------------------------------- ### Build pgloader for Low RAM Environments Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Builds the pgloader application with a specified dynamic memory size limit for low RAM environments. The DYNSIZE variable controls the maximum RAM usage in MB. ```Shell make DYNSIZE=1024 ``` -------------------------------- ### pgloader Migration Command File Source: https://github.com/dimitri/pgloader/blob/master/docs/tutorial/mysql.rst An example of a pgloader command file defining a database migration from MySQL to PostgreSQL, including options like dropping existing tables and creating indexes. ```APIDOC load database from mysql://root@localhost/sakila into postgresql:///sakila WITH include drop, create tables, no truncate, create indexes, reset sequences, foreign keys ``` -------------------------------- ### pgloader Version Placeholder Source: https://github.com/dimitri/pgloader/blob/master/ISSUE_TEMPLATE.md A placeholder for users to input the specific version of pgloader they are using. This is crucial for diagnosing version-specific issues. ```text ``` -------------------------------- ### Basic pgloader Command Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/sqlite.rst This is the simplest command-line example for pgloader, instructing it to load data from a SQLite file into a PostgreSQL database. It supports automatic schema discovery and index building. ```shell $ pgloader sqlite:///path/to/file.db pgsql://pguser@pghost/dbname ``` -------------------------------- ### Restart PostgreSQL Server Source: https://github.com/dimitri/pgloader/wiki/Convert-from-MySQL-with-Docker-installation Restarts the PostgreSQL server service. This action is necessary to apply changes made to configuration files like postgresql.conf and pg_hba.conf. ```bash systemctl restart postgresql.service ``` -------------------------------- ### Build pgloader Docker Image Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Builds a Docker image for pgloader using the provided Dockerfile. The image is tagged as 'pgloader:debian' and can be used to run pgloader in a containerized environment. ```Shell docker build -t pgloader:debian . ``` -------------------------------- ### pgloader Command Output Placeholder Source: https://github.com/dimitri/pgloader/blob/master/ISSUE_TEMPLATE.md A placeholder where users should paste the exact output generated by running the pgloader command. This helps in analyzing errors and unexpected behavior. ```text PASTE HERE THE OUTPUT OF THE PGLOADER COMMAND ``` -------------------------------- ### Get Host IP Address from Docker Container Source: https://github.com/dimitri/pgloader/wiki/Convert-from-MySQL-with-Docker-installation Retrieves the IP address of the host machine from within a running Docker container. This is often necessary for database connections when running services in Docker. ```bash docker run -it --rm alpine /sbin/ip route|awk '/default/ { print $3 }' ``` -------------------------------- ### Build pgloader Disabling Core Compression Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Builds the pgloader application while explicitly disabling SBCL's core compression feature. This can be used to control the size of the generated binary. ```Shell make COMPRESS_CORE=no ``` -------------------------------- ### Build pgloader with CCL Compiler Source: https://github.com/dimitri/pgloader/blob/master/INSTALL.md Builds the pgloader application using the Clojure CL (CCL) Common Lisp compiler instead of SBCL. This is specified by setting the CL variable in the make command. ```Shell make CL=ccl ``` -------------------------------- ### Basic pgloader Command Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/pgsql.rst The simplest command-line example for pgloader to load data from a source PostgreSQL database to a target PostgreSQL database. This command relies on default settings for the migration process. ```shell $ pgloader pgsql://user@source/dbname pgsql://user@target/dbname ``` -------------------------------- ### Run pgloader with Local Command File Source: https://github.com/dimitri/pgloader/wiki/Convert-from-MySQL-with-Docker-installation Executes pgloader using a command file located on the host machine. It utilizes Docker bind mounts to make the local file accessible within the container. ```bash docker run --security-opt seccomp=unconfined \ --rm \ -v /Users/xyz:/mnt \ --name pgloader \ dimitri/pgloader:latest pgloader /mnt/command-file.txt ``` -------------------------------- ### Display pgloader Help Source: https://github.com/dimitri/pgloader/blob/master/bundle/README.md Executes the pgloader binary with the --help flag to show available command-line options and usage instructions. ```shell ./bin/pgloader --help ``` -------------------------------- ### pgloader with Load Command File Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/sqlite.rst An example of using pgloader with a dedicated load command file for more complex migration configurations. This approach allows for detailed control over the loading process. ```shell $ pgloader db.load ``` -------------------------------- ### pgloader Command Line Usage Source: https://github.com/dimitri/pgloader/blob/master/docs/pgloader.rst Demonstrates the basic invocation of pgloader, either by specifying command files or by providing connection strings directly on the command line. ```bash pgloader commands.load pgloader SOURCE TARGET ``` -------------------------------- ### SQL Schema Setup for Geolite Data Source: https://github.com/dimitri/pgloader/blob/master/docs/tutorial/geolite.rst SQL commands executed before data loading. This includes creating the ip4r PostgreSQL extension, the geolite schema, and the location and blocks tables with appropriate columns and data types for storing geolocation information. ```sql create extension if not exists ip4r; create schema if not exists geolite; create table if not exists geolite.location ( locid integer primary key, country text, region text, city text, postalcode text, location point, metrocode text, areacode text ); create table if not exists geolite.blocks ( iprange ip4r, locid integer ); drop index if exists geolite.blocks_ip4r_idx; truncate table geolite.blocks, geolite.location cascade; ``` -------------------------------- ### Run pgloader with Database Connection Strings Source: https://github.com/dimitri/pgloader/wiki/Convert-from-MySQL-with-Docker-installation Executes the pgloader command within a Docker container, specifying source MySQL and target PostgreSQL database connection strings. It uses a specific security option and maps the container to the host. ```bash docker run --security-opt seccomp=unconfined \ --rm \ --name pgloader \ dimitri/pgloader:latest pgloader \ mysql://user:pass@/dbname postgresql://user:pass@/dbname ``` -------------------------------- ### pgloader MS SQL Load Command File Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/mssql.rst Example of a pgloader command file for MS SQL to PostgreSQL migration. This file specifies the source and target databases, schema inclusion, and configuration settings. ```pgloader load database from mssql://user@host/dbname into postgresql:///dbname including only table names like 'GlobalAccount' in schema 'dbo' set work_mem to '16MB', maintenance_work_mem to '512 MB' before load do $$ drop schema if exists dbo cascade; $$; ``` -------------------------------- ### Execute pgloader command and view logs Source: https://github.com/dimitri/pgloader/blob/master/docs/tutorial/dBase.rst Demonstrates how to execute a pgloader command file from the shell and shows a sample of the log output generated during the process, including download, extraction, table creation, and data import statistics. ```shell $ pgloader dbf-zip.load ... LOG Starting pgloader, log system is ready. ... LOG Parsing commands from file "/Users/dim/dev/pgloader/test/dbf-zip.load" ... LOG Fetching 'http://www.insee.fr/fr/methodes/nomenclatures/cog/telechargement/2013/dbf/historiq2013.zip' ... LOG Extracting files from archive '//private/var/folders/w7/9n8v8pw54t1gngfff0lj16040000gn/T/pgloader//historiq2013.zip' table name read imported errors time ----------------- --------- --------- --------- -------------- download 0 0 0 0.167s extract 0 0 0 1.010s create, truncate 0 0 0 0.071s ----------------- --------- --------- --------- -------------- historiq2013 9181 9181 0 0.658s ----------------- --------- --------- --------- -------------- Total import time 9181 9181 0 1.906s ``` -------------------------------- ### Example CREATE TABLE Statement with Added Distribution Column Source: https://github.com/dimitri/pgloader/blob/master/docs/ref/pgsql-citus-target.rst Illustrates how pgloader modifies a table schema during migration to Citus. This example shows the 'impressions' table after pgloader has added the 'company_id' column as the first column, preparing it for distribution. ```pgsql CREATE TABLE "public"."impressions" ( company_id bigint, "id" bigserial, "ad_id" bigint default NULL, "seen_at" timestamp with time zone default NULL, "site_url" text default NULL, "cost_per_impression_usd" numeric(20,10) default NULL, "user_ip" inet default NULL, "user_data" jsonb default NULL ); ``` -------------------------------- ### pgloader Command Line Options Source: https://github.com/dimitri/pgloader/blob/master/docs/pgloader.rst This section details the command-line options for pgloader, allowing direct configuration of data loading operations without a separate command file. Options cover source/target specification, data type forcing, encoding, pre/post-load SQL execution, and debugging. ```APIDOC pgloader Command Line Operations: General Options: --verbose Enables verbose logging. --debug Enables debug logging. Combining --verbose and --debug is equivalent to --client-min-messages data for maximum debug information. Source and Target Configuration: --with