Deploy PostgreSQL with StackGres on OCI
StackGres is a full stack PostgreSQL distribution for Kubernetes, packed into an easy deployment unit. StackGres includes connection pooling, automated backups, monitoring, centralized logging, and a fully-featured management web console.
StackGres integrates the most renowned and production-tested high availability software for Postgres: Patroni.
- It’s fully integrated. If anything fails, the cluster will reheal automatically, without human intervention.
- StackGres exposes one read-write and one read-only connection for the applications, that will be automatically updated after any disruptive event occurs.
An enterprise-grade PostgreSQL stack needs several other ecosystem components and significant tuning. It requires connection pooling, automatic failover and HA, monitoring, backups and DR. The StackGres package and the Oracle Cloud Infrastructure (OCI) components shown in this reference architecture provide a low-effort, scalable, highly available deployment.
Architecture
This architecture shows how to deploy PostgreSQL in an Oracle Container Engine for Kubernetes cluster. Use this architecture for production-ready environments that take advantage of PostgreSQL features:
- No vendor lock-in: PostgreSQL works on any Kubernetes environment.
- Includes a fully-featured Web console.
- The Postgres platform has the most extensions in the world.
- Runs on x86-64 and ARM64 Kubernetes worker nodes.
- Fully Open Source (no split between a free “Community” and an expensive “Enterprise” version).
- Pricing for support and updates, based on cores used.
- Includes “vanilla” Postgres and Babelfish (which offers SQL Server compatibility).
The following diagram illustrates this reference architecture.
The architecture has the following components:
- Tenancy
A tenancy is a secure and isolated partition that Oracle sets up within Oracle Cloud when you sign up for Oracle Cloud Infrastructure. You can create, organize, and administer your resources in Oracle Cloud within your tenancy. A tenancy is synonymous with a company or organization. Usually, a company will have a single tenancy and reflect its organizational structure within that tenancy. A single tenancy is usually associated with a single subscription, and a single subscription usually only has one tenancy.
- Region
An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).
- Compartment
Compartments are cross-region logical partitions within an Oracle Cloud Infrastructure tenancy. Use compartments to organize your resources in Oracle Cloud, control access to the resources, and set usage quotas. To control access to the resources in a given compartment, you define policies that specify who can access the resources and what actions they can perform.
- Availability domain
Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.
One availability domain is shown, but you can deploy across multiple availability domains depending on your production requirements.
- Fault domain
A fault domain is a grouping of hardware and infrastructure within an availability domain. Each availability domain has three fault domains with independent power and hardware. When you distribute resources across multiple fault domains, your applications can tolerate physical server failure, system maintenance, and power failures inside a fault domain.
One fault domain is shown, but you can deploy across multiple fault domains depending on your production requirements.
- Virtual cloud network (VCN) and subnets
A VCN is a customizable, software-defined network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. A VCN can have multiple non-overlapping CIDR blocks that you can change after you create the VCN. You can segment a VCN into subnets, which can be scoped to a region or to an availability domain. Each subnet consists of a contiguous range of addresses that don't overlap with the other subnets in the VCN. You can change the size of a subnet after creation. A subnet can be public or private.
- Load balancer
The Oracle Cloud Infrastructure Load Balancing service provides automated traffic distribution from a single entry point to multiple servers in the back end.
- Bastion service
Oracle Cloud Infrastructure Bastion provides restricted and time-limited secure access to resources that don't have public endpoints and that require strict resource access controls, such as bare metal and virtual machines, Oracle MySQL Database Service, Autonomous Transaction Processing (ATP), Oracle Container Engine for Kubernetes (OKE), and any other resource that allows Secure Shell Protocol (SSH) access. With Oracle Cloud Infrastructure Bastion service, you can enable access to private hosts without deploying and maintaining a jump host. In addition, you gain improved security posture with identity-based permissions and a centralized, audited, and time-bound SSH session. Oracle Cloud Infrastructure Bastion removes the need for a public IP for bastion access, eliminating the hassle and potential attack surface when providing remote access.
- Security list
For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.
- Network address translation (NAT) gateway
A NAT gateway enables private resources in a VCN to access hosts on the internet, without exposing those resources to incoming internet connections.
- Service gateway
The service gateway provides access from a VCN to other services, such as Oracle Cloud Infrastructure Object Storage. The traffic from the VCN to the Oracle service travels over the Oracle network fabric and never traverses the internet.
- Dynamic routing gateway (DRG)
The DRG is a virtual router that provides a path for private network traffic between VCNs in the same region, between a VCN and a network outside the region, such as a VCN in another Oracle Cloud Infrastructure region, an on-premises network, or a network in another cloud provider.
- Object storage
Object storage provides quick access to large amounts of structured and unstructured data of any content type, including database backups, analytic data, and rich content such as images and videos. You can safely and securely store and then retrieve data directly from the internet or from within the cloud platform. You can seamlessly scale storage without experiencing any degradation in performance or service reliability. Use standard storage for "hot" storage that you need to access quickly, immediately, and frequently. Use archive storage for "cold" storage that you retain for long periods of time and seldom or rarely access.
- Internet gateway
The internet gateway allows traffic between the public subnets in a VCN and the public internet.
- FastConnect
Oracle Cloud Infrastructure FastConnect provides an easy way to create a dedicated, private connection between your data center and Oracle Cloud Infrastructure. FastConnect provides higher-bandwidth options and a more reliable networking experience when compared with internet-based connections.
- Local peering gateway (LPG)
An LPG enables you to peer one VCN with another VCN in the same region. Peering means the VCNs communicate using private IP addresses, without the traffic traversing the internet or routing through your on-premises network.
- Container Engine for Kubernetes
Oracle Cloud Infrastructure Container Engine for Kubernetes is a fully managed, scalable, and highly available service that you can use to deploy your containerized applications to the cloud. You specify the compute resources that your applications require, and Container Engine for Kubernetes provisions them on Oracle Cloud Infrastructure in an existing tenancy. Container Engine for Kubernetes uses Kubernetes to automate the deployment, scaling, and management of containerized applications across clusters of hosts.
- PostgreSQL
PostgreSQL is an open-source relational database management system (RDBMS) that is highly extensible and highly scalable. It supports SQL (relational) and JSON (non-relational) querying.
- StackGres
StackGres is a full stack PostgreSQL distribution for Kubernetes, packed into an easy deployment unit. It includes a carefully selected and tuned set of surrounding PostgreSQL components.
- Cloud Guard
You can use Oracle Cloud Guard to monitor and maintain the security of your resources in Oracle Cloud Infrastructure. Cloud Guard uses detector recipes that you can define to examine your resources for security weaknesses and to monitor operators and users for risky activities. When any misconfiguration or insecure activity is detected, Cloud Guard recommends corrective actions and assists with taking those actions, based on responder recipes that you can define.
- Distributed Denial of Service (DDoS) Protection
Distributed Denial of Service (DDoS) attacks are serious cybercrimes committed by attackers that flood company servers with an overwhelming amount of incoming traffic. This overwhelming amount of traffic comes from myriad sources and geolocations, preventing users from accessing the company’s services and sites. All Oracle Cloud data centers have DDoS attack detection and mitigation for high volume layer 3 or 4 DDoS attacks. These DDoS protection services from Oracle Cloud help ensure the availability of Oracle network resources even under sustained layer 3 or 4 attacks.
- Identity and Access Management (IAM)
Oracle Cloud Infrastructure Identity and Access Management (IAM) is the access control plane for Oracle Cloud Infrastructure (OCI) and Oracle Cloud Applications. The IAM API and the user interface enable you to manage identity domains and the resources within the identity domain. Each OCI IAM identity domain represents a standalone identity and access management solution or a different user population.
- LoggingLogging is a highly scalable and fully managed service that provides access to the following types of logs from your resources in the cloud:
- Audit logs: Logs related to events emitted by the Audit service.
- Service logs: Logs emitted by individual services such as API Gateway, Events, Functions, Load Balancing, Object Storage, and VCN flow logs.
- Custom logs: Logs that contain diagnostic information from custom applications, other cloud providers, or an on-premises environment.
- Audit
The Oracle Cloud Infrastructure Audit service automatically records calls to all supported Oracle Cloud Infrastructure public application programming interface (API) endpoints as log events. Currently, all services support logging by Oracle Cloud Infrastructure Audit.
Recommendations
- VCN
When you create a VCN, determine the number of CIDR blocks required and the size of each block based on the number of resources that you plan to attach to subnets in the VCN. Use CIDR blocks that are within the standard private IP address space.
Select CIDR blocks that don't overlap with any other network (in Oracle Cloud Infrastructure, your on-premises data center, or another cloud provider) to which you intend to set up private connections.
After you create a VCN, you can change, add, and remove its CIDR blocks.
When you design the subnets, consider your traffic flow and security requirements. Attach all the resources within a specific tier or role to the same subnet, which can serve as a security boundary.
Use regional subnets.
- Cloud Guard
Clone and customize the default recipes provided by Oracle to create custom detector and responder recipes. These recipes enable you to specify what type of security violations generate a warning and what actions are allowed to be performed on them. For example, you might want to detect Object Storage buckets that have visibility set to public.
Apply Cloud Guard at the tenancy level to cover the broadest scope and to reduce the administrative burden of maintaining multiple configurations.
You can also use the Managed List feature to apply certain configurations to detectors.
- Security Zones
For resources that require maximum security, Oracle recommends that you use security zones. A security zone is a compartment associated with an Oracle-defined recipe of security policies that are based on best practices. For example, the resources in a security zone must not be accessible from the public internet and they must be encrypted using customer-managed keys. When you create and update resources in a security zone, Oracle Cloud Infrastructure validates the operations against the policies in the security-zone recipe, and denies operations that violate any of the policies.
- Network security groups (NSGs)
You can use NSGs to define a set of ingress and egress rules that apply to specific VNICs. We recommend using NSGs rather than security lists, because NSGs enable you to separate the VCN's subnet architecture from the security requirements of your application.
- Load balancer bandwidth
While creating the load balancer, you can either select a predefined shape that provides a fixed bandwidth, or specify a custom (flexible) shape where you set a bandwidth range and let the service scale the bandwidth automatically based on traffic patterns. With either approach, you can change the shape at any time after creating the load balancer.
Considerations
When deploying this architecture, consider these options:
- Homologation with ARM processors
StackGres can be run with ARM (Ampere A1 Compute).
- Architecture options
The architecture of this document is a suggestion and can be changed according to the needs of the project.
- Postgres extensions
StackGres sources over 130 Postgres extensions you can use.
- User privileges
Use the security best practice of least privilege: restrict account access to root or superuser privileges to only when actually needed.
- Monitoring
In addition to OCI services such as OCI Events,OCI Logging, and OCI Monitoring, you can use Prometheus or Grafana for monitoring.
- Backups
StackGres includes continuous archive-based backups, which enables zero data loss recovery.
Backups are always stored on the most durable media available today: OCI Object Storage.
Follow these best practices:
- Do not leave your database exposed to the Internet.
- If possible, segregate your application cluster from the cluster used by Stackgres.
- If you want to create your application in the same cluster, Oracle strongly recommends that you create a different namespace for the application.
- When choosing your Intel, AMD or ARM processor, take into account the size of your workload so as not to undersize your Postgres environment.
Explore More
Learn more about deploying PostgreSQL on OCI.
Review these additional resources:
- For complete installation and configuration instructions for StackGres on OCI, see the solution playbook Deploy a highly-available PostgreSQL stack on an OCI Kubernetes cluster with StackGres
- For a deployment of PostgreSQL on OCI (without StackGres) with a Terraform automation, see Deploy a PostgreSQL database
- Best practices framework for Oracle Cloud Infrastructure
- Learn about architecting a highly available cloud topology
- PostgreSQL documentation
- StackGres documentation