Welcome to the first installment of our series: Scalable Solutions with Percona Distribution for PostgreSQL. In this guide, we will demonstrate how to establish a Citus database spanning multiple nodes by using Percona Distribution for PostgreSQL. This setup empowers your database to efficiently manage increased data volumes, enhance performance, and maintain availability.
This initial section will guide you through establishing a Percona Distribution PostgreSQL in three EC2 instances.
Then, in the next part of this series, Scalable Solutions with Percona Distribution for PostgreSQL (Part 2): Using Citus, we will use these worker nodes to establish a multi-node Citus cluster and implement sharding techniques.
This tutorial will use Ubuntu 20.04 and Percona Distribution for PostgreSQL 15.
What we have so far is this:
- Three EC2 instances in the same subnet.
- A security group in AWS for PostgreSQL with inbound rules allowing traffic on ports 5432, 5433, and 5434 for PostgreSQL.
Installing Percona Distribution for PostgreSQL on the three instances
Let’s first install Percona Distribution for PostgreSQL on each EC2 instance. Use the following procedure for all the three nodes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # Download Debian package file for Percona Realease wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb # Install Percona Debian package sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb # Refresh local package database sudo apt update # Add Percona repository for PostgreSQL to our system's list of repositories. sudo percona-release setup ppg-15 # Install "percona-ppg-server-15" package sudo apt install percona-ppg-server-15 |
Now, let’s verify if the service is operating correctly.
1 | sudo systemctl status postgresql.service |
You should see this output, indicating an ‘Active’ status in all the instances.
1 2 3 4 5 | ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Mon 2023-09-18 13:36:07 UTC; 1h 15min ago Process: 706 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 706 (code=exited, status=0/SUCCESS) |
If you want to check the version of Percona PostgreSQL, you can use the ‘psql’ command-line utility for interacting with PostgreSQL databases. To do this, open a terminal and run the following SQL query: “SELECT version();” This will display the version of Percona Distribution PostgreSQL installed on your system.
1 2 | sudo su postgres psql |
You will have this as an output:
1 2 3 4 5 | postgres=# SELECT VERSION(); version ------------------------------------------------------------------------------------------------------------------------------ PostgreSQL 15.4 - Percona Distribution on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit (1 row) |
Configuring postgresql.conf on three instances
Before starting the database on each node, modifying specific configuration files is essential. To do this, follow these steps:
- Navigate to the PostgreSQL Configuration Directory: Locate the ‘postgresql.conf’ file, typically found at the path ‘/etc/postgresql/15/main’. You will need to access this file to make the necessary adjustments.
- Log in as the ‘postgres’ User: To access and modify the ‘postgresql.conf’ file, you should log in using the “postgres” user account, ensuring the necessary permissions for configuration changes.
1 | sudo su postgres |
3. Configure Essential Settings: You’ll configure vital settings within the ‘postgresql.conf’ file. These settings may include specifying each node’s IP address, PORT, and SSL certificate. Ensure that these values align with your desired database configuration.
Node main
1 2 3 4 5 6 7 8 9 10 | # Ip address listen_addresses = '172.31.88.41' # Port for node1_data worker port = 5432 # - SSL - ssl = on ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' |
Node worker01
1 2 3 4 5 6 7 8 9 10 | # Ip address listen_addresses = '172.31.86.26' # Port for node1_data worker port = 5433 # - SSL - ssl = on ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' |
Node worker02
1 2 3 4 5 6 7 8 9 10 | # Ip address listen_addresses = '172.31.89.45' # Port for node1_data worker port = 5434 # - SSL - ssl = on ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' |
Configuring pg_hba.conf for three instances
Now, let’s modify ‘pg_hba.conf,’ which can be found in the same directory as the ‘postgresql.conf’ file: ‘/etc/postgresql/15/main’.
Node main
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 |
Node worker01
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all postgres 72.31.88.41/32 trust # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 |
Node worker02
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all postgres 172.31.88.41/32 trust # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 |
Note: Using “trust” in the settings is too permissive. See Citus notes about Increasing Worker Security. The PostgreSQL manual explains how to make them more restrictive.
Restart all PostgreSQL instances
Now, we will start the PostgreSQL instances.
1 | sudo systemctl restart postgresql, |
Three nodes of Percona PostgreSQL that are listening on ports 5432, 5433, and 5434.
Node main on port 5432
1 2 3 | ubuntu@ip-172-31-88-41:~$ ss -tuln Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process tcp LISTEN 0 244 172.31.88.41:5432 0.0.0.0:* |
Worker01 on port 5433
1 2 3 | ubuntu@ip-172-31-86-26:~$ ss -tuln Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process tcp LISTEN 0 244 172.31.86.26:5433 0.0.0.0:* |
Worker02 on port 5434
1 2 3 | ubuntu@ip-172-31-89-45:~$ ss -tuln Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process tcp LISTEN 0 244 172.31.89.45:5434 0.0.0.0:* |
Are you ready to see what we can do with these Percona Distributions for PostgreSQL nodes?
Check the Scalable Solutions with Percona Distribution for PostgreSQL (Part 2): Using Citus, and Percona Distribution for PostgreSQL 15: An Introduction to Scalable Database Solutions.
In the meantime, you can learn more about the Percona Distribution for PostgreSQL. Additionally, if you’re interested in deploying PostgreSQL on Kubernetes, you can explore information about the Percona Operator for PostgreSQL.
You can also contact Percona’s experts for assistance in maximizing your application’s performance. We offer support for open-source databases, managed services, and consulting services, or visit our Percona Community forum; we are happy to help.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
