One place for hosting & domains

      PostgreSQL

      Установка и использование PostgreSQL в CentOS 8


      Введение

      Системы управления реляционными базами данных являются ключевым компонентом многих веб-сайтов и приложений. Они предоставляют структурированный способ хранения, организации и доступа к информации.

      PostgreSQL, или Postgres, — это система управления реляционными базами данных, которая обеспечивает имплементацию языка структурированных запросов, более известного как SQL. Она используется во многих популярных проектов, как в крупных, так и в небольших, соответствует стандартам и имеет множество продвинутых функций, таких как надежные транзакции и многопоточность без блокировок чтения.

      Следуя указаниям этого руководства, вы установите последнюю версию PostgreSQL на сервер CentOS 8.

      Предварительные требования

      Для выполнения этого обучающего руководства вам потребуется сервер на базе CentOS 8. Этот сервер должен иметь пользователя non-root user с административными правами, а также брандмауэр, настроенный с помощью firewalld. Чтобы выполнить настройку сервера, воспользуйтесь руководством по начальной настройке сервера CentOS 8.

      Шаг 1 — Установка PostgreSQL

      PostgreSQL доступна в используемом по умолчанию репозитории программного обеспечения AppStream в CentOS 8 и имеет несколько версий, которые можно установить. Вы можете выбрать между этими версиями, активируя подходящий набор пакетов и зависимостей, которые соответствуют версии, которую вы хотите установить, а каждый набор называется потоком модуля.

      В DNF, используемом по умолчанию в CentOS 8 менеджере пакетов, модули представляют собой наборы пакетов RPM, которые вместе составляют крупное приложение. Это необходимо, чтобы сделать установку пакетов и их зависимостей более интуитивно понятной для пользователей.

      В первую очередь просмотрите список доступных потоков для модуля postgresql с помощью команды dnf:

      • dnf module list postgresql

      Output

      postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 client, server PostgreSQL server and client module

      В этом выводе вы можете видеть все три версии PostgreSQL из репозитория AppStream: 9.6, 10 и 12. Поток, который предоставляет Postgres версии 10, является потоком по умолчанию, о чем сигнализирует [d] в его конце. Если вы хотите установить эту версию, запустите sudo dnf install postgresql-server и перейдите к следующему шагу. Однако, хотя версия 10 до сих пор поддерживается, в этом обучающем руководстве мы будем устанавливать версию 12, последний релиз на момент написания.

      Для установки PostgreSQL версии 12 необходимо активировать поток модуля этой версии. При активации потока модуля вы переопределяете поток по умолчанию и делаете все пакеты, связанные с активированным потоком, доступными в системе. Обратите внимание, что только один поток заданного модуля можно активировать в системе в один момент времени.

      Чтобы активировать поток модуля для Postgres версии 12, запустите следующую команду:

      • sudo dnf module enable postgresql:12

      При появлении запроса нажмите y, а затем ENTER, чтобы подтвердить, что вы хотите активировать поток:

      Output

      ==================================================================== Package Architecture Version Repository Size ==================================================================== Enabling module streams: postgresql 12 Transaction Summary ==================================================================== Is this ok [y/N]: y

      Чтобы активировать поток модуля версии 12, вы можете установить пакет postgresql-server для установки PostgreSQL 12 и всех зависимостей:

      • sudo dnf install postgresql-server

      При появлении запроса подтвердите установку, нажав y, а затем ENTER:

      Output

      . . . Install 4 Packages Total download size: 16 M Installed size: 62 M Is this ok [y/N]: y

      После установки программного обеспечения мы выполним ряд шагов инициализации для подготовки нового кластера базы данных для PostgreSQL.

      Шаг 2 — Создание нового кластера баз данных PostgreSQL

      Вам нужно создать новый кластер баз данных PostgreSQL, прежде чем вы сможете создавать таблицы и загружать в них данные. Кластер баз данных — это набор баз данных, управляемых одним экземпляром сервера. Создание кластера баз данных включает в себя создание директорий, в которых будут размещаться данные базы данных, создание таблиц общего каталога и создание баз данных template1 и postgres.

      База данных template1 представляет собой шаблон сортировок, который используется для создания новых баз данных, все, что хранится в template1, даже объекты, которые вы добавляете самостоятельно, будут размещаться в новых базах данных при их создании. База данных postgres — это база данных по умолчанию, предназначенная для пользователей, утилит и сторонних приложений.

      Пакет Postgres, установленный на предыдущем шаге, содержит удобный скрипт с именем postgresql-setup, который помогает администрированию кластеров баз данных на низком уровне. Для создания кластера баз данных запустите скрипт с помощью sudo и опции --initdb:

      • sudo postgresql-setup --initdb

      Результат будет выглядеть следующим образом:

      Output

      * Initializing database in '/var/lib/pgsql/data' * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

      Теперь запустите службу PostgreSQL с помощью systemctl:

      • sudo systemctl start postgresql

      Затем воспользуйтесь systemctl еще раз, чтобы активировать запуск службы при загрузке сервера:

      • sudo systemctl enable postgresql

      Результат будет выглядеть следующим образом:

      Output

      Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

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

      Шаг 3 — Использование ролей и баз данных PostgreSQL

      PostgreSQL использует концепцию ролей для выполнения аутентификации и авторизации клиента. В некоторых аспектах они напоминают обычные учетные записи в Unix, однако Postgres не делает различий между пользователями и группами и предпочитает использовать более гибкий термин — роль.

      После установки Postgres настроена на использование аутентификации ident, что значит, что выполняется привязка ролей Postgres с соответствующей системной учетной записью Unix/Linux. Если роль существует внутри Postgres, пользователь Unix/Linux с тем же именем может выполнить вход в качестве этой роли.

      В ходе установки была создана учетную запись пользователя postgres, которая связана с используемой по умолчанию ролью postgres. Чтобы использовать PostgreSQL, вы можете войти в эту учетную запись.

      Существует несколько способов использования этой учетной записи для доступа к командной строке PostgreSQL.

      Переключение на учетную запись postgres

      Вы можете переключиться на учетную запись postgres на вашем сервере с помощью следующей команды:

      Теперь вы можете немедленно получить доступ к командной строке Postgres с помощью следующей команды:

      В результате вы можете получить доступ к командной строке PostgreSQL, а уже отсюда свободно взаимодействовать с системой управления базами данных.

      Закройте командную строку PostgreSQL с помощью следующей команды:

      В результате вы вернетесь в командную строку Linux для учетной записи postgres. Теперь вернитесь к первоначальной учетной записи следующим образом:

      Доступ к командной строке Postgres без переключения учетных записей

      Также вы можете запускать команды с помощью учетной записи postgres напрямую, используя sudo.

      Например, в последнем примере от вас требовалось перейти в командную строку Postgres с помощью переключения на пользователя postgres и последующего запуска psql, чтобы открыть командную строку Postgres. Вы можете сделать это в один прием с помощью отдельной команды psql, используя пользователя postgres с sudo следующим образом:

      Это позволит выполнить вход в Postgres без необходимости использования промежуточной командной строки bash.

      Вы снова сможете выйти из интерактивного сеанса Postgres с помощью следующей команды:

      На этом шаге мы использовали учетную запись postgres для получения доступа к командной строке psql. Многие варианты использования требуют использования сразу нескольких ролей Postgres. Ниже вы узнаете, как выполнить настройку новых ролей.

      Шаг 4 — Создание новой роли

      К настоящему моменту у вас есть только роль postgres, настроенная внутри базы данных. Вы можете создавать новые роли из командной строки с командой createrole. Флаг --interactive будет запрашивать имя новой роли, а также попросит указать, будут ли у этой роли права суперпользователя.

      Если вы выполнили вход в учетную запись postgres, то можете создать нового пользователя с помощью следующей команды:

      Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:

      • sudo -u postgres createuser --interactive

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

      Output

      Enter name of role to add: sammy Shall the new role be a superuser? (y/n) y

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

      В результате у вас будет новая роль Postgres, но вам предстоит еще добавить базы данных. В следующем разделе описан этот процесс.

      Шаг 5 — Создание новой базы данных

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

      Это означает, что если созданный вами в последнем разделе пользователь будет иметь имя sammy, эта роль попытается подключиться к базе данных, которая также называется sammy по умолчанию. Вы можете создать такую базу данных с помощью команды createdb.

      Если вы используете учетную запись postgres, необходимо ввести следующее:

      Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:

      • sudo -u postgres createdb sammy

      Подобная гибкость предоставляет несколько способов для создания баз данных в зависимости от необходимости.

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

      Шаг 6 — Открытие командной строки Postgres с новой ролью

      Чтобы выполнить аутентификацию с помощью сущности, вам потребуется пользователь Linux с тем же именем, что и имя роли и базы данных в Postgres.

      Если у вас нет соответствующего пользователя Linux, то вы можете создать его с помощью команды adduser. Вы должны воспользоваться учетной записью без прав root с привилегиями sudo (т. е. не выполняя вход в качестве пользователя postgres):

      Когда новая учетная запись будет доступна, вы можете переключиться и потом подключиться к базе данных следующим образом:

      Либо же вы можете сделать следующее:

      Эта команда будет выполнять вход автоматически.

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

      После входа вы можете проверить данные о текущем подключении следующим образом:

      В результате мы получаем следующий вывод:

      Output

      You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

      Это полезно, если вы подключены к нестандартным базам данных или используете нестандартных пользователей.

      При подключении к базе данных вы можете попробовать создать и удалить таблицы.

      Шаг 7 — Создание и удаление таблиц

      Теперь, когда вы уже знаете, как подключиться к системе управления базами данных PostgreSQL, можно переходить к знакомству с основными задачами по управлению, которые решает Postgres.

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

      Базовый синтаксис этой команды выглядит следующим образом:

      CREATE TABLE table_name (
          column_name1 col_type (field_length) column_constraints,
          column_name2 col_type (field_length),
          column_name3 col_type (field_length)
      );
      

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

      Чтобы продемонстрировать процесс, создайте простую таблицу:

      • 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
      • );

      Эта команда создает таблицу с описью оборудования для установки на игровых площадках. Она начинается с идентификатора оборудования, который имеет тип serial. Это тип данных представляет собой целое число с автоматическим инкрементированием. Вы также указали для этого столбца ограничение в виде PRIMARY KEY, что означает, что значения должны быть уникальными и не равны null.

      Для двух из столбцов (equip_id и install_date) команда не указывает длину поля. Это объясняется тем, что некоторые типы столбцов не требуют указания длины, поскольку длина подразумевается в зависимости от типа значения.

      Следующие две строки создают столбцы type и color оборудования соответственно, каждый из которых не может быть пустым. Следующая строка создает столбец location и ограничение, требующее, чтобы значение было одним из восьми возможных вариантов. Последняя строка создает столбец даты, которая указывает дату установки оборудования.

      Вы можете просмотреть вашу новую таблицу, введя следующую команду:

      В результате мы получаем следующий вывод:

      Output

      List of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | sammy public | playground_equip_id_seq | sequence | sammy (2 rows)

      Ваша таблица игрового оборудования готова, но здесь есть что-то под названием playground_equip_id_seq с типом данных sequence. Это представление типа serial, который присвоен столбцу equip_id. Оно отслеживает следующий номер последовательности и создается автоматически для столбцов данного типа.

      Если вы хотите только просмотреть таблицу без последовательности, можете ввести следующую команду:

      В результате вы получите следующее:

      Output

      List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | playground | table | sammy (1 row)

      На этом шаге мы создали образец таблицы. На следующем шаге мы попробуем добавить, запросить и удалить элементы в таблице.

      Шаг 8 — Добавление, запрос и удаление данных в таблице

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

      В качестве примера добавьте строги swing (качели) и slide (горка), вызвав таблицу, куда вы хотите добавить данные, указав столбцы и предоставив данные для каждого столбца:

      • INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
      • INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

      Вы должны быть внимательны при вводе данных, чтобы не допустить нескольких общих проблем. Во-первых, оборачивайте в кавычки не названия столбцов, а значения в столбцах.

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

      Получите добавленную вами информацию, введя следующую команду:

      • SELECT * FROM playground;

      Результат будет выглядеть следующим образом:

      Output

      equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2017-04-28 2 | swing | yellow | northwest | 2018-08-16 (2 rows)

      Вы можете убедиться, что столбец equip_id уже заполнен успешно, а все другие данные были организованы корректно.

      Если же строка slide в таблице разрывается, вы можете удалить строку из таблицы, использовав следующую команду:

      • DELETE FROM playground WHERE type = 'slide';

      Запросите таблицу еще раз:

      • SELECT * FROM playground;

      Вы увидите следующее:

      Output

      equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 2 | swing | yellow | northwest | 2018-08-16 (1 row)

      Вы видите, что ваша горка уже не является частью таблицы.

      Теперь, когда вы добавили и удалили элементы в таблице, вы можете попробовать добавить и удалить столбцы.

      Шаг 9 — Добавление и удаление столбцов из таблицы

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

      • ALTER TABLE playground ADD last_maint date;

      Если вы снова просмотрите данные таблицы, то увидите, что новый столбец был добавлен (но не были добавлены данные):

      • SELECT * FROM playground;

      Вы увидите следующее:

      Output

      equip_id | type | color | location | install_date | last_maint ----------+-------+--------+-----------+--------------+------------ 2 | swing | yellow | northwest | 2018-08-16 | (1 row)

      Удаление столбца является таким же простым. Если вы обнаружите, что ваши работники используют отдельный инструмент для отслеживания истории обслуживания, то можете удалить столбец, введя следующую команду:

      • ALTER TABLE playground DROP last_maint;

      Эта команда удаляет столбец last_maint и любые значения внутри него, но оставляет все другие данные нетронутыми.

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

      Шаг 10 — Обновление данных в таблице

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

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

      • UPDATE playground SET color = 'red' WHERE type = 'swing';

      Вы можете убедиться, что операция прошла успешно, запросив данные еще раз:

      • SELECT * FROM playground;

      Вы увидите следующее:

      Output

      equip_id | type | color | location | install_date ----------+-------+-------+-----------+-------------- 2 | swing | red | northwest | 2010-08-16 (1 row)

      Как видите, ваша горка теперь имеет красный цвет.

      Заключение

      Вы успешно настроили PostgreSQL на вашем сервере CentOS 8. Однако вам еще очень многое предстоит узнать о Postgres. Ниже вы найдете несколько руководств по использованию Postgres:



      Source link

      How To Install and Use PostgreSQL on CentOS 8


      Introduction

      Relational database management systems are a key component of many websites and applications. They provide a structured way to store, organize, and access information.

      PostgreSQL, also known as Postgres, is a relational database management system that provides an implementation of Structured Query Language, better known as SQL. It’s used by many popular projects, both large and small, is standards-compliant, and has many advanced features like reliable transactions and concurrency without read locks.

      By following this guide, you will install the latest version of PostgreSQL on a CentOS 8 server.

      Prerequisites

      To complete this tutorial, you will need a server running CentOS 8. This server should have a non-root user with administrative privileges and a firewall configured with firewalld. To set this up, see our Initial Server Setup guide for CentOS 8.

      Step 1 — Installing PostgreSQL

      PostgreSQL is available from CentOS 8’s default AppStream software repository, and there are multiple versions which you can install. You can choose between these versions by enabling the appropriate collection of packages and dependencies that align with the version you want to install, with each collection referred to as a module stream.

      In DNF, CentOS 8’s default package manager, modules are special collections of RPM packages that together make up a larger application. This is intended to make installing packages and their dependencies more intuitive for users.

      List out the available streams for the postgresql module using the dnf command:

      • dnf module list postgresql

      Output

      postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 client, server PostgreSQL server and client module

      You can see in this output that there are three versions of PostgreSQL available from the AppStream repository: 9.6, 10, and 12. The stream that provides Postgres version 10 is the default, as indicated by the [d] following it. If you want to install that version you could just run sudo dnf install postgresql-server and move on to the next step. However, even though version 10 is still maintained, this tutorial will install Postgres version 12, the latest release at the time of this writing.

      To install PostgreSQL version 12, you must enable that version’s module stream. When you enable a module stream, you override the default stream and make all of the packages related to the enabled stream available on the system. Note that only one stream of any given module can be enabled on a system at the same time.

      To enable the module stream for Postgres version 12, run the following command:

      • sudo dnf module enable postgresql:12

      When prompted, press y and then ENTER to confirm that you want to enable the stream:

      Output

      ==================================================================== Package Architecture Version Repository Size ==================================================================== Enabling module streams: postgresql 12 Transaction Summary ==================================================================== Is this ok [y/N]: y

      After enabling the version 12 module stream, you can install the postgresql-server package to install PostgreSQL 12 and all of its dependencies:

      • sudo dnf install postgresql-server

      When given the prompt, confirm the installation by pressing y then ENTER:

      Output

      . . . Install 4 Packages Total download size: 16 M Installed size: 62 M Is this ok [y/N]: y

      Now that the software is installed, you will perform some initialization steps to prepare a new database cluster for PostgreSQL.

      Step 2 — Creating a New PostgreSQL Database Cluster

      You have to create a new PostgreSQL database cluster before you can start creating tables and loading them with data. A database cluster is a collection of databases that are managed by a single server instance. Creating a database cluster consists of creating the directories in which the database data will be placed, generating the shared catalog tables, and creating the template1 and postgres databases.

      The template1 database is a template of sorts used to create new databases; everything that is stored in template1, even objects you add yourself, will be placed in new databases when they’re created. The postgres database is a default database designed for use by users, utilities, and third-party applications.

      The Postgres package we installed in the previous step comes with a handy script called postgresql-setup which helps with low-level database cluster administration. To create a database cluster, run the script using sudo and with the --initdb option:

      • sudo postgresql-setup --initdb

      You will see the following output:

      Output

      * Initializing database in '/var/lib/pgsql/data' * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

      Now start the PostgreSQL service using systemctl:

      • sudo systemctl start postgresql

      Then, use systemctl once more to enable the service to start up whenever the server boots:

      • sudo systemctl enable postgresql

      This will give the following output

      Output

      Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

      Now that PostgreSQL is up and running, we will go over using roles to learn how Postgres works and how it is different from similar database management systems you may have used in the past.

      Step 3 — Using PostgreSQL Roles and Databases

      PostgreSQL uses a concept called roles to handle client authentication and authorization. These are in some ways similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term role.

      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. In order to use PostgreSQL, you can log in to that account.

      There are a few ways to use this account to access the PostgreSQL prompt.

      Switching Over to the postgres Account

      Switch over to the postgres account on your server by typing:

      You can now access a Postgres prompt immediately by typing:

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

      Exit out of the PostgreSQL prompt by typing:

      This will bring you back to the postgres account’s Linux command prompt. Now return to your original account with the following:

      Accessing a Postgres Prompt Without Switching Accounts

      You can also run commands with the postgres account directly using sudo.

      For instance, in the previous example, you were instructed to access the Postgres prompt by first switching to the postgres user and then running psql to open the Postgres prompt. As an alternative, you could do this in one step by running the single command psql as the postgres user with sudo, like this:

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

      Again, you can exit the interactive Postgres session by typing:

      In this step, you used the postgres account to reach the psql prompt. But many use cases require more than one Postgres role. Read on to learn how to configure new roles.

      Step 4 — Creating a New Role

      Currently, you just have the postgres role configured within the database. You can create new roles from the command line with the createrole command. The --interactive flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.

      If you are logged in as the postgres account, you can create a new user by typing:

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

      • sudo -u postgres createuser --interactive

      The script will prompt you with some choices and, based on your responses, execute the necessary Postgres commands to create a user to your specifications. For this tutorial, create a role named sammy and give it superuser privileges by entering y when prompted:

      Output

      Enter name of role to add: sammy Shall the new role be a superuser? (y/n) y

      You can get more control by passing some additional flags. Check out the options by looking at the man page for createuser:

      Your installation of Postgres now has a new role, but you have not yet added any databases. The next section describes this process.

      Step 5 — 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 such a database with the createdb command.

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

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

      • sudo -u postgres createdb sammy

      This flexibility provides multiple paths for creating databases as needed.

      Now that you’ve created a new database, you will log in to it with your new role.

      Step 6 — 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 then connect to the database by first typing:

      Or, you can do this inline:

      This command will log you in automatically.

      If you want your user to connect to a different database, you can do so by including the -d flag and specifying the database, like this:

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

      This will show the following output:

      Output

      You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

      This is useful if you are connecting to non-default databases or with non-default users.

      Having connected to your database, you can now try out creating and deleting tables.

      Step 7 — Creating and Deleting Tables

      Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

      First, create a table to store some data. As an example, you will make a table that describes some playground equipment.

      The basic syntax for this command is as follows:

      CREATE TABLE table_name (
          column_name1 col_type (field_length) column_constraints,
          column_name2 col_type (field_length),
          column_name3 col_type (field_length)
      );
      

      These commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.

      For demonstration purposes, create a simple table like this:

      • 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
      • );

      This command will create a table that inventories playground equipment. It starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. You’ve also given this column the constraint of PRIMARY KEY, which means that the values must be unique and not null.

      For two of the columns (equip_id and install_date), the command does not specify a field length. This is because some column types don’t require a set length because the length is implied by the type.

      The next two lines create columns for the equipment type and color respectively, each of which cannot be empty. The line after these creates a location column and a constraint that requires the value to be one of eight possible values. The last line creates a date column that records the date on which you installed the equipment.

      You can see your new table by typing:

      This will show the following output:

      Output

      List of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | sammy public | playground_equip_id_seq | sequence | sammy (2 rows)

      Your playground table is here, but there’s also something called playground_equip_id_seq that is of the type sequence. This is a representation of the serial type that you gave your equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type.

      If you want to see just the table without the sequence, you can type:

      This will yield the following:

      Output

      List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | playground | table | sammy (1 row)

      In this step, you created a sample table. In the next step, you will try out adding, querying, and deleting entries in that table.

      Step 8 — Adding, Querying, and Deleting Data in a Table

      Now that you have a table, you can insert some data into it.

      As an example, add a slide and a swing by calling the table you want to add to, naming the columns, and then providing data for each column, like this:

      • INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
      • INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

      You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.

      Another thing to keep in mind is that you do not enter a value for the equip_id column. This is because it is automatically generated whenever a new row in the table is created.

      Retrieve the information you’ve added by typing:

      • SELECT * FROM playground;

      You will see the following output:

      Output

      equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2017-04-28 2 | swing | yellow | northwest | 2018-08-16 (2 rows)

      Here, you can see that your equip_id has been filled in successfully and that all of your other data has been organized correctly.

      If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:

      • DELETE FROM playground WHERE type = 'slide';

      Query the table again:

      • SELECT * FROM playground;

      You will see the following:

      Output

      equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 2 | swing | yellow | northwest | 2018-08-16 (1 row)

      Notice that your slide is no longer a part of the table.

      Now that you’ve added and deleted entries in your table, you can try adding and deleting columns.

      Step 9 — Adding and Deleting Columns from a Table

      After creating a table, you can modify it to add or remove columns. Add a column to show the last maintenance visit for each piece of equipment by typing:

      • ALTER TABLE playground ADD last_maint date;

      If you view your table information again, you will see the new column has been added (but no data has been entered):

      • SELECT * FROM playground;

      You will see the following:

      Output

      equip_id | type | color | location | install_date | last_maint ----------+-------+--------+-----------+--------------+------------ 2 | swing | yellow | northwest | 2018-08-16 | (1 row)

      Deleting a column is just as simple. If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete the column by typing:

      • ALTER TABLE playground DROP last_maint;

      This deletes the last_maint column and any values found within it, but leaves all the other data intact.

      Having now added and deleted columns, you can try updating existing data in the final step.

      Step 10 — Updating Data in a Table

      So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.

      You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the swing record (this will match every swing in your table) and change its color to red:

      • UPDATE playground SET color = 'red' WHERE type = 'swing';

      You can verify that the operation was successful by querying the data again:

      • SELECT * FROM playground;

      You will see the following:

      Output

      equip_id | type | color | location | install_date ----------+-------+-------+-----------+-------------- 2 | swing | red | northwest | 2010-08-16 (1 row)

      As you can see, your slide is now registered as being red.

      Conclusion

      You are now set up with PostgreSQL on your CentOS 8 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres:



      Source link

      How to Deploy PostgreSQL with One-Click Apps


      Updated by Rajakavitha Kodhandapani

      Contributed by
      Rajakavitha Kodhandapani

      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

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

      1. Log in to your Linode Cloud Manager account.

      2. From the Linode dashboard, click on the Create button in the top right-hand side of the screen and select Linode from the dropdown menu.

      3. The Linode creation page will appear. Select the One-Click tab.

      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

      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.

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



      Source link