How to install postgresql on ubuntu

How to Install Postgres on Ubuntu

SUMMARY: This article covers how to install PostgreSQL on Ubuntu Server for Linux. It goes over four steps for installation:

1. Enable the PostgreSQL apt repository

2. Install PostgreSQL on Ubuntu

3. Connect to PostgreSQL

4. Log in to the cluster

Here, we are going to look at how to install PostgreSQL on Ubuntu Server.

This post will help you with installing the PostgreSQL database server on your Ubuntu 18.04, Ubuntu 16.04 and Ubuntu 14.04 systems.

Step 1 – Enable the PostgreSQL apt repository

PostgreSQL packages are available in the default Ubuntu repository, but other versions of PostgreSQL are also available through the PostgreSQL apt repository.

To use the apt repository according to Ubuntu version, follow these steps:

1. Visit the PostgreSQL Ubuntu download site:

2. Select your version of Ubuntu and copy the corresponding line for the apt repository.

For Example:

For Ubuntu 18.04 you should select Bionic (18.04):

3. Create the file “/etc/apt/sources.list.d/pgdg.list” and add the corresponding line for the repository in it:

4. Import the repository signing key and update the package lists:

Step 2 – Install PostgreSQL on Ubuntu

We have added the PostgreSQL official repository to our system, now we need to update the repository list:

To install PostgreSQL on Ubuntu, use the apt-get (or other apt-driving) command:

Step 3 – Connect to PostgreSQL

During PostgreSQL installation, by default, it creates a user “postgres” and also creates a system account (Operating System User) with the same name “postgres.”

So to connect to the PostgreSQL server, log in to your system as user “postgres.”

Step 4 – Log in to the cluster

On Ubuntu, the cluster is initialized during the installation. You can invoke the below command to find the status of the running PostgreSQL cluster:

Now you can log in to the PostgreSQL cluster and query through the psql client:

Your PostgreSQL installation has been completed successfully.

Search Results

No Results

Filters

How to Install and Use PostgreSQL on Ubuntu 20.04.

This guide provides an introduction to PostgreSQL, an open source object-relational database management system (ORDBMS). PostgreSQL builds upon the original Structured Query Language (SQL) specification with many new features, emphasizing compliance. PostgreSQL transactions are atomic, consistent, isolated, and durable (ACID)compliant. PostgreSQL is one of the most popular database systems, and is available for most operating systems, including Ubuntu 20.04.

Before You Begin

If you have not already done so, create a Linode account and Compute Instance. See our Getting Started with Linode and Creating a Compute Instance guides.

Follow our Setting Up and Securing a Compute Instance guide to update your system. You may also wish to set the timezone, configure your hostname, create a limited user account, and harden SSH access.

Advantages and Disadvantages of PostgreSQL

PostgreSQL is a feature-rich application, offering advanced features such as materialized views, triggers, and stored procedures. It can handle a very high workload, including data warehouses or highly-scaled web applications, and is noted for its stability. PostgreSQL can be extended with custom data types and functions, and can integrate with code from different languages. However, due to its focus on compatibility, it does not always match other database systems in terms of performance. In addition, not all open source applications support PostgreSQL.

With a large database schema, PostgreSQL can consume a substantial amount of disc space. To store large amounts of data, we recommend hosting PostgreSQL on a High Memory Linode.

A Summary of the PostgreSQL Installation and Configuration Process

A complete PostgreSQL installation, including basic configuration tasks, consists of the following high-level steps. The following sections describe each step in more detail:

Installing PostgreSQL

Installing the Latest Version of PostgreSQL From the Ubuntu Packages

Update and upgrade the existing packages.

Install PostgreSQL and all dependencies, along with the postgresql-contrib module that provides additional functionality.

Verify PostgreSQL is running as expected.

Installing PostgreSQL From the PostgreSQL Apt Repository

Installing PostgreSQL from the PostgreSQL repository allows you more control over what version to choose. The following process installs the latest stable version of PostgreSQL. As of early 2021, this is version 13.1. You can also choose to install an earlier release of PostgreSQL.

Update and upgrade the existing packages.

Add the new file repository configuration.

Import the signing key for the repository.

Update the package lists.

Install the latest version of PostgreSQL.

Securing PostgreSQL and Accessing the PostgreSQL Shell

Linode recommends increasing the security of new PostgreSQL installation before populating the database. PostgreSQL automatically creates a default user named postgres upon installation. The postgres user has full superadmin privileges, so it is particularly important to implement Linux and database passwords for the account.

Change the password for the postgres Linux account. Choose a strong password and store it in a secure place.

Switch over to the postgres account.

Change the password for the postgres PostgreSQL user to use when connecting over a network.

Confirm PostgreSQL is working properly and you are running the version you expect with the following command. This command returns the version of the PostgreSQL server.

This command returns the version of the server component, which might not be as recent as the overall release number.

Log in to PostgreSQL to confirm you can access the database.

PostgreSQL displays some information about the version and provides a prompt. The output depends upon the details of the installation.

By default, PostgreSQL grants access to local system users without requiring a password. This is known as peer authentication. PostgreSQL obtains the system name of the user and verifies it against the database privileges. To enforce password authentication from local users, you must edit the pg_hba.conf file. Run the following command within the psql shell to determine the location of this file.

PostgreSQl returns a table showing you where the file is located.

Exit PostgreSQL with the \q meta-command, and return to the Linux shell.

Restart PostgreSQL to apply the new access rule.

Installing the PostgreSQL Administration Package

PostgreSQL provides an Adminpack module, which supplies a suite of management and administration tools. More details about the module can be found on the PostgreSQL documentation site. To install this component, perform the following actions.

Enable the extension.

Confirm you correctly installed the module with the dx meta-command.

Adminpack should be listed as one of the modules.

Using PostgreSQL

PostgreSQL operations are based on SQL, which is the standard language for RDBMS systems. If you have worked with SQL before, the commands in the following sections should look familiar. If not, the PostgreSQL documentation offers a short tutorial to get you started.

Working With PostgreSQL Databases

Before creating any tables or adding any table rows, you must create a database to store the data. A database is a collection of one or more tables, and a database cluster refers to all the databases that a PostgreSQL server collectively manages. See the PostgreSQL documentation for more information about databases.

Connect to the new database directly.

The PostgreSQL prompt now shows you are in the testdatabase database.

List all of the databases in the cluster with the \l meta-command.

The new database should appear in the list.

Whenever you are logged into PostgreSQL, you can always find out what database you are logged into, along with the name of the role, using the conninfo meta-command.

The command returns basic information about the database.

While you are logged into PostgreSQL, you can switch to a different database using the \c meta-command.

If you are absolutely certain you do not need a database any longer, you can delete it with the dropdb command.

Working With PostgreSQL Tables

A database table is defined as a set of named columns, each with a specific data type. More information about tables can be found in the PostgreSQL documentation. To create a table inside the testdatabase database, follow these steps.

Connect to the database.

List all of the tables in the database with the \dt meta-command.

PostgreSQL returns a list of all of the tables, including the table you created.

View the complete schema of the customers table, including all of the columns and data types, with the \d+ meta-command.

To delete an existing table, use the DROP TABLE command.

Adding and Reading Data With PostgreSQL

Tables store the actual data as a series of rows. Each row represents an entry within the table, with values for each of the columns. A row represents an individual record within the table. Data is added to a PostgreSQL table on a row-by-row basis with the INSERT command, and is retrieved with the SELECT command. For more information on row insertion, see the PostgreSQL Documentation.

Connect to the database.

Add rows to the customers table using the INSERT INTO command. Follow the command name with the name of the table, the keyword VALUES and a comma-separated list of the values to insert. The values must match the order of the columns in the table definition.

View the contents of a table with the SELECT command.

PostgreSQL returns a list of all columns from all rows in the table.

To view a subset of the table columns, list the specific columns you want to retrieve in place of the * symbol.

In this case, PostgreSQL only returns a list of the last names of the customers.

For this query, PostgreSQL only returns one row.

Use the DELETE command to remove a row from a PostgreSQL table. The WHERE keyword is used to conditionally select rows to delete. If you use the * symbol, all rows are deleted.

Rows can be edited with the UPDATE command. You must specify the columns you want to edit along with the new values. You should also include a conditional expression in the form of a WHERE clause to indicate the rows to change.

View the contents of the entire customers table to see all of the changes.

Working With PostgreSQL Columns

Linode strongly recommends you finalize the complete database schema before implementing it. Nevertheless, there might be times when you have to change your table structure later. PostgreSQL provides the ability to add or delete columns from existing tables, even if they are already populated with data.

If you want to add a column to a pre-existing table, use the ALTER TABLE command. PostgreSQL sets this field to empty for any existing rows in the table.

To delete a column from a table, use the ALTER TABLE command with the DROP keyword. PostgreSQL removes all data from the column from all rows.

Use the d+ meta-command to view the new table structure.

PostgreSQL displays the columns that are currently part of the table.

Creating PostgreSQL Roles (Users) and Groups

For a small single-user database, you might only require the default PostgreSQL account. However, for most applications, you should create a separate account for each user. You can create new PostgreSQL users with the following steps:

Log in with the postgres Linux account, and create a new PostgreSQL role with the createuser command.

PostgreSQL prompts you for a password. Choose a secure password for this user and enter it twice.

To confirm the role has been created, enter the PostgreSQL shell and execute the \du meta-command.

PostgreSQL returns a list of all the roles. The new testuser role does not have any attributes yet.

You can now access the database as this user.

PostgreSQL shows a different prompt this time.

A user’s role can be expanded with the ALTER ROLE command. Run the following command as the postgres user to allow testuser to create databases.

The \du command now displays the new privilege.

PostgreSQL allows you to create groups to simplify the assignment of database privileges. First create a group in the Linux shell.

Enter the PostgreSQL shell as the postgres user and then assign users to the new group.

Use the \du meta-command to confirm the new group and its membership.

To delete a user or group, execute the dropuser command from the Linux shell.

Accessing PostgreSQL Remotely

Linode does not recommend opening up PostgreSQL to listen for connections on public IP addresses because this poses a security risk. If you want to access PostgreSQL remotely, you can use a graphical user interface called pgAdmin. See one of the following Linode guides to pgAdmin for more information:

Learning More About PostgreSQL

This guide only covers the fundamentals, but PostgreSQL is a complex application with many options. Linode recommends you spend some time learning more about PostgreSQL. The PostgreSQL documentation is very comprehensive, and includes a good introductory tutorial. An active community of users supports PostgreSQL. You can find links to some of these user groups on the PostgreSQL site.

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 Friday, February 19, 2021.

Установка PostgreSQL в Ubuntu

Её преимущество в множестве дополнительных функций и улучшений, таких как надежная передача данных и параллелизация без блокировок чтения. Вы можете использовать эту СУБД с различными языками программирования, а её синтаксис запросов PL/pgSQL очень похож на MySQL от Oracle. В этой статье мы рассмотрим, как выполняется установка PostgreSQL в Ubuntu 20.04 из официальных репозиториев и репозитория PostgreSQL (PPA) а так же, как выполнить первоначальную настройку и подготовку к работе c данной СУБД.

Установка PostgreSQL в Ubuntu 20.04

1. Установка из официальных репозиториев

Это очень популярная СУБД, потому программа присутствует в официальных репозиториях. Для установки выполните следующие команды. Сначала обновите списки пакетов:

sudo apt update

Установите СУБД PostgreSQL:

2. Установка из официальных репозиториев PostgreSQL

Если есть необходимость в получение самой последней версии, то необходимо добавить в систему официальный PPA от разработчиков PostgreSQL. Для этого выполните следующие команды:

Далее обновите списки пакетов, чтобы получить самую новую доступную версию:

sudo apt update

Установка PostgreSQL из PPA или официальных репозиториев выглядит одинаково:

Настройка PostgreSQL в Ubuntu 20.04

После установки СУБД откройте терминал и переключитесь на пользователя postgres с помощью команды:

Эта учетная запись создается во время установки программы и на данный момент вы можете получить доступ к системе баз данных только с помощью нее. По умолчанию PostgreSQL использует концепцию ролей для аутентификации и авторизации.

Это очень похоже на учетные записи Unix, но программа не различает пользователей и групп, есть только роли. Сразу после установки PostgreSQL пытается связать свои роли с системными учетными записями, если для имени системной учетной записи существует роль, то пользователь может войти в консоль управления и выполнять позволенные ему действия. Таким образом, после переключения на пользователя postgres вы можете войти в консоль управления:

И посмотреть информацию о соединении:

Чтобы выйти наберите:

Теперь рассмотрим, как создать другие роли и базы данных.

Создание роли postgresql

Вы уже можете полноценно работать с базой данных с помощью учетной записи postgres, но давайте создадим дополнительную роль. Учетная запись postgres является администратором, поэтому имеет доступ к функциям управления. Для создания пользователя выполните команду:

Скрипт задаст лишь два вопроса, имя новой роли и нужно ли делать ее суперпользователем.

Создание базы данных

Точно также как имена ролей сопоставляются с системными пользователями, имя базы данных будет подбираться по имени пользователя. Например, если мы создали пользователя alex, то по умолчанию система попытается получить доступ к базе данных alex. Мы можем ее очень просто создать:

Дальше, чтобы подключиться к этой базе данных нам нужно войти от имени одноименного пользователя:

Заходим в консоль и смотрим информацию о подключении:

Все верно сработало. Мы подключились с помощью роли alex к базе alex. Если нужно указать другую базу данных, вы можете сделать это с помощью опции -d, например:

Все сработало верно, при условии, что все компоненты были настроены как описано выше.

Создание таблиц

Теперь, когда вы знаете, как подключится к базе данных PostgreSQL, давайте рассмотрим, как выполняются основные задачи. Сначала разберем создание таблиц для хранения некоторых данных. Для создания таблицы PostgreSQLиспользуется такой синтаксис:

CREATE TABLE имя_таблицы (имя_колонки1 тип_колонки (длина) ограничения, имя_колонки2 тип_колонки (длина), имя_колонки3 тип_колонки (длина));

Как видите, сначала мы задаем имя таблицы, затем описываем каждый столбец. Столбец должен иметь имя, тип и размер, также можно задать ограничения для данных, которые там будут содержаться. Например:

CREATE TABLE playground (equip_id serial PRIMARY KEY, type varchar (50) NOT NULL, color varchar (25) NOT NULL, location varchar(25) check (location in (‘north’, ‘south’, ‘west’, ‘east’, ‘northeast’, ‘southeast’, ‘southwest’, ‘northwest’)), install_date date );

Мы создали таблицу детской площадки для описания оборудования, которое на ней есть. Сначала идет идентификатор equip_id, который имеет тип serial, это значит, что его значение будет автоматически увеличиваться, ключ primary key значит, что значения должны быть уникальны.

Вы можете вывести все таблицы, выполнив команду:

Выводы

Теперь установка Postgresql в Ubuntu 20.04 завершена, и вы прошли краткий экскурс в синтаксис PgSQL, который очень похож на привычный нам MySQL, но имеет некоторые отличия. Если у вас остались вопросы, спрашивайте в комментариях!

How To Install PostgreSQL on Ubuntu 20.04 [Quickstart]

Introduction

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It’s standards-compliant and has many advanced features like reliable transactions and concurrency without read locks.

This guide demonstrates how to quickly get Postgres up and running on an Ubuntu 20.04 server, from installing PostgreSQL to setting up a new user and database. If you’d prefer a more in-depth tutorial on installing and managing a PostgreSQL database, see How To Install and Use PostgreSQL on Ubuntu 20.04.

Prerequisites

To follow along with this tutorial, you will need one Ubuntu 20.04 server that has been configured by following our Initial Server Setup for Ubuntu 20.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.

Step 1 — Installing PostgreSQL

To install PostgreSQL, first refresh your server’s local package index:

Ensure that the service is started:

Step 2 — Using PostgreSQL Roles and Databases

By default, Postgres uses a concept called “roles” to handle authentication and authorization. These are, in some ways, similar to regular Unix-style users and groups.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres. One way is to switch over to the postgres account on your server by running the following command:

Then you can access the Postgres prompt by running:

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

To exit out of the PostgreSQL prompt, run the following:

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo :

This will log you directly into Postgres without the intermediary bash shell in between.

Again, you can exit the interactive Postgres session by running the following:

Step 3 — Creating a New Role

If you are logged in as the postgres account, you can create a new role by running the following command:

If, instead, you prefer to use sudo for each command without switching from your normal account, run:

Either way, the script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications.

Step 4 — Creating a New Database

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

This means that if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called “sammy” by default. You can create the appropriate database with the createdb command.

If you are logged in as the postgres account, you would type something like the following:

If, instead, you prefer to use sudo for each command without switching from your normal account, you would run:

Step 5 — Opening a Postgres Prompt with the New Role

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

Once this new account is available, you can either switch over and connect to the database by running the following:

Or, you can do this inline:

This command will log you in automatically, assuming that all of the components have been properly configured.

If you want your user to connect to a different database, you can do so by specifying the database like the following:

Once logged in, you can get check your current connection information by running:

Conclusion

You are now set up with PostgreSQL on your Ubuntu 20.04 server. If you’d like to learn more about Postgres and how to use it, we encourage you to check out the following guides:

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

How to Install PostgreSQL on Ubuntu 18.04

Updated Feb 13, 2019

PostgreSQL or Postgres is an open-source general-purpose object-relational database management system. PostgreSQL has many advanced features which allow you to create complex web applications.

In this tutorial, we will show you how to install PostgreSQL on Ubuntu 18.04 and explore the fundamentals of basic database administration.

Prerequisites #

Install PostgreSQL on Ubuntu #

At the time of writing this article, the latest version of PostgreSQL available from the official Ubuntu repositories is PostgreSQL version 10.4.

To install PostgreSQL on your Ubuntu server follow the steps below:

Refresh the local package index and install the PostgreSQL server along with the PostgreSQL contrib package which provides several additional features for the PostgreSQL database:

Verifying PostgreSQL Installation

Once the installation is completed, the PostgreSQL service will start automatically.

To verify the installation we will try to connect to the PostgreSQL database server using the psql and print the server version :

PostgreSQL Roles and Authentication Methods #

Database access permissions within PostgreSQL are handled with the concept of roles. A role can represent a database user or a group of database users.

The postgres user is created automatically when you install PostgreSQL. This user is the superuser for the PostgreSQL instance and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as the postgres user first you need to switch to the user postgres and then you can access a PostgreSQL prompt using the psql utility:

From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:

You can also access the PostgreSQL prompt without switching users using the sudo command:

The postgres user is typically used only from the local host and it is recommended not to set the password for this user.

Creating PostgreSQL Role and Database #

You can create new roles from the command line using the createuser command. Only superusers and roles with CREATEROLE privilege can create new roles.

In the following example, we will create a new role named john a database named johndb and grant privileges on the database.

Create a new PostgreSQL Role

The following command will create a new role named “john”:

Create a new PostgreSQL Database

Create a new database named “johndb” using the createdb command:

To grant permissions to the john user on the database we created in the previous step, connect to the PostgreSQL shell:

and run the following query:

Enable remote access to PostgreSQL server #

save the file and restart the PostgreSQL service with:

Verify the changes with the ss utility:

As you can see from the output above the PostgreSQL server is listening on all interfaces (0.0.0.0).

The last step is to configure the server to accept remote connections by editing the pg_hba.conf file.

Below are some examples showing different use cases:

Conclusion #

You have learned how to install and configure PostgreSQL on your Ubuntu 18.04 server.

Consult the PostgreSQL 10.4 Documentation for more information on this topic.

If you have any questions, please leave a comment below.