Sql

Бд postgresql: Курс молодого бойца PostgreSQL / Хабр

Содержание

PostgreSQL : Документация: 11: 1.3. Создание базы данных : Компания Postgres Professional

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

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

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

Для создания базы данных, в этом примере названной mydb, выполните следующую команду:

$ createdb mydb

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

Если вы видите сообщение типа:

createdb: command not found

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

$ /usr/local/pgsql/bin/createdb mydb

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

Ещё один возможный ответ:

createdb: не удалось подключиться к базе postgres:
  не удалось подключиться к серверу: No such file or directory
        Он действительно работает локально и принимает
        соединения через Unix-сокет "/tmp/.s.PGSQL.5432"?

Это означает, что сервер не работает или createdb не может к нему подключиться. И в этом случае пересмотрите инструкции по установке или обратитесь к администратору.

Также вы можете получить сообщение:

createdb: не удалось подключиться к базе postgres:
  ВАЖНО:  роль "joe" не существует

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

postgres). Также возможно, что вам назначено имя пользователя PostgreSQL, не совпадающее с вашим именем в ОС; в этом случае вам нужно явно указать ваше имя пользователя PostgreSQL, используя ключ -U или установив переменную окружения PGUSER.

Если у вас есть учётная запись пользователя, но нет прав на создание базы данных, вы увидите сообщение:

createdb: создать базу данных не удалось:
  ОШИБКА:  нет прав на создание базы данных

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

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

Создать базу данных postgresql и пользователя БД

Создать базу данных postgresql можно через какой-либо веб-интерфейс, установленный на сервере (например, pgAdmin) или в консоли. Второй способ более универсален.

 

Прежде всего требуется подключиться к серверу по SSH.

Далее подключиться к базе от имени пользователя postgres (по умолчанию существует только он)

sudo -u postgres psql

could not change directory to «/root»
psql (9.2.24)
Type «help» for help.

 

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

cd /tmp

sudo -u postgres psql

Или

su -u postgres

psql

 

Оказавшись к консоли можно создать базу данных

postgres=# CREATE DATABASE app;

CREATE DATABASE

 

Затем пользователя, которому присвоить пароль.

postgres=# CREATE USER appadmin WITH PASSWORD 'jkfdk8H0Indgshy6hk';

CREATE ROLE

 

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

 

Например, полный доступ:

postgres=# GRANT ALL PRIVILEGES ON DATABASE app to appadmin;

GRANT

 

 

Также требуется скорректировать значения в одном из конфигурационных файлов

mcedit /var/lib/pgsql/data/pg_hba. conf

 

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

 

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

 

В файле потребуется заменить в двух строках, начинающихся с host тип аутентификации с ident на md5. Это последнее значение в каждой строке.

 

# TYPE DATABASE USER ADDRESS METHOD

# «local» is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 ident
host all all ::1/128 md5

 

Для вступления в силу изменений нужно перезапустить сервис

systemctl restart postgresql

 

 

Установка postgresql на Centos и Debian

Если postgresql на сервере еще нет, его нужно установить.

Centos 7

yum install epel-release

yum install postgresql-server postgresql-devel postgresql-contrib

Debian 9

apt-get update

apt-get install postgresql

 

После установки на CentOS требуется выполнить начальную настройку. Скрипт подобен mysql_secure_instalation в MySQL.

postgresql-setup initdb

Initializing database … OK

 

Для Debian / Ubuntu сервис запускается автоматически, для Centos его нужно стартовать и добавить в автозагрузку

systemctl start postgresql

systemctl enable postgresql

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

Создание базы данных PostgreSQL и подключение к ней – AWS

Сеть и безопасность

  • Public accessibility: выберите Yes. Инстансу базы данных будет присвоен IP-адрес, что позволит подключаться к базе данных непосредственно с пользовательского устройства.
  • VPC security groups: выберите Create new VPC security group. Будет создана группа безопасности, поддерживающая соединение между IP-адресом используемого устройства и созданной базой данных.

Настройки базы данных

  • Database name: введите имя базы данных, содержащее от 1 до 64 буквенно-цифровых символов. Если имя не указано, Amazon RDS не сможет автоматически создать базу данных на основе нового инстанса БД. Для этого учебного пособия используется myDatabase.
  • Port: сохраните значение по умолчанию «5432».
  • Option group: Эта настройка недоступна на уровне бесплатного пользования. С помощью групп настроек в Amazon RDS активируются и настраиваются дополнительные возможности.  Дополнительную информацию см. в разделе Работа с группами настроек.
  • IAM DB authentication: выберите Disable. С помощью этой настройки можно управлять данными для доступа к базе данных с использованием пользователей и групп AWS IAM.

Шифрование

Эта настройка недоступна на уровне бесплатного пользования. Дополнительную информацию см. в разделе Шифрование ресурсов Amazon RDS.

Резервное копирование

  • Backup Retention Period: в этом поле можно выбрать количество дней, в течение которых сохраняются резервные данные. Для этого учебного пособия выберите значение 1 day.
  • Backup Window: задайте значение по умолчанию – No Preference.

Мониторинг

  • Enhanced Monitoring: выберите Enable Enhanced Monitoring.  При активации расширенного мониторинга в режиме реального времени становятся доступны метрики для операционной системы (ОС), на которой работает инстанс БД. Дополнительную информацию см. в разделе Просмотр метрик инстанса БД.

Performance Insights

Для выполнения заданий этого учебного пособия выберите Disable Performance Insights.

Обслуживание

  • Auto Minor Version Upgrade: выберите Enable auto minor version upgrade, чтобы получать доступные автоматические обновления.
  • Maintenance Window: выберите значение No Preference.

Защита от удаления

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

PostgreSQL - ВЫБРАТЬ базу данных

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

  • База данных SQL Prompt
  • Командная строка ОС

База данных SQL Prompt

Предположим, что вы уже запустили свой клиент PostgreSQL и попали в следующую подсказку SQL —

postgres=#

Вы можете проверить список доступных баз данных, используя команду \ l , т.е. команду backslash el следующим образом:

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-# 

Теперь введите следующую команду для подключения / выбора нужной базы данных; здесь мы подключимся к базе данных testdb .

postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=# 

Командная строка ОС

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

psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=# 

Теперь вы вошли в PostgreSQL testdb и готовы выполнять свои команды внутри testdb. Для выхода из базы данных вы можете использовать команду \ q.

что это за программа? Свободная объектно-реляционная система управления базами данных (СУБД) PostgreSQL

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

Краткая история создания

PostgreSQL, первоначально называемый Postgres, был создан в UCB профессором по информатике Майклом Стоунбрейкером. Он начал работу в 1986 году в Postgres в качестве обновленного проекта своего предшественника Ingres и теперь принадлежит Computer Associates.

Основные периоды:

  • 1977-1985 гг. - период, в котором был разработан проект под названием Ingres, а также найдено доказательство концепции для реляционных БД. В 1980 году была основана компания Ingres, которая в 1994 году была приобретена Computer Associates.
  • 1986-1994 гг. - период, в котором была принята кодовая база POSTGRES. Хотя первоначальный вариант был в том, что это PostgreSQL не использовала кодовую базу в качестве своей основы. Кроме того, была разработана концепция в Ingres с акцентом на ориентацию объектов и язык запросов – Quel и коммерциализирован как Illustra.
  • 1994-1995 гг. - в этот период была добавлена поддержка SQL. Выпущен Postgres 95, который позже, в 1996 году, был переиздан как PostgreSQL 6.0. Была создана глобальная команда разработчиков PostgreSQL с бесплатной системой постреляционной БД с открытым исходным кодом. Она применяется сегодня во всех операционных системах.

25-летняя история совершенствования PostgreSQL предоставляет огромный набор возможностей для разработчиков и администраторов баз данных, поставляемых на надежном сервере программного обеспечения, используемом во всем мире. PostgreSQL оценивается в 5 лучших базах данных DBEngine.

Особенности новейшей разработки

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

  1. Невосприимчивость к чрезмерному развертыванию.
  2. Перераспределение - это то, что некоторые патентованные поставщики баз данных считают своей проблемой № 1 из-за соответствия лицензии. С PostgreSQL никто не может подать в суд на пользователя за нарушение лицензии. Так как нет никаких связанных лицензионных затрат на ПО.
  3. Лучшая поддержка при создании базы данных PostgreSQL, чем у патентообладателей.
  4. Яркое сообщество профессиональных энтузиастов PostgreSQL.
  5. Значительная экономия на штатных расходах.
  6. Низкие требования к обслуживанию и настройке, чем ведущие запатентованные базы данных, но при этом сохраняют все функции, стабильность и производительность.
  7. Учебные программы продукта обычно рассматриваются как гораздо более дорогостоящие, эффективные, управляемые и практичные в реальном мире, чем у ведущих поставщиков собственных баз данных.
  8. Легендарная надежность и стабильность.
  9. Исходный код доступен бесплатно.
  10. Если пользователю необходимо каким-либо образом настроить или расширить PostgreSQL, тогда он сможет сделать это с минимальными усилиями и без каких-либо затрат.

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

Реестр российского ПО

18 марта 2016 года Экспертный совет по ПО в России одобрил 155 программ для внесения в Росреестр. Среди российских лидеров ABBYY и "Касперского" были PostgreSQL. Включили в список "ЛИНТЕР" и Postgres. Выданная лицензия PostgreSQL дает право собственникам на разработку и коммерческую передачу полученных от нее программ. Это справедливая оценка 20-летней работы команды Postgres Professional над проектом. А что в настоящее время? Postgres Professional издала обновленный релиз свободной версии СУБД Postgres Pro 9. 6.2.1. Функциональные возможности СУБД:

  • мультимастерный кластер;
  • адаптивные запросы;
  • секционирование таблиц;
  • блочная компрессия данных;
  • счетчик для транзакций 64-битных версий.

Принцип ПО СУБД PostgreSQL базируется на машинном обучении, в связи с чем оно может осуществить оптимизацию планирования запросов. Нововведения резко (в 10 раз) освобождают занятую данными память и создают скачок производительности. Для решения проблем с переполнением систем PostgreSQL Professional применен 64-битный счетчик транзакций с поддержкой 100 млн транзакций/сутки. Расширение pg_pathman позволяет секционировать таблицы и повысить масштабируемость через адаптированность алгоритмов планирования.

Установка в Linux/Unix

Для пользования PostgreSQL Ubuntu необходимо выполнить инструкции по установке на компьютере Linux. Перед тем как приступить к установке, убеждаются, что вошли в систему как пользователь root:

  1. Выбрать нужный номер версии PostgreSQL и, насколько это возможно, платформу от EnterpriseDB. Например, загружают postgresql-9.2.4-1-linux-x64.run для 64-битного процессора CentOS-6.
  2. После запуска установщика PostgreSQL Ubuntu отвечают на несколько основных вопросов, таких как местоположение установки, пароль пользователя, который будет использовать базу данных, и номер порта.
  3. Сохраняют их по умолчанию, кроме пароля, который можно предоставить по выбору.
  4. Установка PostgreSQL на компьютере Linux завершена.

Команды и полезные ссылки SQL

Используя psql, можно сгенерировать полный список команд с помощью команды \ help.

Для синтаксиса конкретной команды используют следующую: postgres-# \help.

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

Следующие ресурсы содержат дополнительную информацию о PostgreSQL. Полезные ссылки:

  1. Домашняя страница - официальный сайт PostgreSQL, в котором представлена последняя версия установки, новости и полная информация, разъясняющая что это за программа PostgreSQL.
  2. PHP PostgreSQL - предоставляет полную информацию о поддержке PHP для баз данных PostgreSQL.
  3. Драйвер PostgreSQL JDBC - это библиотека для доступа и создания файлов базы данных PostgreSQL на Java.
  4. DBD-Pg-2.19.3, который будет использоваться вместе с модулем Perl DBI.
  5. DBI-1.625 - модуль для предоставления общего интерфейса для любой базы данных, включая PostgreSQL.
  6. PostgreSQL Python - является самым популярным адаптером базы данных PostgreSQL для языка программирования Python. Он предоставляет интерфейс SQL, соответствующий спецификации DB-API 2.0.

Общие параметры настройки Permalink

Конфигурирование базы данных PostgreSQL может быть сложным процессом. Ниже приведены некоторые основные параметры конфигурации, рекомендуемые при использовании PostgreSQL в Linode. Все эти параметры более подробно описаны в руководстве по настройке PostgreSQL.

Директива

Задача

listen_addresses = 'localhost'

По умолчанию Postgres прослушивает только localhost. Однако, редактируя этот раздел и заменяя localhostIP, можно заставить Postgres прослушивать другой IP-адрес. Используйте '*' для прослушивания всех IP-адресов.

max_connections = 50

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

shared_buffers = 128 МБ

Как указано в официальной документации, эта директива изначально устанавливается на низкое значение. На платформе Linode это может быть 1/4 ОЗУ.

wal_level

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

synchronous_commit = off

При использовании Linode можно включить настоящую Директиву off.

archive_mode = on

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

Тип данных при создании таблиц

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

Это дает несколько преимуществ:

  1. Согласованность. Операции против столбцов одного и того же типа данных дают согласованные результаты и, как правило, самые быстрые.
  2. Валидация. Правильное использование типов данных подразумевает проверку их формата и отказ от данных за пределами объема данных.
  3. Компактность. Поскольку столбец может содержать один тип значения, он хранится компактным образом.
  4. Производительность. Правильное использование типов данных обеспечивает наиболее эффективное хранение данных. Сохраненные значения могут быть быстро обработаны, что повышает производительность.

Рассмотрим денежные типы.

Размещение платформы в Windows

Для размещения PostgreSQL Windows на ПК следуют приведенным далее инструкциям по установке.

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

  1. Выбрать номер версии PostgreSQL. Например, postgresql-9.2.4-1-windows.exe для ПК с ОС Windows, работающего в 32-разрядном режиме, в качестве администратора для установки PostgreSQL.
  2. Выбрать место, где нужно его установить. По умолчанию он устанавливается в папке Program Files.
  3. Следующим шагом процесса установки будет выбор каталога, в котором будут храниться данные. По умолчанию он находится в каталоге data.
  4. Затем установка PostgreSQL Windows запрашивает пароль, поэтому можно использовать любой пароль.
  5. Следующий шаг - указа

Бэкап и восстановление базы 1С в бд postgresql

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

Если у вас есть желание научиться профессионально строить и поддерживать процессы непрерывной разработки и доставки (continuous integration / continuous delivery), рекомендую пройти интенсив CI/CD или Непрерывная поставка с Docker и Kubernetes в OTUS. Подробности смотрите по .

Данная статья является частью единого цикла статьей про сервер Debian.

Введение

Ранее я рассказывал о том, как установить и настроить postgresql для работы с 1С, а затем как провести анализ производительности базы 1С и по возможности увеличить быстродействие. После успешного выполнения первых двух задач, мы можем приступать к эксплуатации системы. Когда рабочая база 1С уже на сервере, обязательно нужно настроить ее регулярный бэкап. Желательно так же периодически проводить очистку и переиндексацию sql базы. Это увеличит ее быстродействие. Выполнять эти операции лучше всего автоматически, в нерабочее время. Именно этим мы и займемся в этой статье.

Бэкап и восстановление базы 1C в бд postgresql

Способов бэкапа базы данных postgresql много. Я буду использовать самый простой - выгрузка базы данных в обычный текстовый sql скрипт с помощью pg_dump. Подробно о работе этой утилиты и ее настройках можно прочитать вот тут - https://postgrespro.ru/docs/postgrespro/9.6/app-pgdump. В сети много примеров и готовых скриптов для решения вопроса архивации баз postgresql. Например, есть вот такой скрипт. Когда я его увидел, мне просто стало лень с ним разбираться. Написать простенький свой мне гораздо проще.

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

Редактируем в файле /etc/postgresql/9.6/main/pg_hba.conf строку, приведя ее к такому виду:

local   all             all                                     trust

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

# systemctl restart postgresql

Бэкап базы данных выполняется простой командой:

# pg_dump -U postgres base1c | pigz > /backup/base1c.sql.gz
postgres имя пользователя базы данных
base1c название бд с 1С
pigz архиватор
base1c.sql.gz файл, куда будет сохранена резервная копия

Обращаю внимание на архиватор pigz. Я его использую, потому что он умеет жать данные, нагружая все ядра процессора, в отличие от gzip. Прирост производительности 2-3 раза. Рекомендую. На debian он ставится из стандартного репозитория:

# apt-get -y install pigz

В centos из epel:

# yum -y install epel-release
# yum -y install pigz

Попробуйте вручную в консоли выполнить команду и посмотреть на результат. Вы должны получить заархивированный файл с текстовыми sql командами в открытом виде. Теперь попробуем восстановить базу данных 1С из архива. Тут будут нюансы. Первым делом разархивируем файл:

# unpigz /backup/base1c.sql.gz

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

# unpigz -c /backup/base1c.sql.gz > base1c.sql

Создадим на сервере новую базу данных, в которую будем восстанавливать резервную копию. Перед этим посмотрим список баз данных на сервере:

# psql -U postgres -l

Создаем новую базу данных:

# createdb --username postgres -T template0 base1c-restored

Смотрим, что получилось:

Базу данных создали. Теперь загружаем в нее наш бэкап 1с:

# psql -U postgres base1c-restored < /backup/base1c.sql

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

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

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

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

# cat /root/bin/backup-sql.sh
#!/bin/sh

# Устанавливаем дату
DATA=`date +"%Y-%m-%d_%H-%M"`

# Записываем информацию в лог с секундами
echo "`date +"%Y-%m-%d_%H-%M-%S"` Start backup base1c" >> /var/log/postgresql/service.log

# Бэкапим базу данных base1c и сразу сжимаем
/usr/bin/pg_dump -U postgres base1c | pigz > /backup/$DATA-base1c.sql.gz

echo "`date +"%Y-%m-%d_%H-%M-%S"` End backup base1c" >> /var/log/postgresql/service.log

# Удаляем в папке с бэкапами архивы старше 3-х дней
/usr/bin/find /backup -type f -mtime +3 -exec rm -rf {} \;

Я указал в названии файла с бэкапом 1с базы использовать текущую дату с точностью до минуты. В лог я пишу информацию с точностью до секунды, чтобы было точно видно, сколько длился бэкап. Просто для справки информация, можно обойтись и без лога совсем. В конце удаляю из папки все архивы старше 3-х дней. Я обычно сервером с бэкапами забираю информацию с целевых хостов. То есть я буду подключаться к sql серверу и забирать с него архивы и уже на сервере бэкапов буду их хранить и ротировать в зависимости от желаемой глубины архива. А здесь я удаляю почти сразу архивы, не храню их, чтобы не занимать место. Если вы будете хранить их долгосрочно на этом же сервере, то просто измените цифру 3 на нужное вам число дней, за которые вы хотите иметь архивную копию своей базы 1С.

Использование программы PostgreSQL Backup

Для бэкапа базы данных постгрес есть удобная и бесплатная для двух баз программа под windows - PostgreSQL Backup. Я ее установил, проверил, сделал бэкап, потом восстановил из бэкапа. Все отлично работает. Из полезных функций:

  • встроенный планировщик
  • автоматическое сжатие бэкапа
  • отправка оповещений на email

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

Обновление статистики и реиндексация в postgresql

С бэкапами разобрались, теперь настроим регламентные операции на уровне субд, чтобы поддерживать быстродействие базы данных. Тут особых комментариев не будет, в интернете очень много информации на тему регламентных заданий для баз 1С. Я просто приведу пример того, как это выглядит в postgresql.

Выполняем очистку и анализ базы данных 1С:

# vacuumdb --full --analyze --username postgres --dbname base1c

Реиндексация таблиц базы данных:

# reindexdb --username postgres --dbname base1c

Завернем все это в скрипт с логированием времени выполнения команд:

# cat /root/bin/service-sql.sh
#!/bin/sh

# Записываем информацию в лог
echo "`date +"%Y-%m-%d_%H-%M-%S"` Start vacuum base1c" >> /var/log/postgresql/service.log
# Выполняем очистку и анализ базы данных
/usr/bin/vacuumdb --full --analyze --username postgres --dbname base1c
echo "`date +"%Y-%m-%d_%H-%M-%S"` End vacuum base1c" >> /var/log/postgresql/service.log

sleep 2

echo "`date +"%Y-%m-%d_%H-%M-%S"` Start reindex base1c" >> /var/log/postgresql/service.log
# Переиндексирвоать базу
/usr/bin/reindexdb --username postgres --dbname base1c
echo "`date +"%Y-%m-%d_%H-%M-%S"` End reindex base1c" >> /var/log/postgresql/service.log

Сохраняем скрипт и добавляем в планировщик. Хотя я для удобства сделал еще один скрипт, который объединяет бэкап и обслуживание и уже его добавил в cron:

# cat all-sql.sh
#!/bin/sh

/root/bin/backup-sql.sh
sleep 2
/root/bin/service-sql.sh

Добавялем в /etc/crontab:

# Бэкап и обслуживание БД
1 3 * * * root /root/bin/all-sql.sh

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

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

Заключение

Вот и все, что я хотел рассказать по поводу бэкапа и обслуживания баз postgresql в связке с 1С. Если у кого есть еще полезная информация, прошу поделиться в комментариях. Возможно с бэкапом есть какие-то нюансы, особенно на больших базах. Но мне негде тестировать, больших рабочих баз более 10 гб у меня нет под рукой, а с теми что были, все отлично работает.

Напоминаю, что данная статья является частью единого цикла статьей про сервер Debian.

CI/CD или Непрерывная поставка с Docker и Kubernetes

Если вы хотите стать специалистом, отвечающим за настройку процессов непрерывной разработки и доставки (continuous integration / continuous delivery), рекомендую пройти интенсив CI/CD или Непрерывная поставка с Docker и Kubernetes в OTUS. Курс не для новичков, для поступления нужны хорошие знания по Linux. Обучение длится 4 дня. На интенсве вы научитесь:
  • Сделать выкатку кода простым и приятным процессом в духе «нажми одну кнопку», который можно выполнять десятки раз в день.
  • Развернуть масштабируемую инфраструктуру для запуска микросервисов.
  • Создать прозрачный и понятный continuous delivery pipeline и выкатывать изменения часто и небольшими частями.
  • Делать десятки тестовых стендов в день для разных команд и разных версий продукта, при этом конфигурация стенда будет практически идентичной боевой среде.
  • Увеличить скорость разработки, тестирования и выкатки за счет применения современной системы автоматизации развертывания контейнеров Docker.
  • Получить быструю обратную связь от систем мониторинга и логирования на всех основных этапах жизненного цикла ПО.
Смотрите подробнее программу по .
Помогла статья? Подписывайся на telegram канал автора
Анонсы всех статей, плюс много другой полезной и интересной информации, которая не попадает на сайт.

Подключитесь к базе данных PostgreSQL с помощью команды psql

Подключитесь к базе данных PostgreSQL с помощью команды psql


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

Прежде чем использовать команду psql, вам необходимо получить доступ к удаленной оболочке через SSH.

Подключение к PostgreSQL с помощью psql

Сначала вам нужно узнать детали вашего подключения
Hos t: postgresql.guebs.net
Имя пользователя : имя_пользователя
Пароль : *********
База данных : имя_базы_данных

Они будут даны вам, когда вы создадите новую базу данных PostgreSQL.

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

  psql -h postgresql.guebs.net -U имя_пользователя-имя_базы_данных  

Нажмите Enter. Оболочка запросит пароль:

  Пароль для пользователя имя_пользователя:  

Нажмите Enter.Если пароль правильный, мы увидим что-то вроде этого:

  psql (9.4.8)
SSL-соединение (протокол: TLSv1.2, шифр: ECDHE-RSA-AES256-GCM-SHA384, бит: 256, сжатие: выключено)
Введите "help" для получения справки.

имя_базы_данных = & gt;  

В дальнейшем вы можете выполнять SQL-запросы или команды postgresql для управления своей базой данных.

Команды внутри psql

Источник: Шпаргалка Kartones Postgres

Сводка команд psql
Задача Команда Psql
Закрытое соединение \ q
Подключиться к базе данных \ c __base_datos__
Список баз данных \ л
Показать определение таблицы, включая триггеры \ d __table__
Схемы списков \ dn
Список функций \ df
Просмотры списка \ дв
Показать код функции SQL \ df + __function
Красивоформатный \ х

Настройте сервер PostgreSQL и pgAdmin с помощью Docker

В этой статье я покажу вам, как использовать Docker Compose для создания контейнера PostgreSQL и доступа к нему с помощью pgAdmin 4, веб-интерфейса администрирования PostgreSQL.Я также покажу вам, как получить доступ к серверу базы данных PostgreSQL, работающему в контейнере Docker, из DataGrip IDE. Итак, приступим.

Требования:

Для того, чтобы читать эту статью, на вашем компьютере должен быть установлен Docker. В LinuxHint есть множество статей, которым вы можете следовать, чтобы установить Docker в желаемый дистрибутив Linux, если он еще не установлен. Так что не забудьте посетить LinuxHint.com, если у вас возникли проблемы с установкой Docker.

Установка Docker Compose:

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

$ sudo curl -L "https: // github.com / docker / compose / Release / download / 1.24.1 /
docker-compose - $ (uname -s) - $ (uname -m) "-o / usr / local / bin / docker-compose

.

ПРИМЕЧАНИЕ. curl может не быть установлен в вашем дистрибутиве Linux. В этом случае вы можете установить curl с помощью следующей команды:

Ubuntu / Debian / Linux Монетный двор:

$ sudo apt install curl -y

CentOS / RHEL / Fedora:

$ sudo dnf установить curl -y

После загрузки бинарного файла docker-compose выполните следующую команду:

$ sudo chmod + x / usr / local / bin / докер-составить

Теперь проверьте, работает ли команда docker-compose следующим образом:

Он должен распечатать информацию о версии, как показано на снимке экрана ниже.

Настройка Docker Compose для проекта:

Теперь создайте каталог проекта (скажем, ~ / docker / pgdev ) следующим образом:

$ mkdir -p ~ / докер / pgdev

Теперь перейдите в каталог проекта ~ / docker / pgdev следующим образом:

Теперь создайте файл docker-compose.yaml в каталоге проекта ~ / docker / pgdev и введите следующие строки в docker-compose.yaml файл.

версия: "3.7"
службы:
db:
образ: postgres: 12.2
перезапуск: всегда
среда:
POSTGRES_DB: postgres
POSTGRES_USER: admin
POSTGRES_PASSWORD: секрет
PGDATA: / var / lib / postgresq Volume :
- db-data: / var / lib / postgresql / data
порты:
- "5432: 5432"

pgadmin:
image: dpage / pgadmin4: 4.18
перезапуск: всегда
среда:
PGADMIN_DEFAULT_EMAIL: admin @ lin @ lin @ .com
PGADMIN_DEFAULT_PASSWORD: секрет
PGADMIN_LISTEN_PORT: 80
порты:
- «8080: 80»
тома:
- pgadmin-data: / var / lib / pgadmin
ссылки:
- «db: pgsq6-volume:
»
db-data:
pgadmin-data:

Файл docker-compose.yaml должен выглядеть следующим образом.

Здесь я создал 2 сервиса db и pgadmin .

Служба

db запустит образ postgres: 12.2 (из DockerHub) в контейнере Docker.

Служба pgadmin запустит образ dpage / pgadmin4: 4.18 (из DockerHub) в другом контейнере Docker.

В службе db переменные среды POSTGRES_DB , POSTGRES_USER , POSTGRES_PASSWORD используются для установки имени базы данных по умолчанию, имени пользователя admin и пароля пользователя admin для сервера PostgreSQL соответственно.Переменная среды PGDATA используется для настройки сервера PostgreSQL для хранения данных в каталоге / var / lib / postgresql / data контейнера.

В службе pgadmin переменные среды PGADMIN_DEFAULT_EMAIL , PGADMIN_DEFAULT_PASSWORD используются для установки адреса электронной почты и пароля для входа в веб-интерфейс pgAdmin соответственно. PGADMIN_LISTEN_PORT используется для установки порта 80 pgAdmin в контейнере.

В службе db все содержимое каталога / var / lib / postgresql / data будет постоянно сохраняться в томе db-data .

В службе pgadmin все содержимое каталога / var / lib / pgadmin будет постоянно сохраняться в томе pgadmin-data .

В службе db порт контейнера 5432 (справа) сопоставлен с портом хоста Docker 5432 (слева).

В службе pgadmin порт контейнера 80 (справа) сопоставлен с портом хоста Docker 8080 (слева).

В службе pgadmin создается псевдоним хоста pgsql-server для контейнера db . Итак, вы можете получить доступ к серверу PostgreSQL, используя pgsql-server в качестве имени хоста (IP-адрес не требуется).

Запуск сервера PostgreSQL и pgAdmin:

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

Службы должны запускаться в фоновом режиме.

Как видите, порт 8080 и 5432 открываются службой docker-proxy .

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

Как видите, для службы db порт хоста Docker 5432 сопоставлен с TCP-портом контейнера 5432 .

Для службы pgadmin порт хоста Docker 8080 сопоставлен с TCP-портом контейнера 80 .

Доступ к pgAdmin 4 или серверу PostgreSQL с других компьютеров:

Если вы хотите получить доступ к серверу базы данных pgAdmin 4 или PostgreSQL с другого компьютера в вашей сети, вы должны знать IP-адрес вашего хоста Docker.

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

В моем случае IP-адрес моего хоста Docker 192.168.20.160 . Для вас все будет иначе. Так что не забудьте заменить его теперь своим.

Доступ к pgAdmin из веб-браузера:

Теперь вы можете легко получить доступ к pgAdmin 4 из вашего веб-браузера.

Посетите http: // localhost: 8080 со своего хоста Docker или http://192.168.20.160:8080 с любого компьютера в вашей сети. Вы должны увидеть страницу входа в pgAdmin. Войдите, используя свой адрес электронной почты и пароль.

После входа в систему вы должны увидеть панель управления pgAdmin.

Теперь, чтобы добавить сервер PostgreSQL, работающий как контейнер Docker, щелкните правой кнопкой мыши Servers , а затем перейдите к Create > Server…

На вкладке Общие введите свой сервер Имя .

Теперь перейдите на вкладку Connection и введите pgsql-server как Имя хоста / адрес , 5432 как Порт , postgres как База данных обслуживания , admin как Имя пользователя , секрет как Пароль и отметьте Сохранить пароль? Флажок . Затем нажмите Сохранить .

pgAdmin 4 должен быть подключен к вашей базе данных PostgreSQL.Теперь вы можете работать со своей базой данных PostgreSQL сколько угодно.

Доступ к PostgreSQL из DataGrip:

Вы также можете получить доступ к серверу базы данных PostgreSQL из DataGrip IDE или любых других SQL IDE.

В случае DataGrip щелкните + в разделе Базы данных и перейдите к Data Source > PostgreSQL .

Драйвер PostgreSQL не может быть установлен. В этом случае нажмите Загрузить .

Драйвер должен быть установлен. Теперь введите IP-адрес хоста Docker 192.168.20.160 (в моем случае) как Host , 5432 как порт , admin как User , secret как пароль , postgres в качестве базы данных и щелкните Test Connection .

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

Теперь нажмите ОК .

Теперь вы можете управлять своими базами данных PostgreSQL из DataGrip.

Остановка PostgreSQL и pgAdmin:

Теперь, чтобы остановить службы db и pgadmin , выполните следующую команду:

Необходимо остановить службы db и pgadmin .

Очистка данных PostgreSQL и pgAdmin:

Если вы хотите удалить все данные и настройки базы данных PostgreSQL, а также все настройки pgAdmin, вы должны удалить тома db-data и pgadmin-data .

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

Как видите, есть 2 тома для проекта pgdev , pgdev_db-data и pgdev_pgadmin-data .

Вы можете удалить эти тома с помощью следующей команды:

$ том докера rm pgdev_db-data pgdev_pgadmin-data

Ссылки:

[1] https://hub.docker.com/_/postgres?tab=description
[2] https: // hub.docker.com/r/dpage/pgadmin4/
[3] https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

Вход в Postgres: как войти в базу данных Postgresql

Часто задаваемые вопросы по входу в Postgres

: Как мне войти в базу данных Postgres из командной строки?

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

Команды входа в Postgres

Если вы вошли в систему на том же компьютере, что и Postgres, вы можете использовать следующую команду входа psql, указав базу данных ( mydb ) и имя пользователя ( myuser ):

psql -d mydb -U myuser
 

Если вам нужно войти в базу данных Postgres на сервере с именем myhost , вы можете использовать эту команду входа в Postgres:

psql -h myhost -d mydb -U myuser
 

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

psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W 

Справка по команде Postgres psql

Теперь, если по какой-то причине ни один из этих параметров у вас не работает, вы можете получить дополнительную помощь по клиенту Postgresql, набрав эту команду psql :

psql --help
 

, что приводит к этому выводу из Postgresql 8.0.3 клиент:

Это psql 8.0.3, интерактивный терминал PostgreSQL.

Применение:
  psql [ОПЦИИ] ... [ИМЯ БД [ИМЯ ПОЛЬЗОВАТЕЛЯ]]

Общие настройки:
  -d ИМЯ БД указать имя базы данных для подключения (по умолчанию: "root")
  -c КОМАНДА запустить только одну команду (SQL или внутреннюю) и выйти
  -f FILENAME выполнить команды из файла, затем выйти
  -l перечислить доступные базы данных, затем выйти
  -v ИМЯ = ЗНАЧЕНИЕ установить ИМЯ переменной psql в ЗНАЧЕНИЕ
  -X не читать файл запуска (~ /.psqlrc)
  --help показать эту справку, затем выйти
  --version вывести информацию о версии, затем выйти

Варианты ввода и вывода:
  -a эхо всего ввода из скрипта
  -e эхо-команды, отправленные на сервер
  -E отображать запросы, которые генерируют внутренние команды
  -q работать тихо (без сообщений, только вывод запроса)
  -o FILENAME отправить результаты запроса в файл (или | канал)
  -n отключить расширенное редактирование командной строки (строка чтения)
  -s пошаговый режим (подтверждать каждый запрос)
  -S однострочный режим (конец строки завершает команду SQL)

Параметры формата вывода:
  -Режим вывода невыровненной таблицы (-P формат = невыровненный)
  -H Режим вывода таблицы HTML (-P формат = html)
  -t печатать только строки (-P tuples_only)
  -T ТЕКСТ установить атрибуты тега HTML-таблицы (ширина, граница) (-P tableattr =)
  -x включить вывод развернутой таблицы (-P развернутый)
  -P VAR [= ARG] установить для параметра печати VAR значение ARG (см. Команду \ pset)
  -F STRING установить разделитель полей (по умолчанию: "|") (-P fieldsep =)
  -R STRING установить разделитель записей (по умолчанию: новая строка) (-P recordsep =)

Варианты подключения:
  -h HOSTNAME, узел сервера базы данных или каталог сокета (по умолчанию: "локальный сокет")
  -p ПОРТ порт сервера базы данных (по умолчанию: "5432")
  -U ИМЯ имя пользователя базы данных (по умолчанию: "root")
  -W запрос пароля (должно происходить автоматически)

Для получения дополнительной информации введите "\?" (для внутренних команд) или "\ help"
(для команд SQL) из psql или обратитесь к разделу psql в
документация PostgreSQL.
.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *