Join
Volodichev
@volodichev
October 18, 2021

Install and configure PostgreSQL

TODO:

I. Install and configure PostgreSQL

II. Install and configure PGadmin

0. Update the system (Для начала обновите индекс пакетов вашего сервера, если вы не делали этого последнее время):

sudo apt update -y

I. Install and configure PostgreSQL

1. Install required packages(postgres) Since this is your first time using apt in this session, refresh your local package index. Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:

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

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Adding PostgreSQL apt repository to System:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

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

sudo apt update -y

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

sudo apt -y install postgresql postgresql-contrib

Now that the software is installed, we can go over how it works and how it may be different from similar database management systems you may have used.

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

После завершения установки вы можете убедиться, что служба PostgreSQL активна. Для чего в командной строке наберите:

sudo systemctl is-active postgresql

Также, посмотрите, включена ли служба:

sudo systemctl is-enabled postgresql

И наконец, вы можете увидеть статус службы PostgreSQL:

sudo systemctl status postgresql

https://ruvds.com/ru/helpcenter/postgresql-pgadmin-ubuntu/

Подключитесь к системе с помощью учётной записи postgres:

sudo su - postgres
или
sudo -i -u postgres

Подключившись, выполните команду psql:

psql

источник: losst.ru/ustanovka-postgresql-ubuntu-16-04

II. Install and configure PGadmin

pgAdmin — это платформа с открытым исходным кодом для администрирования и разработки для PostgreSQL и связанных с ней систем управления базами данных. Платформа написана на Python и jQuery и поддерживает все функции PostgreSQL. Вы можете использовать pgAdmin для любых операций, начиная с записи базовых SQL-запросов и заканчивая осуществлением мониторинга ваших баз данных и настройки продвинутых архитектур баз данных.

1. Установим следующие зависимости. Список зависимостей включает:

•libgmp3-dev— библиотека арифметических операций с многократно увеличенной точностью;

•libpq-dev — библиотека с файлами заголовков и статической библиотекой, которая помогает осуществлять коммуникации с бэкэндом PostgreSQL

• build-essential —

• python3-dev —

• libffi-dev —

• uwsgi-core —

• uwsgi-plugin-python3 — 

sudo apt -y install build-essential libgmp3-dev libpq-dev python3-dev libffi-dev uwsgi-core uwsgi-plugin-python3

2. Создадим несколько директорий, где pgAdmin будет хранить данные сессий, данные хранилища и журналы:

sudo mkdir -p /var/lib/pgadmin4/sessions
sudo mkdir /var/lib/pgadmin4/storage
sudo mkdir /var/log/pgadmin4

2.1 Создадим обычного пользователя "sammy"

sudo useradd -c "not root user sammy" -g sudo -d /home/sammy -m -s /bin/bash sammy&& sudo passwd sammy

3. Изменим владельца этих директорий на пользователя без прав root и группу. Это необходимо, потому что первоначально они принадлежат вашему root-пользователю, но мы будем устанавливать pgAdmin из виртуальной среды, принадлежащей вашему пользователю без прав root, а процесс установки подразумевает создание ряда файлов внутри этих директорий. После завершения установки мы изменим владельца на пользователя www-data и группу, чтобы директорию можно было использовать через сеть:

sudo chown -R sammy:www-data /var/lib/pgadmin4 /var/log/pgadmin4

4. Создадим виртуальную среду и активируем ее:

sudo python3 -m venv /home/pgadmin/venv
source /home/pgadmin/venv/bin/activate

4.1 Обновим pip и пр. (Пользователь sudo)

(venv) sammy@{servername}:$ /home/pgadmin/venv/bin/python -m pip install --upgrade pip setuptools

5. Загрузим исходный код pgAdmin4.

Чтобы получить последнюю версию исходного кода, перейдем на https://www.pgadmin.org/download/pgadmin-4-python и нажмем ссылку для последней версии: https://www.postgresql.org/ftp/pgadmin/pgadmin4/v6.10/pip/

На этой странице вам необходимо скопировать ссылку на файл, которая заканчивается на .whl, стандартный формат пакетов для дистрибутивов Python.

https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.10/pip/pgadmin4-6.10-py3-none-any.whl

Запустим следующую команду wget, для загрузки файла .whl на ваш сервер:

(venv) sammy@{servername}:$ wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.10/pip/pgadmin4-6.10-py3-none-any.whl

6. Установим wheel:

(venv) sammy@{servername}:$ /home/pgadmin/venv/bin/python -m pip install wheel

7. Затем установите пакет pgAdmin 4 с помощью следующей команды*:

(venv) sammy@{servername}:$ /home/pgadmin/venv/bin/python -m pip install pgadmin4-6.10-py3-none-any.whl

*Она отвечает за установку pgAdmin и его зависимостей. Прежде чем подключаться к вашей базе данных, необходимо внести несколько изменений в настройку программы.

8. Хотя pgAdmin был установлен на сервере, осталось несколько шагов, которые нужно выполнить, чтобы гарантировать, что в наличии все разрешения и конфигурации, необходимые для правильной работы с веб-интерфейсом.

Основной файл конфигурации pgAdmin с именем config.py считывается перед любым другим файлом конфигурации. Его содержание можно использовать в качестве отправной точки для последующих настроек конфигурации, которые можно указать в других файлах конфигурации pgAdmin, но чтобы избежать непредвиденных ошибок, вы не должны редактировать файл config.py самостоятельно. Мы внесем некоторые изменения конфигурации в новый файл с именем config_local.py, который будет считываться непосредственно после основного файла.

Создайте этот файл сейчас, используя текстовый редактор. Мы будем использовать nano(заменить python3.x):

(venv) sammy@{servername}:$ nano /home/pgadmin/venv/lib/python3.8/site-packages/pgadmin4/config_local.py

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

environments/my_env/lib/python3.8/site-packages/pgadmin4/config_local.py

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SERVER_MODE = True

ALLOW_SAVE_PASSWORD = True

Вот что делают эти пять директив*:

  • LOG_FILE: данная директива определяет файл, в котором будут храниться журналы pgAdmin.
  • SQLITE_PATH: pgAdmin хранит данные о пользователях в базе данных SQLite, и эта директива указывает программному обеспечению PgAdmin на базу данных конфигурации. Поскольку этот файл находится в постоянной директории /var/lib/pgadmin4, ваши пользовательские данные после обновления не будут потеряны.
  • SESSION_DB_PATH: указывает, какая директория будет использоваться для хранения данных сеанса.
  • STORAGE_DIR: определяет, где pgAdmin будет хранить другие данные, например резервные копии и сертификаты безопасности.
  • SERVER_MODE: установка значения True для этой директивы говорит о том, что pgAdmin должен запускаться в режиме сервера, а не в режиме настольного компьютера.

*Обратите внимание, что каждый из этих путей файла указывает на директории, созданные на шаге 1.

9. После добавления этих строк сохраните и закройте файл (нажмите CTRL + X, а затем нажмите Y и ENTER). После внесения этих изменений запустите скрипт установки pgAdmin, чтобы задать учетные данные для входа (заменить python3.x):

(venv) sammy@{servername}:$ /home/pgadmin/venv/bin/python /home/pgadmin/venv/lib/python3.8/site-packages/pgadmin4/setup.py

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

Output. . .
Enter the email address and password to use for the initial pgAdmin user account:

Email address: username@example.com
Password:
Retype password:

11. После этого необходимо деактивировать вашу виртуальную среду:

(venv) sammy@{servername}:$ deactivate

12. Nginx и uwsgi запускаются под www-data:www-data, поэтому у настроек необходимо изменить владельцев командами:

sudo chown -R www-data:www-data /var/lib/pgadmin4/ /var/log/pgadmin4/
sudo chmod -R 755 /var/lib/pgadmin4/ /var/log/pgadmin4/


Источник: https://www.digitalocean.com/community/tutorials/how-to-install-configure-pgadmin4-server-mode-ru

Установка uwsgi

Для установки uwsgi необходимо выполнить команду:

sudo apt -y install uwsgi-core uwsgi-plugin-python3
(venv) sammy@{servername}:$ pip install uwsgi

В /etc/uwsgi/pgadmin.ini

[uwsgi]
uid             = www-data
gid             = www-data
plugins         = python3
chdir           = /home/pgadmin/venv/lib/python3.8/site-packages/pgadmin4/
wsgi-file       = /home/pgadmin/venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.wsgi
master          = true
processes       = 1
threads         = 4 
socket          = /home/pgadmin/pgadmin.sock
chmod-socket    = 664
vacuum          = true
mount           = /pgadmin=pgAdmin4:app
manage-script-name = true

В /etc/systemd/system/ uwsgi@.service

[Unit]
Description=uWSGI service unit
After=syslog.target

[Service]
ExecStart=/home/pgadmin/venv/bin/uwsgi --ini /etc/uwsgi/%I.ini
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -INT $MAINPID
Restart=always
Type=notify
StandardError=syslog
NotifyAccess=all
KillSignal=SIGQUIT

[Install]
WantedBy=multi-user.target

sudo service uwsgi@pgadmin start

Настройка Nginx

Для установки nginx необходимо выполнить команду:

sudo apt install nginx-full

а в директорию /etc/nginx/sites-available/ добавить настройки:

upstream pgadmin {
      server unix:///home/pgadmin/pgadmin.sock;
}

server {
    #Через субдомен
    listen 80;
	server_name pgadmin.site.com;
	access_log off;
    error_log off;
	charset utf-8;
	
	location / {		
		include /etc/nginx/uwsgi_params;
		uwsgi_pass pgadmin;
		uwsgi_modifier1 30;
	}	
			
}

server {
    #Через подпапку
    listen 80;
	server_name site.com;
	access_log off;
    error_log off;
	charset utf-8;
	
	location /pgadmin {
		include /etc/nginx/uwsgi_params;
		uwsgi_pass pgadmin;
		uwsgi_param SCRIPT_NAME /pgadmin;
		uwsgi_modifier1 30;
	}				
}

https://gist.github.com/rdnvndr/51ae8692bebcd02325ca7c046fe5ade2 (ответы на проблемы)

sudo chown -R www-data:www-data /home/pgadmin
sudo chown -R root:root  /etc/uwsgi

sudo chmod -R 664 /etc/uwsgi
sudo chmod -R 764 /home/pgadmin
sudo chmod -R 664 /home/pgadmin/pgadmin.sock
sudo chmod -R 664 /etc/systemd/system/uwsgi@.service
sudo chmod -R 764 /var/lib/pgadmin4
sudo chmod -R 764 /var/log/pgadmin4

chmod 755 /var/log/pgadmin /path/to/working_directory/lib
$ chown -R www-data:www-data /var/log/pgadmin /path/to/working_directory/lib

 sudo systemctl enable uwsgi@pgadmin

sudo -u postgres psql

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

CREATE DATABASE {project};

Создание новой роли POSTGRESQL:

CREATE USER {user} WITH PASSWORD '{password}';

Поменяем кодировку:

ALTER ROLE {user} SET client_encoding TO 'utf8';

И еще по мелочи:

ALTER ROLE {user} SET default_transaction_isolation TO 'read committed';
ALTER ROLE {user} SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE {project} TO {user};

удалить пользователя:

DROP USER user_name;


Изменить тип колонки:

ALTER TABLE table_name ALTER COLUMN column_name TYPE boolean USING column_name::boolean;

удалить таблицу

DROP TABLE [ IF EXISTS ] имя [, ...] [ CASCADE | RESTRICT ];

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

\d

Основные команды PostgreSQL в интерактивном режиме:

\connect db_name – подключиться к базе с именем db_name
\du – список пользователей
\dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
\di – индексы
\ds – последовательности
\dt – список таблиц
\dt+ — список всех таблиц с описанием
\dt *s* — список всех таблиц, содержащих s в имени
\dv – представления
\dS – системные таблицы
\d+ – описание таблицы
\o – пересылка результатов запроса в файл
\l – список баз данных
\i – читать входящие данные из файла
\e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
\d “table_name” – описание таблицы
\i запуск команды из внешнего файла, например \i /my/directory/my.sql
\pset – команда настройки параметров форматирования
\echo – выводит сообщение
\set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет)

https://github.com/cmshiang/PgAdmin4-Nginx-uwsgi

https://gist.github.com/rdnvndr/51ae8692bebcd02325ca7c046fe5ade2

https://www.digitalocean.com/community/tutorials/how-to-install-configure-pgadmin4-server-mode-ru

https://askubuntu.com/questions/1230782/postgresql-admin-tool-behind-nginx

https://github.com/rbernardes/nginx-pgadmin

https://www.pgadmin.org/docs/pgadmin4/development/server_deployment.html#standalone-uwsgi-configuration

https://www.digitalocean.com/community/tutorials/how-to-install-configure-pgadmin4-server-mode-ru

https://habr.com/ru/post/340460/

https://postgrespro.ru/docs/postgrespro/13/sql-commands

https://gist.github.com/igorpronin/f25cd61ffb976fbc6854b9c2684e5f01

Volodichev
October 18, 2021, 14:36
0 views
0 reactions