Pgloader – migrate your data from any SQL database into PostgreSQL
source link: https://github.com/dimitri/pgloader
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
PGLoader
pgloader is a data loading tool for PostgreSQL, using the COPY
command.
Its main advantage over just using COPY
or \copy
, and over using a Foreign Data Wrapper
, is its transaction behaviour, where pgloader
will keep a separate file of rejected data, but continue trying to copy
good data in your database.
The default PostgreSQL behaviour is transactional, which means that any erroneous line in the input data (file or remote database) will stop the entire bulk load for the table.
pgloader also implements data reformatting, a typical example of that
being the transformation of MySQL datestamps 0000-00-00
and 0000-00-00 00:00:00
to PostgreSQL NULL
value (because our calendar
never had a year zero
).
Versioning
pgloader version 1.x is quite old and was developed in TCL
.
When faced with maintaining that code, the new emerging development
team (hi!) picked python
instead because that made sense at the
time. So pgloader version 2.x was written in python.
The current version of pgloader is the 3.x series, which is written in Common Lisp for better development flexibility, runtime performance, and support of real threading.
The versioning is now following the Emacs model, where any X.0 release
number means you're using a development version (alpha, beta, or release
candidate). The next stable versions are going to be 3.1
then 3.2
etc.
When using a development snapshot rather than a released version the version number includes the git hash (in its abbreviated form):
-
pgloader version "3.0.99"
Release candidate 9 for pgloader version 3.1, with a git tag named
v3.0.99
so that it's easy to checkout the same sources as the released code. -
pgloader version "3.0.fecae2c"
Development snapshot again git hash
fecae2c
. It's possible to have the same sources on another setup with using the git commandgit checkout fecae2c
. -
pgloader version "3.1.0"
Stable release.
LICENCE
pgloader is available under The PostgreSQL Licence .
INSTALL
You can install pgloader directly from apt.postgresql.org and from official debian repositories, see packages.debian.org/pgloader .
$ apt-get install pgloader
You can also use a docker image for pgloader at https://hub.docker.com/r/dimitri/pgloader/ :
$ docker pull dimitri/pgloader $ docker run --rm --name pgloader dimitri/pgloader:latest pgloader --version $ docker run --rm --name pgloader dimitri/pgloader:latest pgloader --help
Build from sources
pgloader is now a Common Lisp program, tested using the SBCL (>= 1.2.5) and Clozure CL implementations with Quicklisp .
When building from sources, you should always build from the current git HEAD
as it's basically the only source that is managed in a way to ensure
it builds aginst current set of dependencies versions.
Building from sources on debian
$ apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev $ cd /path/to/pgloader $ make pgloader $ ./build/bin/pgloader --help
Building from sources on macOS
When using brew
, it should be a simple brew install --HEAD pgloader
.
When using macports , then we have a situation to deal with with shared objects pgloader depends on, as reported in issue #161 at https://github.com/dimitri/pgloader/issues/161#issuecomment-201162647 :
I was able to get a clean build without having to disable compression after symlinking /usr/local/lib to /opt/local/lib. Note that I did not have anything installed to /usr/local/lib so I didn't lose anything here.
Building from sources on Windows
Building pgloader on Windows is supported, thanks to Common Lisp implementations being available on that platform, and to the Common Lisp Standard for making it easy to write actually portable code.
It is recommended to have a look at the issues labelled with Windows support if you run into trouble when building pgloader:
https://github.com/dimitri/pgloader/issues?utf8=✓&q=label%3A%22Windows%20support%22%20
Building Docker image from sources
You can build a Docker image from source using SBCL by default:
$ docker build .
Or Clozure CL (CCL):
$ docker build -f Dockerfile.ccl .
More options when building from source
The Makefile
target pgloader
knows how to produce a Self Contained
Binary file for pgloader, found at ./build/bin/pgloader
:
$ make pgloader
By default, the Makefile
uses SBCL
to compile your
binary image, though it's possible to build using CCL
.
$ make CL=ccl pgloader
If using SBCL
and it supports core compression, the make process will
use it to generate a smaller binary. To force disabling core
compression, you may use:
$ make COMPRESS_CORE=no pgloader
The --compress-core
is unique to SBCL, so not used when CC
is different
from the sbcl
value.
You can also tweak the default amount of memory that the pgloader
image
will allow itself using when running through your data (don't ask for more
than your current RAM tho):
$ make DYNSIZE=8192 pgloader
The make pgloader
command when successful outputs a ./build/bin/pgloader
file for you to use.
Usage
You can either give a command file to pgloader or run it all from the command line, see the pgloader quick start on https://pgloader.readthedocs.io for more details.
$ ./build/bin/pgloader --help $ ./build/bin/pgloader <file.load>
For example, for a full migration from SQLite:
$ createdb newdb $ pgloader ./test/sqlite/sqlite.db postgresql:///newdb
Or for a full migration from MySQL, including schema definition (tables, indexes, foreign keys, comments) and parallel loading of the corrected data:
$ createdb pagila $ pgloader mysql://user@localhost/sakila postgresql:///pagila
Recommend
-
108
Page not found · GitHub · GitHub The 'liuyuanyuan/FantasticPostgres' repository doesn't contain the 'migrate/pgloader_intro.md' path in 'master'.
-
4
# How to migrate your MySQL database to the cloud
-
2
pgloader loads data into PostgreSQL and allows you to implement Continuous Migration from your current database to PostgreSQL. Read the White...
-
3
pgloader介绍 pgloader是一个数据导入工具,使用COPY命令将数据导入到PostgreSQL。pgloader有两种工作模式,一种是从文件导入,一种是迁移数据库。pgloader在两种情况下都使用PostgreSQL的COPY协议高效的传输数据。 openGau...
-
4
Using Heroku, it’s quick and easy to add a PostgreSQL database to your application, but over time these Heroku add-ons may not fit your needs. That’s where Amazon’s
-
0
How to migrate data to a distributed database with ShardingSphere Apache ShardingSphere's new elastic migration feature lets you...
-
0
Migrating from MySQL to PostgreSQL Using pgloader December 18, 2023 Robert Bernier These days, there’s been a lot of talk in th...
-
3
Ubuntu Manpage: pgloader xenial (
-
1
PGLoader pgloader is a data loading tool for PostgreSQL, using the COPY command. Its main advantage over just using COPY or \copy, and over using a Foreign Data Wrapper, is its transa...
-
6
Command Line pgloader loads data from various sources into PostgreSQL. It can transform the data it rea...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK