Deploy PostgreSQL through the Linode Marketplace

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.

Deploying a Marketplace App

The Linode Marketplace lets you easily deploy software on a Compute Instance 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 app you would like to deploy.

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 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 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.

Configuration Options

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

PostgreSQL Options

Limited Sudo User

You need to fill out the following fields to automatically create a limited sudo user, with a strong generated password for your new Compute Instance. This account will be assigned to the sudo group, which provides elevated permissions when running commands with the sudo prefix.

• Limited sudo user: Enter your preferred username for the limited user. No Capital Letters, Spaces, or Special Characters.

  Locating The Generated Sudo Password

  A password is generated for the limited user and stored in a .credentials file in their home directory, along with application specific passwords. This can be viewed by running: cat /home/$USERNAME/.credentials

  For best results, add an account SSH key for the Cloud Manager user that is deploying the instance, and select that user as an authorized_user in the API or by selecting that option in Cloud Manager. Their SSH pubkey will be assigned to both root and the limited user.

• Disable root access over SSH: To block the root user from logging in over SSH, select Yes. You can still switch to the root user once logged in, and you can also log in as root through Lish.

  Accessing The Instance Without SSH

  If you disable root access for your deployment and do not provide a valid Account SSH Key assigned to the authorized_user, you will need to login as the root user via the Lish console and run cat /home/$USERNAME/.credentials to view the generated password for the limited user.

Getting Started after Deployment

Obtain the Credentials

Once the app is deployed, you need to obtain the credentials from the server.

To obtain credentials:

1. Log in to your new Compute Instance using one of the methods below:

   • Lish Console: Log in to Cloud Manager, click the Linodes link in the left menu, and select the Compute Instance you just deployed. Click Launch LISH Console. Log in as the root user. To learn more, see Using the Lish Console.
   • SSH: Log in to your Compute Instance over SSH using the root user. To learn how, see Connecting to a Remote Server Over SSH.

2. Run the following command to access the credentials file:

   cat /home/$USERNAME/.credentials

This returns passwords that were automatically generated when the instance was deployed. Save them. Once saved, you can safely delete the file.

Using PostgreSQL

Modify the Postgres Users

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

1. To change to the PostgreSQL user’s Linux shell from root or the sudo user created during deployment, run the commands:

as root: su postgres
as sudo: sudo su postgres

Create a Database

To create a database and connect to it as the postgres Linux user:

1. To create a sample database called mytestdb, run:

   createdb mytestdb
   

2. To connect to the mytestdb database, run:

   psql mytestdb
   

You get 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

For more on PostgreSQL, checkout the following guides:

• Securely Manage Remote PostgreSQL Servers

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.

• pgAdmin Documentation
• PostgreSQL Documentation