Setting Up a PostgreSQL Database on a Remote Server Using CloudRay

Setting up a PostgreSQL database on a remote server can be time-consuming, but automation tools like CloudRay simplify the process. CloudRay allows you to automate server configurations, setup, and database management through scripting.

In this guide, we will walk you through setting up a PostgreSQL database on a remote Ubuntu server using CloudRay. You will learn how to add the server to CloudRay, automating PostgreSQL installation and configuration, creating a database and user, and scheduling backups.

By the end of this article, you will have a fully functional PostgreSQL instance that is automated and easily manageable by CloudRay.

Contents

• Adding Servers to CloudRay
• Assumptions
• Create the Automation Script
  • Installation and Configuration Script
  • Database Setup Script
• Create a Variable Group
• Running the Script with CloudRay
• Scheduling PostgreSQL Database Backup with CloudRay’s Schedules (Optional)
• Related Guides

Adding Servers to CloudRay

Before getting started, make sure your target servers are connected to CloudRay. If you haven’t done this yet, follow our servers docs to add and manage your server

Assumptions

• This guide assumes you’re using Ubuntu 24.04 LTS as your server’s operating system. If you’re using a different version or a different distribution, adjust the commands accordingly.

Create the Automation Script

To streamline the setup and management processes, you’ll need three Bash scripts:

1. Installation and Configuration Script: This script will install PostgreSQL and modifies its configuration files accordingly
2. Database setup Script: This script will creates a PostgreSQL database, user, and a sample table
3. Backup Script: This script will automate the backup processes for PostgreSQL database

Let’s begin with the installation and configuration of PostgreSQL.

Installation and Configuration Script

To create the Installation and configuration script, you need to follow these steps:

1. Go to Scripts in your CloudRay project
2. Click New Script
3. Name: Install and Configure PostgreSQL Database. You can give it any name of your choice.
4. Copy this code:

#!/bin/bash

# Exit immediately if a command exits with a non-zero status
set -e

# Update package lists
sudo apt update -y

# Install PostgreSQL without interactive prompts
sudo apt install -y postgresql postgresql-contrib

# Start and enable PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Modify postgresql.conf to allow external connections
sudo sed -i "s/^#listen_addresses = 'localhost'/listen_addresses = '*' /" {{postgres_conf}}

# Enable logging for PostgreSQL
echo "logging_collector = on" | sudo tee -a {{postgres_conf}}
echo "log_directory = '/var/log/postgresql'" | sudo tee -a {{postgres_conf}}
echo "log_filename = 'postgresql-%Y-%m-%d.log'" | sudo tee -a {{postgres_conf}}
echo "log_rotation_age = 1d" | sudo tee -a {{postgres_conf}}
echo "log_rotation_size = 10MB" | sudo tee -a {{postgres_conf}}

# Modify pg_hba.conf to allow remote access with password authentication
echo "host    all             all             0.0.0.0/0               md5" | sudo tee -a {{pg_hba_conf}}
echo "host    all             all             ::/0                    md5" | sudo tee -a {{pg_hba_conf}}

# Reload PostgreSQL to apply authentication changes
sudo systemctl reload postgresql

# Restart PostgreSQL to apply changes
sudo systemctl restart postgresql

echo "PostgreSQL installed and configured successfully."

Here is a breakdown of what each command in the Install and Configure PostgreSQL Database does:

• Updates package lists to ensure we install the latest versions
• Installs the latest version of PostgreSQL and additional components
• Starts and enables PostgreSQL to ensure it runs on system startup
• Modifies postgresql.conf to allow external connections
• Enables logging for PostgreSQL to store logs in /var/log/postgresql and rotate them daily or when they exceed 10MB
• Modifies pg_hba.conf to enforce password authentication for all remote connections (both IPv4 and IPv6)
• Reloads PostgreSQL to apply authentication changes without restarting the database
• Restarts PostgreSQL to apply changes

Database Setup Script

After the installation and configuration of PostgreSQL, you set up the postgreSQL database by creating a Postgres database, a dedicated user with privileges, and a table if necessary.

Similarly, follow these steps to setup the database:

1. Go to Scripts > New Script
2. Name: Setup Postgres Database
3. Add code:

#!/bin/bash

set -e

# Create the user and database
sudo -u postgres psql <<EOF
CREATE USER {{db_user}} PASSWORD '{{db_pass}}';
CREATE DATABASE {{db_name}};
GRANT ALL PRIVILEGES ON DATABASE {{db_name}} TO {{db_user}};
ALTER DATABASE {{db_name}} OWNER TO {{db_user}};
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO {{db_user}};
GRANT ALL ON SCHEMA public TO {{db_user}};
CREATE TABLE UserGrades (
    Name VARCHAR(100),
    Grade VARCHAR(50)
);
EOF

echo "PostgreSQL database {{db_name}} with user {{db_user}} created successfully."

This is what the Setup Postgres Database does:

• Switches to the postgres user and runs SQL commands
• Creates a new PostgreSQL user
• Creates a new database
• Grants full access to the database
• Assigns ownership of the database to the user
• Grants table permissions
• Creates a sample table

Create a Variable Group

Now, before running the scripts, you need to define values for the placeholders {{postgres_conf}}, {{pg_hba_conf}}, {{db_name}}, {{db_user}}, and {{db_pass}} used in the scrips. CloudRay processes all scripts as Liquid templates. This allows you to use variables dynamically across different servers.

To ensure that these values are automatically substituted when the script runs, follow these steps to create a variable Group:

1. Navigate to Variable Groups: In your CloudRay project, go to “Scripts” in the top menu and click on “Variable Groups”.
2. Create a new Variable Group: Click on “Variable Group”.
3. Add the following variables:

• postgres_conf: This is the Bash variable that stores the file path of the PostgreSQL configuration file
• pg_hba_conf: This is the Bash variable that stores the file path of the PostgreSQL Host-Based Authentication (HBA) configuration file
• db_name: This is the name of the database
• db_user: The name of the user
• db_pass: The password of the for the database user

Since the variables are setup, proceed with running the scripts with CloudRay

Running the Script with CloudRay

You can choose to run the scripts individually or execute them all at once using CloudRay’s Script Playlists. Since there are multiple scripts, using CloudRay playlists will help automate the execution sequence and save time.

Here are the steps to follow:

1. Navigate to “Script Playlists”: Click on the Scripts tab in the CloudRay interface

2. Click “Add Script Playlist”: This initiates the creation of a new playlist
3. Provide a Name: Give your playlist a unique name (For example “PostgreSQL Setup Automation and Management”)
4. Add Scripts in Order: Select and add the scripts sequentially

5. Save the Playlist: Click “create playlist” to store your new playlist.

Once your script playlist is created, proceed with execution:

1. Navigate to Runlogs: In your CloudRay project, go to the Runlogs section in the top menu.
2. Create a New Runlog: Click on New Runlog.
3. Configure the Runlog: Provide the necessary details:

• Server: Select the server where you need PostgreSQL to be installed
• Script Playlist: Choose the playlist you created (For example “PostgreSQL Setup Automation and Management”)
• Variable Group: Select the variable group you set up earlier

4. Execute the Script: Click on Run Now to start the execution

Your PostgreSQL is now seamlessly setup and managed with CloudRay. That’s it! Happy deploying!

Scheduling PostgreSQL Database Backup with CloudRay’s Schedules (Optional)

CloudRay also offers Schedules, allowing you to execute scripts automatically at specific intervals or times. This feature is useful for tasks such as automating database backups.

To automate PostgreSQL database backups with CloudRay, you first need to create a backup script that the scheduler will execute.

You can follow similar steps as the previous ones to create the backup script:

1. Go to Scripts > New Script
2. Name: Backup Postgres Database
3. Add code:

#!/bin/bash

set -e

BACKUP_FILE="{{db_name}}_$(date +%F_%H-%M-%S).sqlc"

# Create .pgpass file for authentication
PGPASS_FILE="$HOME/.pgpass"
echo "localhost:5432:{{db_name}}:{{db_user}}:{{db_pass}}" > "$PGPASS_FILE"
chmod 600 "$PGPASS_FILE"

echo "Performing database backup..."

# Perform database backup
pg_dump -h localhost -U "{{db_user}}" -d "{{db_name}}" -p 5432 -F c -f "$BACKUP_FILE"

# Confirm backup success
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"
else
    echo "Backup failed!"
    exit 1
fi

This is what the Setup Postgres Database does:

• Defines the path for PostgreSQL authentication
• Stores database credentials
• Secures the credentials file
• Creates a backup of the database in compressed format
• Checks if the backup was successful: If successful, it prints the backup path; otherwise, it reports failure and exits with an error code.

For example, if you want to back up your database on the first day of every month at 12:00 AM, you can configure a CloudRay schedule to handle this automatically.

Here are the steps to achieve this:

1. Navigate to Schedules: In your CloudRay dashboard, go to the “Schedules” tab.

2. Click “Add Schedule”: Start creating a new schedule.

3. Submit Schedule: Click “Submit” to activate your new schedule.

CloudRay will automatically execute the backup script at the scheduled time, ensuring that your database is regularly backed up without manual intervention.

Related Guides

• Install MongoDB
• Deploy MySQL Server
• Automte PHPMyAdmin Deployment