PostgreSQL - Get started

How to provision PostgreSQL ?

To get started, head over to ITCare and search your target Global Service where you will create your new PostgreSQL.

Search your Global service in the top search bar and click on it to display its information page.

Once inside your Global Service, click on the Create Resource button and then select PostgreSQL.

Go to Managed databases and select PostgreSQL and pick the required version.

Fill in the form then click Next. Select your customizations and click Next.

Review the synthesis before submitting the form.

Provisioning can take up to 2 hours based on the current load on automation.

Once the deployment is ready, you will be notified by email.

How to manage your PostgreSQL ?

On the resource page of your PostgreSQL, you can take any action available by using the Manage button in the upper right corner. This includes, starting, stopping, deleting, rebooting, resizing and much more.

How to access your PostgreSQL cluster ?

When your cluster is created with cegedim.cloud ITCare, you obtained an sql role with credentials.

With these credentials, you may connect to the cluster with its name on tcp port 5432. You may use postgres database to connect to.

If your cluster is named "mycluster", here is an example on how to connect using Python:

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)

How to access your PostgreSQL cluster with TLS/SSL ?

When your cluster is created with cegedim.cloud ItCare, you obtained an sql role named "admin" with credentials.

If you choose to activate TLS, you have received the root certificate you should trust to and give to the library you used to connect, for example psycopg2.

With these credentials, you may connect to the cluster with its name on tcp port 5432. You may use postgres database to connect to.

If your cluster is named "mycluster" here is an example on how to connect using Python:

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)

How to create a role ?

It is safer than to not use an admin role for applications. Once connected, you may create a regular role as the following (replace <a_role> and <very_strong_password> with your own credentials)

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

How to create a database ?

if you want create a database whom owner will be the role you have just created, use the following SQL requests :

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

How to create a database with another encoding and/or collation ?

You may use the following SQL requests with template0 database as template database:

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';

How to restore in self-service ?

The PostgreSQL PaaS has a functionality allowing to restore a PostgreSQL PaaS (source) to another PostgreSQL PaaS (destination) at a given time (using Point-In-Time Recovery) under the following constraints:

• the user must have access to the cloud of the source farm and the destination farm

• the source must be backuped (option chosen during creation)

• both source and destination must be active

• the source and destination must be different

• the source and destination must be in the same version of PostgreSQL

• the source and destination must be in version 12 or higher

• the target time must not be in the future (bounded on the right by the current time).

• the time target must not be less than 7 days (for non-production services) or 14 days (for production services) from the current time (bounded on the left by the retention of backups)

You can choose to include or exclude the time target in the restoration process.

How to install the oracle_fdw extension?

You can install the oracle_fdw extension on your databases. This extension allows you to connect to a PostgreSQL deployment and read/write tables that are actually located on an Oracle server. To do this, using ITCare, go to the Manage menu, then Manage Extensions, select your database, then oracle_fdw, and click the Submit button. Remember that according to best practices, you have created a role and a dedicated database:

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

Once the extension is installed, you must create several objects in the database. You must have the following items to perform this operation:

• the username and password for the account (admin) that was provided to you when you created your PostgreSQL deployment.

• the role that will connect to the PostgreSQL database and access the Oracle database

• The SID of the Oracle database

• The username and password for the role that has the right to connect to the Oracle database and read/write the tables in question.

• The name and definition of the Oracle tables

For the rest of this example, we will assume that a table has been created in Oracle as follows:

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

Using your admin account on the customer database, you must create a mapping between your Oracle server, the Oracle database, and the Oracle role (the role that created the person table, for example)

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;

Finally, with the myrole role, connected to the customer database (in 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)

If an error occurs when declaring the correspondences between the server and the Oracle role, the admin role can make the following changes:

-- modify the foreign server retrospectively if a parameter has been entered incorrectly
alter server oracle_server OPTIONS (SET dbserver ‘//so19cust03.hosting.cegedim.cloud:1521/SO19CUSTO3’);
-- modify a user mapping
ALTER USER MAPPING FOR myrole SERVER oracle_server OPTIONS (SET password ‘theGoodPassword’);

The documentation for the oracle_fdw extension is available here.

How to install the postgres_fdw extension?

You can install the postgres_fdw extension on your databases. This extension allows you to read/write tables located in another PostgreSQL deployment. To do this, using ITCare, go to the Manage menu, then Manage Extensions on your pg16 deployment, select your database, then postgres_fdw, and click the Submit button. Remember that according to best practices, you have created a role and a dedicated database on each of the PostgreSQL deployments:

On the pg15 server in version 15, for example (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)

On the pg16 server (pg16.pg.cegedim.cloud):

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

With the admin role on the customer database of the pg16 deployment, create the mappings with the pg15 server and the rolea role:

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 ‘******’); -- use the password for the rolea role on the pg15 server
grant usage on foreign server pg15 to roleb;

With the roleb role on the pg16 deployment, customer database, import the definition of the mytable table:

-- import the definition of the mytable table from pg15
IMPORT FOREIGN SCHEMA public LIMIT TO (mytable) FROM SERVER pg15 INTO public;
-- the table is now 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)

Demos

Restoring a PostgreSQL Database

The process of restoring a PostgreSQL database is an important step. Let's see how to proceed below: