Create a Linode account to try this guide with a $ credit.
This credit will be applied to any valid services used during your first  days.

The PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.

Marketplace App Cluster Notice
This Marketplace App deploys 3 Compute Instances to create a highly available and redundant PostgreSQL cluster, each with the plan type and size that you select. Please be aware that each of these Compute Instances will appear on your invoice as separate items. To instead deploy PostgreSQL on a single Compute Instance, see Deploy PostgreSQL through the Linode Marketplace.

Deploying a Marketplace App

The Linode Marketplace lets you easily deploy an application cluster on Compute Instances using Cloud Manager. See Get Started with Marketplace Apps for complete steps.

  1. Log in to Cloud Manager and select the Marketplace link from the left navigation menu. This displays the Linode Create page with the Marketplace tab pre-selected.

  2. Under the Select App section, select the cluster app you would like to deploy. Marketplace Apps that are deployed as clusters have a cluster label next to the app’s name.

  3. Complete the form by following the steps and advice within the Creating a Compute Instance guide. Depending on the Marketplace App you selected, there may be additional configuration options available. See the Configuration Options section below for compatible distributions, recommended plans, and any additional configuration options available for this Marketplace App.

  4. Click the Create Linode button. Once the first Compute Instance has been provisioned and has fully powered on, wait for the software installation to complete. If the instance is powered off or restarted before this time, the other Compute Instances may never be deployed and the software installation will likely fail.

To verify that the app has been fully installed, see Get Started with Marketplace Apps > Verify Installation. Once installed, follow the instructions within the Getting Started After Deployment section to access the application and start using it.

Note
Estimated deployment time: The PostgreSQL cluster should be fully deployed and configured within 5-10 minutes after the first Compute Instance has finished provisioning.

Configuration Options

  • Supported distributions: Ubuntu 22.04 LTS
  • Recommended minimum plan: All plan types and sizes can be used.

PostgreSQL Options

  • Linode API Token (required): Your API token is used to deploy additional Compute Instances as part of this cluster. At a minimum, this token must have Read/Write access to Linodes. If you do not yet have an API token, see Get an API Access Token to create one.

  • Limited sudo user (required): A limited user account with sudo access is created as part of this cluster deployment. Enter your preferred username for this limited user. The password is automatically created. See Obtaining Usernames and Passwords.

  • Domain (required): The domain name you wish to use, such as example.com. This domain name is only used to identify your cluster and as part of the system’s hostname. No domain records are created within Linode’s DNS Manager.

  • Add SSH Keys to all nodes? If you select yes, any SSH Keys that are added to the root user account (in the SSH Keys section), are also added to your limited user account on all deployed Compute Instances.

  • PostgreSQL cluster size: This field cannot be edited, but is used to inform you of the number of Compute Instances that are created as part of this cluster.

Warning
Do not use a double quotation mark character (") within any of the App-specific configuration fields, including user and database password fields. This special character may cause issues during deployment.

Getting Started after Deployment

Obtaining Usernames and Passwords

After your cluster has been fully provisioned, use the instructs below to obtain and save passwords that were generated on your behalf during deployment.

  1. Log in to your new Compute Instance through Lish or SSH using the root user and the associated password you entered when creating the instance. If you opted to include your SSH keys as part of this deployment, you can also log in using those keys as either the root user or the limited user account you specified during deployment.

  2. The passwords have been saved in a .deployment-secrets.txt file located in your user’s home directory. You can view this file in your preferred text editor or through the cat command. In the command below, replace [username] with the limited sudo user you created during deployment.

    cat /home/[username]/.deployment-secrets.txt

    The file contains your system’s limited username and password.

    File: /home/[user]/.deployment-secrets.txt
    1
    2
    3
    4
    5
    6
    
    # BEGIN ANSIBLE MANAGED BLOCK
    # system user
    
    user: example-user
    password: v[[<]xw`pm/]:I+F2:$|1je!nqw|%V2h
    # END ANSIBLE MANAGED BLOCK

Using PostgreSQL

Modify the Postgres Users

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

Important
The postgres user should not be used for other purposes (e.g. connecting to other networks). Doing so presents a serious risk to the security of your databases.
  1. Change the postgres user’s Linux password:

    sudo passwd postgres
  2. Issue the following commands to set a password for the postgres database user. Be sure to replace newpassword with a strong password and keep it in a secure place.

    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 will be used to connect to the database via the network. Peer authentication will be used by default for local connections. See the Secure Local PostgreSQL Access section for information about changing this setting.

Create a Database

Run the commands in this section as the postgres Linux user.

  1. Create a sample database called mytestdb:

    createdb mytestdb
  2. Connect to the test database:

    psql mytestdb
  3. You will see the following output:

    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 which 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 in this section from the PostgreSQL client shell that you opened to create mytestdb database.

  1. Create a table called “employees” in your test database:

    CREATE TABLE employees (employee_id int PRIMARY KEY, first_name varchar, last_name varchar);
  2. Insert a record into the table:

    INSERT INTO employees VALUES (1, 'John', 'Doe');
  3. View the contents of the “employees” table:

    SELECT * FROM employees;

    This produces the following output:

    employee_id | first_name | last_name
    -------------+------------+-----------
                1 | John       | Doe
    (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 will not need to create the same role twice if you’d like 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:

    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:

    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:

    GRANT ALL ON employees TO examplerole;
  4. Exit the PostgreSQL shell by entering \q.

Next Steps

Note
Currently, Linode does not manage software and systems updates for Marketplace Apps. It is up to the user to perform routine maintenance on software deployed in this fashion.

For more on PostgreSQL, checkout the following guides:

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

This page was originally published on


Your Feedback Is Important

Let us know if this guide was helpful to you.