Content-type: text/plain; charset=utf-8
MIME-version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\))
Subject: Re: Archive to secondary Postgres instance
From: Evan Bauer <evanbauer@mac.com>
In-reply-to: 
 <CAL1zGT4zpg2a1py9RCo9NR8qLLtth20fC9GfiXN5bUM16vaYXQ@mail.gmail.com>
Date: Sat, 01 Sep 2018 10:29:50 -0400
Cc: pgsql-admin@lists.postgresql.org
Content-transfer-encoding: quoted-printable
Message-id: <59930584-CF26-46B2-A4C9-B1AE77ED8ECC@mac.com>
References: 
 <CAL1zGT4zpg2a1py9RCo9NR8qLLtth20fC9GfiXN5bUM16vaYXQ@mail.gmail.com>
To: David Rydzewski <david.rydzewski@gmail.com>
Precedence: bulk

David,

Your approach #1 is one that has been proven for years in both commerce =
and securities trading applications.  The pitfalls in approach #2 have =
always made it too problematic for me =E2=80=94 regardless of the DBMS =
involved.

A third approach is to use a change data capture (CDC) tool.  These =
tools manage rules-based asymmetric replication between databases =E2=80=94=
 it basically provides your option #1 with a logical definition of the =
transfer, provides both seeding and catchup capabilities, but without =
having to manage a ton of replication scripts.  I=E2=80=99ve used the =
approach for large-scale financial services applications where the =
=E2=80=9Ctrade=E2=80=9D database of active transactions has to be =
incredibly fast (and therefore small with short-lived rows), and the =
"trade-history=E2=80=9D database needs to maintain years of data for =
compliance and analysis.  The asymmetric publisher to subscriber =
relationship allows for all kinds of interesting indexing and =
aggregation through materialized views in the history database that you =
could never afford in the =E2=80=9Clive=E2=80=9D OLTP database.

Talend is the commercial open source leader in this area for =
database-to-database CDC replication; my limited experience with it =
(though not with PostgreSQL) has been good.  Debezium is based on Apache =
Kafka, looks promising, but requires both an extension for PostgreSQL =
publishers and a custom application on the subscriber side.  There are a =
number of commercial products in this space as well.  IBM=E2=80=99s =
InfoSphere CDC product is the one I know best, but it is both =
proprietary and (when I last looked) had limited PostgreSQL support.  =
There is a list of other alternatives on the PostgreSQL wiki at: =
https://wiki.postgresql.org/wiki/Ecosystem:Replication

Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


> On Aug 31, 2018, at 21:57, David Rydzewski <david.rydzewski@gmail.com> =
wrote:
>=20
> I have a postgres database that is doubling in size each year and =
contains many online orders that were delivered long ago and are now =
dormant in the database.
>=20
> My desire is to offload these dormant orders to a secondary Postgres =
instance, so that the information is still available for viewing =
purchase history, while keeping the active orders in the main, and =
eventually smaller Postgres instance.
>=20
> These orders live in a highly normalized model that spans over 30 =
tables.
>=20
> I have thought of two approaches to handle this "archiving" use case.
>=20
> One approach is pull based. As orders naturally go dormant, execute =
custom SQL to move this information into the secondary postgres =
instance. A separate process would then purge this order information =
from the main database after a period of time (e.g., custom deletes). =
The upside is that the process is fairly lightweight and =
straightforward. The downside is you have to maintain two data models =
and the SQL scripts whenever the data model changes.
>=20
> Second approach is logical replication. The upside is that you don't =
have to maintain two data models separately. The downside is that it =
becomes very tricky to differentiate between a "normal" delete, which =
you would want to apply on the secondary instance versus an "archive" =
delete which you wouldn't want to apply on the secondary. It could also =
get complex when replication fails and you have to re-initiate the =
stream.
>=20
> My question is whether anyone else has solved this and if so, how? I'm =
leaning toward pull-based, because replication doesn't feel right with =
the complexity around "selective" deletes, and other replication failure =
scenarios.
>=20
> Thanks!
>=20
>=20
>=20
>=20
>=20