Postgres в ретроспективе / блог компании postgres professional / хабр

Псевдо роль public

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

Роль public по умолчанию имеет следующие привилегии:

для всех баз данных:
- CONNECT – это означает что любая созданная роль сможет подключаться к базам данных, но не путайте с привилегией LOGIN;
- TEMPORARY – любая созданная роль сможет создавать временные объекты во всех база данных и объекты эти могут быть любого размера;
для схемы public:
- CREATE (создание объектов) – любая роль может создавать объекты в этой схеме;
- USAGE (доступ к объектам) – любая роль может использовать объекты в этой схеме;
для схемы pg_catalog и information_schema
USAGE (доступ к объектам) – любая роль может обращаться к таблицам системного каталога;

:
для всех функций

EXECUTE (выполнение) – любая роль может выполнять любую функцию. Ещё нужны ещё права USAGE на ту схему, в которой функция находится, и права к объектам к которым обращается функция.

:

Это сделано для удобства, но снижает безопасность сервера баз данных.

Запуск Apache 2.4 с модулем 1С внутри Docker контейнера

Про Apache и про Linux слышали, наверное, все. А вот про Docker пока нет, но он сильно набирает популярность последнее время и не зря. Поделюсь своим опытом и дам пошаговую инструкцию настройки веб-сервера Apache с модулем 1С внутри Docker контейнера на Linux хосте. При этом сам сервер 1С может находиться совсем на другой машине и на другой операционной системе

Это не важно, главное чтобы Apache смог достучаться до сервера 1С по TCP. В статье дам подробное пояснение по каждой используемой команде со ссылками на документацию по Docker, чтобы не создавалось ощущение непонятной магии

Также прилагаю git репозиторий с описанием всей конфигурации, можете попробовать развернуть у себя буквально за 10 минут.

Используемые инструменты и материалы

Компьютер: Windows 10, Диски: hdd-ОС, hdd-WAL файлы, sdd-база, temp_stat на RAM DISK 40 MB, intel core i5-4460 3.2 Ghz, RAM 8 Ggb
Платформа 1С версии 8.3.15
PgAdmin4 https://www.pgadmin.org/download/pgadmin-4-windows/
Обработка просмотра структуры 1с на сервере (в интернете их много, прикреплять не буду)
Яндекс поиск
Справка по секциям PostgreSQL https://postgrespro.ru/docs/postgrespro/12/ddl-partitioning#DDL-PARTITIONING-DECLARATIVE
Справка по значениям: MINVALUE, MAXVALUE и DEFAULT https://postgrespro.ru/docs/postgrespro/12/sql-createtable
Справка по типу данных Bytea https://postgrespro.ru/docs/postgrespro/12/functions-binarystring
Справка по работе с датами и временем в PostgreSQL https://postgrespro.ru/docs/postgrespro/12/functions-datetime
Статья по секционированию https://habr.com/ru/company/postgrespro/blog/353472/
Некоторые скрипты из статьи //infostart.ru/public/1148863/
PotgreSQL_1C 12 от разработчиков PostgresPro https://1c.postgres.ru/

Параметры PostgreSQL

shared_buffers	1 GB
temp_buffers	256 MB
work_mem	256 MB
maintenance_work_mem	1 GB
wal_sync_method	open_datasync
max_wal_size	10 GB
effective_cache_size	4 GB
autovacuum_max_workers	2
autovacuum_vacuum_cost_limit	800
autovacuum_vacuum_scale_factor	0.01
autovacuum_analyze_scale_factor	0.01
max_locks_per_transaction	512
enable_partition_pruning	on
bytea_output	‘hex’

Базы данных и шаблоны

Когда мы создаём новые кластер командой у нас создается 3 одинаковые базы данных:

postgres
template0
template1

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 |
 template0 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

База postgres используется, чтобы по умолчанию к ней подключаться. Принципиально она не нужна, но есть приложения которым она может понадобится, поэтому лучше её не удалять.

Две дополнительные базы template0 и template1 – это шаблоны. Новая база всегда создается путём копирования из другой шаблонной базы. По умолчанию для шаблона используется база template1. Поэтому, если у вас есть расширения, которыми вы пользуетесь, можете их заранее создать в template1.

Основная задача базы template0 заключается в том, что бы она никогда не менялась. Она используется, например при загрузке базы из дампа. Вначале вы создаёте базу из template0, а затем туда заливаете сохранённый дамп. Также база template0 позволяет создавать базы с использованием категорий локалей не по умолчанию (LC_COLLATE, LC_CTYPE).

Привилегии по умолчанию

Привилегии по умолчанию – это такие привилегии, которые добавятся к каким-то ролям на объект при его создании. Например роль Алиса хочет чтобы при создании новой таблицы доступ к ней сразу же получала роль Боб.

Привилегии по умолчанию создаются командой ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES  GRANT <привилегии> ON <класс_объектов> TO <роль>;

В примере выше <класс_объектов> это может быть, например таблица, функция, представление и т.п. То есть создаём мы какой-то объект из этого класса и сразу срабатывает команда выдачи привилегий: .

Аналогично можно удалять такие привилегии:

ALTER DEFAULT PRIVILEGES  REVOKE <привилегии> ON <класс_объектов> FROM <роль>;

Например сделаем так, чтобы при создании функций (любым пользователем), право их выполнять забиралось у роли public:

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM public;

Затем вам придется отдельным ролям давать эту привилегию вручную. Или можете сделать отдельную групповую роль, которая сможет выполнять функции, и включать неё другие роли.

Установка RedHat Enterprise Linux 8 (RHEL 8.4). Подключение RHEL8 к домену Active Directory. Запуск терминального клиента.

Операционная система – это один из краеугольных камней в фундаменте организации. От нее напрямую зависит надежность и безопасность корпоративной IT-инфраструктуры. Red Hat Enterprise Linux разработана с учетом всех требований и особенностей коммерческой эксплуатации Linux в производственной среде. Она проста в администрировании и управлении при развертывании приложений в физических, виртуальных и облачных средах. Обеспечивает высокую производительность и доступность приложений, а также обладает достаточной гибкостью, чтобы поддерживать рост организации и внедрение новых решений. Red Hat Enterprise Linux ценят за надежность, безопасность, стабильность, высокую производительность и масштабируемость, которые платформа предоставляет организациям. Клиентские решения Red Hat Enterprise Linux переносят эти инновации на рабочий стол.

Режим совместимости конфигурации 1С

Приветствую, коллеги! В этой статье будет сделан обзор функции совместимости конфигурации 1С с другими версиями конфигураций 1С, а также рассмотрено, как выбрать и настроить режим совместимости конфигурации с версией 1С 8.3.
Во-первых, разберём главное понятие в этой статье: режим совместимости в конфигурации – это устройство, благодаря которому выводится номер версии системы, под которую станет открыто приложение 1С:Предприятие. Данный режим существует на платформе 1С начиная с версий 8.2 и 8.3 (платформа версии 1С:Предприятие 8.3 совместима с платформой версии 1С:Предприятие 8.2).

Создание нового пользователя

Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.

1. Создание пользователя

а) Добавление новой роли (пользователя) из оболочки SQL:

=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;

* в примере создана роль dmosk с паролем myPassword.

б) Добавление новой роли (пользователя) из командной строки Linux:

createuser -P dmosk

2. Назначение прав на использование базы данных

Даем права на базу командой:

=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;

Теперь подключаемся к базе, к которой хотим дать доступ:

=# \c database1

* в примере подсоединимся к базе с названием database1.

а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;

* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.

б) Также можно дать доступ к базе для определенных таблиц:

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;

* в данном примере мы даем права на таблицу table1.

Выходим из SQL-оболочки:

database1=# \q

3. Настройка файла pg_hba.conf

Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь расположения данных для PostgreSQL:

=# SHOW config_file;

В ответ мы получим, что-то на подобие:

——————————————
/var/lib/pgsql/9.6/data/postgresql.conf
(1 row)

* в данном примере /var/lib/pgsql/9.6/data/ — путь расположения конфигурационных файлов.

Открываем pg_hba.conf:

vi /var/lib/pgsql/9.6/data/pg_hba.conf

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

…
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
…

* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчаниюhost all all 127.0.0.1/32 ident.

После перезапускаем службу:

systemctl restart postgresql-9.6

* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.

Учетная запись для резервного копирования

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

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

=# CREATE USER bkpuser WITH PASSWORD ‘bkppasswd’;

* мы создадим учетную запись bkpuser с паролем bkppasswd.

Предоставляем права на подключения к базе

=# GRANT CONNECT ON DATABASE database TO bkpuser;

* в данном примере к базе database.

Подключаемся к базе (в нашем примере database):

=# \c database

Даем права на все последовательности в схеме:

=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;

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

Кому не надо переходить на Postgres

Итак, кому на Postgres переходить точно не нужно:

Первая причина – если вам сильно страшно. Если сильно боитесь Linux, текстовых конфигурационных файлов, а не не галочек и параметров в визуальной части как у MS Management Studio, вы сильно не готовы тратить на это хоть какое-то время, даже 2-3 дня – не надо. Тут работает принцип – лучше та СУБД, которую вы знаете. Знаете другую СУБД – не надо.
Второй момент – у вас есть лицензионный MS SQL, он работает и вы не собираетесь разворачивать новый продукт СУБД для новых инсталляций баз. Тут работает другой принцип: работает – не трогай. Не ожидайте, что после перехода с MS на Postgres будет взрывной рост производительности обе СУБД сейчас почти сравнялись по скорости в работе с 1С и показывают результаты +-10% на разных запросах. Начитаетесь вредных советов по настройке Postgres и примените все подряд, не разбираясь, зачем вы это сделали, а просто потому, что какому-то чуваку это помогло победить один отчет, а он просто не в курсе, что у него легла остальная система.

В общем, если страшно или все работает, спокойно работайте на MS SQL.

Часто встречающиеся ошибки 1С и общие способы их решения Промо

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

Кому надо переходить на Postgres

И теперь, наверное, самое важное: кому точно надо переходить на Postgres?

Если у вас проходит новая инсталляция продуктовой системы. Если вы ставите новую систему, я бы рекомендовал перейти хотя бы на бесплатный Postgres. Рекомендую сразу это делать на Linux. Можно и на Windows, но тут проблема не столько в том, что Postgres плохой или Windows плохой – плохо работает их связка в части файловой системы. Опять же из-за наших структур баз данных, где у нас по 30 тыс. элементов в одной таблице (7 тыс. таблиц, 25 тыс. индексов). С таким количеством файлов винда работает плохо, а Postgres хранит каждый элемент системы в отдельном файле – более того, он эти файлы разбивает по 1 Гб, для каждого файла есть отдельные служебные файлы. И так на одну базу может получиться 100-300 тысяч файлов. Когда заходишь в папку, а там 300 тыс. файлов, можете оценить скорость этой работы. Linux же с этим работает прекрасно. Поэтому, если у вас новая инсталляция продукта, надо пробовать Postgres на Linux.
Разработчикам 1С. В идеале, на всех ваших ноутбуках и рабочих ПК должен стоять бесплатный Postgres. И вы в идеале должны разворачивать себе клиент-серверную систему 1С. Да, возникнут вопросы, что серверные ключи для 1С дорогие. Ну купите мини-сервер на пять пользователей за 15 тыс. рублей. Почему надо? Очень часто сталкиваемся с тем, что разрабатываете вы для клиент-серверной системы. а разработка идет в файловой системе. А потом начинается… Так что разрабатывать надо на такой же системе, в которой планируете работать.
Если вы делаете переход с файловой. До этого мы обсуждали, что переходить на бесплатный Express бессмысленно, поскольку у вас база не влезет. Если вы разрабатываете базу с объемом в 100 Гб, и вам нужны данные для разработки, то переход на Express бессмысленен. На Postgres переходите и работаете – в том числе и на бесплатный.
Если вы работаете под Минкомсвязью, вам нужно брать ПО только из реестра Минкомсвязи – вам переход на PostgreSQL, причем только платный Postgres Pro. Других вариантов нет.
Причина, по которой в свое время моя команда перешла на Postgres – потому что я был смелый и любознательный патриот. Мы решили попробовать, реально ли наши умеют писать СУБД. Оказалось, умеют и круто. Более того, работать с техподдержкой Postgres Pro – одно удовольствие. Все люди говорят на русском, работают круглосуточно, подключаются быстро. Я такой техподдержки до сих пор ни у кого не видел. Кто хоть раз писал на [email protected] знают, какой может быть техподдержка на русском языке. А это прямо небо и земля.

Но обычно у тех, кому надо переходить на PostgreSQL, у тех и не особо болит после перехода. А кому на Postgres не надо, но они переходят – те потом начинают говорить, что ничего не работает. Ну да, если ты не готов, то, наверное, и не работает.

Установка параметров на лету

Для своего сеанса можно изменить параметры с context=user. Для этого используется конструкция:

Например сделаем это для work_mem:

postgres@postgres=# SET work_mem TO '64MB';
SET
Time: 0,119 ms

postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'\gx
----+---------
name            | work_mem
setting         | 65536
unit            | kB
boot_val        | 4096
reset_val       | 10240
source          | session
sourcefile      |
sourceline      |
pending_restart | f
context         | user

Time: 0,651 ms

Как видим, теперь источником является текущая сессия, а параметр равен 64 MB, но если мы перечитаем конфигурацию параметр снова станет равным 10 MB.

Чтобы вернуть все на место нужно просто перезайти в psql. Или выполнить команду :

postgres@postgres=# RESET work_mem;
RESET
Time: 0,211 ms

postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'\gx
----+-------------------------------------------
name            | work_mem
setting         | 10240
unit            | kB
boot_val        | 4096
reset_val       | 10240
source          | configuration file
sourcefile      | /usr/local/pgsql/data/postgresql.auto.conf
sourceline      | 3
pending_restart | f
context         | user

Time: 0,632 ms

Тоже самое может проделывать приложение для одной транзакции, и если транзакция откатывается, то и значение параметра откатывается вместе с ней:

postgres@postgres=# BEGIN;
BEGIN
Time: 0,070 ms

postgres@postgres=# SET work_mem TO '64MB';
SET
Time: 0,085 ms

postgres@postgres=# SHOW work_mem;
 work_mem
----------
 64MB
(1 row)

Time: 0,102 ms

postgres@postgres=# ROLLBACK;
ROLLBACK
Time: 0,108 ms

postgres@postgres=# SHOW work_mem;
 work_mem
----------
 10MB
(1 row)

Time: 0,120 ms

Как вы могли заметить посмотреть текущее значение параметра ещё можно так:

Бесплатный MS SQL vs бесплатный PostgreSQL

Нельзя сравнивать платный MS SQL – Standard и Enterprise – с бесплатным PostgreSQL, это не совсем корректное сравнение. Нельзя ожидать от бесплатного ПО такой же скорости, как и от платного, причем неслабо платного.

Давайте сравним бесплатную версию MS SQL Express и бесплатные версии PostgreSQL.

Бесплатные версии Postgres – это:

Самосборки, когда вы с сайта postgresql.org скачали исходники, добавили туда патчи для 1С и собрали свою сборку. Она бесплатна, пожалуйста, пользуйтесь.
То же самое можно сделать, скачав сборку, которую собрали специалисты фирмы «1С» с сайта – releases.1с.ru. Эта сборка будет отличаться от вашей самосборки тем, что она после сборки протестирована фирмой 1С.
Есть еще третий вариант – сайт 1с.postgres.ru. Это сборка от компании Postgres Professional – эта команда работает со своими серверами сборки, и они делают бесплатную версию для 1С. где в ванильную сборку добавляют несколько своих патчей, которые считают нужным. На то какие патчи они добавят, а какие – нет, мы с вами повлиять не можем. Но на моей практике сборка с 1с.postgres.ru работает намного стабильнее. Кто имеет доступ на партнерские форумы 1С, там есть несколько обращений, когда у людей сбоили самосборки или сборки с releases, при этом ставим сборки с 1с.postgres.ru с той же версией Postgres, и все отлично работает. Поэтому я бы советовал ставить отсюда.

Теперь давайте сравним бесплатные версии MS SQL Express и Postgres. Какие ограничения они накладывают:

На количество ядер.
- MS SQL Express в последней поддерживаемой 1С 17 версии, позволяет использовать только 4 ядра.
- бесплатный Postgres – безлимитное количество ядер.
То же самое по памяти.
- MS SQL Express может использовать 1,5 Гб памяти и все.
- Postgres – безлимитно.
Объем базы:
- MS SQL Express – 10 Гб,
- у Postgres – безлимитно.
Отказоустойчивость:
- в MS SQL Express не поддерживается вообще.
- В Postgres доступны логические и физические репликации любого уровня каскадирования. Вы можете одновременно делать с мастера десятки реплик. Или сделать реплику с мастера, а с реплики – еще одну (иногда это имеет смысл). Бесплатный Postgres позволит снимать бэкапы с реплики, не трогая этими задачами мастер.
- Есть несколько важных моментов:
  - бэкап у MS SQL консистентен на момент окончания бэкапа, а бэкап Postgres’a (dump) консистентен на момент начала бэкапа.
  - Если вы сливаете этот dumpс мастера Postgres (с главного сервера), то в момент этого бэкапа вы не сможете произвести структурные изменения в 1С, так как они содержат в себе команды drop для таблиц либо удаления столбцов. Postgres не позволит изменить структуру данных в части удаления, пока он снимает dump. Этот момент нужно учитывать при проектировании системы.
Регламентные операции плюс бэкапы
- На MS SQL Express регламенты можно делать только скриптами и cron. Нет там планировщика (агента SQL Server), его там не существует. Вы не сможете внутри SQL создать никаких расписаний или операций. Надо будет батнички нарисовать, засунуть это либо в cron, либо в планировщик Windows.
- То же самое на Postgres, все регламентные операции, бэкапы и восстановления из бэкапов – это скрипты, cron или планировщик Windows, смотря в какой системе вы это делаете.
Ещё момент – бесплатные версии обоих продуктов не входят в список импортозамещающих продуктов. Компаниям госсектора эти базы данных запрещены в использовании вообще, не только в 1С.
Также на бесплатном Postgres и бесплатном MS SQL нет ФСТЭКа. Никто не получает сертификаты ФСТЭК на эти продукты. Забегая вперед, скажу, что и в платном MS SQL ФСТЭКа нет.

С бесплатными версиями все более менее понятно, поехали в платные версии.

Ansible + Git

Для управления всеми конфигурациями мы используем Ansible.

Грубо говоря, у нас все скрипты по настройке серверов лежат в Git. И Ansible может простыми командами настраивать.

Например, вам надо на сервере открыть какой-то порт – в этом случае вы запускаете определенный playbook, внутри файла добавляете номер порта, нажимаете «Применить на всех серверах» и у вас на всех серверах открывается какой-то порт.

Нужно установить какое-то дополнительное приложение – все это происходит быстро.

Ansible упрощает администрирование и дает администратору понимание, как был настроен сервер. Он помогает нам, чтобы администратор входил в строй достаточно быстро, чтобы управление серверами было просто и понятно.

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

Мы в своей работе используем Ansible – это очень удобно.

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

Если у нас один сервер упал, идет переключение. Мы перенастроим и за несколько часов выведем сервер в боевую готовность – будет поднята свеженькая система со всеми необходимыми настройками. Очень удобно, пользуйтесь Ansible, изучайте.

Мета-команды PostgreSQL

Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд.
Это не SQL запросы, а команды специфичные для PostgreSQL.

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

Всем мета-командам предшествует обратная косая черта , за которой следует фактическая команда.

Список всех баз данных

Чтобы получить список всех баз данных на сервере, ты можешь использовать команду .

Ввод этой мета-команды в оболочке Postgres выведет:

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

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

postgres — это просто пустая база данных.
«template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.

Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.

Подключаемся к базе данных PostgreSQL

Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы).
Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.

Когда ты находишься внутри оболочки (shell), то можешь использовать команду (или ), за которой следует имя
базы данных. Если бы у тебя была другая база данных под названием , то подключиться к ней можно было бы так:

Полностью в терминале у тебя получится что-то такое:

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

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

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

Перед выполнением этой команды вам необходимо войти в базу данных.

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

Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных
руководствах) и владельца.

Владелец (owner) — это пользователь, который создал таблицу.

Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.

Список пользователей и ролей

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

Обрати внимание, что первый столбец называется — роль (role name).
И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей. В PostgreSQL пользователи и роли практически
одинаковы

В PostgreSQL пользователи и роли практически
одинаковы.

У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание
других новых ролей.

Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.

Здесь мы видим только одну роль, суперпользователя по умолчанию.

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

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

Групповые привилегии

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

INHERIT – атрибут роли, который включает автоматическое наследование привилегий;
NOINHERIT – атрибут роли, который требует явное выполнение SET ROLE.

В 13 PostgreSQL при инициализации кластера создаются следующие роли вместе с суперпользователем postgres:

pg_signal_backend – право посылать сигналы обслуживающим процессам, например можно вызвать функцию pg_reload_conf() или завершить процесс с помощью функции pg_terminate_backend();
pg_read_all_settings – право читать все конфигурационные параметры, даже те, что обычно видны только суперпользователям;
pg_read_all_stats – право читать все представления pg_stat_* и использовать различные расширения, связанные со статистикой, даже те, что обычно видны только суперпользователям;
pg_stat_scan_tables – право выполнять функции мониторинга, которые могут устанавливать блокировки в таблицах, возможно, на длительное время;
pg_monitor – право читать и выполнять различные представления и функции для мониторинга. Эта роль включена в роли pg_read_all_settings, pg_read_all_stats и pg_stat_scan_tables;
pg_read_server_files – право читать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполняя копирование и другие функции работы с файлами;
pg_write_server_files – право записывать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполнять копирование и другие функции работы с файлами.
pg_execute_server_program – право выполнять программы на сервере (от имени пользователя, запускающего СУБД).

Установка RedHat Enterprise Linux 8 (RHEL 8.4). Подключение RHEL8 к домену Active Directory. Запуск терминального клиента.

Эмигранты в Панаме: день пляжа на острове Бока-Чика для выпускников тура

Мне нужно программно вставить десятки миллионов записей в базу данных postgres. В настоящее время я выполняю 1000 операторов вставки в одном «запросе».

Есть ли лучший способ сделать это, какой-нибудь оператор массовой вставки, о котором я не знаю?

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

35 Я также написал немного более подробное описание в stackoverflow.com/questions/12206600/….
26 @CraigRinger Вау, «немного подробнее» — лучшее преуменьшение, которое я видел за всю неделю;)
Попробуйте установить пакет NpgsqlBulkCopy
1 -Поскольку индексы также используются для физического размещения записей БД. Не уверен, что удаление индексов в любой базе данных — хорошая идея.
Но ваш рекомендуемый, ничего в памяти !!! И если размер вашего пакета может быть небольшим числом, очень-очень плохо сработал этот класс Я пробую класс npgsql CopyIn, потому что он похож на сопоставление в формате CSV в запросе PG. Вы можете попробовать для Big Table?

Существует альтернатива использованию COPY, синтаксис многострочных значений, поддерживаемый Postgres. Из документации:

Приведенный выше код вставляет две строки, но вы можете расширять его произвольно, пока не наберете максимальное количество подготовленных токенов операторов (это может быть 999 долларов, но я не уверен на 100%). Иногда нельзя использовать COPY, и это достойная замена в таких ситуациях.

12 Знаете ли вы, насколько эффективен этот метод по сравнению с COPY? Если вы столкнулись с проблемой с разрешениями, перед тем, как попробовать это, используйте COPY … FROM STDIN Если вы используете безопасность на уровне строк, это лучшее, что вы можете сделать. «COPY FROM не поддерживается для таблиц с безопасностью на уровне строк» в версии 12

COPY намного быстрее, чем расширенный INSERT Самое важное в такой процедуре (необработанные данные проглатывание) — преобразование, выраженный в стандарте SQL (без использования экзотических инструментов). См

Stackoverflow.com/a/62493516/287948

1 Стоит отметить, что ограничение на количество вставок / копий, которое вы можете добавить в одну транзакцию, вероятно, намного выше, чем все, что вы попытаетесь сделать. Вы можете добавить миллионы и миллионы строк в одну транзакцию и не столкнуться с проблемами.
@SumeetJain Да, я просто отмечу «золотую середину» скорости с точки зрения количества копий / вставок за транзакцию.
Будет ли это блокировать таблицу во время выполнения транзакции?

Функция с массивами может использоваться вместе с многострочным синтаксисом VALUES. Я думаю, что этот метод медленнее, чем использование но мне это пригодится при работе с psycopg и python (python перешел к становится pg ):

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

тот же синтаксис для массовых обновлений:

Ты можешь использовать что «несколько быстрее, чем форматы текста и CSV». Делайте это только в том случае, если у вас есть миллионы строк для вставки и если вам удобны двоичные данные.

Вот пример рецепта на Python с использованием psycopg2 с двоичным вводом.

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

Мой первый подход всегда: создать (временную) таблицу со структурой, аналогичной целевой таблице (создать таблицу tmp AS select * from target, где 1 = 0), и начать с чтения файла во временную таблицу. Затем я проверяю, что можно проверить: дубликаты, ключи, которые уже существуют в цели и т. Д.

Затем я просто выполняю команду «вставить в target select * from tmp» или что-то подобное.

Если это не удается или занимает слишком много времени, я прерываю его и рассматриваю другие методы (временное удаление индексов / ограничений и т. Д.)

Я реализовал очень быстрый загрузчик данных Postgresq с собственными методами libpq. Попробуйте мой пакет https://www.nuget.org/packages/NpgsqlBulkCopy/

Я только что столкнулся с этой проблемой и рекомендую csvsql (релизы) для массового импорта в Postgres. Чтобы выполнить массовую вставку, просто а затем используйте , который подключается к вашей базе данных и создает отдельные таблицы для всей папки CSV.

1 Для csvsql, чтобы также очистить исходный csv от любых возможных ошибок форматирования, лучше всего следовать этим инструкциям, больше документации здесь