PostgreSQL - Didacticiels

Comment provisionner un PaaS PostgreSQL ?

Pour commencer, rendez-vous sur ITCare et recherchez votre service global cible où vous créerez votre nouveau PostgreSQL.

Recherchez votre service Global dans la barre de recherche supérieure et cliquez dessus pour afficher sa page d'information.

Une fois dans votre Service Global, cliquez sur le bouton Créer une ressource et sélectionnez PostgreSQL.

Aller sur Bases de données managées, choisir PostgreSQL et sélectionner la version requise.

Remplir le formulaire et cliquer sur Suivant. Définir les personnalisations et cliquer sur Suivant.

Valider la synthèse et soumettre le formulaire.

Le provisionnement peut prendre jusqu'à 2 heures en fonction de la charge actuelle de l'automatisation.

Une fois le déploiement prêt, vous en serez informé par e-mail.

Comment gérer votre PostgreSQL ?

Sur la page de ressources de votre PostgreSQL, vous pouvez effectuer toutes les actions disponibles en utilisant le bouton Gérer dans le coin supérieur droit. Cela inclut le démarrage, l'arrêt, la suppression, le redémarrage, le redimensionnement et bien plus encore.

Comment accéder à votre cluster PostgreSQL ?

Lorsque votre cluster est créé avec ITCare, vous avez obtenu un rôle sql avec des informations d'identification.

Avec ces informations d'identification, vous pouvez vous connecter au cluster avec son nom sur le port tcp 5432. Vous pouvez utiliser la base de données postgres pour vous connecter.

Si votre cluster s'appelle "mycluster" vous pouvez utiliser le code python suivant :

import psycopg2

# Connect to the postgres database
conn = psycopg2.connect(database='postgres', user='myuser', password='mystrongpwd',host='mycluster.pg.cegedim.cloud')
# Open a cursor to perform database operations
cursor = conn.cursor()
# Execute a query
cursor.execute("SELECT datname from pg_database")
# Retrieve query results
records = cursor.fetchall()
# Print all results
for record in records:
  print(record)

Comment accéder à votre cluster PostgreSQL avec TLS/SSL ?

Lorsque votre cluster est créé avec ITCare, vous avez obtenu un rôle sql avec des informations d'identification.

Si vous choisissez d'activer TLS, vous avez reçu le certificat racine auquel vous devez faire confiance et que vous devez donner à la bibliothèque que vous utilisée pour vous connecter, par exemple psycopg2.

Avec ces informations d'identification, vous pouvez vous connecter au cluster avec son nom sur le port tcp 5432. Vous pouvez utiliser la base de données postgres pour vous connecter.

Si votre cluster s'appelle "mycluster" vous pouvez utiliser le code python suivant :

import psycopg2
 
# Connect to the postgres database
conn = psycopg2.connect(database='postgres', user='myuser', password='mystrongpwd', host='mycluster.pg.cegedim.cloud', sslmode='verify-full', sslrootcert='cegedimcloud.pg.crt')
# Open a cursor to perform database operations
cursor = conn.cursor()
# Execute a query
cursor.execute("SELECT datname from pg_database")
# Retrieve query results
records = cursor.fetchall()
# Print all results
for record in records:
  print(record)

Comment créer un rôle ?

Il est plus sûr de ne pas utiliser un rôle d'administrateur pour les applications. Une fois connecté, vous pouvez créer un rôle standard comme suit (remplacez <a_role> et <very_strong_password> par vos propres informations d'identification) :

create role <a_role> login password '<very_strong_password>';

Comment créer une base de données ?

Si vous voulez créer une base de données dont le propriétaire sera le rôle que vous venez de créer, utilisez les requêtes SQL suivantes :

grant <a_role> to admin;
create database <my_database> owner <a_role>;

Comment créer une base de données avec un autre encodage et/ou collation ?

Vous pouvez utiliser les requêtes SQL suivantes avec la base de données template0 comme base de données template :

create database <my_database> owner <a_role> template template0 LC_COLLATE 'fr_FR.utf8';
create database <my_database> owner <a_role> template template0 encoding 'LATIN1' LC_COLLATE 'fr_FR';

Comment restaurer en libre-service ?

Le PaaS PostgreSQL dispose d'une fonctionnalité permettant de restaurer un PaaS PostgreSQL (source) vers un autre PaaS PostgreSQL (destination) à un moment donné (en utilisant le Point-In-Time Recovery) sous les contraintes suivantes :

• l'utilisateur doit avoir accès au cloud de la ferme source et de la ferme de destination

• la source doit être sauvegardée (option choisie lors de la création)

• la source et la destination doivent être actives

• la source et la destination doivent être différentes

• la source et la destination doivent être dans la même version de PostgreSQL

• la source et la destination doivent être en version 12 ou supérieure

• l'heure cible ne doit pas être dans le futur (délimitée à droite par l'heure actuelle).

• l'heure cible ne doit pas être inférieure à 7 jours (pour les services de non-production) ou à 14 jours (pour les services de production) avec pour référence l'heure actuelle (délimitée à gauche par la rétention des sauvegardes).

Vous pouvez choisir d'inclure ou d'exclure la cible temporelle dans le processus de restauration.

Comment installer l'extension oracle_fdw ?

Vous pouvez installer l'extension oracle_fdw sur vos bases de données. Cette extension vous permet de vous connecter à un déploiement PostgreSQL et de lire/écrire des tables situées réellement dans un serveur Oracle. Pour ce faire, à l'aide d'ITCare, utiliser le menu Gérer puis Gérer les extensions, choisissez votre base de données, puis oracle_fdw et cliquer sur le bouton Soumettre. On rappelle que selon les bonnes pratiques, vous avez créé un rôle et une base de données dédiée:

create role myrole login;
alter role myrole password '********';
grant myrole to admin;
create database customer owner myrole;

Une fois l'extension installée vous devez créer plusieurs objets dans la base de donnée. Vous devez disposez des éléments suivants pour réaliser cette opération:

• l'identifiant et le mot de passe du compte (admin) qui vous ont été communiqués lors de la création de votre déploiement PostgreSQL.

• le role qui se connectera à la base de données PostgreSQL et qui accédera à la base de données Oracle

• le SID de la base Oracle

• l'identifiant et le mot de passe du rôle qui le droit de se connecter à la base de données Oracle et qui a le droit de lire/écrire les tables considérées.

• le nom et la définition des tables Oracle

Pour la suite de l'exemple, on supposera que sous Oracle, une table a été créée comme suit:

CREATE TABLE person (
  id NUMBER(10, 0) PRIMARY KEY,
  name VARCHAR2(64),
  ts TIMESTAMP
);

En utilisant votre compte admin sur la base de données customer, vous devez créer une correspondance entre votre serveur Oracle, la base de données Oracle et le rôle Oracle (rôle qui par exemple aura créé la table person)

CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//myora.hosting.cegedim.cloud:1521/mysid');
CREATE USER MAPPING FOR myrole SERVER oracle_server OPTIONS (user 'orauser', password 'mypwd');
GRANT USAGE on foreign server oracle_server to myrole;

Enfin, avec le rôle myrole, connecté à la base de données customer (dans PostgreSQL):

CREATE FOREIGN TABLE person (
    id BIGINT OPTIONS (key 'true') NOT NULL,
    name VARCHAR(64),
    ts TIMESTAMP WITHOUT TIME ZONE
)
SERVER oracle_server
OPTIONS (table 'PERSON');
select * from person;
 id |   name    |             ts            
----+-----------+----------------------------
  0 | Asterix   | 2025-10-08 09:17:26.591058
  1 | Obelix    | 2025-10-08 09:17:34.893623
  2 | Panoramix | 2025-10-08 09:17:47.758639
  3 | Idefix    | 2025-10-08 09:17:58.063794
(4 rows)

En cas d'erreur lors de la déclaration des correspondances entre le serveur et le rôle Oracle, le rôle admin peut faire les modifications suivantes:

-- modifier à posteriori le foreign server si on s'est trompé dans un paramètre
alter server oracle_server OPTIONS (SET dbserver '//so19cust03.hosting.cegedim.cloud:1521/SO19CUSTO3');
-- modifier un user mapping
ALTER USER MAPPING FOR myrole SERVER oracle_server OPTIONS (SET password 'leBonPassWord');

La documenration de l'extension oracle_fdw est accessible ici.

Comment installer l'extension postgres_fdw ?

Vous pouvez installer l'extension postgres_fdw sur vos bases de données. Cette extension vous permet de lire/écrire des tables se situant dans un autre déploiement PostgreSQL. Pour ce faire, à l'aide d'ITCare, utiliser le menu Gérer puis Gérer les extensions sur votre déploiement pg16, choisissez votre base de données, puis postgres_fdw et cliquer sur le bouton Soumettre. On rappelle que selon les bonnes pratiques, vous avez créé un rôle et une base de données dédiée sur chacun des déploiements PostgreSQL:

Sur le serveur pg15 en version 15 par exemple (pg15.pg.cegedim.cloud)

create role rolea login;
alter role rolea password '***********';
grant rolea to admin;
create database mydb owner rolea;
\c mydb
CREATE TABLE mytable (
    id INTEGER PRIMARY KEY,
    ts TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
alter table mytable owner rolea;
insert into mytable values(0);
insert into mytable values(1);
insert into mytable values(2);
insert into mytable values(3);
select * from mytable;
 id |              ts              
----+-------------------------------
  0 | 2025-10-07 09:00:47.607772+00
  1 | 2025-10-07 09:00:50.998144+00
  2 | 2025-10-07 09:00:53.639238+00
  3 | 2025-10-07 09:00:56.134559+00
(4 rows)

Sur le serveur pg16 (pg16.pg.cegedim.cloud):

create role roleb login;
alter role roleb password '**********';
grant roleb to admin;
create database customer owner roleb;

Avec le rôle admin sur la base de données customer du déploiement pg16, créer les correspondances avec le serveur pg15 et le rôle rolea:

create server pg15 foreign data wrapper postgres_fdw options (host 'pg15.pg.cegedim.cloud', dbname 'mydb');
create user mapping for roleb server pg15 options(user 'rolea', password '******'); -- utiliser le mot de passe du rôle rolea du serveur pg15
grant usage on foreign server pg15 to roleb;

Avec le rôle roleb sur le déploiement pg16, base de données customer, importer la définition de la table mytable:

-- importer la définition de la table mytable de pg15
IMPORT FOREIGN SCHEMA public LIMIT TO (mytable) FROM SERVER pg15 INTO public;
-- la table est désormais accessible :
 select * from mytable;
 id |              ts              
----+-------------------------------
  0 | 2025-10-07 09:00:47.607772+00
  1 | 2025-10-07 09:00:50.998144+00
  2 | 2025-10-07 09:00:53.639238+00
  3 | 2025-10-07 09:00:56.134559+00
(4 rows)

Démo

Restauration d'une base de données PostgreSQL

Le processus de restauration d'une base de données PostgreSQL est une étape importante. Voyons comment procéder ci-dessous :