PostgreSQL (cluster)

Deploy PostgreSQL, an open source relational database platform designed for reliability, standards compliance, and scalability across many application types.

📘

Cluster deployment

The app is deployed with three Linode instances to create a highly available PostgreSQL cluster, each with the plan type and size you select.

Each of these Linode instances will appear on your invoice as separate items. To deploy PostgreSQL on a single instance, see the PostgreSQL guide.

1. Deploy PostgreSQL cluster

 Estimated deployment time: 5-10 minutes

Base distribution and plan

• Supported distribution: Ubuntu 24.04 LTS
• Recommended plans: All plan types and sizes can be used.

Configuration options

StackScript ID: 1068726

Note: After the app deployment completes, the password for your limited sudo user is generated and stored in the .deployment-secrets.txt file in the home directory. Log in to your instance as root through the Lish console or SSH, then run cat /home/$USERNAME/.deployment-secrets.txt to view its contents.

Use API, CLI, or Terraform

curl --location 'https://api.linode.com/v4/linode/instances' \
  --header 'Content-Type: application/json' \
  --header 'Accept: application/json' \
  --header 'Authorization: Bearer abc123def456hij789klm' \
  --data-raw '{
    "region": "us-east",
    "type": "g6-standard-2",
    "image": "linode/ubuntu24.04",
    "label": "my-postgresql-cluster-one-click-app",
    "root_pass": "@C0mpl3x#P@ssw0rd",
    "stackscript_id": 1068726,
    "stackscript_data": {
      "token_password": "abc123def456hij789klm",
      "cluster_name": "example-cluster",
      "sudo_username": "jsmith"
    }
  }'

linode-cli linodes create \
  --region us-east \
  --type g6-standard-2 \
  --label my-postgresql-cluster-one-click-app \
  --image linode/ubuntu24.04 \
  --root_pass @C0mpl3x#P@ssw0rd \
  --stackscript_id 1068726 \
  --stackscript_data '{"token_password":"abc123def456hij789klm", "cluster_name":"example-cluster", "sudo_username":"jsmith"}'

resource "linode_instance" "my-linode" {
  region         = "us-east"
  type           = "g6-standard-2"
  label          = "my-postgresql-cluster-one-click-app"
  image          = "linode/ubuntu24.04"
  root_pass      = "@C0mpl3x#P@ssw0rd"
  stackscript_id = 1068726
  stackscript_data = {
    "token_password" = "abc123def456hij789klm"
    "cluster_name"   = "example-cluster"
    "sudo_username"  = "jsmith"
  }
}

2. Use PostgreSQL

Modify Postgres users

By default, PostgreSQL creates a Linux user named postgres to access the database software.

To keep your database secure, don't use the postgres user for other purposes, like connecting to other networks.

1. Change the postgres user’s Linux password.

   Bash

   sudo passwd postgres

2. Issue these commands to set a password for the postgres database user. Replace newpassword with a strong password and keep it in a secure place.

   Bash

   su - postgres
   psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

   This user is distinct from the postgres Linux user. The Linux user is used to access the database, and the PostgreSQL user is used to perform administrative tasks on the databases.

   The password set in this step is used to connect to the database via the network. Peer authentication is used by default for local connections.

Create a database

Run the commands as the postgres Linux user.

1. Create a sample database called mytestdb.

   Bash

   createdb mytestdb

2. Connect to the test database.

   If the connection is successful, psql displays its version information and opens an interactive session.

   The mytestdb=# prompt indicates that you are connected to the mytestdb database and can enter SQL commands.

   CommandOutput

   psql mytestdb

   psql (12.2 (Debian 12.2-2.pgdg90+1))
   Type "help" for help.
   
   mytestdb=#

This is the PostgreSQL client shell, in which you can issue SQL commands. To see a list of available commands, use the \h command. You may find more information on a specific command by adding it after \h.

Create tables

This section contains examples that create a test database with an employee's first and last name, assigning each a unique key.

When creating your own tables, you may specify as many parameters (columns) as you need and name them appropriately.

Run the commands from the PostgreSQL client shell that you opened to create the mytestdb database.

1. Create a table called employees in your test database.

   Bash

   CREATE TABLE employees (employee_id int PRIMARY KEY, first_name varchar, last_name varchar);

2. Insert a record into the table.

   Bash

   INSERT INTO employees VALUES (1, 'John', 'Smith');

3. View the contents of the employees table.

   The query returns all columns and rows in the employees table. In this example, the table contains one employee.

   CommandOutput

   SELECT * FROM employees;

   employee_id | first_name | last_name
   ------------+------------+-----------
             1 | John       | Smith
   (1 row)

4. Exit the PostgreSQL shell by entering the \q command.

Create PostgreSQL roles

PostgreSQL grants database access through roles, which are used to specify privileges. Roles can be understood as having a similar function to Linux users.

In addition, roles may also be created as a set of other roles, similar to a Linux group.

PostgreSQL roles apply globally, so you don't need to create the same role twice if you want to grant it access to more than one database on the same server.

The example commands in this section should be run as the postgres Linux user.

1. Add a new user role, then a password at the prompt.

   Bash

   createuser examplerole --pwprompt

   If you need to delete a role, you can use the dropuser command in place of createuser.

2. Connect to the database.

   Bash

   psql mytestdb

   You'll be connected as the postgres database user by default.

3. From the PostgreSQL shell, enter the following to grant all privileges on the table employees to the user examplerole:

   Bash

   GRANT ALL ON employees TO examplerole;

4. Exit the PostgreSQL shell by entering \q.

Additional resources

For more information about the installed packages, see their official documentation.

• PostgreSQL Documentation
• pgAdmin Documentation

For details on managing remote PostgreSQL servers, see our guide.

• Securely Manage Remote PostgreSQL Servers