The addition of the KEEPALIVE and SWITCH WAL messages has been done in a way
that our logical transaction memory representation could hold such messages
before the BEGIN statement. Not all the code got the memo...

This column isn't part of Postgres Logical Decoding API and relying on it
would prevent pgcopydb from being compatible with other output plugins in
the future.

Also, our internal tracking of the LSN position was pretty confused at
times, using a single LSN value makes it simpler to follow our logic.

To still be able to follow WAL filename changes we introduce the SWITCH WAL
statement in our SQL files too, not just the JSON files anymore. The replay
process knows how to parse those SWITCH WAL statements.

To still be able to match our endpos with the actual LSN position in the
WAL, we introduce a new KEEPALIVE statement in our SQL and JSON files too.
The replay process knows how to parse those KEEPALIVE statements and mark
the progress on the replication origin tracking on the target database.

It fixes a corner case when --drop-if-exists is used to migrate a database that
contains no tables. Before this change, `copydb_target_drop_tables` generates an
invalid SQL query`DROP TABLE IF EXISTSCASCADE`.

This allows to manually drop the cache created with --cache option.

The apt.postgresql.org debian repository has archived its support for debian
stretch release, in a way that we need to now use
apt-archive.postgresql.org.

We could switch to using a bare debian system as the base for our docker
image here, but we would then have to provide and maintain the entry points
scripting that the official docker image is providing.

* Implement an option to cache pg_table_size() results.

In some environments computing the pg_table_size() can be quite slow, so we
might benefit from managing a cache of the table sizes that we can re-use.
This cache is implemented as the pgcopydb.table_size table on the source
database, which is created with the command pgcopydb list tables --cache.

* Filter out pgcopydb schema when listing sequences, indexes.

* Avoid using pgcopydb list tables in tests.

Because we now create a schema (and leave it behind) when running the
pgcopydb list tables command, avoid using it in the tests, specifically when
targetting the target database.

Instead, use pgcopydb list extensions, which doesn't leave objects behind.

* Update tests/cdc JSON file with the new extra transaction.

* Fix tests/follow with new pgcopydb list table skipping pgcopydb schema.

We can't use `pgcopydb list table | grep sentinel` anymore, because we
excluded the pgcopydb schema from the list commands now that we have both
the sentinel table and the table_size tables in there.

The UPDATE and DELETE statements already had double-quoting for column
names, but the INSERT statements where missing that treatment.

Co-authored-by: Ubuntu <azureuser@cpj-ubuntu-22.olnuqzujx23ulcdmjltleu4scc.cx.internal.cloudapp.net>

Postgres pg_restore is easily confused with SQL object dependencies when the
archive is filtered (either using --section or --use-list, or both). To
avoid that problem, issue a DROP TABLE IF EXISTS ... CASCADE; for all the
tables we are going to process when --drop-if-exists is in use.

The ACL and COMMENT entries that pgcopydb is interested into both have no
namespace attached to them, so we expect a " - " at this position. We could
still have to parse entries with a namespace, so we might as well refrain
from issueing WARN messages when the namespace isn't "-".

Fixes #123.

The "switch" messages don't have an XID field, so refrain from validating
the current transaction's XID when parsing WAL switch messages.

When a connection exception happens we know it's a transient error and it's
worth retrying. In scenarios with thousands of tables to COPY, being able to
just retry internally is a big help.

This reduces confusion when using the commands, when the previous behaviour
would accept any argument on the command line. Also fixes the help strings
for pgcopcydb stream sentinet set apply|prefetch command, that doesn't take
the <true|false> argument anymore (never did, really).

The docker build does `make clean install` and we want to keep the version
file around in that environment. Have a new `make maintainer-clean` entry to
remove the version file.

* Implement Logical Decoding compatibility with Postgres 9.6.

Several function and column names have changed (xlog to wal) between
Postgres major versions 9.6 and 10, and also in Postgres 9.6 creating a
logical replication slot seems to always export a (new) snapshot.

In this PR we adjust pgcopydb behavior to rely on the logical replication
protocol CREATE_REPLICATION_SLOT command to export our snapshot when the
option --follow is used and the source server is Postgres 9.6.

* Add tests/follow-9.6 to the CI.

* Add support for Postgres 9.5 and 10.

Both needed special attention, in particular in the test suite.

- split files into smaller specialised units,

  - rename "stream.c" to "ld_stream.c" to share the Logical Decoding prefix
    with other modules "ld_apply.c" and "ld_transform.c",

  - create blobs.c and extensions.c units,

  - split "table-data.c" schema preparation function into new module
    "copydb_schema.c",

  - rename utility files with the _utils prefix, like others in the same
    category.

While a CREATE INDEX command or an ALTER TABLE command is running, at the
tcp level we're entirely quiet. When that command runs for more than 2
hours, with the default Linux settings, our connection might get
interrupted. Prevent against that.

In the context of the main session that holds onto the snapshot and does
nothing, we really want to avoid that session getting killed by Postgres
because of the idle_in_transaction_session_timeout.

There is a case when we reach the endpos at startup (or restart), and we
have a JSON file around that was not transformed yet: the current LSN is not
past this file yet, and the previous time when streaming we didn't reach to
the endpos yet.

Now we need to transform that JSON file into SQL, even though we don't have
an open file pointer to call fclose() on.

- make sure to reset the FILE * pointer to NULL after closing the file.
 - use a stable sub-process to transform JSON files into SQL.

For the second point, we re-use the queuing infrastructure introduced in a
recent patch. This allows to have a single sub-process started that will
handle all the JSON to SQL transformations during replay.

* Use docker-compose run in tests, instead of docker-compose up.

In passing, also change the test build dependencies in a way that
docker-compose knows about our usage of the common pagila container. This
allows skipping useless builds of that intermediate container.

The docker-compose run command also is a better UI for our testing when
compared to using docker-compose up and then waiting until the main
container stops.

* Separate our build pgcopydb service and the test service.

* Attempt to fix GitHub worklow for tests: compute version string first.

* Review and simplify docker-compose build dependencies.

In short docker-compose does not know how to handle build time dependencies
between different containers. I suppose we would need a place to push the
intermediate images and pull them later, and we're now using the local
docker daemon for that by building the pre-requisites manually before
invoking the docker-compose build and run commands.

Change the static memory allocation to a dynamic one for the array of line
pointers. Even if we only require a sizeof(char *) (a single pointer) per
line to allocate, we might need a lot of them.

* Implement pgcopydb list extensions.

* Implement pgcopydb copy extensions.

* Add support for extension configuration in pgcopydb copy extensions.

* Fix extension listing query.

* Add a unit test covering extensions, and fix some bugs.

* Fix schema queries for sequences when filtering is involved.

* Add schema/data that requires Postgis in the extensions unit test.

* Remove extra logging that's not necessary anymore.

* Implement pgcopydb clone --skip-extensions.

This allows to skip creating extensions on the target database, which
requires superuser privileges. This could now be implemented with another
command such as pgcopydb copy extensions.

* Fix connection management for pgcopydb copy extensions.

* Logs and comments clean-up.

* Make sure we can --skip-extensions and use a non-superuser role.

The test case now uses non-superuser for the pgcopydb clone operation, and
deals with roles and extensions separately, with superuser privileges. For
that to work, we need to skip restoring the ACL and COMMENT entries for the
extensions and the schema they depend on, as that's superuser territory.

* Copy extension configuration table user-data in pgcopydb clone.

* Add a documentation section about Postgres superuser.

Add documentation for the sentinel table, and add an --json option to the
command pgcopydb stream sentinel get.

Also document that pg_dumpall --roles-only needs superuser privileges.