Guides - Deploying PostgreSQL through the Linode Marketplace
Quickly deploy a Compute Instance with many various software applications pre-installed and ready to use.
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 allows you to easily deploy software on a Compute Instance using the Cloud Manager. See Get Started with Marketplace Apps for complete steps.
Log in to the Cloud Manager and select the Marketplace link from the left navigation menu. This displays the Linode Create page with the Marketplace tab pre-selected.
Under the Select App section, select the app you would like to deploy.
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.
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.
NoteEstimated deployment time: PostgreSQL should be fully installed within 2-5 minutes after the Compute Instance has finished provisioning.
- Supported distributions: Debian 11
- Recommended minimum plan: All plan types and sizes can be used.
Limited User (Optional)
You can optionally fill out the following fields to automatically create a limited user for your new Compute Instance. This is recommended for most deployments as an additional security measure. This account will be assigned to the sudo group, which provides elevated permission when running commands with the
- Limited sudo user: Enter your preferred username for the limited user.
- Password for the limited user: Enter a strong password for the new user.
- SSH public key for the limited user: If you wish to login as the limited user through public key authentication (without entering a password), enter your public key here. See Creating an SSH Key Pair and Configuring Public Key Authentication on a Server for instructions on generating a key pair.
- Disable root access over SSH: To block the root user from logging in over SSH, select Yes (recommended). You can still switch to the root user once logged in and you can also log in as root through Lish .
Getting Started after Deployment
After PostgreSQL has finished installing, you will be able to access PostgreSQL from the console via ssh with your Linode’s IPv4 address:
Log out and log back in as your limited user account.
Update your server:
sudo apt-get update && sudo apt-get upgrade
Modify the Postgres Users
By default, PostgreSQL will create a Linux user named
postgres to access the database software.
postgresuser 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.
postgresuser’s Linux password:
sudo passwd postgres
Issue the following commands to set a password for the
postgresdatabase user. Be sure to replace
newpasswordwith 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
postgresLinux 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.
Create a sample database called
Connect to the test database:
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
\hcommand. You may find more information on a specific command by adding it after
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
Create a table called “employees” in your test database:
CREATE TABLE employees (employee_id int PRIMARY KEY, first_name varchar, last_name varchar);
Insert a record into the table:
INSERT INTO employees VALUES (1, 'John', 'Doe');
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)
Exit the PostgreSQL shell by entering the
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.
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
dropusercommand in place of
Connect to the database:
You’ll be connected as the
postgresdatabase user by default.
From the PostgreSQL shell, enter the following to grant all privileges on the table
employeesto the user
GRANT ALL ON employees TO examplerole;
Exit the PostgreSQL shell by entering
NoteCurrently, 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:
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