1. Guides & Tutorials
  2. Linode Platform
  3. One-Click Apps
  4. How to Deploy PostgreSQL with One-Click Apps

Deploying PostgreSQL with One-Click Apps

Updated by Rajakavitha Kodhandapani Contributed by Rajakavitha Kodhandapani

Contribute on GitHub

Report an Issue | View File | Edit File

PostgreSQL One-Click App

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.

Deploy a PostgreSQL One-Click App

Linode’s One-Click App Marketplace allow you to easily deploy software on a Linode using the Linode Cloud Manager. To access Linode’s One-Click App Marketplace:

  1. Log in to your Linode Cloud Manager account.

  2. From the Linode dashboard, click on the Marketplace button in the left-hand navigation menu.

  3. The Linode creation page will appear, with the One-Click and Marketplace tabs pre-selected.

  4. Under the Select App section, select the app you would like to deploy:

    Select a One-Click App to deploy

  5. Once you have selected the app, proceed to the app’s Options section and provide values for the required fields.

Linode Options

Configuration Description
Select an Image Debian 9 is currently the only image supported by the PostgreSQL One-Click App, and it is pre-selected on the Linode creation page. Required.
Region The region where you would like your Linode to reside. In general, it’s best to choose a location that’s closest to you. For more information on choosing a DC, review the How to Choose a Data Center guide. You can also generate MTR reports for a deeper look at the network routes between you and each of our data centers. Required.
Linode Plan Your Linode’s hardware resources. The Linode plan you deploy your PostgreSQL on should account for the estimated workload. If you are standing up a simple web page, you can use a Nanode or 2GB Linode. If you will deploy a more robust web app, then consider a plan with higher RAM and CPU allocations. If you decide that you need more or fewer hardware resources after you deploy your app, you can always resize your Linode to a different plan. Required.
Linode Label The name for your Linode, which must be unique between all of the Linodes on your account. This name will be how you identify your server in the Cloud Manager’s Dashboard. Required.
Root Password The primary administrative password for your Linode instance. This password must be provided when you log in to your Linode via SSH. It must be at least 6 characters long and contain characters from two of the following categories: lowercase and uppercase case letters, numbers, and punctuation characters. Your root password can be used to perform any action on your server, so make it long, complex, and unique. Required.

Getting Started after Deployment

Access PostgreSQL

After PostgreSQL has finished installing, you will be able to access PostgreSQL from the console via ssh with your Linode’s IPv4 address:

  1. SSH into your Linode and create a limited user account.

  2. Log out and log back in as your limited user account.

  3. Update your server:

    sudo apt-get update && apt-get upgrade

Using PostgreSQL

Modify the Postgres Users

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

Caution
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 One-Click 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.

Join our Community

Find answers, ask questions, and help others.

This guide is published under a CC BY-ND 4.0 license.