Sql сервер postgresql


Содержание

Раздел 1: Установка программного обеспечения базы данных PosrgreSQL и импорт базы ключевых слов

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

ВАЖНО: Если у вас нет опыта работы с БД или этот опыт минимален, то мы настоятельно рекомендуем начать с минимальной базы для отработки процесса.

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

Скачивание и установка сервера баз данных PostgreSQL

1. Скачаем бесплатную программу для базы данных PostgreSQL c официального сайта http://www.postgresql.org/download/windows/

1. Скачаем бесплатную программу для базы данных PostgreSQL c официального сайта http://www.postgresql.org/
download/windows/

Затем перейдем по ссылке на подробную страницу версий базы данных для разных ОС: http://www.enterprisedb.com/products-services-training/pgdownload#windows:

ВАЖНО: Мы рекомендуем устанавливать сервер баз данных и делать выборки в 64-битной системе с минимум 6 Гб оперативной памяти при работе с минимальной и расширенной базами. В случае максимальной базы рекомендуемый объем оперативной памяти — от 32 Гб , также желателен быстрый диск. На компьютере с 32-битной системой процесс импорта данных, их индексации и собственно выборки будет проходить очень и очень долго, поэтому лучше отказаться от этой идеи.

Если вы не знаете разрядность своей ОС, то ее можно посмотреть в свойствах компьютера (правая кнопка мыши на пункте «Компьютер», выбрать в меню «Свойства»):

2. Устанавливаем скачанную БД PostgreSQL. Во время установки практически все по умолчанию …

… за исключением нескольких моментов.

При выборе папки для хранения данных – если у вас на системном диске мало места или он не самый быстрый из ваших дисков, то рекомендуем выбрать другой, на котором много свободного места и у которого показатели скорости чтения/записи выше. Например, мы выбрали диск D :

Примечание: для того, чтобы процедура импорта и индексации базы ключевых слов прошла успешно, вам нужно, чтобы свободного места на диске было примерно в три раза больше, чем размер базы. Например, текстовая база из 457 млн.слов занимает 11,2 Гб, тогда на диске, где расположена папка под хранение проектов, должно быть как минимум 33 Гб. Эта цифра ориентировочна и для других баз будет отличаться.

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

Порт, региональные настройки оставляем по умолчанию.

На последнем шаге установки появляется экран:

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

Сервер баз данных PostgreeSQL установлен, переходим к его настройке.

Настройка сервера баз данных PostgreSQL

1. Оптимизируем конфигурацию для ускорения работы сервера. В структуре папок, куда вы настроили хранение данных при установке PostgreSQL, находим файл postgresql.conf (в нашем случае, путь к этому файлу d:\PostgreSQL\9.5\data\postgresql.conf ). Открываем Блокнот (Notepad) , в Блокноте выбираем и открываем postgresql.conf (для этого в окне диалога «Открыть» выбираем для отображения все форматы файлов), в файле конфигурации находим пункты # work_mem и # maintenance_work_mem , меняем значения на:

work_mem = 256MB
maintenance_work_mem = 2020MB

Примечание: Обратите внимание на то, что в файле конфигурации мы убираем комментирование ( # ) для исправляемых пунктов, иначе новые настройки не применятся. Далее нужно сохранить измененный файл postgresql.conf .

После установки сервер БД стартовал автоматически. Для того, чтобы изменения в конфигурации вступили в силу, его нужно перезапустить, но проще просто перезагрузить компьютер – в таком случае измененные настройки конфигурации наверняка вступят в силу.

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

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

Открываем программу для управления базами данных PosgreSQL — PGAdmin .

1. Для подключения к серверу дважды кликнем на иконке сервера:

Введите пароль, который вы использовали на этапе установки. Установите галочку «Сохранить пароль» для удобства дальнейшей работы:

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

2. Создаем базу для хранения английских ключевых слов. Для этого на пункте «Базы данных» в меню по правой кнопке мыши выбираем пункт «Новая база данных» .

Затем в открывшемся окне создания базы вписываем название MyDB , нажимаем OK :

Этого достаточно для создания базы.

Создание таблицы, импорт данных в таблицу и их индексация

Теперь, когда база MyDB создана, создадим таблицу eng_data_table в этой базе и импортируем в нее английские ключевые слова из текстового файла, который мы скачали.

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

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

1. Становимся на названии базы и нажимаем кнопку SQL в верхней панели инструментов:

Появляется окно редактора запросов SQL. Для создания таблицы копируем и вставляем такую команду:

CREATE TABLE «eng_data_table» (
«keyword» VARCHAR NOT NULL
) WITH (fillfactor = 100, o >

Нажимаем зеленую кнопку выполнения запроса в верхней панели:

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

drop table if exists «eng_data_table»;

2. Импортируем данные из файла базы английских ключевых слов, который мы скачали с сайта и распаковали на локальный диск e:\downloads\EnglishKeywords.txt , в созданную таблицу eng_data_table .

Для этого в SQL редактор вставим такой запрос:

copy «eng_data_table» from ‘e:\downloads\EnglishKeywords.txt’;

и нажмем зеленую кнопку выполнения запроса в верхней панели:

Импорт выполняется в среднем 20-40 минут.

После выполнения команды copy архив и распакованный файл базы можно удалить, если у вас мало свободного места на диске.

Примечание: Если вы импортируете расширенную базу, обратите внимание на то, что название файла расширенной базы отличается от названия минимальной базы. В этом случае в синтаксисе команды копирования базы EnglishKeywords.txt нужно поменять на EnglishKeywordsExtended.txt . Соответственно при работе с максимальной базой нужно указать название EnglishKeywordsMax.txt

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

3. Запускаем операцию оптимизации таблицы подобным образом:

Оптимизация выполняется в среднем 15-30 минут.

Примечание: Запросы выполняем по одному, предварительно стирая уже выполненные предыдущие запросы. О том, выполнен запрос или нет, вы можете узнать из сообщения в Панели вывода внизу (оно появляется, когда запрос выполнен), а время выполнения вы можете узнать в строке состояния внизу окна.

4. Индексируем таблицу для быстрого поиска в базе. Синтаксис запроса для индексации нашей таблицы такой:

CREATE INDEX «eng_data_table_idx» ON «eng_data_table» USING gin ((to_tsvector(‘english’::regconfig, («keyword»)::text)));

Примечание: Этот шаг самый длительный, займет 3-5 часов в зависимости от мощности вашего ПК. Для ускорения индексации лучше закрыть программы, потребляющие много ОЗУ и активно работающие с диском. Тем не менее, если вы продолжаете пользоваться вашим ПК, то в это время ваш компьютер может притормаживать, это нормально.

Если процесс индексации затягивается (например, для индексации английской базы в 500 млн. строк нужно 2-3 часа), то скорее всего вы 1) не поменяли/не сохранили настройки конфигурации либо 2) поменяли конфиг, но не перезагрузили компьютер.


5. Обновляем статистику таблицы (позволит эффективно использовать только что созданный индекс):

vacuum analyze «eng_data_table»;

Оптимизация займет 5-10 минут.

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

Установка PostgreSQL 11 на Windows. Пошаговая инструкция

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

PostgreSQL — это бесплатная система управления базами данных (СУБД). PostgreSQL 11 – это новая версия данной СУБД.

Пошаговое описание установки PostgreSQL 11 на Windows

PostgreSQL реализована для многих операционных систем: Windows, Linux, macOS. Сейчас мы подробно рассмотрим все действия, которые необходимо выполнить, чтобы установить PostgreSQL на операционную систему Windows: начиная с загрузки графического установщика, который, кстати, включает и pgAdmin 4 – это графический инструмент управления PostgreSQL, с помощью которого можно писать SQL запросы, и заканчивая русификацией pgAdmin 4.

Шаг 1 — Загрузка графического установщика PostgreSQL 11 для Windows

Скачать PostgreSQL 11 для Windows можно, конечно же, с официального сайта PostgreSQL, вот ссылка — https://www.postgresql.org/download/windows/

После перехода на страницу можем сразу нажимать на ссылку «Download the installer», в данном случае нас перенесет на сайт компании EnterpriseDB, которая и подготавливает графические дистрибутивы PostgreSQL для многих платформ, в том числе и для Windows.

Далее выбираем платформу и версию PostgreSQL, в нашем случае — это Windows и PostgreSQL 11. Нажимаем на ссылку «Windows x86-64» — это версия для 64 разрядных версий Windows.

В итоге у Вас должен загрузиться файл postgresql-11.0-1-windows-x64.exe размером примерно 187 мегабайт.

Шаг 2 – Запуск установщика PostgreSQL 11

Запускаем скаченный файл. Сначала инсталлятор проверит наличие всех необходимых компонентов, в частности Visual C++ Redistributable, в случае необходимости, т.е. их отсутствия, он их сам установит.

После этого откроется окно приветствия, нажимаем «Next».

Шаг 3 – Указываем каталог для установки PostgreSQL 11

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

Шаг 4 – Выбираем компоненты для установки

На этом шаге мы можем отметить компоненты, которые нам необходимо установить, как видите, в числе компонентов есть и pgAdmin 4, оставляем галочки напротив нужных нам компонентов и жмем «Next».

Шаг 5 – Указываем каталог для файлов баз данных

Далее нам необходимо указать каталог, в котором по умолчанию будут располагаться файлы баз данных. В данном случае лучше указать отдельный диск. Нажимаем «Next».

Шаг 6 – Задаем пароль для системного пользователя postgres

Теперь нам нужно задать пароль для пользователя postgres, иными словами, для администратора PostgreSQL Server. Вводим пароль и подтверждаем его. Нажимаем «Next».

Шаг 7 – Указываем порт для экземпляра PostgreSQL

Далее в случае необходимости мы можем изменить порт, на котором будет работать PostgreSQL Server, но можно оставить и по умолчанию. Нажимаем «Next».

Шаг 8 – Указываем кодировку данных в базе

Потом, если есть необходимость указать конкретную кодировку данных в базе, мы можем выбрать из выпадающего списка нужную нам Locale. Я оставляю по умолчанию, жмем «Next».

Шаг 9 – Проверка параметров установки PostgreSQL

Здесь мы просто проверяем введенные нами ранее параметры для установки PostgreSQL, если все правильно, т.е. все то, что Вы и вводили, нажимаем «Next».

Шаг 10 – Запуск процесса установки

Для того чтобы запустить процесс установки PostgreSQL в данном окне, нажимаем «Next».

Шаг 11 – Завершение установки

Процесс установки PostgreSQL 11 занимает всего 2-3 минуты. Когда появится окно с сообщением «Completing the PostgreSQL Setup Wizard» установка PostgreSQL, pgAdmin 4 и других компонентов будет завершена.

В последнем окне нам предложат запустить Stack Builder для загрузки и установки дополнительных компонентов, если Вам ничего такого не нужно, то снимайте галочку «Lanch Stack Builder at exit?» и нажимайте «Finish».

Запуск pgAdmin 4 и подключение к серверу PostgreSQL 11

pgAdmin 4 у Вас установился вместе PostgreSQL, для того чтобы запустить pgAdmin 4, нажмите «Меню Пуск — > PostgreSQL 11 -> pgAdmin 4».

Новая версия pgAdmin 4 имеет веб интерфейс, поэтому у Вас запустится браузер, в котором откроется приложение pgAdmin 4.

Чтобы осуществить подключение к только что установленному локальному серверу PostgreSQL 11 в обозревателе серверов, щелкаете по пункту «PostgreSQL 11».

В результате запустится окно «Connect to Server», в котором Вам нужно ввести пароль системного пользователя postgres, т.е. это тот пароль, который Вы придумали, когда устанавливали PostgreSQL. Вводим пароль, ставим галочку «Save Password», для того чтобы сохранить пароль, и каждый раз не вводить его, и нажимаем «OK».

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

Как установить русский язык в pgAdmin 4?

По умолчанию интерфейс pgAdmin 4 на английском языке, если Вас это не устраивает, Вы можете очень просто изменить язык на тот, который Вам нужен. pgAdmin 4 поддерживает много языков, в том числе и русский.

Для того чтобы изменить язык pgAdmin 4 необходимо зайти в меню «File -> Preferences».

Затем найти пункт «User Languages», и в соответствующем поле выбрать значение «Russian». Для сохранения настроек нажимаем «OK», после этого перезапускаем pgAdmin 4 или просто обновляем страницу в браузере.

Теперь pgAdmin 4 русифицирован.

Открытие Query Tool (Запросник)

Для написания SQL запросов в pgAdmin 4 используется инструмент Query Tool или на русском «Запросник», его можно запустить с помощью иконки на панели или из меню «Инструменты».

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

У меня все, надеюсь, статья была Вам интересна и полезна, пока!

SQL Server and PostgreSQL Linked Server Configuration — Part 2

By: Sadequl Hussain | Updated: 2015-06-29 | Comments (17) | Related: 1 | 2 | 3 | More > Other Database Platforms
Problem

We need to have PostgreSQL and SQL Server database platforms communicate in our environment. We need to access the PostgreSQL data from SQL Server in an efficient manner. Based on the steps from your first tip, how can we take the next steps to setup the data access? Can we create a Linked Server from SQL Server to PostgreSQL to access the data?

Solution

In part 1 of this series, we rolled out a simple database infrastructure with a PostgreSQL instance and a SQL Server instance. We have seen how both the servers could communicate with each other at network level. Then we restored a sample database in PostgreSQL and created two of its table structures in SQL Server.


In this tip, we will show how SQL Server can access Postgres data and populate those tables.

Install PostgreSQL ODBC Driver

Although the de-facto data access library for any modern database should be based on OLE DB, PostgreSQL’s official site doesn’t list any freely available x64 bit OLE DB providers. As mentioned in part 1, there’s a 64-bit OLE DB provider available from a third-party vendor. However, that driver comes with a price tag. The free OLE DB version available from Postgres site is for 32-bit only.

Илон Маск рекомендует:  abbr в HTML

However, PostgreSQL also provides a 64-bit ODBC driver that’s downloadable from its official site. In our example, we will download and install this 64-bit ODBC driver (psqlODBC) on our SQL Server.

Step 1: Remote desktop to SQL Server

Step 2: Browse to PostgreSQL’s official download site for psqlODBC and download the zip file containing the x64 bit .msi installer. The file we will download is called psqlodbc_09_03_0300-x64-1.zip. We can see the driver is for PostgreSQL 9.3 and it’s meant for 64-bit Windows. Once the download completes, unzip the file. The extracted content looks like this:

Step 3: Double-click to start the psqlodbc_x64.msi installer. The next few images show the straightforward installation process.

Create ODBC Data Source

Once the driver has been installed, it’s time to create a System DSN from it. So let’s start the ODBC Data Source (64 bit) application from the Server Manager applet (see below).

In the next few screenshots, we can see how an ODBC data source is created.

Step 1: First let’s choose the System DSN tab and then click Add.

Step 2: Next we choose the PostgreSQL Unicode (x64) version and click Finish.

Step 3: In the dialog box that pops-up, provide a name and description for the data source, specify the database name, server’s IP address, port, user name and password as connection parameters. Once done, test the details by clicking on the Test button.

If the test is successful, click Save and then click OK in the ODBC Data Source Administrator.

Create a SQL Server Linked Server to PostgreSQL

Step 1: Start SQL Server Management Studio and connect to the SQL Server instance as «sa» or a sysdmin role member. Expand the Server Objects folder, right click on the Linked Servers node, and then choose «New Linked Server. » option from the pop-up menu.

On the General tab of the New Linked Server dialog box, choose the «Other data source» option, select «Microsoft OLE DB Provide for ODBC Drivers» option from the Provider drop-down list, provide a name for the Product and specify the Data Source name. The data source should be the one we just created: in this case it’s world_db_postgres.

Step 2: In the Security tab, choose the fourth option («Be made using this security context») and provide a login name and password to connect to the remote PostgreSQL instance. In this case we have used the built-in Postgres super user account to keep things simple.

Step 3: In the Server Options tab, choose the following options:

Click OK. If the connection is successful, the Linked Server will be created without any error.

Expanding the Linked Server node in SQL Server Management Studio would show us the tables in the world database in PostgreSQL.

Access PostgreSQL Data from SQL Server

Now that we can see the remote data, let’s fetch it into SQL Server. Open a new query window in Management Studio, select the world database and execute the following commands:

The result should show 4079 rows have been copied. Next, execute this command:

This should show 239 rows have been copied.

To be sure, you can count the number of rows in the local tables.

Conclusion

So now we have it. We have created an ODBC connection against the remote PostgreSQL instance, created a linked server on top of it and then executed two commands to copy across the data. There was no need to export the source data into text files and importing them using BCP or BULK INSERT.

This process can obviously be automated via scripts and stored procedures that are called by SQL Server Integration Services Packages or SQL Server Agent Jobs. SQL Server doesn’t give us any option to create push or pull replication subscription against PostgreSQL databases.

We haven’t discussed data access speed via ODBC, nor have we discussed any migration pitfalls like data type mismatches. The idea was to show how SQL Server can access PostgreSQL data seamlessly. Executing any PostgreSQL functions or stored procedures from SQL Server is another area your data migration team may have to consider.

Next Steps
  • Stay tuned for the final part of this series
  • Download and install the PostgreSQL ODBC driver and configure a data source and linked server to access PostgreSQL data
  • Visit PostgreSQL official website for more information

Настройка PostgreSQL для 1С

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

  • Масштабируемость;
  • Сокращение стоимости владения ПО, по сравнению с MS SQL;
  • Надежность хранения данных.

Можно продолжать увеличивать и объем базы 1С, и количество одновременно работающих в ней пользователей без снижения производительности системы и роста затрат.

Перевод 1С с файловой версии на PostgreSQL

Производительность файловой версии 1С резко падает при достижении следующих показателей:

  • Количество одновременно работающих пользователей — больше 20;
  • Объем базы — больше 4 Гб;
  • Большой объем ежедневного ввода однотипных документов, например, отгрузок.

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

Для перехода на клиент-серверную версию 1С, дополнительно потребуются как лицензия на 1С-сервер (покупается дополнительно к имеющемуся пакету лицензирования 1С), так и СУБД SQL, адаптированная для работы с 1С. В качестве СУБД для этого подходят:

  • MS SQL Server;
  • MS SQL Express;
  • PostgreSQL;
  • Oracle;
  • IBM DB2.

Из них наиболее популярны:

Если база 1С меньше 10 Гб и за ее размером постоянно следить, то MS SQL EXPRESS можно использовать как временный вариант:

  • Бесплатная СУБД;
  • Производство компании «Microsoft»;
  • Рекомендована разработчиком для «тестирования и ознакомления» и имеет ограничения — оперативная память менее 1 Гб, размер базы данных менее 10 Гб, использование только 1 процессора. Не имеет механизмов автоматического запуска регламентных заданий и создания резервных копий.

Компания с солидным бюджетом на регулярную закупку или аренду ПО, может использовать MS SQL для 1С в качестве надежного и производительного решения:


  • Платная СУБД;
  • Производство компании «
  • Не имеет ограничений по использованию оперативной памяти, размеров баз данных и количества процессоров.

Связка 1С + PostgreSQL применяется в компаниях любого размера, с любым количеством пользователей и размером информационных баз:

  • Бесплатная СУБД;
  • Свободное ПО (open source);
  • Не имеет ограничений по использованию оперативной памяти, размеров баз данных и количества процессоров.

При исчерпании возможностей файловой версии 1С оптимальным бизнес-решением станет переход на PostgreSQL:

  • Возможность масштабирования — отсутствуют технические ограничения по размерам базы, количеству пользователей, процессорам и т.д.;
  • Экономия на регулярных платежах за SQL-лицензии, по сравнению с MS SQL;
  • Экономия средств и усилий за счет разового перехода на PostgreSQL по сравнению со ступенчатым переходом — вначале с файловой 1С на MS SQL, потом — для экономии затрат с MS SQL на PostgreSQL.
  • Чтобы не увеличивать нагрузку на ваши рабочие сервера и быстро подобрать необходимые параметры серверных мощностей, мы предоставляем на 30 дней частное облако («песочницу»). Это позволяет отработать связку 1С-базы, 1С-сервера и PostgreSQL и протестировать работу на 2-3 пользователях. После отработки оптимальной конфигурации можно продолжить работу и сопровождение 1С в частном облаке (гибридная ИТ-инфраструктура) или перенести данные на собственные сервера.

Перенос базы 1С с MS SQL на PostgreSQL

Не каждый ИТ-бюджет выдержит политику лицензирования компании «Microsoft». Многие компании переводят свои базы 1С с MS SQL на бесплатный PostgreSQL, а экономия на покупке лицензий с лихвой окупает затраты на перенос базы.

При переносе 1С на PostgreSQL надо учитывать:

Типовые 1С-конфигурации практически без проблем переносятся на PostgreSQL. Если же проводились доработки конфигурации и при этом не соблюдался стандарт запросов SQL92, то 1С на PostgreSQL работать не будет. Потребуется переделка запросов во всех измененных отчетах и процедурах.

При очень близких показателях по производительности, версии PostgreSQL под Windows быстрее обновляются и содержат больше полезных патчей. Если же PostgreSQL работает под Linux, работу 1С потребуется заново настраивать при каждом ее обновлении и устанавливать новые сборки PostgreSQL.

При установке 32-битного 1С-сервера на 64-битную ОС количество используемой памяти ограничено 4 гигобайтами. Рекомендуем использовать 64-битные версии операционных систем и 1С-сервера.

Если вы только собираетесь развертывать 1С в сети вашей компании, то лучше сразу ориентироваться на версию 1С:Предприятия на PostrgeSQL. В ином случае в будущем может потребоваться выполнение проекта по переводу 1С уже с СУБД MS SQL на PostgreSQL, что может повлечь дополнительные затраты.

Типовой проект по переводу 1С с MS Sql на PostgreSQL

  • Количество пользователей: 20;
  • Объем 1С-базы: 10Гб.
  • Терминальный сервер 1С;
  • 1С-сервер;
  • СУБД PostgreSQL.

Используется схема из двух звеньев, при которой база 1С работает на одном сервере, а 1С-сервер и PostgreSQL — на втором. Такой подход позволяет, при умеренном бюджете, измерять и оптимизировать нагрузку на каждый элемент системы, добиваясь общей максимальной производительности.

Этапы выполнения проекта:

Настройка серверов

Включает в себя:

  • Настройку технологического журнала и дампов;
  • Настройку ОС — профилей пользователей, настройка сетевого стека и т.д.

Настройка 1С-сервера и PostgreSQL

  • Установить и настроить СУБД PostgreSQL в максимально производительной конфигурации — отключить режим Energy Saving и т.д.;
  • Установить «Сервер 1С: Предприятие», для обеспечения доступа платформы 1С к SQL-данным.

Конвертация баз данных 1С в формат PostgreSQL

Перенос данных 1С из текущего формата в формат PostgreSQL, проводят одним из следующих способов:

  • Штатные механизмы конвертации. Например, создание резервной копии базы в файле с расширением «.dt» и восстановление ее на PostgresQL-сервере, используя новое подключение;
  • При помощи специальных утилит, распространяемых сторонними производителями ПО;
  • Воссоздав DDL-скрипт базы MS SQL в формате SQL92 (штатным инструментарием этой СУБД) и сгенерировав заново структуру БД в PostgresQL. Сами данные могут быть выгружены в любом формате (CSV, XML и т.д.) и загружены в таблицы сгенерированной структуры;
  • Задействовав ODBC.

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

Проект по переводу 1С на PostgreSQL требует знаний и квалификации сразу в нескольких ИТ-областях. Такие проекты выполняет команда из системного инженера, специалиста по SQL-базам и 1С-программиста, специализирующегося на данных конфигурациях 1С.

Сроки и стоимость проекта по переводу 1С на PostgreSQL

Наши преимущества

Мы умеем переводить 1С на PostgreSQL и готовы сделать это быстро и качественно:

  • Обеспечиваем весь комплекс работ — над каждым проектом работает слаженная команда профессионалов по своим направлениям (системное администрирование, оптимизация SQL-баз, 1С-конфигурации);
  • У нас работают 1С-программисты по всем основным конфигурациям, с опытом настройки 1С:Предприятие более 15 лет;
  • Наши системные инженеры обслуживают сервера на Windows, на Linux и знают, как добиться максимальной производительности на разных версиях ОС;
  • Предоставляем в аренду частные облака и для постоянной работы и для тестирования работы 1С на PostgreSQL. Знаем все о распределенных вычислениях, оптимизации нагрузок на сервера и отказоустойчивости. Поможем проверить любую новую конфигурацию ИТ-инфраструктуры, чтобы добиться максимальной производительности.

Как начать работу?

Сколько будет стоить перевод вашей 1С на PostgreSQL?
Запросите предварительный расчет:

Нужно оптимизировать работу 1С?
Получите рекомендации нашего ИТ-эксперта:

Введение в PostgreSQL

Что такое PostgreSQL. Установка сервера

PostgreSQL является одной из наиболее популярных систем управления базами данных. Сам проект postgresql эволюционировал из другого проекта, который назывался Ingres. Формально развитие postgresql началось еще в 1986 году. Тогда он назывался POSTGRES. А в 1996 году проект был переименован в PostgreSQL, что отражало больший акцент на SQL. И собственно 8 июля 1996 года состоялся первый релиз продукта.

С тех пор вышло множество версий postgresql. Текущей версией является версия 12. Однако регулярно также выходят подверсии.

PostgreSQL поддерживается для всех основных операционных систем — Windows, Linux, MacOS.

PostgreSQL развивается как opensource. Исходный код проекта можно найти в репозитории на гитхабе по адресу https://github.com/postgres/postgres.


Установка

На странице https://www.postgresql.org/download/ можно найти ссылки на загрузку различных дистрибутивов для различных операционных систем. В частности, для загрузки дистрибутива для Windows надо перейти на страницу https://www.enterprisedb.com/downloads/postgres-postgresql-downloads и указать все необходимые опции для загрузки: версию postgres и операционную систему.

Тут же можно найти дитрибутивы и для других систем.

Запустим программу установки:

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

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

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

Оставим путь по умолчанию и перейдем к следующему шагу. Затем необходимо будет установить пароль для суперпользователя postgres :

При установке запомним пароль, так как он потребуется для подключения к серверу. Затем нужно будет установить порт, по которому будет запускаться сервер. Можно оставить порт по умолчанию:

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

После этого мы увидим сводку по всем настройкам:

И если нас все устраивает, то можно нажать на кнопку Next, и начнется установка

И после завершения установки мы увидем следующее окно, и для выхода нажмем на кнопку Finish:

Таким образом, сервер PostgreSQL установлен, и мы можем начинать с ним работать.

PostgreSQL vs MS SQL для 1С

Давайте признаемся честно, хоть 1С Предприятие и совместимо со многими СУБД, но по факту 99 процентов работают либо в MS SQL или в бесплатной PostgreSQL.

Другими словами эти две «субдешки» завоевали рынок клиент-серверной 1С.

И можно смело предполагать, что если компания не работает в MS SQL то, скорее всего, просто используют PostgreSQL.

Соответственно сравнивать «Постгрес» есть смысл разве что только с MS SQL.

Сегодня много пишут, как об MS SQL так и о PostgreSQL, но обычно объективно не сравнивают их.

В этой статье мы же разберем основные технические моменты бесплатной PostgreSQL, сравнивая ее c MS SQL.

Что позволит Вам в будущем сделать оптимальный выбор и быть готовым к разным «неожиданностям» или что будет более правильно «особенностям» работы в этой бесплатной СУБД.

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

Сразу отвечу на вопрос, который волнует многих новичков!

ДА! MS SQL работает быстрее PostgreSQL, это факт! И на это есть ряд причин!

Возможно, я кого-то прямо сразу разочаровал, и возможно, Вы не согласны с таким утверждением, извиняюсь, но сама физика работы этой бесплатной СУБД не позволяет ему опередить MS SQL, особенно если мы говорим о такой связке как «Монстр 1С» и PostgreSQL.

Подобные доводы Вы не раз встретите на различных конференциях и семинарах посвященных этой СУБД. Никто ничего не скрывает и не отрицает, факт есть факт.

Тем не менее, производительности PostgreSQL вполне достаточно, для того, чтоб пользователи могли комфортно работать в 1С.

Будь это десяток пользователей или даже несколько сотен, одновременно работающих в 1С Предприятии.

Почему «Монстр 1С» ?

Собственно так 1С видит PostgreSQL без установленных специальных «патчей» и расширений.

Да, что называется из коробки, скачав дистрибутив PostgreSQL на оф. сайте Вы не сможете использовать его для работы в связке с 1С. 1С-ка будет жутко тормозить и просто останавливаться, отказываться работать.

Почему так происходит, и зачем «патчи»?

Дело в том что 1С Предприятие создает огромное количество временных таблиц в процессе своей работы, речь может идти о тысячах таблиц в секунду, а если взять, например регистр «Срез последних» — «ОстаткиИОбороты», там вполне могут и по миллиону строк быть.

Дело в том что по умолчанию (без «патчей») PostgreSQL не считает статистику по этим большим временным таблицам, другими словами оптимизатор запросов который руководствуется данными из статистики (а она как помним пуста, нечего считать) грубо говоря, делает выборку методом SELECT * что конечно будет работать очень и очень медленно!

Отсюда грандиозные тормоза в 1С!

Конечно это не все проблемы, которые нужно решить, чтоб PostgreSQL работал в паре с 1С нормально. Нужны будут и другие «патчи» и специальные расширения и после 15-20 пользователей, еще и доп. настройки в «конфиге»

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

Второе что мне сильно не нравится в PostgreSQL это отсутствие многопоточности в рамках одного запроса в сравнении с MS SQL.

(Начиная с версии 9.6 сделали первую попытку распараллеливания запросов, но пока работает плохо, иногда эффект обратный ). но за попытку 5! )

Илон Маск рекомендует:  Глава 4 логический подход к построению систем ии

Что конечно влияет на производительность, чтоб Вы понимали простым языком —

PostgreSQL способен уложить Ваш 48-ми ядерный сервер, одним большим запросом!

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

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

И чуть не забыл, сравниваем мы PostgreSQL c MS SQL Standard не Express!

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

таких как 10 Гб на базу, использование одного процесора, 1 Гб оперативной памяти,

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

Разве что у вас очень маленькая база и всего пара пользователей, (да и то бывают тормоза 1 гб для СУБД очень мало).

Так что сравниваем PostgreSQL с популярной версией Standard.

СКРИПТЫ.

PostgreSQL это прежде всего скрипты в сравнении с MS SQL, большинство операций приходится делать руками, да можно установить конечно pgAdmin 4 и некоторые базовые вещи выполнять через интерфейс, но подчеркну, что базовые , а шаг влево шаг вправо и нужно писать скрипт, или БАШ на Линуксе или cmd, powershell наWindows.

Просмотр и анализ трассировок с помощью приложения SQL Server Profiler.

Всем известный SQL Server Profiler в PostgreSQL отсутствует, причем под словом «отсутствует» я имею, введу напрочь, увы, нет ничего подобного в PostgreSQL.

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

Можно настроить лог и потом это все перебирать — но долго!

Вот пример:


Программист 1С пытается отладить какой-нибудь большой запрос, он долго выполняется, например 30 минут, так вот в PostgreSQL, чтоб данные попали в лог, этот запрос должен выполнится! Представляете, как долго можно отлаживать такой запрос?

В то время как в MS SQL можно прервать выполнение запроса и в Профайлере его разобрать, так как он там уже будет, но со статусом «failed».

По разновидности создания «бэкапов» Постгресу нет равных!

Здесь Вам и инкрементный «бекап» и полное резервное копирование и непрерывное WAL архивирование.

Как собственно есть и частичное резервное копирование и частичное восстановление данных.

Можно настроить непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery (PITR)).

Также репликация, доступна изначально в PostgreSQl без каких либо «патчей» утилит и дополнений!

  • Каскадная репликация
  • Потоковая репликация
  • Синхронная репликация
  • Непрерывное архивирование на резервном сервере

Все это есть, уже изначально в PostgreSQl и конечно нет в «экспрессе» и недоступно на версии MS SQL Standard.

Чтоб получить все выше перечисленное в MS SQL, нужно покупать очень дорогой MS SQL Enterprise, сейчас что-то около 15 000$ долларов.

Чего нет в сравнении с MS SQL ?

НЕТ диференциального «бэкапа»

Да в PostgreSQl нет дифференциального «бэкапа», но есть различные аналоги инкрементного создания «бэкапов».

Например, инкрементный «бэкап» на уровне блоков.

ЕСТЬ разделение TABLESPACE-ов, что уже по умолчанию поддерживает 1С!

Которого к слову нет в MS SQL!

Например, Вы можете настроить на каком диске у вас будут «индексы» и на каком диске будет находиться «таблица», очень удобно при планировании IТ инфраструктуры, когда речь идет о больших базах данных 1С.

PostgreSQl намного быстрее обновляет статистику (если точнее — считает ее)

Например тот же MS SQL в терабайтной базе может считать статистку час, а вот PostgreSQL минуты 2-3. )

Внимание!

MS SQL в «бэкап» помещает уже посчитанную статистику, чего не делает PostgreSQL!

И когда Вы восстановитесь из такого «бэкапа» будут тормоза, пока не сделаете ONLINE_ANALYZE, чтоб пересчитать статистику. Тоже самое касается файла *dt.

Выгрузили – загрузили, нужно считать статистику ONLINE_ANALYZE!

Используя PostgreSQl очень редко нужен REINDEX!

Фактически стоит использовать только когда, есть подозрения, что целостность базы данных повреждена.

Можно делать «бэкапы» с исключением таблиц!

Например, у вас в компании работают несколько программистов 1С, они гарантированно будут делать себе резервные копии создавать «бэкапы» для дальнейшей разработки.

В итоге страдают пользователи, база тормозит во время создания большого «бэкапа» особенно если в этой базе есть такие вещи как различные прикрепленные файлы, архивы, документы из писем. Такие таблицы с файлами запросто могут содержать сотни гигабайт. И их же можно исключить в PostgreSQl создавая «бэкап», тем самым малого размера, и со всем функционалом одновременно.

Так мы лишний раз не нагружаем сетевые устройства, не забиваем канал, тратим намного меньше времени на создание такого «бэкапа»

В итоге все в выигрыше! И пользователи, и программисты и админы спят спокойно.

В этой статье мы разобрали лишь базовые отличия PostgreSQl от MS SQL, (есть и другие) но определится с выбором в пользу той или иной СУБД, статья должна помочь!

АйТи бубен

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

Инструменты сайта

Содержание

PostgreSQL

PostgreSQL (произносится «Постгре-Эс-Кю-Эль», коротко называется «Постгрес») — свободная объектно-реляционная система управления базами данных (СУБД система управления базами данных). Использует порт 5432/tcp/udp. PostgreSQL использует только один механизм хранения данных под названием Postgres storage system (система хранения Postgres), в котором транзакции и внешние ключи полностью функциональны, в отличии от MySQL, в котором InnoDB и BDB являются единственными типами таблиц, которые поддерживают транзакции.

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

MVCC — одна из ключевых технологий доступа к данным, которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность, данные из таблицы сразу не удаляются, а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных, и созданием новых записей с измененными полями. Таким образом, история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям, которые были удалены или изменены в параллельных незакрытых транзакциях. Техника, используемая в этом подходе, относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.

Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота, полей xmin, xmax осуществляется фильтрация записей.

pg_hba.conf идентификация пользователей

pg_hba.conf — настройка политики доступа к базам данных и идентификации пользователей сервера PostgreSQL .

В этом файле описываются клиентские компьютеры сети, с которых разрешен доступ к SQL серверу PostgreSQL , а также методы идентификации клиентов. Этот файл может содержать два вида записей:

Примеры записей pg_hba.conf:

Кодировка БД PostgreSQL и locale

PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (Настройка переменных локализации в Linux), иначе не будут работать строковые функции сортировки, upper/lower и т.п. Локаль общая для всех процессов сервера — соответственно он не может создать две базы в разных кодировках — кодировка всегда одна для всего сервера и всех его БД.

Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):

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

Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.

Клиенты администрирования PostgreSQL

psql — PostgreSQL interactive terminal.

В директории /usr/share/doc/postgresql* можно найти дополнительную информацию по запуску.

Посмотреть и удалить активные запросы

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

SELECT запросы можно снимать из ОС командой kill

Транзакции в PostgreSQL

В PostgreSQL Транзакция — это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.


PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с >

Выполним транзакцию для test777:

Мониторинг, логи, размер БД PostgreSQL

Лог файлы

Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.

Мониторинг

Текущую активность базы данных легко оценить с помощью команды ps, для вывода в реальном времени (с задержкой 1 секунда) можно использовать утилиту watch:

Так как для каждого клиента создаётся своя копия процесса postmaster, то это позволяет подсчитать число активных клиентов. Статусная строка даёт информацию о состоянии клиента. Фразы writer process, stats buffer process и stats collector process соответствуют системным процессам, запущенным самим PostgreSQL при старте. Пользовательские процессы имеют статусную строку вида:

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

Views сборщик статистики

Представления (Views) сборщика статистики.

Если в PostgreSql postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.

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

Стандартные Statistics Views. Вывести все представления каталога

Вывести соотношение hit/read. При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет — делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.

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

Статистика по индексам. Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.

Уровни блокировок таблиц

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.

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

Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.

Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.

Automatic Vacuuming

Автоматическая сборка мусора (Automatic Vacuuming).

Синтаксис VACUUM:

Синтаксис ANALYZE:

Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение SQL- запросов. То есть, если не хочется настраивать автоматическую сборку мусора, то в любом случае её придётся делать только теперь в ручную. Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов. Регулярное исполнение команд VACUUM и ANALYZE обязательно. Это необходимо по той причине, что иначе не получится заново использовать дисковое пространство, которое занимают ранее удалённые или изменённые строки и не удастся обновить статистику для планировщика запросов. И то и другое отрицательно сказывается на эффективности использования ресурсов и производительности запросов. Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс, который, соответственно, так и называется autovacuum daemon. Все настройки для этого процесса хранятся в PostgreSql postgresql.conf. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать, то в любом случае необходимо производить сборку мусора и набор статистики в ручную.

Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции:

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

Практика показала, что без более-менее регулярных запусков vacuum full analyze производительность системы постепенно падает, причем чем дальше, тем больше. Разница между vacuum и vacuum full заключается в том, что full физически переписывает на диске всю таблицу таким образом, чтобы в ней не оставалось «дырок» от удаленных или обновленных записей. Но его недостаток в том, что во время работы таблица полностью блокируется(включая и select запросы), что может привести к проблемам на популярном сервере — начнет скапливаться очередь запросов, ожидающих доступа к базе, каждый запрос требует памяти, память кончается, начинается запись в Как посмотреть информацию о swap?, из-за отсутствия памяти сам vacuum тоже начинает использовать swap и все начинает работать очень-очень медленно. Простой VACUUM (Без FULL) просто восстанавливает пространство и делает его доступным для повторного использования. Эта форма команды умеет работать параллельно с обычными чтение и запись таблицы, без монопольной блокировки.

Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.

Получение >

lcr_id — колонка автоинкрементная (lcr_id | integer | not null default nextval(‘df_lcr_list_lcr_id_seq’::regclass)). Запрос возвращает значение колонки lcr_id для вставленной записи, в этом случае id записи.

Системные таблицы pg_

Системные таблицы(System Catalogs) PostgreSQL начинаются с префикса pg_.

Имя таблицы Назначение таблицы
1 pg_aggregate aggregate functions
2 pg_am index access methods
3 pg_amop access method operators
4 pg_amproc access method support procedures
5 pg_attrdef column default values
6 pg_attribute table columns («attributes»)
7 pg_authid authorization identifiers (roles)
8 pg_auth_members authorization identifier membership relationships
9 pg_cast casts (data type conversions)
10 pg_class PostgreSQL System Catalogs tables, indexes, sequences, views («relations»)
11 pg_constraint check constraints, unique constraints, primary key constraints, foreign key constraints
12 pg_conversion encoding conversion information
13 pg_database databases within this database cluster Хранятся имена доступных баз данных
14 pg_depend dependencies between database objects
15 pg_description descriptions or comments on database objects В таблице хранятся описания объектов, для которых была применена функция COMMENT (расширение PostgreSQL). Например COMMENT ON TABLE mytable IS ‘Эта строка будет сохранена в системной таблице pg_description.’;
16 pg_enum enum label and value definitions
17 pg_foreign_data_wrapper foreign-data wrapper definitions
18 pg_foreign_server foreign server definitions
19 pg_index additional index information
20 pg_inherits table inheritance hierarchy
21 pg_language languages for writing functions
22 pg_largeobject large objects
23 PostgreSQL pg_listener asynchronous notification support Используется механизмом LISTEN/NOTIFY. pg_listener существует в версиях PostgreSQL меньше 9.
24 pg_namespace schemas
25 pg_opclass access method operator classes
26 pg_operator operators
27 pg_opfamily access method operator families
28 pg_pltemplate template data for procedural languages
29 pg_proc functions and procedures
30 pg_rewrite query rewrite rules
31 pg_shdepend dependencies on shared objects
32 pg_shdescription comments on shared objects
33 pg_statistic planner statistics
34 pg_tablespace tablespaces within this database cluster
35 pg_trigger triggers Триггеры хранятся в системной таблице pg_trigger, что позволяет получить информацию о существующих триггерах на программном уровне.
36 pg_ts_config text search configurations
37 pg_ts_config_map text search configurations’ token mappings
38 pg_ts_dict text search dictionaries
39 pg_ts_parser text search parsers
40 pg_ts_template text search templates
41 pg_type data types
42 pg_user_mapping mappings of users to foreign servers
Представления (View) Назначение
43 pg_cursors open cursors
44 pg_group groups of database users
45 pg_indexes indexes
46 pg_locks блокировки в PostgreSQL currently held locks Содержит информацию о блокировках. Уровни блокировок таблиц .
47 pg_prepared_statements prepared statements
48 pg_prepared_xacts prepared transactions
49 pg_roles database roles
50 pg_rules rules
51 pg_settings parameter settings
52 pg_shadow database users Существует для обратной совместимости, она имитирует каталог, который существовал в PostgreSQL до версии 8.1.
53 pg_stats planner statistics
54 pg_tables tables
55 pg_timezone_abbrevs time zone abbreviations
56 pg_timezone_names time zone names
57 pg_user database users Информативный характер о пользователях, пароль содержится в таблице pg_shadow
58 pg_user_mappings user mappings
59 pg_views views

Partitioning (Партицирование)

Partitioning (партицирование, секционирование).

Введение в PostgreSQL

Что такое PostgreSQL. Установка сервера

PostgreSQL является одной из наиболее популярных систем управления базами данных. Сам проект postgresql эволюционировал из другого проекта, который назывался Ingres. Формально развитие postgresql началось еще в 1986 году. Тогда он назывался POSTGRES. А в 1996 году проект был переименован в PostgreSQL, что отражало больший акцент на SQL. И собственно 8 июля 1996 года состоялся первый релиз продукта.

С тех пор вышло множество версий postgresql. Текущей версией является версия 12. Однако регулярно также выходят подверсии.

PostgreSQL поддерживается для всех основных операционных систем — Windows, Linux, MacOS.

PostgreSQL развивается как opensource. Исходный код проекта можно найти в репозитории на гитхабе по адресу https://github.com/postgres/postgres.

Установка

На странице https://www.postgresql.org/download/ можно найти ссылки на загрузку различных дистрибутивов для различных операционных систем. В частности, для загрузки дистрибутива для Windows надо перейти на страницу https://www.enterprisedb.com/downloads/postgres-postgresql-downloads и указать все необходимые опции для загрузки: версию postgres и операционную систему.

Тут же можно найти дитрибутивы и для других систем.

Запустим программу установки:

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

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

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

Оставим путь по умолчанию и перейдем к следующему шагу. Затем необходимо будет установить пароль для суперпользователя postgres :

При установке запомним пароль, так как он потребуется для подключения к серверу. Затем нужно будет установить порт, по которому будет запускаться сервер. Можно оставить порт по умолчанию:

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

После этого мы увидим сводку по всем настройкам:

И если нас все устраивает, то можно нажать на кнопку Next, и начнется установка


И после завершения установки мы увидем следующее окно, и для выхода нажмем на кнопку Finish:

Таким образом, сервер PostgreSQL установлен, и мы можем начинать с ним работать.

Перенос базы данных PostgreSQL в MS SQL Server [дубликат]

У меня есть база данных PostgreSQL, которую я хочу переместить на SQL Server — как схему, так и данные. Я беден, поэтому я не хочу платить деньги. Я тоже ленив, поэтому я не хочу много работать. В настоящее время я делаю эту таблицу за столом, и есть около 100 таблиц. Это очень утомительно.

Есть ли какой-то трюк, который делает то, что я хочу?

2 ответа

Я считаю, что вы, возможно, получили пониженные голоса из-за невероятной простоты создания простого сценария SQL из PostgreSQL, который (теоретически) может быть запущен как раз в любой СУБД. Если пользователь является обычным пользователем PostgreSQL, он звучит как тупой вопрос.

Это не справедливо, так как выясняется, что это на самом деле проблема с умеренной сложностью (хотя это связано с нечетным синтаксисом и интерфейсом SQL Server, чем с отказом PostgreSQL).

Вы должны быть способны найти полезную информацию в принятом ответе на этой странице Serverfault: https://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005 .

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

load будет довольно медленным, но опция —column-inserts генерирует наиболее общие инструкции INSERT, доступные для каждой строки данных, и должна быть совместимой.

EDIT: Предложения по преобразованию схемы следуют:

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

Отредактируйте этот файл, чтобы добавить строку BEGIN TRANSACTION; в начало и ROLLBACK TRANSACTION; до конца. Теперь вы можете загрузить его и запустить в окне запроса на SQL Server. Если вы получите какие-либо ошибки, убедитесь, что вы переместились в нижнюю часть файла, выделите инструкцию ROLLBACK и запустите ее (нажав F5, когда вы подсвечиваете оператор).

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

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

  • PostgreSQL делает автоматическое увеличение полей, связывая поле NOT NULL INTEGER с SEQUENCE , используя a DEFAULT . В SQL Server это столбец IDENTITY , но они не совсем то же самое. Я не уверен, что они эквивалентны, но если ваша исходная схема заполнена полями «id», у вас могут быть проблемы. Я не знаю, имеет ли SQL Server CREATE SEQUENCE , поэтому вам, возможно, придется их удалить.
  • Функции базы данных / Хранимые процедуры не переводятся между платформами РСУБД. Вам нужно будет удалить любые CREATE FUNCTION операторы и перевести алгоритмы вручную.
  • Будьте осторожны с кодировкой файла данных. Я человек Linux, поэтому я понятия не имею, как проверить кодировку в Windows, но вам нужно убедиться, что ожидаемый SQL Server такой же, как файл, который вы импортируете из PostgreSQL. pg_dump имеет опцию —encoding= , которая позволит вам установить конкретную кодировку. Я, кажется, помню, что Windows имеет тенденцию использовать двухбайтную кодировку UTF-16 для Unicode, где PostgreSQL использует UTF-8. У меня возникла проблема с SQL Server на PostgreSQL из-за вывода UTF-16, поэтому было бы интересно исследовать.
  • Тип данных PostgreSQL TEXT — это просто VARCHAR без максимальной длины. В SQL Server TEXT является . сложным (и устаревшим). Каждое поле в исходной схеме, объявленное как TEXT , должно быть пересмотрено для соответствующего типа данных SQL Server.
  • SQL Server имеет дополнительные типы данных для данных UNICODE . Я недостаточно знаком с этим, чтобы делать предложения. Я просто указываю, что это может быть проблемой.

Сравнение MySQL и PostgreSQL

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

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

Системы управления базами данных

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

Реляционные системы управления базами данных позволяют размещать данные в таблицах, связывая строки из разных таблиц и, таким образом, связывая разные, объединенные логически данные. Перед тем, как вы сможете сохранять данные, необходимо создать таблицы определенного размера и указать тип данных для каждого столбца. Столбы представляют поля данных, а сами данные размещены в строках. Обе системы управления базами данных, и MySQL vs Postgresql принадлежат к реляционным. Дальше мы рассмотрим подробнее чем отличаются обе программы. А теперь перейдем к более детальному рассмотрению.

Краткая история

MySQL

Разработка MySQL началась еще в 90х годах. Первый внутренний выпуск базы данных состоялся в 1995 году. За это время разработкой программы занимались несколько компаний. Разработка была начата шведской компанией MySQL AB, которую приобрела Sun Microsystems, которая, собственно перешла в собственность Oracle. На данный момент, начиная с 2010 года, разработкой занимается Oracle.

Postgresql

Разработка Postrgresql началась в далеком 1986 году в стенах Калифорнийского университета Беркли. Разработка длилась почти восемь лет, затем проект разделился на две части коммерческую базу данных IIlustra и полностью свободный проект Postrgesql, который разрабатывается энтузиастами.

Хранение данных

MySQL

MySQL — это реляционная база данных, для хранения данных в таблицах используются различные движки, но работа с движками спрятана в самой системе. На синтаксис запросов и их выполнение движок не влияет. Поддерживаются такие основные движки MyISAM, InnoDB, MEMORY, Berkeley DB. Они отличаются между собой способом записи данных на диск, а также методами считывания.

Postgresql

Postgresql представляет из себя объектно реляционную базу данных, которая работает только на одном движке — storage engine. Все таблицы представлены в виде объектов, они могут наследоваться, а все действия с таблицами выполняются с помощью объективно ориентированных функций. Как и в MySQL все данные хранятся на диске, в специально отсортированных файлах, но структура этих файлов и записей в них очень сильно отличается.

Стандарт SQL

Независимо от используемой системы управления базами данных, SQL — это стандартизированный язык выполнения запросов. И он поддерживается всеми решениями, даже MySQL или Postgresql. Стандарт SQL был разработан в 1986 году и за это время уже вышло нескольких версий.

MySQL

MySQL поддерживает далеко не все новые возможности стандарта SQL. Разработчики выбрали именно этот путь развития, чтобы сохранить MySQL простым для использования. Компания пытается соответствовать стандартам, но не в ущерб простоте. Если какая-то возможность может улучшить удобство, то разработчики могут реализовать ее в виде своего расширения не обращая внимания на стандарт.

Postgresql

Postgresql — это проект с открытым исходным кодом, он разрабатывается командой энтузиастов, и разработчики пытаются максимально соответствовать стандарту SQL и реализуют все самые новые стандарты. Но все это приводит к ущербу простоты. Postgresql очень сложный и из-за этого он не настолько популярен как MySQL.

Возможности обработки

Из предыдущего пункта выплывают и другие отличия postgresql от mysql, это возможности обработки данных и ограничения. Естественно, соответствие более новым стандартам дает более новые возможности.

MySQL

При выполнении запроса MySQL загружает весь ответ сервера в память клиента, при больших объемах данных это может быть не совсем удобно. В основном по функциям Postgresql превосходит Mysql, дальше рассмотрим в каких именно.

Postgresql

Postgresql поддерживает использование курсоров для перемещения по полученным данным. Вы получаете только указатель, весь ответ хранится в памяти сервера баз данных. Этот указатель можно сохранять между сеансами. Здесь поддерживается построение индексов сразу для нескольких столбцов таблицы. Кроме того, индексы могут быть различных типов, кроме hash и b-tree доступны GiST и SP-GiST для работы с городами, GIN для поиска по тексту, BRIN и Bloom.

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

Производительность

Базы данных должны обязательно быть оптимизированы для окружения, в котором вы будете работать. Исторически так сложилось что MySQL ориентировалась на максимальную производительность, а Postgresql разрабатывалась как база данных с большим количеством настроек и максимально соответствующую стандарту. Но со временем Postgresql получил много улучшений и оптимизаций.

MySQL

В большинстве случаев для организации работы с базой данных в MySQL используется таблица InnoDB, эта таблица представляет из себя B-дерево с индексами. Индексы позволяют очень быстро получить данные из диска, и для этого будет нужно меньше дисковых операций. Но сканирование дерева требует нахождения двух индексов, а это уже медленно. Все это значит что MySQL будет быстрее Postgresql только при использовании первичного ключа.

Postgresql

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

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

Типы данных

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

MySQL

MySQL поддерживает такие типы данных:

  • TINYINT: очень маленькое целое.;
  • SMALLINT: маленькое целое;
  • MEDIUMINT: целое среднего размера;
  • INT: целое нормального размера;
  • BIGINT: большое целое;
  • FLOAT: знаковое число с плавающей запятой одинарной точности;
  • DOUBLE, DOUBLE PRECISION, REAL: знаковое число с плавающей запятой двойной точности
  • DECIMAL, NUMERIC: знаковое число с плавающей запятой;
  • DATE: дата;
    DATETIME: комбинация даты и времени;
  • TIMESTAMP: отметка времени;
  • TIME: время;
    YEAR: год в формате YY или YYYY;
  • CHAR: строка фиксированного размера, дополняемая справа пробелами до максимальной длины;
  • VARCHAR: строка переменной длины;
  • TINYBLOB, TINYTEXT: двоичные или текстовые данные максимальной длиной 255 символов;
  • BLOB, TEXT: двоичные или текстовые данные максимальной длиной 65535 символов;
  • MEDIUMBLOB, MEDIUMTEXT: текст или двоичные данные;
  • LONGBLOB, LONGTEXT: текст или двоичные максимальной данные длиной 4294967295 символов;
  • ENUM: перечисление;
  • SET: множества.

Postgresql

Поддерживаемые типы полей в Postgresql достаточно сильно отличаются, но позволяют записывать точно те же данные:

  • bigint: знаковое 8-байтовое целое;
  • bigserial: автоматически увеличиваемое 8-байтовое целое;
  • bit: двоичная строка фиксированной длины;
  • bit varying: двоичная строка переменной длины;
  • boolean: флаг;
  • box: прямоугольник на плоскости;
  • byte: бинарные данные;
  • character varying: строка символов фиксированной длины;
  • character: строка символов переменной длины;
  • cidr: сетевой адрес IPv4 или IPv6;
  • circle: круг на плоскости;
  • date: дата в календаре;
  • double precision: число с плавающей запятой двойной точности;
  • inet: адрес интернет IPv4 или IPv6;
  • integer: знаковое 4-байтное целое число;
  • interval: временной промежуток;
  • line: бесконечная прямая на плоскости;
  • lseg: отрезок на плоскости;
  • macaddr: MAC-адрес;
  • money: денежная величина;
  • path: геометрический путь на плоскости;
  • point: геометрическая точка на плоскости;
  • polygon: многоугольник на плоскости;
  • real: число с плавающей точкой одинарной точности;
  • smallint: двухбайтовое целое число;
  • serial: автоматически увеличиваемое четырехбитное целое число;
  • text: строка символов переменной длины;
  • time: время суток;
  • timestamp: дата и время;
  • tsquery: запрос текстового поиска;
  • tsvector: документ текстового поиска;
  • uuid: уникальный идентификатор;
  • xml: XML-данные.

Как видите, типов данных в Postgresql больше и они более разнообразны, есть свои типы полей для определенных видов данных, которых нет MySQL. Отличие MySQL от Postgresql очевидно.

Разработка

Оба проекта имеют открытый исходный код, но развиваются по-разному. Развитие MySQL нравится далеко не всем. И в этом сравнение mysql и postgresql дает много отличий.

MySQL

База данных MySQL разрабатывается компанией Oracle и ходят слухи, что компания намерено тормозит развитие движка. Было создано очень много форков проекта, в том числе форк MariaDB от разработчика оригинальной MySQL. Но все же развитие остается медленным.

Postgresql

Как было сказано в начале статьи разработка началась в университете Беркли. Затем перешла в коммерческую компанию. Сейчас программа разрабатывается независимой группой программистов и советом нескольких компаний. Новые версии выпускаются достаточно активно и получают все новые и новые функции.

Выводы

В этой статье мы выполнили сравнение mysql и postgresql, рассмотрели основные отличия обоих систем управления базами данных и попытались понять что лучше postgresql или mysql. В общем результате лучшим по возможностях получается Postgresql, но он сложен и не везде его можно применять. MySQL проще, но не поддерживает некоторых интересных функций. А какую базу данных вы выберите для своего проекта? Почему именно ее? Напишите в комментариях!

На завершение видео с описанием возможностей и перспектив Postgresql:

Илон Маск рекомендует:  all в CSS
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL