Databases for PostgreSQL come destinazione di replica logica

IBM Cloud® Databases for PostgreSQL supporta la replica logica, in cui è possibile creare un sottoscrittore o un editore. È inoltre possibile impostare l' PostgreSQL e esterno come editore e l'implementazione dell' Databases for PostgreSQL e come sottoscrittore, nonché replicare i dati da un database esterno nell'implementazione.

La replica logica è disponibile solo nelle distribuzioni che utilizzano la versione 10 o successive dell' PostgreSQL. I link alla documentazione di PostgreSQL rimandano alla versione attuale di PostgreSQL. Se hai bisogno della documentazione relativa a una versione specifica, puoi trovare i link alle diverse versioni di PostgreSQL nella pagina della documentazione di PostgreSQL.

Configurazione dell'editore

L'istanza esterna PostgreSQL è l'editore e deve essere configurata affinché la distribuzione Databases for PostgreSQL si connetta e sia in grado di estrarre correttamente i dati.

Funzioni dell'editore

create_publisher

In ogni database è possibile creare più pubblicazioni per pubblicare le modifiche a un diverso abbonato.

Arguments:

    publisher_name               The Unique name of publisher.
    for table                    To publish single/list of tables
    for all table                To publish all tables, along with future tables.
    for all tables in schema     To publish all tables in schema, along wth future tables.

Usage:
    exampledb=> CREATE PUBLICATION my_publication;
    CREATE PUBLICATION

list_publisher

Elenca il numero di pubblicazioni presenti in ciascun database.

Usage:
   exampledb=# select * from pg_publication;
   oid  |    pubname     | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
   -------+----------------+----------+--------------+-----------+-----------+-----------+-------------
   16401 | my_publication |       10 | t            | t         | t         | t         | t

alter_publisher

Modificare la definizione della pubblicazione.

Syntax:
ALTER PUBLICATION name ADD publication_object [, ...]
ALTER PUBLICATION name SET publication_object [, ...]
ALTER PUBLICATION name DROP publication_object [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name


 Usage:
    exampledb=# ALTER PUBLICATION my_publication RENAME TO my_publication_new;
    ALTER PUBLICATION

drop_publisher

Rimuovere la pubblicazione non in uso.

Usage:
    exampledb=# drop publication my_publication_new;
    DROP PUBLICATION

Considera i seguenti prerequisiti:

Configurare l'esterno (editore) PostgreSQL wal_level=logical.
Ogni tabella selezionata per la replica deve contenere una chiave primaria o avere REPLICA IDENTITY.
Il tuo PostgreSQL esterno (editore) deve avere un utente di replica che abbia il privilegio PostgreSQL REPLICATION e tale utente deve avere i privilegi SELECT e USAGE sui database insieme ai privilegi REPLICATION.
L' PostgreSQL e da cui stai effettuando la replica deve avere le funzionalità " TLS " e " SSL " abilitate.
Fornire privilegi BYPASSRLS all'utente esterno (publisher) di replica dell' PostgreSQL, se è abilitata la sicurezza a livello di riga.

La replica logica presenta alcuni limiti intrinseci e alcune restrizioni, descritti nella documentazione di PostgreSQL. Esaminali prima di decidere che la replica logica è appropriata per il tuo caso d'uso.

Configurazione dell'abbonato

Per configurare la distribuzione Databases for PostgreSQL e assicurarsi che i dati siano replicati correttamente, verificare quanto segue.

È necessario creare un database nell'installazione client con lo stesso nome del database che si intende replicare.
La replica logica funziona a livello di tabella, quindi ogni tabella selezionata per la pubblicazione deve essere creata nel subscriber prima di avviare il processo di replica logica. (Potete usare pg_dump per aiutarvi) Non è necessario che la tabella del sottoscrittore sia identica a quella dell'editore. Tuttavia, la tabella del sottoscrittore deve contenere almeno tutte le colonne presenti nella tabella dell'editore. Le colonne aggiuntive presenti nel sottoscrittore non devono avere vincoli NOT NULL o di altro tipo. In caso contrario, la replica fallisce.

I comandi nativi di PostgreSQL richiedono i privilegi di superutente, che non sono disponibili nelle distribuzioni Databases for PostgreSQL. L'installazione comprende invece una serie di funzioni che possono essere utilizzate per impostare e gestire la replica logica per la sottoscrizione.

Solo l'utente amministratore fornito da Databases for PostgreSQL dispone dei permessi necessari per eseguire i seguenti comandi di replica, che consentono di sottoscrivere e replicare contenuti da un editore esterno di PostgreSQL.

Funzioni dell'abbonato

create_subscription

Arguments:
    subscription_name   Unique name to create the subscription channel with
    host_ip             Publisher hostname or public IP address
    port                Port number publisher is running on
    password            Password of the `admin` user on the publisher
    username            `admin` user created on the publisher
    db_name             The name of the database to be replicated
    publisher_name      The name of publisher channel on the publisher

    These additional configuration options are only supported in PostgreSQL 17 and later.
    copy_data           The copy_data parameter (true or false, default: true)
                        determines whether the initial data from the publication
                        should be copied to the subscriber when the subscription
                        is created.
    origin              The origin parameter (ANY or NONE, default: NONE) allows
                        filtering of changes based on their origin. It helps exclude
                        or include changes coming from specific nodes in multi-node or
                        cascading replication setups.
    failover            The failover parameter (true or false, default: false) enables
                        support for automatic failover of the subscription between replicated
                        nodes, ensuring continuity during primary node transitions.

Usage:
    exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','password','admin','exampledb','my_publication');

    PostgreSQL 17 and later
    exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','password','admin','exampledb','my_publication'  'true',  'ANY' , 'true');

delete_subscription

Arguments:
    subscription_name   Name the subscription channel to delete
    db_name             The name of the replicated database

Usage:
exampledb=> SELECT delete_subscription('subs1', 'exampledb');

list_subscriptions

Arguments:
    None

Usage:
    exampledb=> SELECT * FROM list_subscriptions();

disable_subscription

Arguments:
    subscription_name   Name the subscription channel to disable
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT disable_subscription('subs1','exampledb');

enable_subscription

Arguments:
    subscription_name   Name the subscription channel to enable
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT enable_subscription('subs1','exampledb');

subscription_slot_none

Questa funzione serve a impostare il nome dello slot di un abbonamento su NESSUNO. È necessario per eliminare una sottoscrizione in modo che uno slot di replica remoto non possa essere abbandonato o non esista o non sia mai esistito.

Arguments:
    subscription_name   Name the subscription channel to alter
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT subscription_slot_none('subs1','exampledb');

refresh_subscription

Questa funzione è usata per aggiornare una sottoscrizione sul sottoscrittore dopo che sono state apportate modifiche sul publisher, come l'aggiunta o la rimozione di una tabella.

Arguments:
    subscription_name   Name the subscription channel to refresh
    db_name             The name of the replicated database

Usage:
    exampledb=> SELECT refresh_subscription('subs1','exampledb');

Impostazione della replica logica sul publisher

Per configurare il proprio PostgreSQL esterno come editore, eseguire le seguenti operazioni.

Modificare l'elemento locale pg_hba.conf e aggiungere quanto segue.
```
hostssl    replication            replicator         0.0.0.0/0      md5
hostssl    all                    replicator         0.0.0.0/0      md5
```
Il campo "replicatore" è l'utente impostato con il privilegio PostgreSQL REPLICATION.
Modificare l'postgresql.conf locale con la configurazione di replica logica richiesta. Impostare wal_level su 'logico' e impostare listen_addresses='*' per accettare connessioni da qualsiasi host.
```
listen_addresses='*'
wal_level = logical
```
Riavvia il server PostgreSQL.

Ora è possibile definire un editore sul database e aggiungere le tabelle che si desidera replicare al sottoscrittore.

Accedere al database da cui si desidera pubblicare con il proprio utente di replica.
```
psql -U replicator -d exampledb
```

Creare il canale di pubblicazione.

exampledb=> CREATE PUBLICATION my_publication;

Aggiungere tabelle all'editore.
```
exampledb=> ALTER PUBLICATION my_publication ADD TABLE my_table;
```
Il numero di lavoratori che eseguono la sincronizzazione, definito dal parametro di configurazione max_logical_replication_workers, è limitato e non può essere modificato. Pertanto, utilizzate il minor numero possibile di pubblicazioni e aggiungete il maggior numero possibile di tabelle a una pubblicazione.

Impostazione della replica logica sul subscriber

Per configurare la distribuzione Databases for PostgreSQL come sottoscrittore, eseguire i seguenti passi.

Accedere al database creato per la replica con l'utente admin.
```
psql -U admin -d exampledb
```

Eseguire la seguente query per chiamare la funzione create_subscription e creare il canale degli abbonati.

exampledb=> SELECT create_subscription('subs1','130.215.223.184','5432','admin','password','exampledb','my_publication');

Monitoraggio della replica

È possibile monitorare lo stato della replica logica sia dal publisher che dal subscriber eseguendo la seguente query su entrambi.

exampledb=> SELECT * FROM pg_stat_replication;