DBLink in iDempiere

We have a simple example how we can use dblink in postgresql using idempiere database in order to backup the logs table due to this table grow quickly so we'll backup old data to another database whether in the same server or another one.

 
-- create backup of the change log table

drop table ni_changelog_backup;

create table ni_changelog_backup as
select
    ch.*
from ad_changelog ch
where ch.created::date < '20220101'::date
limit 100;

-- cleaning the new table

truncate table ni_changelog_backup;

-- verify that table is empty

select ch.* from ni_changelog_backup ch;

-- verify postgresql version

select version();

-- to active dblink extension

create extension dblink;

-- transfer data from one database to another

insert into ni_changelog_backup
select
    k.*
from dblink('dbname=(dbname) port=5432 host=(servername) user=(username) password=(userpassword)',
'select
    ch.*
from ad_changelog ch
where ch.created::date < ''20220101''::date
limit 100;') as k
(
    ad_changelog_id numeric(10,0),
    ad_session_id numeric(10,0),
    ad_table_id numeric(10,0),
    ad_column_id numeric(10,0),
    ad_client_id numeric(10,0),
    ad_org_id numeric(10,0),
    isactive character(1),
    created timestamp without time zone,
    createdby numeric(10,0),
    updated timestamp without time zone,
    updatedby numeric(10,0),
    record_id numeric(10,0),
    oldvalue character varying(2000),
    newvalue character varying(2000),
    undo character(1),
    redo character(1),
    iscustomization character(1),
    trxname character varying(60),
    description character varying(255),
    eventchangelog character(1),
    ad_changelog_uu character varying(36)
);

select ch.* from ni_changelog_backup ch;