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.