PostgreSQL

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

1. Deploy PostgreSQL

 Estimated deployment time: 2-5 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: 611376

Note: After the app deployment completes, the password for your limited sudo user is generated and stored in the .credentials file in the home directory, along with application-specific passwords. Log in to your instance as root through the Lish console or SSH, then run cat /home/$USERNAME/.credentials 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-one-click-app",
    "root_pass": "@C0mpl3x#P@ssw0rd",
    "stackscript_id": 611376,
    "stackscript_data": {
      "user_name": "jsmith"
    }
  }'

linode-cli linodes create \
  --region us-east \
  --type g6-standard-2 \
  --label my-postgresql-one-click-app \
  --image linode/ubuntu24.04 \
  --root_pass @C0mpl3x#P@ssw0rd \
  --stackscript_id 611376 \
  --stackscript_data '{"user_name":"jsmith"}'

resource "linode_instance" "my-linode" {
  region         = "us-east"
  type           = "g6-standard-2"
  label          = "my-postgresql-one-click-app"
  image          = "linode/ubuntu24.04"
  root_pass      = "@C0mpl3x#P@ssw0rd"
  stackscript_id = 611376
  stackscript_data = {
    "user_name" = "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 to the PostgreSQL user’s Linux shell from root or the sudo user created during deployment.

   Bash

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