{"id":29697,"date":"2023-12-13T08:58:42","date_gmt":"2023-12-13T07:58:42","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=29697"},"modified":"2023-12-14T14:03:27","modified_gmt":"2023-12-14T13:03:27","slug":"new-postgresql-service-in-oci","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/new-postgresql-service-in-oci\/","title":{"rendered":"New PostgreSQL service in OCI"},"content":{"rendered":"\n
PostgreSQL, an open-source relational database management system, offers robust features and capabilities for data storage and management. Deploying PostgreSQL within the Oracle Cloud Infrastructure (OCI) provides a scalable and reliable environment. In this guide, we’ll explore the process of setting up PostgreSQL in OCI. We will also push a bit more to learn how backup\/restore and failover works.<\/p>\n\n\n\n
Let’s discover the new PostgreSQL service available in OCI \ud83d\ude42<\/p>\n\n\n\n
Prerequisite<\/h2>\n\n\n\n
For this training, you need an OCI account, of course. You also need a VM with the OCI CLI, as not everything is manageable from the GUI, like the possibility to a do a failover for example. I\u2019m personally running an instance with a Rocky 9 image.<\/p>\n\n\n\n
Deploying PostgreSQL in OCI with the GUI<\/h2>\n\n\n\n
From the sidebar navigation, go to Databases > PostgreSQL > databases and then click on \u201cCreate PostgreSQL Database system\u201d. As we are doing a project from scratch, you can continue with the default installation \u201cCreate new Database system\u201d. Now, you can update all the necessary fields: Database system configuration, Network configuration, Database system administrator credentials. The PostgreSQL service necessitates a private IP as it can’t be deployed on a public subnet. To connect to it, you’ll need to create a computed instance in a public subnet. In your VCN, don’t forget to add an ingress rule to allow the traffic with port 5432 for your private subnet.<\/p>\n\n\n\n
You can also notice that the only available version for PostgreSQL is the major version 14, which is two major version behind the current version.<\/p>\n\n\n\n<\/figure>\n\n\n\n
Note that during this process you can also chose the number of nodes you want to deploy. By default you have only one node, which will be the primary of your PostgreSQL cluster.<\/p>\n\n\n\n
At the same time, you can initiate the creation of an instance with a public IP. It will be used later to connect to the PostgreSQL cluster and to issue OCI related commands. The DBSystem creation took me approximately 8 minutes.<\/p>\n\n\n\n
Installation of OCI CLI and PostgreSQL CLI<\/h2>\n\n\n\n
Once the computed instance with a public IP is deployed, the next step involves installing the OCI CLI and the PostgreSQL CLI.<\/p>\n\n\n\n
To issue commands on your OCI environment, you need to setup the OCI config file. The easiest way to do it is to go to your OCI user profile and then into the API keys section. When creating a new key, it will give you a config file template. You then just need to edit the path to your private key file.<\/p>\n\n\n\n
Retrieving IP Endpoint and Connection Attempt<\/h2>\n\n\n\n
Once your database system is ready, fetch the IP endpoint from the PostgreSQL Database system. Try to connect to your cluster with `psql` using the command:<\/p>\n\n\n\n
[rocky@pg-service-jumphost-frj ~]$ psql -h 10.0.1.173 -U postgres -d postgres\nPassword for user postgres:\npsql (14.10, server 14.9)\nSSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)\nType \"help\" for help.\n\npostgres=>\n<\/code><\/pre>\n\n\n\n
If the connection fails, double check your network configuration and security policy.<\/p>\n\n\n\n
Creating a Replica Node<\/h2>\n\n\n\n
Creating a replica by adding a node is a crucial step in PostgreSQL deployment in OCI. This process generally takes around 8 minutes. In PostgreSQL>Database systems>Database system details click the button \u201cAdd node\u201d.<\/p>\n\n\n\n<\/figure>\n\n\n\n
Once created, if you go to the detail of each node, you can see that they have a private IP address. You can directly connect to the desired node using this IP. By default, if you use the endpoint IP of the DBSystem, it\u2019ll connect you to the primary instance.<\/p>\n\n\n\n
To verify the replication between the two nodes, create a database on your primary instance.<\/p>\n\n\n\n
From what I could observe and find on various blogs\/documentation, the PostgreSQL service from OCI use an asynchronous physical standby replication inside the same Availability Domain. At the end of this blog, I will add some useful links that speak about this topic.<\/p>\n\n\n\n
Backup and restore<\/h2>\n\n\n\n
Once connected successfully, various database operations can be performed. Let\u2019s try to see how backup and restore performs.<\/p>\n\n\n\n
Still on the OCI website, click on the \u201cCreate backup\u201d button. You can control the status of your backup.<\/p>\n\n\n\n<\/figure>\n\n\n\n
When the backup is done, connect to your cluster and drop the database you just created. Click on the Restore button to restore your backup and verify that the database has been restored.<\/p>\n\n\n\n
Bonus: If you stay connected to a PostgreSQL node while starting the restore process, it will automatically terminate the existing connections and attempt to reset them after the restore.<\/p>\n\n\n\n
postgres=> drop database sephiroth ;\nDROP DATABASE\npostgres=> \\l\nFATAL: terminating connection due to administrator command\nSSL connection has been closed unexpectedly\nThe connection to the server was lost. Attempting reset: Succeeded.\npsql (14.10, server 14.9)\nSSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)\npostgres=> \\l\n List of databases\n Name | Owner | Encoding | Collate | Ctype | Access privileges\n-----------+---------------+----------+-------------+-------------+---------------------------------\n postgres | oci_superuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |\n sephiroth | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |\n template0 | oci_superuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/oci_superuser +\n | | | | | oci_superuser=CTc\/oci_superuser\n template1 | oci_superuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/oci_superuser +\n | | | | | oci_superuser=CTc\/oci_superuser\n(4 rows) \n<\/code><\/pre>\n\n\n\n
Failover and Recovery<\/h2>\n\n\n\n
The failover process is critical for ensuring high availability. It\u2019s important to test it before setting up your cluster in production.<\/p>\n\n\n\n
You don\u2019t have an option to do a switchover or a failover through the OCI Website. I decided to try to delete the primary node, so that the replica becomes the new primary, but OCI implemented a protection and it\u2019s impossible to delete a primary node. I also noticed that it\u2019s impossible to delete a node if your Database system is not in an \u201cActive\u201d state.<\/p>\n\n\n\n
You can do a failover with the OCI CLI. The only required parameter is the DB system ID that you are trying to failover. You can use various option for the failover process, please have a look at the documentation.<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/12\/image.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/12\/image-1.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/12\/image-2.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/12\/image-3.png\")
<\/figure>\n\n\n\n