How to install postgresql on centos 7

Как установить PostgreSQL на CentOS 7

How to Install PostgreSQL on CentOS 7

В этом руководстве мы покажем вам два разных способа установки PostgreSQL на вашем компьютере с CentOS 7. Первый способ проведет вас через шаги, необходимые для установки PostgreSQL v9.2.23 из репозиториев CentOS, а второй покажет, как установить последнюю версию PostgreSQL из официальных репозиториев PostgreSQL.

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

Мы также рассмотрим основы администрирования баз данных PostgreSQL.

Установите PostgreSQL из репозиториев CentOS

Чтобы установить PostgreSQL на вашем сервере CentOS, выполните следующие действия:

Чтобы установить сервер PostgreSQL вместе с пакетом постов PostgreSQL, который предоставляет несколько дополнительных функций для базы данных PostgreSQL, просто введите:

Инициализация базы данных

Инициализируйте базу данных PostgreSQL с помощью следующей команды:

Чтобы запустить службу PostgreSQL и включить ее при загрузке, просто введите:

Проверка установки PostgreSQL

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

Установите PostgreSQL из репозиториев PostgreSQL

Для установки последней версии PostgreSQL на ваш сервер CentOS выполните следующие действия:

Включение репозитория PostgreSQL

Чтобы включить репозиторий PostgreSQL, просто установите rpm файл репозитория :

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

Инициализация базы данных

Для инициализации базы данных PostgreSQL введите:

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

Проверка установки PostgreSQL

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

PostgreSQL Роли и методы аутентификации

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

postgres Пользователя создается автоматически при установке PostgreSQL. Этот пользователь является суперпользователем экземпляра PostgreSQL и эквивалентен пользователю root MySQL.

Чтобы войти на сервер PostgreSQL как пользователь postgres, сначала вам нужно переключиться на пользователя postgres, а затем получить доступ к приглашению PostgreSQL с помощью psql утилиты:

Отсюда вы можете взаимодействовать с вашим экземпляром PostgreSQL. Чтобы выйти из оболочки PostgreSQL, введите:

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

Создание роли и базы данных PostgreSQL

Только суперпользователи и роли с CREATEROLE привилегиями могут создавать новые роли.

В следующем примере мы создадим новую роль с именем john базы данных johndb и предоставим ей привилегии.

Подключитесь к PostgreSQL Shell

Создать новую роль PostgreSQL

Следующая команда создаст новую роль с именем john:

Создать новую базу данных PostgreSQL

Создайте новую базу данных с именем johndb, используя createdb команду:

Чтобы предоставить разрешения john пользователю в базе данных, которую мы создали на предыдущем шаге, выполните следующий запрос:

Включить удаленный доступ к серверу PostgreSQL

сохраните файл и перезапустите сервис PostgreSQL с помощью:

Проверьте изменения с помощью ss утилиты:

Как видно из приведенного выше вывода, сервер PostgreSQL прослушивает все интерфейсы (0.0.0.0).

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

Вывод

Вы узнали, как установить и настроить PostgreSQL на вашем сервере CentOS 7.

Вы можете обратиться к документации PostgreSQL для получения дополнительной информации по этой теме.

How to Install PostgreSQL on CentOS/RHEL 7

PostgreSQL 10 Released. PostgreSQL is an open-source object-relational, highly scalable, SQL-compliant database management system. PostgreSQL is developed at the University of California at Berkeley Computer Science Department. This article will help you to install PostgreSQL 10 on CentOS, RHEL and Fedora Systems.

Step 1 – Add Postgres Yum Repository

The first step is to install PostgreSQL repository in your system, Use one of below commands as per your system architecture and operating system.

For more details visit PostgreSQL repositories link page where you can get repository package rpm for various operating systems.

Step 2 – Install PostgreSQL 10 Server

After enabling PostgreSQL yum repository in your system use following command to install PostgreSQL 10 on your system with yum package manager.

Step 3 – Initialize PGDATA

After installing PostgreSQL server, It’s required to initialize it before using the first time. To initialize database use below command.

Above command will take some time to initialize PostgreSQL first time. PGDATA environment variable contains the path of data directory.

PostgreSQL data directory Path: /var/lib/pgsql/10/data/

Setp 4 – Start PostgreSQL Server

To start PostgreSQL service using the following command as per your operating systems. Also, enable PostgreSQL service to autostart on system boot.

For CentOS/RHEL 7 and Fedora

For CentOS/RHEL 6

Step 5 – Verify PostgreSQL Installation

After completing above steps, you have installed PostgreSQL 10 on your server, Let’s log in to postfix to verify that installation completed successfully.

You may create a password for user postgres for security purpose.

Congratulation’s! You have successfully installed PostgreSQL Server. Read below article to install phpPgAdmin.

Thanks for using this tutorial for installing PostgreSQL 10 on CentOS/RHEL 7/6 and Fedora 28/27/26 systems.

How To Install PostgreSQL 11.x on CentOS 7

Based on Postgres 4.2, PostgreSQL was developed at Berkeley Computer Science Development, University of California. It was created to be a powerful open source relational database system able to run across multiple platforms such as Linux, Windows, Solaris, Mac, etc. The code is available for anyone to modify and distribute under PostgreSQL license.

PostgreSQL is not the only contender in the database industry but what makes this a superior choice is that due to high-level stability and lower management overhead, the cost of PostgreSQL ownership is much lower in the long run. PostgreSQL is also the first database platform to implement Multi-Version Concurrency Control or MVCC.

In this tutorial, we are going to learn how to install and configure PostgreSQL 11.x on CentOS 7.

Step 1: Update CentOS

It is always best to start with an updated operating system. If you have not done so, use the following command to update CentOS and reboot:

Step 2: Configure Yum Repo

Add the PostgreSQL repository in CentOS 7.

Visit PostgreSQL Repositories for additional package information on various operating systems.

Step 3: Install PostgreSQL Server and Client

Use the following command to install PostgreSQL:

Step 4: Initialize Database

After installation is complete, we need to initialize the PostgreSQL database, which will create a data directory to store database and few configuration files:

Step 5: Enable and Start PostgreSQL Service

Enable the service so the PostgreSQL starts automatically during server reboot:

Step 6: Configure Firewall

If the firewall is configured on CentOS, we need to open ports so remote users can connect to PostgreSQL:

Step 7: Enable Remote Access

To allow PostgreSQL to accept remote connections, first, we need to change the listen to address to * in the configuration file:

Also, we need to let PostgreSQL know to accept remote connections:

Restart service after making configuration changes:

Step 8: Set PostgreSQL Admin Password

Set admin user and password for PostgreSQL:

Step 9: Create A Demo User and Database

Login to PostgreSQL console to create a user and database for testing:

Login as a demo user to test:

To modify the password for a database with a particular user:

Step 10: pgAdmin Tool

pgAdmin is a management and development tool for PostgreSQL server. It provides a graphical user interface to administer and a development platform all through a user-friendly interface.

Step 10.1: Installing pgAdmin

Install pgAdmin through repository already added for PostgreSQL:

Package pgadmin4-web and web server httpd are also automatically installed as needed dependencies.

Step 10.2: Enable pgAdmin Apache Configuration

A sample configuration file for pgAdmin is installed during installation. We need to rename or copy it as following:

Step 10.3: Configure VirtualHost

Edit the Apache configuration file to add a VirtualHost as appears below:

Step 10.4: Configure Data Directories

Create data directories to be used by pgAdmin and mention the directories in the configuration file:

Step 10.5: Create pgAdmin Configuration Database

Run the python script to create a database to store pgAdmin configurations:

When prompted, fill in the Email address and Password. This email address and password will be used as login credential to access the pgAdmin web interface:

Step 10.6: Directories Permission

Set permissions for pgAdmin directories so Apache user can access:

Step 10.7: Configure SELinux

Ignore this step if SELinux is disabled. But if it is enabled in Enforcing mode, create the following policies to allow the web server to access pgAdmin interface:

Enable and start httpd service:

Step 10.8: Configure Firewall

Open web service ports so that pgAdmin interface can be accessed:

Search Results

No Results

Filters

How to Install PostgreSQL Relational Databases on CentOS 7

The PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. This guide will help you install and configure PostgreSQL on your CentOS 7 Linode.

Before You Begin

Familiarize yourself with our Setting Up and Securing a Compute Instance and complete the steps for setting your Linode’s hostname and timezone.

Complete the sections of our Securing Your Server guide to create a standard user account, harden SSH access and remove unnecessary network services.

Update your system:

Install PostgreSQL

In this section, we’ll cover two different methods for installing PostgreSQL. If you do not need the latest version, we recommend using the first method to install PostgreSQL from the CentOS repositories.

Unless otherwise noted, the instructions in subsequent sections of this guide will be compatible with versions installed by either method.

Install From the CentOS Repositories

Initialize your Postgres database and start PostgreSQL:

Optional: Configure PostgreSQL to start on boot:

Install From the Postgres Repositories

Alternatively, you can install the latest version from the Postgres repositories. As of this publication, PostgreSQL 9.6.3 is the most recent version available for CentOS 7, but these steps can be applied to any RPM-based installation.

Select the version you wish to install from the Postgres Yum repositories. Locate the CentOS 7 link for your chosen version and download it to your Linode:

Install the RPM, as well as the EPEL repositories, which will be used to satisfy dependencies:

Update Yum to apply your changes and install PostgreSQL. When installing Postgres manually, you will have to specify the version:

Initialize your database and start PostgreSQL:

Optional: Configure PostgreSQL to start on boot:

Configure PostgreSQL

Secure the Postgres Users

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

Change the postgres user’s Linux password:

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.

Note that 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.

Access the PostgreSQL Shell

The PostgreSQL client shell allows you to issue SQL commands to administer your databases. As the postgres Linux user, log in by running:

This will log you in as the postgres database user. You’ll see a prompt similar to this:

Work with Databases

This section will cover how to create, delete and access databases.

Create a Database

You can create databases with the createdb command. Create a sample database called mytestdb by running this command as the postgres Linux user:

It’s also possible to assign ownership of the database to a specific Postgres user/role. For example, you could assign ownership to the examplerole role by running:

The createdb command has several additional options, which can be found in the PostgreSQL documentation.

Connect to a Database

You can use the psql command to connect to a specific database.

Connect to the test database:

You will see the following output:

By default, you will connect to a database as your peer-authenticated user. However, if you’ve enabled local password access, it’s also possible to specify which user you wish to connect as:

You’ll be prompted to enter the password for the examplerole database user before you access the shell.

List Databases

From the Postgres shell, you can list all of your databases with the \l or \list command. You will receive output similar to this:

You may also show the current database and user by entering \c from the Postgres shell. Additional info, like socket and port, will be included if you use \conninfo :

Delete a Database

You can delete, or drop, databases with the dropdb command. For example, to delete the mytestdb database created previously, issue this command as the postgres Linux user:

Work With Tables

PostgreSQL databases use tables to store and organize information within a database. In this section, you’ll find practical examples for adding, removing and manipulating tables. Unless otherwise noted, the commands in this section should be issued from the Postgres shell once you’ve connected to your database.

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.

Create a table called “employees” in your test database:

Insert a record into the table:

View the Content of a Table

To view the contents of the “employees” table:

This produces the following output:

List Tables in a Database

You can list all tables in the current database with the \dt command:

Delete Tables

Add Columns

Tables can be altered to add definitions, data types and columns. In this example you’ll add a new start_date column that uses the date data type.

Add the start_date column to the employees table:

Verify your change:

You’ll see that the new column has been created, but it does not contain any data:

In this example you’ve used the date data type, but PostgreSQL tables support several different types of data. See the PostgreSQL Documentation for a full explanation of supported data types.

Add and Update Rows

Update the start_date field for the user with the value 1 in the employee_id column:

Create a new row in the employees table:

Verify your changes:

You’ll see that the start date of 2016-09-28 has been added to the first row, and that a new row has been created for “Jane Smith”:

Remove Columns and Rows

In this section, you’ll remove a column from your table and then remove the second row.

Use ALTER TABLE to remove the start_date column you made previously:

Now use DELETE to remove the second row of your employees table. The following command will remove the row with a value of 2 in the employee_id column:

Confirm your changes:

Your table now consists of a single row, with the start_date column removed:

Query a Table

You can use queries to pull specific information from your database. This command will query your employees table to only return values for the employee_id and last_name columns:

You’ll receive an output similar to this:

PostgreSQL supports many querying options. See the PostgreSQL Documentation for more information.

Work With Roles

PostgreSQL grants database access via 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.

Create Roles

You will be prompted to create a password for the new user.

Give a Role Access to a Database

In this example, you’ll give the newly created examplerole user access to your database.

Connect to the database:

You’ll be connected as the postgres database user by default.

From the PostgreSQL shell, enter the following to grant all privileges on the table employees to the user examplerole :

List All Roles

Group Roles

For ease of administration, it’s possible to add multiple user roles to a single group, so that their privileges can be managed as a whole. In this section you’ll create a new group and add the examplerole user to it. These commands should be run as the postgres Linux user.

Log into the Postgres shell and add examplerole to the new group:

The createuser command has several other options. See the PostgreSQL documentation for more details.

Alter Roles

While specific settings and privileges can be applied to a role when it’s created, you can also modify a role’s properties later on. In this example, we’ll modify the examplerole user so that it can create new databases. The commands in this section should be run as the postgres Linux user.

Log in as the postgres database user:

From the Postgres shell, add the CREATEDB parameter to the examplerole user:

A number of permissions can be applied when creating or altering a role. See the PostgreSQL Documentation for more details.

Use \du to confirm your changes. You’ll see that the “Create DB” attribute is listed next to the examplerole user:

Delete Roles

The dropuser command is used to delete PostgreSQL roles. To delete the examplerole user, issue this command as the postgres Linux user:

Peer Authentication

PostgreSQL uses peer authentication by default. This means that database connections will be granted to local system users if their Linux username matches the name of their PostgreSQL role. To make use of peer authentication effectively, you would need to create both a Linux user and a corresponding PostgreSQL role. For the examplerole role you just created, you can use peer authentication by creating an examplerole local system user. This command must be run as a user with sudo access:

Note that you will be prompted to create a password for the new examplerole Linux user. Alternatively, you can follow our steps to secure local access.

Secure PostgreSQL

Secure Local Access

While PostgreSQL’s default peer authentication is useful in cases where a particular system user will be running a local program (e.g., scripts, CGI/FastCGI processes owned by separate users, etc.), you may wish to require passwords for greater security.

Commands in this section should be run as the postgres Linux user unless otherwise specified.

Edit the /var/lib/pgsql/data/pg_hba.conf file, under the # «local» is for Unix domain socket connections only header:

Replace peer with md5 on this line to activate password authentication using an MD5 hash.

To enable these changes, you need to restart PostgreSQL. However, you did not grant the postgres user sudo privileges for security reasons. Return to the normal user shell:

Restart PostgreSQL and switch back to the postgres user:

You will be prompted to enter the password for the examplerole user and then given psql shell access to the database. When using a database, you may check access privileges for each of its tables with the \z command.

Secure Remote Access

PostgreSQL listens for connections on localhost by default, and it is not advised to reconfigure it to listen on public IP addresses. If you wish to make PostgreSQL externally accessible, it’s recommended that you follow the Postgres documentation for using SSL to secure your remote connections. Alternatively, you could connect to PostgreSQL over an SSH tunnel. To access your databases remotely using a graphical tool, please follow one of these 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 page was originally published on Monday, December 12, 2016.

How to install postgresql on centos 7

In this article, you’ll come to know how to install Jenkins on Rocky Linux 8 or CentOS 8. …

How to Enable SSH Service on Rocky Linux 8 or CentOS 8

In this article, we’ll show you how to enable SSH service on Rocky Linux 8 or CentOS 8. If …

How to Install BpyTop Resource Monitoring Tool in Linux

In this article, we’ll show you how to install BpyTOP resource monitoring tool in Linux. Bpytop is a …

How to Upgrade Java 8 to Java 11 on Ubuntu 20

In this article, we’ll show you how to upgrade Java 8 to Java 11 on Ubuntu 20.04 Steps …

How to Install Java 8 on Ubuntu 20

In this article, we’ll show you how to install Java 8 on Ubuntu 20.04 LTS. Steps to Install …

How to Monitor Ubuntu Performance Using Netdata

In this article, we’ll see how to monitor Ubuntu performance using Netdata. Netdata is an open-source real-time monitoring …

How Software Maintenance Provides Continuity Of Service To Users?

In this article, we’ll explain how software maintenance provides Continuity Of Service To Users. Each and every youngster …

Top 10 Open Source Artificial Intelligence Tools for Linux

Artificial learning and intelligence have seeped into the roots of almost every technical aspect now. From wristwatches to …

10 Useful Commands for Filtering Text for Effective File Operations in Linux

Linux is an open-source OS or Operating System. It has been used for a multitude of purposes, from …

How is AI Accelerating DevOps in 2022

In this article, We’ll explain how is AI accelerating DevOps in 2022. The 21st Century is called the …