Monday, July 2, 2018

pgquarrel 0.4 released

A new pgquarrel version was released. When I started to develop pgquarrel I wanted a software that was: (i) PostgreSQL-centric, (ii) supports different PostgreSQL versions, (iii) does not depend on other software, (iv) run faster, (v) multiplatform, and (vi) flexible. There were a lot of migration tools out there but all of them are generic (it couldn't show me that a reloption was changed or that a new domain was created). After another release, the light in the end of the tunnel seems closer than when I started to code.

pgquarrel supports the most important objects:
  • tables;
  • indexes;
  • functions;
  • views;
  • materialized views;
  • schemas;
  • sequences;
  • triggers;
  • types;
  • domains.
It also supports PostgreSQL-centric objects such as:
  • extensions;
  • rules;
  • event triggers;
  • text search;
  • operators;
  • security labels.
This new release adds support for partitioning, typed tables, and extended statistics. It adds options to select what kind of objects you want to compare. It also adds a bunch of command line options that turns pgquarrel script-friendly (it is no longer necessary to create a configuration file).

$ pgquarrel --source-dbname=dev --target-dbname=prod --source-host=192.168.0.8 --target-host=192.168.0.6 --function=false --comment=true
--
-- pgquarrel 0.4.0
-- quarrel between 9.1.24 and 10.4
--

CREATE DOMAIN public.cep AS text
    CONSTRAINT cep_check CHECK ((VALUE ~ '^\d{5}-d{3}$'::text));

CREATE TABLE public.bar (
description character varying(30) NOT NULL,
foo_id integer,
id integer NOT NULL
);

ALTER TABLE ONLY public.bar
    ADD CONSTRAINT bar_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.bar
    ADD CONSTRAINT bar_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foo(id);

COMMENT ON TABLE public.bar IS 'this is another table';

COMMENT ON COLUMN public.bar.description IS 'this is a column';

ALTER TABLE ONLY public.foo DROP COLUMN removeit;

ALTER TABLE public.foo RESET (autovacuum_enabled);

If you have any questions, suggestions or bugs, open an issue or send a pull request.

Thanks to Rafał Cieślak and  Gilles Darold for their contributions.

Saturday, March 31, 2018

wal2json 1.0 released

wal2json 1.0 was released! I started to develop wal2json more than 4 years ago. At that time it was just a proof of concept for logical decoding. It was one of the first logical decoding plugins and maybe one of the most popular (because CDC solutions -- such as Kafka -- became popular and it is supported by Amazon RDS since 9.5).

It works on PostgreSQL 9.4+ and in all PostgreSQL supported platforms. Although we don't have packages for it yet, it is easier to install.

It has some parameters to control what it outputs and its behavior. You can choose to include or exclude:
  • transaction id;
  • transaction timestamp;
  • table schema;
  • type names (and also type modifier);
  • type oids;
  • not null information;
  • end of transaction LSN;
  • unchanged TOAST columns.
There is also options to:
  • pretty print output (indentation and formatting);
  • write after each row instead of after each transaction (useful for big transactions because postgres has memory limitations -- 1 GB).
And the last features I implemented:
  • filter tables (don't send rows from those tables);
  • choose tables (send only rows from those tables).
 If you have any questions, suggestions or bugs, open an issue or send a pull request.

Thanks to Daniele Varrazzo, Robert Coup and David Fettter for their contributions.