Техника безопасности при работе с PostgreSQL. Postgresql для чайников

Иллюстрированный самоучитель по PostgreSQL › Введение [страница - 2] | Самоучители по программированию

Введение

Для кого написана эта книга?

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

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

Книга ориентирована на широкий круг читателей, интересующихся объектно-реляционной системой управления базами данных (ОРСУБД) PostgreSQL. Предполагается, что читатель знаком с системами Linux и Unix, хотя и не является экспертом в области баз данных. Хотя все примеры тестировались в системе Red Hat Linux, практически весь материал относится к большинству систем семейства Unix.

Структура книги

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

Часть I, "Общие сведения и установка", знакомит читателя с PostgreSQL. В ней рассказано, что такое PostgreSQL, где найти этот пакет и как установить его в системе. В ней также рассматриваются различные ключи компиляции, позволяющие настроить PostgreSQL для конкретной ситуации.

В части II, "Использование PostgreSQL", рассматривается широкий круг вопросов, от реляционных СУБД и языка SQL до нетривиальных возможностей расширения функций и операторов PostgreSQL. Глава 3, "Краткий курс SQL", начинается с описания теоретических принципов построения реляционных баз данных и таблиц, а также представляет некоторые основные понятия – команды, ключевые слова, идентификаторы и типы данных. В главе 4, "SQL в PostgreSQL", знакомство с SQL продолжается. В частности, в ней описаны основные операции с базами данных – создание и удаление таблиц, вставка записей, копирование и выборка данных, использование представлений. В главе 5, "Операторы и функции", рассматриваются стандартные операторы и функции PostgreSQL, а в главе 6, "Клиенты PostgreSQL", приводится дополнительная информация о клиентах psql и PgAccess. Вторая часть книги завершается главой 7, "Нетривиальные возможности", в которой описаны особенности PostgreSQL, рассчитанные на опытных пользователей (индексы, наследование, массивы, ограничения, триггеры, последовательности и курсоры). Кроме того, в этой главе рассматриваются возможности расширения PostgreSQL за счет определения пользовательских операторов и функций.

Часть III, "Администрирование PostgreSQL", посвящена вопросам, представляющим интерес для каждого администратора баз данных (или для того, кто хочет им стать). В главе 8, "Аутентификация и шифрование", представлены средства аутентификации PostgreSQL и поддерживаемые типы шифрования данных. Глава 9, "Управление базами данных", описывает фундаментальные принципы управления базами данных PostgreSQL, включая инициализацию файловой системы и запуск/остановку сервера. В этой главе также приведена информация о создании и удалении баз данных, архивации и восстановлении архивов. В главе 10, "Управление пользователями и группами", рассказано о создании и удалении учетных записей пользователей и групп, а также об управлении привилегиями доступа.

Часть IV, "Программирование в PostgreSQL", знакомит читателя с программированием для PostgreSQL и процедурным языком PL/pgSQL, JDBC (Java Database Connectivity) и LXP. В главе 11, "PL/pgSQL", приводится информация о языке PL/pgSQL, включении его поддержки в базах данных и различных возможностях программирования. Глава 12, "JDBC", посвящена созданию JDBC-интерфейса с PostgreSQL и основам его практического использования. Эта часть книги завершается главой 13, "LXP", в которой рассматриваются проблемы установки, настройки и использования сервера приложений LXP совместно с сервером HTTP Apache.

Завершает книгу часть V, "Команды", которая содержит подробный справочник с описанием всех стандартных и расширенных команд SQL, поддерживаемых в PostgreSQL. Кроме того, в эту часть включено несколько технических приложений.

Платформа и версия PostgreSQL

На момент написания книги последняя версия PostgreSQL имела номер 7.1.3. Эта версия использовалась во всех примерах и для построения образца базы данных booktown. Все примеры должны быть совместимы со всеми версиями PostgreSQL 7.1, по этой причине в тексте книги часто упоминается версия 7.1.x.

Принятые обозначения

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

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

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

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

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

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

samoychiteli.ru

Развертывание кластера Postgres-xl для чайников / Хабр

Здравствуйте. Хочу поделиться с хабровчанами своим опытом развертывания кластера Postgres-xl в виде мини-инструкции для «чайников». Статей и мануалов на тему развертывания кластера postgres-xl не то чтобы много, но достаточно. И в них всех есть пару существенных недостатков на взгляд такого человека как я, который никогда прежде не занимался кластеризацией и тем более никогда прежде не работал в линукс-подобных осях. Все статьи подобного рода написаны для людей уже более-менее знакомых с линуксом и развертыванием postgresql/postgres-xl на таком окружении.

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

Так как много статей «для опытных» уже написано, и на хабре тоже, я опущу описание самого Postgres-xl, его компонентов и их типов (ролей).

Часть 1. Подготовка окружения

Для тестового кластера была выбрана конфигурация из 4 узлов: GTM, GTM-Standby и 2 нода (GTM-proxy, Coordinator, Datanode):

GTM1 192.168.1.100 GTM-Active: 6666
GTM2 192.168.1.101 GTM-Stanby: 6666
NODE1 192.168.1.102 GTM-Proxy1: 6666 Coordinator1: 5432 Datanode1: 15432
NODE2 192.168.1.103 GTM-Proxy2: 6666 Coordinator2: 5432 Datanode2: 15432

Все узлы являются виртуализированными машинами с 1024 Мб оперативной памяти и процессором с частотой 2.1Ghz. В выборе дистрибутива ОС я остановился на последней версии CentOS 7.0, его установку я тоже опущу. Устанавливал Minimal версию.

Часть 2. Установка зависимостей

Итак, у нас есть 4 чистых машины с установленным CentOS. Прежде чем приступить к скачке исходников из sourceforge установим для начала пакеты необходимые для компиляции самих исходников.# yum install -y wget vim gcc make kernel-devel perl-ExtUtils-MakeMaker perl-ExtUtils-Embed readline-devel zlib-devel openssl-devel pam-devel libxml2-devel openldap-devel tcl-devel python-devel flex bison docbook-style-dsssl libxslt Т.к. мы имеем чистую установку CentOS, то я добавил в этот шаг установку wget — менеджера загрузок и vim — текстового редактора. Также после установки пакетов не лишним будет обновить остальные пакеты командой:# yum update -y Дождавшись окончания обновления, приступаем к следующей части процесса.

Часть 3. Загрузка исходного кода, его компиляция и установка

Для загрузки исходников выполняем команду:# wget http://sourceforge.net/projects/postgres-xl/files/latest/download # mv download pgxl-9.2.src.tar.gz Или так:# wget http://sourceforge.net/projects/postgres-xl/files/latest/download -O pgxl-9.2.src.tar.gz Копируем скачанный архив в нужную папку и распаковываем:# cp pgxl-9.2.src.tar.gz /usr/local/src/ # cd /usr/local/src/ # tar -xzvf pgxl-9.2.src.tar.gz Архив распаковывается в папку postgres-xl, проверяем командой:# ls Для компиляции исходников и последующих установки и запуска нам нужна учетная запись не root пользователя, например:# useradd postgres # passwd postgres Далее вводим и повторяем пароль, затем предоставляем права этому пользователю на всю папку с исходниками:# chown -R postgres.postgres postgres-xl # cd postgres-xl Теперь нужно с помощью ./configure сконфигурировать исходники перед началом их компиляции, я использовал эту команду со следующими опциями:# ./configure --with-tcl --with-perl --with-python --with-pam --with-ldap --with-openssl --with-libxml Подробнее об этих опциях можно почитать на странице официальной документации, тут.

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

# ./configure Для того чтобы скомпилированные исходники были переносимыми (чтобы не выполнять все предыдущие шаги на каждом из узлов кластера), нужно добавить ещё пару параметров --prefix и --disable-rpath. В итоге команда для установки с параметрами по умолчанию будет выглядеть так:# ./configure --prefix=/usr/local/pgsql --disable-rpath Параметр --prefix — это путь установки, он равен '/usr/local/pgsql' по умолчанию Параметр --disable-rpath — этот параметр делает скомпилированные исходники переносимыми.

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

# su postgres $ gmake world либо# su postgres -c 'gmake world' Если компиляция прошла успешно, то последняя строчка в логе должна выглядеть так: Postgres-XL, contrib and HTML documentation successfully made. Ready to install.

Всё! Всё скомпилировано, можно копировать папку /usr/local/src/postgres-xl на остальные узлы кластера и устанавливать.

Установка происходит по команде:

# gmake install-world Повторяем данную команду на всех узлах кластера и приступаем к конфигурированию.

Часть 4. Конфигурирование

Для начала нужно произвести некоторые пост-инсталляционные настройки. Объявление environment переменных:# echo 'export PGUSER=postgres' >> /etc/profile # echo 'export PGHOME=/usr/local/pgsql' >> /etc/profile # echo 'export PATH=$PATH:$PGHOME/bin' >> /etc/profile # echo 'export LD_LIBRARY_PATH=$PGHOME/lib' >> /etc/profile После чего надо перелогиниться. Логаут делаем командой:# exit Теперь приступаем к настройке узлов кластера. Для начала создаём папку с данными и инициализируем её в соответствии с ролью сервера.

GTM1/GTM2:

# mkdir $PGHOME/gtm_data # chown -R postgres.postgres $PGHOME/gtm_data # su - postgres -c "initgtm -Z gtm -D $PGHOME/gtm_data" NODE1:# mkdir -p $PGHOME/data/data_gtm_proxy1 # mkdir -p $PGHOME/data/data_coord1 # mkdir -p $PGHOME/data/data_datanode1 # chown -R postgres.postgres $PGHOME/data/ # su - postgres -c "initdb -D $PGHOME/data/data_coord1/ --nodename coord1" # su - postgres -c "initdb -D $PGHOME/data/data_datanode1/ --nodename datanode1" # su - postgres -c "initgtm -D $PGHOME/data/data_gtm_proxy1/ -Z gtm_proxy" NODE2:# mkdir -p $PGHOME/data/data_gtm_proxy2 # mkdir -p $PGHOME/data/data_coord2 # mkdir -p $PGHOME/data/data_datanode2 # chown -R postgres.postgres $PGHOME/data/ # su - postgres -c "initdb -D $PGHOME/data/data_coord2/ --nodename coord2" # su - postgres -c "initdb -D $PGHOME/data/data_datanode2/ --nodename datanode2" # su - postgres -c "initgtm -D $PGHOME/data/data_gtm_proxy2/ -Z gtm_proxy" Далее редактируем файлы конфигурации на узлах кластера.

GTM1:

gtm.conf# vi $PGHOME/gtm_data/gtm.conf nodename = 'gtm_master' listen_addresses = '*' port = 6666 startup = ACT log_file = 'gtm.log' log_min_messages = WARNING GTM2:gtm.conf# vi $PGHOME/gtm_data/gtm.conf nodename = 'gtm_slave' listen_addresses = '*' port = 6666 startup = STANDBY active_host = 'GTM1' #здесь можно указать IP основного GTM хоста, в моём случае '192.168.1.100' active_port = 6666 log_file = 'gtm.log' log_min_messages = WARNING NODE1:

GTM_PROXY:

gtm_proxy.conf# vi $PGHOME/data/data_gtm_proxy1/gtm_proxy.conf nodename = 'gtm_proxy1' listen_addresses = '*' port = 6666 gtm_host = 'GTM1' gtm_port = 6666 log_file = 'gtm_proxy1.log' log_min_messages = WARNING

COORDINATOR1

postgresql.conf# vi $PGHOME/data/data_coord1/postgresql.conf listen_addresses = '*' port = 5432 pooler_port = 6667 gtm_host = 'localhost' # здесь должен быть адрес/имя хоста gtm_proxy, в моём случае - это localhost gtm_port = 6666 pgxc_node_name = 'coord1' pg_hba.conf# vi $PGHOME/data/data_coord1/pg_hba.conf host all all 192.168.1.0/24 trust

DATANODE1

postgresql.conf# vi $PGHOME/data/data_datanode1/postgresql.conf listen_addresses = '*' port = 15432 pooler_port = 6668 gtm_host = 'localhost' gtm_port = 6666 pgxc_node_name = 'datanode1' pg_hba.conf# vi $PGHOME/data/data_datanode1/pg_hba.conf host all all 192.168.1.0/24 trust NODE2:

GTM_PROXY:

gtm_proxy.conf# vi $PGHOME/data/data_gtm_proxy2/gtm_proxy.conf nodename = 'gtm_proxy2' listen_addresses = '*' port = 6666 gtm_host = 'GTM1' gtm_port = 6666 log_file = 'gtm_proxy2.log' log_min_messages = WARNING COORDINATOR2postgresql.conf# vi $PGHOME/data/data_coord2/postgresql.conf listen_addresses = '*' port = 5432 pooler_port = 6667 gtm_host = 'localhost' gtm_port = 6666 pgxc_node_name = 'coord2' pg_hba.conf# vi $PGHOME/data/data_coord2/pg_hba.conf host all all 192.168.1.0/24 trust

DATANODE2

postgresql.conf# vi $PGHOME/data/data_datanode2/postgresql.conf listen_addresses = '*' port = 15432 pooler_port = 6668 gtm_host = 'localhost' gtm_port = 6666 pgxc_node_name = 'datanode2' pg_hba.conf# vi $PGHOME/data/data_datanode2/pg_hba.conf host all all 192.168.1.0/24 trust

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

# firewall-cmd --zone=public --add-port=5432/tcp --permanent # firewall-cmd --zone=public --add-port=15432/tcp --permanent # firewall-cmd --zone=public --add-port=6666/tcp --permanent # firewall-cmd --zone=public --add-port=6667/tcp --permanent # firewall-cmd --zone=public --add-port=6668/tcp --permanent # firewall-cmd --reload Впрочем для GTM1/GTM2 машин будет достаточно открыть только 6666 порт.

Часть 5. Запуск узлов кластера

Теперь мы добрались непосредственно до запуска узлов кластера. Чтобы запустить узлы кластера нужно выполнить следующие команды на соответствующих узлах от имени postgres пользователя:# su - postgres $ gtm_ctl start -Z gtm -D $PGHOME/{data_dir} $ gtm_ctl start -Z gtm_proxy -D $PGHOME/{data_dir} $ pg_ctl start -Z datanode -D $PGHOME/{data_dir} $ pg_ctl start -Z coordinator -D $PGHOME/{data_dir} Где '{data_dir}' имя соответствующей папки для GTM это: 'data/gtm_data', для datanode1 это: 'data/data_datanode1/' и т.д.

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

src/postgres-xl/contrib/start-scripts/linux# cat /usr/local/src/postgres-xl/contrib/start-scripts/linux #! /bin/sh # chkconfig: 2345 98 02 # description: PostgreSQL RDBMS # This is an example of a start/stop script for SysV-style init, such # as is used on Linux systems. You should edit some of the variables # and maybe the 'echo' commands. # # Place this file at /etc/init.d/postgresql (or # /etc/rc.d/init.d/postgresql) and make symlinks to # /etc/rc.d/rc0.d/K02postgresql # /etc/rc.d/rc1.d/K02postgresql # /etc/rc.d/rc2.d/K02postgresql # /etc/rc.d/rc3.d/S98postgresql # /etc/rc.d/rc4.d/S98postgresql # /etc/rc.d/rc5.d/S98postgresql # Or, if you have chkconfig, simply: # chkconfig --add postgresql # # Proper init scripts on Linux systems normally require setting lock # and pid files under /var/run as well as reacting to network # settings, so you should treat this with care. # Original author: Ryan Kirkpatrick <[email protected]> # contrib/start-scripts/linux ## EDIT FROM HERE # Installation prefix prefix=/usr/local/pgsql # Data directory PGDATA="/usr/local/pgsql/data" # Who to run the postmaster as, usually "postgres". (NOT "root") PGUSER=postgres # Where to keep a log file PGLOG="$PGDATA/serverlog" # It's often a good idea to protect the postmaster from being killed by the # OOM killer (which will tend to preferentially kill the postmaster because # of the way it accounts for shared memory). Setting the OOM_SCORE_ADJ value # to -1000 will disable OOM kill altogether. If you enable this, you probably # want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0", so that # individual backends can still be killed by the OOM killer. #OOM_SCORE_ADJ=-1000 # Older Linux kernels may not have /proc/self/oom_score_adj, but instead # /proc/self/oom_adj, which works similarly except the disable value is -17. # For such a system, enable this and compile with "-DLINUX_OOM_ADJ=0". #OOM_ADJ=-17 ## STOP EDITING HERE # The path that is to be used for the script PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin # What to use to start up the postmaster. (If you want the script to wait # until the server has started, you could use "pg_ctl start -w" here. # But without -w, pg_ctl adds no value.) DAEMON="$prefix/bin/postmaster" # What to use to shut down the postmaster PGCTL="$prefix/bin/pg_ctl" set -e # Only start if we can find the postmaster. test -x $DAEMON || { echo "$DAEMON not found" if [ "$1" = "stop" ] then exit 0 else exit 5 fi } # Parse command line parameters. case $1 in start) echo -n "Starting PostgreSQL: " test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1 echo "ok" ;; stop) echo -n "Stopping PostgreSQL: " su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast" echo "ok" ;; restart) echo -n "Restarting PostgreSQL: " su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w" test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1 echo "ok" ;; reload) echo -n "Reload PostgreSQL: " su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s" echo "ok" ;; status) su - $PGUSER -c "$PGCTL status -D '$PGDATA'" ;; *) # Print help echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2 exit 1 ;; esac exit 0 Для всех ролей копируем этот скрипт в директорию '/etc/rc.d/init.d/' с каким нибудь внятным именем. У меня вышло примерно так:# cp /usr/local/src/postgres-xl/contrib/start-scripts/linux /etc/rc.d/init.d/pgxl_gtm # cp /usr/local/src/postgres-xl/contrib/start-scripts/linux /etc/rc.d/init.d/pgxl_gtm_prx # cp /usr/local/src/postgres-xl/contrib/start-scripts/linux /etc/rc.d/init.d/pgxl_dn # cp /usr/local/src/postgres-xl/contrib/start-scripts/linux /etc/rc.d/init.d/pgxl_crd Далее начинаем адаптировать скрипты под каждый конкретный инстанс на каждом узле. После некоторых небольших модификаций, скрипт для GTM стал выглядеть следующим образом (для удобства я убрал комментарии и незначимые области):pgxl_gtm# vi /etc/rc.d/init.d/pgxl_gtm #! /bin/sh # chkconfig: 2345 98 02 # description: PostgreSQL RDBMS # Installation prefix prefix=/usr/local/pgsql # Data directory PGDATA="$prefix/gtm_data" # Who to run the postmaster as, usually "postgres". (NOT "root") PGUSER=postgres # Where to keep a log file PGLOG="$PGDATA/serverlog" # The path that is to be used for the script PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:$prefix/bin # What to use to shut down the postmaster PGCTL="$prefix/bin/gtm_ctl" # Which cluster role PGROLE="gtm" set -e # Only start if we can find the postmaster. test -x $PGCTL || { echo "$PGCTL not found" if [ "$1" = "stop" ] then exit 0 else exit 5 fi } # Parse command line parameters. case $1 in start) echo -n "Starting PostgreSQL: " test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj su - $PGUSER -c "$PGCTL start -Z $PGROLE -D '$PGDATA' &" >>$PGLOG 2>&1 echo "ok" ;; stop) echo -n "Stopping PostgreSQL: " su - $PGUSER -c "$PGCTL stop -Z $PGROLE -D '$PGDATA' -m fast" echo "ok" ;; restart) echo -n "Restarting PostgreSQL: " su - $PGUSER -c "$PGCTL stop -Z $PGROLE -D '$PGDATA' -m fast -w" test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj su - $PGUSER -c "$PGCTL start -Z $PGROLE -D '$PGDATA' &" >>$PGLOG 2>&1 echo "ok" ;; reload) echo -n "Reload PostgreSQL: " su - $PGUSER -c "$PGCTL restart -Z $PGROLE -D '$PGDATA'" echo "ok" ;; status) su - $PGUSER -c "$PGCTL status -Z $PGROLE -D '$PGDATA'" ;; *) # Print help echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2 exit 1 ;; esac exit 0 Как вы можете видеть я добавил '$PGHOME/bin' в переменную PATH, убрал DAEMON, в PGCTL прописал путь к утилите gtm_ctl в директории '$PGHOME/bin' для управления ролями GTM и GTM_PROXY, так же добавил переменную PGROLE необходимую для запуска узлов кластера.

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

PGDATA — это путь к директории с данными для данной роли узла.PGROLE — роль данного инстанса в кластере. Бывает gtm, gtm_proxy, coordinator, datanode.PGCTL — утилита запуска сервера, для gtm и gtm_proxy это 'gtm_ctl', а для coordinator и datanode это 'pg_ctl'

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

GTM_PROXY1:

pgxl_gtm_prx# vi /etc/rc.d/init.d/pgxl_gtm_prx PGDATA="$prefix/data/data_gtm_proxy1" PGCTL="$prefix/bin/gtm_ctl" PGROLE="gtm_proxy" GTM_PROXY2:pgxl_gtm_prx# vi /etc/rc.d/init.d/pgxl_gtm_prx PGDATA="$prefix/data/data_gtm_proxy2" PGCTL="$prefix/bin/gtm_ctl" PGROLE="gtm_proxy" COORDINATOR1:pgxl_crd# vi /etc/rc.d/init.d/pgxl_crd PGDATA="$prefix/data/data_coord1" PGCTL="$prefix/bin/pg_ctl" PGROLE="coordinator" COORDINATOR2:pgxl_crd# vi /etc/rc.d/init.d/pgxl_crd PGDATA="$prefix/data/data_coord2" PGCTL="$prefix/bin/pg_ctl" PGROLE="coordinator" DATANODE1:pgxl_dn# vi /etc/rc.d/init.d/pgxl_dn PGDATA="$prefix/data/data_datanode1" PGCTL="$prefix/bin/pg_ctl" PGROLE="datanode" DATANODE2:pgxl_dn# vi /etc/rc.d/init.d/pgxl_dn PGDATA="$prefix/data/data_datanode2" PGCTL="$prefix/bin/pg_ctl" PGROLE="datanode" Почти готово! Теперь надо сделать эти скрипты исполняемыми, выполнив на каждом узле соответствующую команду:# chmod a+x /etc/rc.d/init.d/pgxl_gtm # chmod a+x /etc/rc.d/init.d/pgxl_gtm_prx # chmod a+x /etc/rc.d/init.d/pgxl_crd # chmod a+x /etc/rc.d/init.d/pgxl_dn Теперь добавляем скрипты в атозагрузку:# chkconfig --add pgxl_gtm # chkconfig --add pgxl_gtm_prx # chkconfig --add pgxl_crd # chkconfig --add pgxl_dn И запускаем:# service pgxl_gtm start # service pgxl_gtm_prx start # service pgxl_crd start # service pgxl_dn start Как прошел запуск можно посмотреть в файле лога в директории данных, а можно выполнить команду:# service pgxl_gtm status # service pgxl_gtm_prx status # service pgxl_crd status # service pgxl_dn status Если всё прошло успешно приступаем к настройке узлов.

Часть 6. Настройка узлов кластера

Произведем настройку узлов кластера в соответствии с мануалом:NODE1# su - postgres $ psql -p 5432 -c "DELETE FROM pgxc_node" $ psql -p 5432 -c "CREATE NODE coord1 WITH (TYPE='coordinator',HOST='192.168.1.102',PORT=5432)" $ psql -p 5432 -c "CREATE NODE coord2 WITH (TYPE='coordinator',HOST='192.168.1.103',PORT=5432)" $ psql -p 5432 -c "CREATE NODE datanode1 WITH (TYPE='datanode',HOST='192.168.1.102',PORT=15432)" $ psql -p 5432 -c "CREATE NODE datanode2 WITH (TYPE='datanode',HOST='192.168.1.103',PORT=15432)" Проверить что получилось можно с помощью команды:$ psql -p 5432 -c "select * from pgxc_node" Если всё в порядке, рестартуем пул:$ psql -p 5432 -c "select pgxc_pool_reload()" При успешной конфигурации команда вернет 't', то есть true.

В большинстве мануалов после этого шага приступают создавать тестовые таблицы и выполнять тестовые запросы, но с гарантией в 99,9% я вам скажу — при попытке выполнить INSERT вы получите в логах вот такие записи:

STATEMENT: insert into test select 112233445566, 0123456789; ERROR: Invalid Datanode number или вот STATEMENT: SET global_session TO coord2_21495;SET datestyle TO iso;SET client_min_messages TO notice;SET client_encoding TO UNICODE;SET bytea_output TO escape; ERROR: Invalid Datanode number STATEMENT: Remote Subplan ERROR: node "coord2_21580" does not exist STATEMENT: SET global_session TO coord2_21580;SET datestyle TO iso;SET client_min_messages TO notice;SET client_encoding TO UNICODE;SET bytea_output TO escape; ERROR: Invalid Datanode number STATEMENT: Remote Subplan ERROR: Invalid Datanode number STATEMENT: Remote Subplan ERROR: Invalid Datanode number STATEMENT: Remote Subplan LOG: Will fall back to local snapshot for XID = 96184, source = 0, gxmin = 0, autovac launch = 0, autovac = 0, normProcMode = 0, postEnv = 1 ERROR: node "coord2_22428" does not exist STATEMENT: SET global_session TO coord2_22428; ERROR: Invalid Datanode number А всё потому, что в заумных мануалах «для опытных», где всё просто как два пальца об асфальт пропущен важный шаг — заполнение других узлов в самих DATANODE'ах. А делается это довольно просто, на обоих узлах данных в нашей конфигурации выполняем следующее:$ psql -p 5432 -c "EXECUTE DIRECT ON (datanode1) 'DELETE FROM pgxc_node'" $ psql -p 5432 -c "EXECUTE DIRECT ON (datanode1) 'create NODE coord1 WITH (TYPE=''coordinator'',HOST=''192.168.1.102'',PORT=5432)'" $ psql -p 5432 -c "EXECUTE DIRECT ON (datanode1) 'create NODE coord2 WITH (TYPE=''coordinator'',HOST=''192.168.1.103'',PORT=5432)'" $ psql -p 5432 -c "EXECUTE DIRECT ON (datanode1) 'create NODE datanode1 WITH (TYPE=''datanode'',HOST=''192.168.1.102'',PORT=15432)'" $ psql -p 5432 -c "EXECUTE DIRECT ON (datanode1) 'create NODE datanode2 WITH (TYPE=''datanode'',HOST=''192.168.1.103'',PORT=15432)'" $ psql -p 5432 -c "EXECUTE DIRECT ON (datanode1) 'SELECT pgxc_pool_reload()'" Соответственно строку EXECUTE DIRECT ON (datanode1) меняем на EXECUTE DIRECT ON (datanode2) для нода номер 2.

И вуаля! Теперь можно смело создавать таблицы и тестировать наш кластер. Но это уже совсем другая история…

Заключение

Вот и всё, всё настроено и всё работает, казалось бы — ничего сложного нет, на за этой статьёй скрывается целая неделя поиска и курения мануалов. Самым безобидным сейчас кажется этап скачки/компиляции и установки исходников, но на самом деле там тоже хватало проблем (конечно же дело в моей неопытности в работе на таком окружении), например код упорно не хотел компилироваться и кидал вот такую ошибку: '/usr/bin/perl' /bin/collateindex.pl -f -g -i 'bookindex' -o bookindex.sgml HTML.index Can't open perl script "/bin/collateindex.pl": No such file or directory make[4]: *** [bookindex.sgml] Error 2 make[4]: Leaving directory `/usr/local/src/postgres-xl/doc-xc/src/sgml' make[3]: *** [sql_help.h] Error 2 make[3]: Leaving directory `/usr/local/src/postgres-xl/src/bin/psql' make[2]: *** [all-psql-recurse] Error 2 make[2]: Leaving directory `/usr/local/src/postgres-xl/src/bin' make[1]: *** [all-bin-recurse] Error 2 make[1]: Leaving directory `/usr/local/src/postgres-xl/src' make: *** [all-src-recurse] Error 2 Позже на каком-то китайском форуме нашёл ответ, что нужно установить библиотеку docbook-style-dsssl и так далее, каждый новый сюрприз заводил меня в тупик из-за отсутствия опыта и полных мануалов (для чайников, таких как я) как таковых.

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

Дальше я планирую заняться настройкой Load-Balance, мигрировать базу из обычного PostgreSQL 9.4 под управлением Windows в собранный кластер postgres-xl 9.2 на CentOS 7.0, как следует протестировать самые тяжелые запросы в нашем проекте уже в кластере, сравнить с результатами Standalone PostgreSQL, заняться тюнингом настроек кластера, поиграться с PostGIS в кластере и т.д. Так что, если хабровчанам будет полезна эта статья или что-либо из того, что я перечислил — с удовольствием поделюсь этим с вами.

Спасибо за внимание.

habr.com

Техника безопасности при работе с PostgreSQL / Хабр

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

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

Версионируйте схему базы данных

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

Боль! Никогда не применяйте изменения сразу на боевую базу

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

Боль! Перед тем, как написать delete или update, напишите where

А еще перед тем, как запустить код, выдохните, просчитайте до трех и удостоверьтесь, что вы в сессии нужной базы. Про truncate я вообще молчу, без трех «Отче наш» даже не думайте запускать, аминь!UPD. koropovskiy: полезнее выставлять set autocommit off для текущего сеанса. tgz: или перед каждым update и delete пишите begin.

Test Driven Development

Вначале всегда пишите тесты, а потом создавайте объекты базы данных. Речь идет про любые объекты: схемы, таблицы, функции, типы, расширения — никаких исключений! Вначале это кажется тяжко, но впоследствии вы много раз скажете себе спасибо. Даже при первичном создании схемы легко что-то упустить. А при рефакторинге таблиц через полгода только написанные вами тесты уберегут от внезапного выстрела в ногу в какой-нибудь функции. В случае PostgreSQL есть замечательное расширение pgTAP. Я рекомендую для каждой схемы создавать дополнительно схему «имя_схемы_tap», в которой писать функции для тестирования. А потом просто прогонять тесты через pg_prove.

Не забывайте настроить PITR

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

Согласованность данных

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

Создавайте внешние ключи deferrable initially deferred

В таком случае вы откладываете проверку ограничения на конец транзакции, что позволяет безнаказанно получать несогласованность в ходе ее выполнения (но в конце все согласуется или вызовет ошибку). Более того, меняя флаг внутри транзакции на immediate, можно принудительно сделать проверку ограничения в нужный момент транзакции.UPD. В комментариях указывают, что deferrable — неоднозначная практика, которая упрощает ряд задач импорта, но усложняет процесс отладки внутри транзакции и является плохой практикой для начинающих разработчиков. Хоть я упрямо склоняюсь к тому, что лучше иметь deferrable ключи, чем не иметь их, учитывайте альтернативный взгляд на вопрос.

Не используйте схему public

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

Отдельная схема для API

Для функций, которые вызываются на стороне приложения, можно создать отдельную схему «api_v_номер_версии». Это позволит четко контролировать, где лежат функции, являющиеся интерфейсами к вашей базе. Для наименования функций в этой схеме можно использовать шаблон «сущность_get/post/patch/delete_аргументы».

Триггеры для аудита

Лучше всего триггеры подходят для аудита действий. Так же рекомендую создать универсальную триггерную функцию, для записи любых действий произвольной таблицы. Для этого нужно вытащить данные о структуре целевой таблицы из information_schema и понять, old или new строка будет вставляться в зависимости от производимого действия. За счет такого решения код становится любовным и прельстивым более поддерживаемым. Если же вы планируете использовать триггеры для подсчета регистра накоплений, то будьте аккуратны в логике — одна ошибка и можно получить неконсистентные данных. Поговаривают, это очень опасное кунг-фу.

Боль! Импорт данных в новую схему

Самое ужасное, но регулярно происходящее событие в жизни разработчика баз данных. В PostgreSQL очень помогают FDW, тем более их хорошо прокачали в 9.6 (если их разработчики озаботятся, то FDW могут строить план на удаленной стороне). Кстати, есть такая удобная конструкция как «import foreign schema», которая спасает от написания оберток над кучей таблиц. Так же хорошей практикой является иметь набор функции, сохраняющие набор SQL команд для удаления и восстановления существующих в базе внешних и первичных ключей. Импорт рекомендую осуществлять, вначале написав набор view с данными, идентичных по структуре целевым таблицам. И из них сделать вставку, используя copy (не insert!). Всю последовательность SQL команд лучше держать в отдельном версионируемом файле и запускать их через psql с ключом -1 (в единой транзакции). Кстати, импорт — это единственных случай, когда в PostgreSQL можно выключить fsync, предварительно сделав бэкап и скрестив пальцы.

Не пишите на SQL:1999

Нет, правда, с тех пор много воды утекло: целое поколение выпустилось из школы, мобильники из кирпичей превратились в суперкомпьютеры по меркам 1999 года. В общем, не стоит писать так, как писали наши отцы. Используйте «with», с ним код становится чище и его можно читать сверху вниз, а не петлять среди блоков join'ов. Кстати, если join делается по полям с одинаковым названием, то лаконичнее использовать «using», а не «on». Ну и конечно, никогда не используйте в боевом коде offset. А еще есть такая прекрасная вещь «join lateral», про которую часто забывают — и в этот момент в мире грустит котенок.UPD. Используя «with» не забывайте, что результат его выполнения создает CTE, которое отъедает память и не поддерживает индексы при запросе к нему. Так что употребленные слишком часто и ни к месту «with» могут негативно сказаться на производительности запроса. Поэтому не забывайте анализировать запрос через планировщик. «with» особенно хорош, когда нужно получить таблицу, которая будет по-разному использоваться в нескольких частях запроса ниже. И помните, «with» радикально улучшает читабельность запроса и в каждой новой версии PostgreSQL работает все эффективнее. При прочих равных — предпочитайте именно эту конструкцию.

Временные таблицы

Если можете написать запрос без временных таблиц — не раздумывайте и напишите! Обычно CTE, создаваемое конструкцией «with», является приемлемой альтернативой. Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл… и да, еще один грустный котенок на планете.

Боль! Самый страшный антипаттерн в SQL

Никогда не используйте конструкции вида

select myfunc() from table; Время выполнения такого запроса возрастает в линейной зависимости от количества строк. Такой запрос всегда можно переписать в нечто без функции, применяемой к каждой строке, и выиграть пару порядков в скорости выполнения.

Главный секрет запросов

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

Используй индексы, Люк!

От того, сколь правильно вы их создадите и будете использовать, зависит, будет запрос выполняться десятые доли секунды или минуты. Я рекомендую ознакомиться с сайтом Маркуса Винанда по устройству b-tree индексов — это лучшее общедоступное объяснение по сбалансированным деревьям, которое я видел в Интернете. И книжка у него тоже крутая, да.

group by или window function?

Нет, понятно, window function может больше. Но иногда агрегацию можно посчитать и так и так. В таких случаях я руководствуюсь правилом: если агрегация считается по покрывающим индексам — только group by. Если покрывающих индексов нет, то можно пробовать window function.

set_config

set_config можно использовать не только для выставление настроек для postgresql.conf в рамках транзакции, но и для передачи в транзакцию пользовательской переменной (если ее заранее определить в postgresql.conf). С помощью таких переменных в транзакции можно очень интересно влиять на поведение вызываемых функций.

FTS и триграммы

Они чудесны! Они даруют нам полнотекстовый и нечеткий поиск при сохранении всей мощи SQL. Просто не забывайте ими пользоваться.

Вызов собственных исключений

Зачастую, в большом проекте приходится вызывать много исключений со своими кодами и сообщениями. Чтобы в них не запутаться, есть вариант создать для исключений отдельный тип вида «код — текст исключения», а так же функции для их вызова (обертка над «raise»), добавления и удаления. А если вы покрыли все свои объекты базы тестами, то вы не сможете случайно удалить код исключения, который уже где-либо используется.

Много паранойи мало не бывает

Хорошая практика — не забывать настроить ACL на таблицы, а функции запускать с «security definer». Когда функции работают только на чтение, фэншуй требует выставлять у них флаг «stable».

Боль! Вишенка на торте

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

habr.com

PostgreSQL. Краткий справочник.

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

Подключение к СУБД

Первое, что нужно сделать - получить доступ к PostgreSQL, доступ в качестве суперпользователя. Настройки аутентификации находятся в файле pg_hba.conf.

# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer

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

$ sudo -u postgres psql postgres postgres

Чтобы иметь возможность подключаться по сети, надо в pg_hdba.conf добавить строку:

# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 0.0.0.0/0 md5

Метод аутентификации md5 означает, что для подключения придется ввести пароль. Это не очень удобно, если вы часто пользуетесь консолью psql. Если вы хотите автоматизировать какие-то действия, то плохая новость в том, что psql не принимает пароль в качестве аргумента. Есть два пути решения этих проблем: установка соответствующей переменной окружения и хранение пароля в специальном файле .pgpass.

Установка переменной окружения PGPASSWORD

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

export PGPASSWORD=mypasswd

Переменная будет доступна в текущей сессии. Если нужно задать переменную для всех сессий, то надо добавить строку из примера в файл .bashrc или .bash_profile

Хранение пароля в файле .pgpass

Если мы говорим о Linux, то файл должен находится в $HOME (/home/username). Права на запись и чтение должны быть только у владельца (0600). В файл нужно записывать строки вида:

hostname:port:database:username:password

В первые четыре поля можно записать "*", что будет означать отсутствие фильтрации (полную выборку).

Получение справочной информации

\? - выдаст все доступные команды вместе с их кратким описанием, \h - выдаст список всех доступных запросов, \h CREATE - выдаст справку по конкретному запросу.

Управление пользователями СУБД

Как получить список пользователей PostgreSQL?

Или можно сделать запрос к таблице pg_user.

SELECT * FROM pg_user;

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

Из командной оболочки psql это можно сделать с помощью команды CREATE.

CREATE USER username WITH password 'password';

Или можно воспользоваться терминалом.

createuser -S -D -R -P username

Ввод пароля будет запрошен.

Изменение пароля пользователя

ALTER USER username WITH PASSWORD 'password';

Изменение ролей пользователя

Чтобы пользователь имел право создавать базы данных, выполните запрос:

ALTER ROLE username WITH CREATEDB;

Управление базами данных

Вывод списка баз данных в терминале psql:

Тоже самое из терминала Linux:

psql -l

Создание базы данных из psql (PostgreSQL Terminal)

CREATE DATABASE dbname OWNER dbadmin;

Создание новой базы данных при помощи терминала:

createdb -O username dbname;

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

Если пользователь является владельцем (owner) базы данных, то у него есть все права. Но если вы хотите дать доступ другому пользователю, то сделать это можно с помощью команды GRANT. Запрос ниже позволит пользователю подключаться к базе данных. Но не забывайте о конфигурационном файле pg_hba.conf, в нем тоже должны быть соответствующие разрешения на подключение.

GRANT CONNECT ON DATABASE dbname TO dbadmin;

Частые вопросы

Как узнать, существует ли база данных?

Check if database exists in postgreSQL using shell

Как узнать, существует ли пользователь?

Например, можно выполнить команду:

psql postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='USER_NAME'"

Если пользователь существует, будет возращена единица, иначе ничего не будет возвращено.

Дополнительная информация

PostgreSQL. Файл pg_hba.conf.

www.mvoronin.pro

Иллюстрированный самоучитель по PostgreSQL › Что такое PostgreSQL? › Возможности PostgreSQL [страница - 6] | Самоучители по программированию

Возможности PostgreSQL

Как упоминалось выше, PostgreSQL считается самой совершенной СУБД, распространяемой на условиях открытых исходных текстов. В PostgreSQL реализованы многие возможности, обычно присутствующие только в коммерческих СУБД, таких как DB2 и Oracle. Ниже перечислены основные возможности PostgreSQL версии 7.1.x.

Объектно-реляционная модель. Работа с данными в PostgreSQL основана на объектно-реляционной модели, что позволяет задействовать сложные процедуры и системы правил. Примерами нетривиальных возможностей этой категории являются декларативные запросы SQL, контроль параллельного доступа, поддержка многопользовательского доступа, транзакции, оптимизация запросов, поддержка наследования и массивов.
Простота расширения. В PostgreSQL поддерживаются пользовательские операторы, функции, методы доступа и типы данных.
Полноценная поддержка SQL. PostgreSQL соответствует базовой спецификации SQL99 и поддерживает такие нетривиальные средства, как объединения стандарта SQL92.
Проверка целостности ссылок. PostgreSQL поддерживает проверку целостности ссылок, обеспечивающую правильность данных в базе.
Гибкость API. Гибкость API PostgreSQL позволяет легко создавать интерфейсы к РСУБД PostgreSQL. В настоящее время существуют программные интерфейсы для Object Pascal, Python, Perl, PHP, ODBC, Java/JDBC, Ruby, TCL, C/ C+ и Pike.
Процедурные языки. В PostgreSQL предусмотрена поддержка внутренних процедурных языков, в том числе специализированного языка PL/pgSQL, являющегося аналогом PL/SQL, процедурного языка Oracle. Одним из преимуществ PostgreSQL является возможность использования Perl, Python и TCL в качестве внутренних процедурных языков.
МVСС. Технология MVCC (Multi-Version Concurrency Control) используется в PostgreSQL для предотвращения лишних блокировок (locking). Каждый, кто хоть раз работал с другими СУБД на базе SQL (например, MySQL или Access), наверняка замечал, что обращение к базе данных для чтения иногда сопровождается задержками, связанными с попытками записи в базу данных. Проще говоря, операции чтения блокируются операциями, производящими обновление записей. Применение технологии MVCC в PostgreSQL полностью решает эту проблему. MVCC лучше низкоуровневой блокировки, поскольку операции чтения никогда не блокируются операциями записи. Вместо этого PostgreSQL отслеживает все транзакции, выполняемые пользователями базы данных, что позволяет работать с записями без ожидания их освобождения.

Клиент-сервер. В PostgreSQL используется архитектура "клиент-сервер" с распределением процессов между пользователями. В целом она напоминает методику работы с процессами в Apache 1.3.x. Главный (master) процесс создает дополнительные подключения для каждого клиента, пытающегося установить соединение с PostgreSQL.

Опережающая регистрация изменений. Опережающая регистрация изменений (Write Ahead Logging, WAL) повышает надежность данных. Все изменения данных протоколируются до их непосредственной актуализации в базе. Наличие протокола изменений гарантирует, что в случае сбоя базы данных (что весьма маловероятно) данные можно будет восстановить по запротоколированным транзакциям. После восстановления системы пользователь продолжает работу с состояния, непосредственно предшествовавшего сбою.

samoychiteli.ru

Иллюстрированный самоучитель по PostgreSQL › Команды PostgreSQL [страница - 308] | Самоучители по программированию

В данной главе приведена сводная информация по всем основным командам SQL, поддерживаемым в PostgreSQL. В этот справочник включены как стандартные команды SQL (например, INSERT и SELECT), так и специфические команды PostgreSQL (такие, как CREATE OPERATOR и CREATE TYPE).

Модификация таблиц и атрибутов полей.

Копирование данных между файлами и таблицами. | Синтаксис: | COPY [ BINARY ] таблица [ WITH OIDS ] | FROM { 'файл' | stdin } | [ [ USING ] DELIMITERS 'разделитель' ] | [ WITH NULL AS ' строка _null' ] COPY [BINARY ] table [ WITH OIDS ] | TO { 'файл' | stdout } | [ [ USING ] DELIMITERS 'разделитель' ] | [ WITH NULL AS 'строка_null' ] | Параметры: | BINARY.

Создание новой базы данных в PostgreSQL. | Синтаксис: | CREATE DATABASE база_данных | [ WITH [ LOCATION = { 'каталог' | DEFAULT } ] | [ TEMPLATE = шаблон DEFAULT ] | [ ENCODING = имя_нодировки | номер_кодировки | DEFAULT ] ] | Параметры: | база_данных. Имя создаваемой базы данных. | каталог.

Определение нового языка, используемого при создании функций. | Синтаксис: | CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE 'язык' | HANDLER обработчик | LANCOMPILER 'комментарий' | Параметры: | TRUSTED. Ключевое слово TRUSTED означает, что PostgreSQL разрешает непривилегированным пользователям обходить ограничения, связанные с наличием прав доступа к языку'.

Создание новой таблицы. | Синтаксис: | CREATE [ TEMPORARY | TEMP ] TABLE таблица ( | { поле тип [ограничение_поля [… ] ] | ограничение_таблицы } | [….

Создание нового триггера. | Синтаксис: | CREATE TRIGGER триггер { BEFORE | AFTER } { событие [ OR событие… ] } | ON таблица | FOR EACH { ROW STATEMENT } | EXECUTE PROCEDURE функция (аргументы) | Параметры: | триггер. Имя создаваемого триггера. | таблица. Имя таблицы, с которой ассоциируется триггер. | событие.

Определение нового типа данных в базе. | Синтаксис: | CREATE TYPE тип (INPUT = входная_функция, OUTPUT = входная_функция | , INTERNALLENGTH = { внутренний_размер | VARIABLE } | [, EXTERNALLENGTH – { внешний_размер | VARIABLE } ] | [, DEFAULT = "значение_по^умолчанию" ] | [, ELEMENT = элемент ] [.

Определение нового курсора. | Синтаксис: | DECLARE курсор | [ BINARY ] [ INSENSITIVE ] [ SCROLL ] | CURSOR FOR запрос | [ FOR { READ ONLY | UPDATE [ OF поле [….]]}] | Параметры: | курсор. Имя нового курсора. | BINARY.

Удаление записей из таблицы. | Синтаксис: | DELETE FROM [ ONLY ] таблица [ WHERE условие ] | Параметры: | таблица. Имя таблицы, из которой удаляются записи. | условие. Критерий отбора удаляемых записей. Структура условия аналогична условию секции WHERE команды SELECT – за дополнительной информацией об условиях обращайтесь к описанию команды SELECT.

Удаление оператора из базы данных. | Синтаксис: | DROP OPERATOR оператор | ({ левый__тип NONE }. | { правый_тип | NONE }) | Параметры: | оператор. Удаляемый оператор. | левый_тип \ NONE. Тип левого операнда (или NONE при его отсутствии). | правый_тип \ NONE. Тип правого операнда (или NONE при его отсутствии).

Выборка записей с использованием курсора. | Синтаксис: | FETCH направление | [ количество_записей ] { IN | FROM } курсор | направление:: – { FORWARD | BACKWARD | RELATIVE } | количество_записей:: = { число \ ALL NEXT PRIOR } | Параметры: | направление. Необязательный параметр, определяющий направление выборки.

Предоставление прав доступа пользователю, группе или всем пользователям базы данных. | Синтаксис: | GRANT привилегия [,…] ON объект [….] | ТО { PUBLIC | GROUP группа \ пользователь } | Параметры: | привилегия. Предоставляемая привилегия. Допустимые значения:

Вставка новых записей в таблицу. | Синтаксис: | INSERT INTO таблице [ (поле [….]) ] | { DEFAULT VALUES | VALUES (значение [….]) | | запрос } | Параметры: | таблица. Таблица, в которую вставляются новые данные. | поле. Имя поля, для которого задается значение.

Блокировка записей в транзакциях. | Синтаксис: | LOCK [ TABLE ] таблица | LOCK [ TABLE ] таблица IN режим | режим:: = { [ ROW | ACCESS ] { SHARE | EXCLUSIVE } | | SHARE ROW EXCLUSIVE } MODE | Параметры: | таблица. Имя таблицы, для которой устанавливается блокировка. | режим.

Перемещение курсора к другой записи. | Синтаксис: | MOVE [ направление ] [ количество ] | { IN | FROM } курсор | Параметры: | направление. Направление, в котором перемещается указанный курсор. За дополнительной информацией о направлениях обращайтесь к описанию команды FETCH. | количество.

Восстановление индексов в таблицах. | Синтаксис: | REINDEX { TABLE | DATABASE | INDEX } объект [ FORCE ] | Параметры: | TABLE | DATABASE | INDEX. Тип индексируемого объекта. | объект. Имя индексируемого объекта. | FORCE. Ключевое слово FORCE восстанавливает индексы для всех перечисленных объектов.

Отмена привилегий доступа у пользователя, группы или всех пользователей. | Синтаксис: | REVOKE привилегия […. ] | ON объект [….] | FROM { PUBLIC | GROUP группа \ пользователь } | Параметры: | привилегия. Отменяемая привилегия.

Выборка записей из таблицы или представления. | Синтаксис: | SELECT [ ALL | DISTINCT [ ON (уникальное_выражение [….]) ] ] | цель [ AS выходное_имя ] [….] | [ FROM источник [ {. | CROSS JOIN }…] ] [ WHERE условие_фильтрации ] | [ GROUP BY условие_группировки [….

Создание новой таблицы по результатам команды SELECT. | Синтаксис: | SELECT [ ALL | DISTINCT [ ON (уникальное_выражение [….]) ] ] | цель [ AS выходное имя ] [,…] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] новая_таблица ] | [ FROM источник [ {.

Присваивание значений конфигурационным переменным. | Синтаксис: | SET переменная {ТО = } { значение \ 'значение' DEFAULT } | SET TIME ZONE { 'часовой_пояс' \ LOCAL DEFAULT } | Параметры: | переменная. Имя конфигурационной переменной, которой присваивается новое значение. | значение.

Выбор режима проверки ограничений в текущей транзакции. | Синтаксис: | SET CONSTRAINTS { ALL режим […. ] } | { DEFERRED | IMMEDIATE } | Параметры: | ALL. Ключевое слово ALL означает, что указанный режим должен относиться ко всем ограничениям в текущей транзакции. | режим.

Выбор уровня изоляции текущей транзакции. | Синтаксис: | SET TRANSACTION ISOLATION LEVEL | { READ COMMITTED | SERIALIZABLE } | SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL | { READ COMMITTED | SERIALIZABLE } | Параметры: | READ COMMITED.

Серверный процесс выходит из режима ожидания уведомлений. | Синтаксис: | UNLISTEN { событие \ * } | Параметры: | событие. Имя события, ожидаемого сервером. | *. Отмена ожидания всех событий, определенных ранее. | Результаты: | UNLISTEN. Это сообщение выдается при успешном выполнении команды UNLISTEN.

samoychiteli.ru

PostgreSQL : Документация: 9.6: Часть VI. Справочное руководство : Компания Postgres Professional

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

Все эти справочные статьи также публикуются в виде традиционных страниц «man».

Содержание

I. Команды SQLABORT — прервать текущую транзакциюALTER AGGREGATE — изменить определение агрегатной функцииALTER COLLATION — изменить определение правила сортировкиALTER CONVERSION — изменить определение перекодировкиALTER DATABASE — изменить атрибуты базы данныхALTER DEFAULT PRIVILEGES — определить права доступа по умолчаниюALTER DOMAIN — изменить определение доменаALTER EVENT TRIGGER — изменить определение событийного триггераALTER EXTENSION — изменить определение расширенияALTER FOREIGN DATA WRAPPER — изменить определение обёртки сторонних данныхALTER FOREIGN TABLE — изменить определение сторонней таблицыALTER FUNCTION — изменить определение функцииALTER GROUP — изменить имя роли или членствоALTER INDEX — изменить определение индексаALTER LANGUAGE — изменить определение процедурного языкаALTER LARGE OBJECT — изменить определение большого объектаALTER MATERIALIZED VIEW — изменить определение материализованного представленияALTER OPERATOR — изменить определение оператораALTER OPERATOR CLASS — изменить определение класса операторовALTER OPERATOR FAMILY — изменить определение семейства операторовALTER POLICY — изменить определение политики защиты на уровне строкALTER ROLE — изменить роль в базе данныхALTER RULE — изменить определение правилаALTER SCHEMA — изменить определение схемыALTER SEQUENCE — изменить определение генератора последовательностиALTER SERVER — изменить определение стороннего сервераALTER SYSTEM — изменить параметр конфигурации сервераALTER TABLE — изменить определение таблицыALTER TABLESPACE — изменить определение табличного пространстваALTER TEXT SEARCH CONFIGURATION — изменить определение конфигурации текстового поискаALTER TEXT SEARCH DICTIONARY — изменить определение словаря текстового поискаALTER TEXT SEARCH PARSER — изменить определение анализатора текстового поискаALTER TEXT SEARCH TEMPLATE — изменить определение шаблона текстового поискаALTER TRIGGER — изменить определение триггераALTER TYPE — изменить определение типаALTER USER — изменить роль в базе данныхALTER USER MAPPING — изменить определение сопоставления пользователейALTER VIEW — изменить определение представленияANALYZE — собрать статистику по базе данныхBEGIN — начать блок транзакцииCHECKPOINT — записать контрольную точку в журнал транзакцийCLOSE — закрыть курсорCLUSTER — кластеризовать таблицу согласно индексуCOMMENT — задать или изменить комментарий объектаCOMMIT — зафиксировать текущую транзакциюCOMMIT PREPARED — зафиксировать транзакцию, которая ранее была подготовлена для двухфазной фиксацииCOPY — копировать данные между файлом и таблицейCREATE ACCESS METHOD — создать новый метод доступаCREATE AGGREGATE — создать агрегатную функциюCREATE CAST — создать приведениеCREATE COLLATION — создать правило сортировкиCREATE CONVERSION — создать перекодировкуCREATE DATABASE — создать базу данныхCREATE DOMAIN — создать доменCREATE EVENT TRIGGER — создать событийный триггерCREATE EXTENSION — установить расширениеCREATE FOREIGN DATA WRAPPER — создать новую обёртку сторонних данныхCREATE FOREIGN TABLE — создать стороннюю таблицуCREATE FUNCTION — создать функциюCREATE GROUP — создать роль в базе данныхCREATE INDEX — создать индексCREATE LANGUAGE — создать процедурный языкCREATE MATERIALIZED VIEW — создать материализованное представлениеCREATE OPERATOR — создать операторCREATE OPERATOR CLASS — создать класс операторовCREATE OPERATOR FAMILY — создать семейство операторовCREATE POLICY — создать новую политику защиты на уровне строк для таблицыCREATE ROLE — создать роль в базе данныхCREATE RULE — создать правило перезаписиCREATE SCHEMA — создать схемуCREATE SEQUENCE — создать генератор последовательностиCREATE SERVER — создать сторонний серверCREATE TABLE — создать таблицуCREATE TABLE AS — создать таблицу из результатов запросаCREATE TABLESPACE — создать табличное пространствоCREATE TEXT SEARCH CONFIGURATION — создать конфигурацию текстового поискаCREATE TEXT SEARCH DICTIONARY — создать словарь текстового поискаCREATE TEXT SEARCH PARSER — создать анализатор текстового поискаCREATE TEXT SEARCH TEMPLATE — создать шаблон текстового поискаCREATE TRANSFORM — создать трансформациюCREATE TRIGGER — создать триггерCREATE TYPE — создать новый тип данныхCREATE USER — создать роль в базе данныхCREATE USER MAPPING — создать сопоставление пользователя для стороннего сервераCREATE VIEW — создать представлениеDEALLOCATE — освободить подготовленный операторDECLARE — определить курсорDELETE — удалить записи таблицыDISCARD — очистить состояние сеансаDO — выполнить анонимный блок кодаDROP ACCESS METHOD — удалить метод доступаDROP AGGREGATE — удалить агрегатную функциюDROP CAST — удалить приведение типаDROP COLLATION — удалить правило сортировкиDROP CONVERSION — удалить преобразованиеDROP DATABASE — удалить базу данныхDROP DOMAIN — удалить доменDROP EVENT TRIGGER — удалить событийный триггерDROP EXTENSION — удалить расширениеDROP FOREIGN DATA WRAPPER — удалить обёртку сторонних данныхDROP FOREIGN TABLE — удалить стороннюю таблицуDROP FUNCTION — удалить функциюDROP GROUP — удалить роль в базе данныхDROP INDEX — удалить индексDROP LANGUAGE — удалить процедурный языкDROP MATERIALIZED VIEW — удалить материализованное представлениеDROP OPERATOR — удалить операторDROP OPERATOR CLASS — удалить класс операторовDROP OPERATOR FAMILY — удалить семейство операторовDROP OWNED — удалить объекты базы данных, принадлежащие ролиDROP POLICY — удалить политику защиты на уровне строк для таблицыDROP ROLE — удалить роль в базе данныхDROP RULE — удалить правило перезаписиDROP SCHEMA — удалить схемуDROP SEQUENCE — удалить последовательностьDROP SERVER — удалить описание стороннего сервераDROP TABLE — удалить таблицуDROP TABLESPACE — удалить табличное пространствоDROP TEXT SEARCH CONFIGURATION — удалить конфигурацию текстового поискаDROP TEXT SEARCH DICTIONARY — удалить словарь текстового поискаDROP TEXT SEARCH PARSER — удалить анализатор текстового поискаDROP TEXT SEARCH TEMPLATE — удалить шаблон текстового поискаDROP TRANSFORM — удалить трансформациюDROP TRIGGER — удалить триггерDROP TYPE — удалить тип данныхDROP USER — удалить роль в базе данныхDROP USER MAPPING — удалить сопоставление пользователя для стороннего сервераDROP VIEW — удалить представлениеEND — зафиксировать текущую транзакциюEXECUTE — выполнить подготовленный операторEXPLAIN — показать план выполнения оператораFETCH — получить результат запроса через курсорGRANT — определить права доступаIMPORT FOREIGN SCHEMA — импортировать определения таблиц со стороннего сервераINSERT — добавить строки в таблицуLISTEN — ожидать уведомленияLOAD — загрузить файл разделяемой библиотекиLOCK — заблокировать таблицуMOVE — переместить курсорNOTIFY — сгенерировать уведомлениеPREPARE — подготовить оператор к выполнениюPREPARE TRANSACTION — подготовить текущую транзакцию для двухфазной фиксацииREASSIGN OWNED — сменить владельца объектов базы данных, принадлежащих заданной ролиREFRESH MATERIALIZED VIEW — заменить содержимое материализованного представленияREINDEX — перестроить индексыRELEASE SAVEPOINT — высвободить ранее определённую точку сохраненияRESET — восстановить значение по умолчанию заданного параметра времени выполненияREVOKE — отозвать права доступаROLLBACK — прервать текущую транзакциюROLLBACK PREPARED — отменить транзакцию, которая ранее была подготовлена для двухфазной фиксацииROLLBACK TO SAVEPOINT — откатиться к точке сохраненияSAVEPOINT — определить новую точку сохранения в текущей транзакцииSECURITY LABEL — определить или изменить метку безопасности, применённую к объектуSELECT — получить строки из таблицы или представленияSELECT INTO — создать таблицу из результатов запросаSET — изменить параметр времени выполненияSET CONSTRAINTS — установить время проверки ограничений для текущей транзакцииSET ROLE — установить идентификатор текущего пользователя в рамках сеансаSET SESSION AUTHORIZATION — установить идентификатор пользователя сеанса и идентификатор текущего пользователя в рамках сеансаSET TRANSACTION — установить характеристики текущей транзакцииSHOW — показать значение параметра времени выполненияSTART TRANSACTION — начать блок транзакцииTRUNCATE — опустошить таблицу или набор таблицUNLISTEN — прекратить ожидание уведомленияUPDATE — изменить строки таблицыVACUUM — провести сборку мусора и, возможно, проанализировать базу данныхVALUES — вычислить набор строкII. Клиентские приложения PostgreSQLclusterdb — кластеризовать базу данных PostgreSQLcreatedb — создать базу данных PostgreSQLcreatelang — установить процедурный язык PostgreSQLcreateuser — создать новую учётную запись PostgreSQLdropdb — удалить базу данных PostgreSQLdroplang — удалить процедурный язык PostgreSQLdropuser — удалить учётную запись пользователя PostgreSQLecpg — встроенный C-препроцессор SQLpg_basebackup — создать резервную копию кластера PostgreSQLpgbench — запустить тест производительности PostgreSQLpg_config — вывести информацию об установленной версии PostgreSQLpg_dump — выгрузить базу данных PostgreSQL в формате скрипта в файл или архивpg_dumpall — выгрузить кластер баз данных PostgreSQL в формате скриптаpg_isready — проверить соединение с сервером PostgreSQLpg_receivexlog — приём журналов транзакций с сервера PostgreSQLpg_recvlogical — управление потоками логического декодирования PostgreSQLpg_restore — восстановить базу данных PostgreSQL из файла архива, созданного командой pg_dumppsql — интерактивный терминал PostgreSQLreindexdb — переиндексировать базу данных PostgreSQLvacuumdb — выполнить очистку и анализ базы данных PostgreSQLIII. Серверные приложения PostgreSQLinitdb — создать кластер баз данных PostgreSQLpg_archivecleanup — вычистить файлы архивов WAL PostgreSQLpg_controldata — вывести управляющую информацию кластера баз данных PostgreSQLpg_ctl — инициализировать, запустить, остановить или управлять сервером PostgreSQLpg_resetxlog — сбросить журнал предзаписи и другую управляющую информацию кластера PostgreSQLpg_rewind — синхронизировать каталог данных PostgreSQL с другим каталогом, ответвлённым от негоpg_test_fsync — подобрать наилучший вариант wal_sync_method для PostgreSQLpg_test_timing — определить издержки замера времениpg_upgrade — обновить экземпляр сервера PostgreSQLpg_xlogdump — вывести журнал предзаписи кластера БД PostgreSQL в понятном человеку

postgrespro.ru

Техника безопасности при работе с PostgreSQL. Postgresql для чайников

Иллюстрированный самоучитель по PostgreSQL › Введение [страница - 2] | Самоучители по программированию

Введение

Для кого написана эта книга?

Структура книги

Платформа и версия PostgreSQL

Принятые обозначения

Развертывание кластера Postgres-xl для чайников / Хабр

Часть 1. Подготовка окружения

Часть 2. Установка зависимостей

(adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); Часть 3. Загрузка исходного кода, его компиляция и установка

Часть 4. Конфигурирование

Часть 5. Запуск узлов кластера

Часть 6. Настройка узлов кластера

Заключение

Техника безопасности при работе с PostgreSQL / Хабр

PostgreSQL. Краткий справочник.

Подключение к СУБД

Установка переменной окружения PGPASSWORD

Хранение пароля в файле .pgpass

Получение справочной информации

Управление пользователями СУБД

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

Изменение пароля пользователя

Изменение ролей пользователя

Управление базами данных

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

Частые вопросы

Как узнать, существует ли база данных?

Как узнать, существует ли пользователь?

Дополнительная информация

Иллюстрированный самоучитель по PostgreSQL › Что такое PostgreSQL? › Возможности PostgreSQL [страница - 6] | Самоучители по программированию

Возможности PostgreSQL

Иллюстрированный самоучитель по PostgreSQL › Команды PostgreSQL [страница - 308] | Самоучители по программированию

PostgreSQL : Документация: 9.6: Часть VI. Справочное руководство : Компания Postgres Professional

Часть 3. Загрузка исходного кода, его компиляция и установка