Postgresql

Материал из First-Leon
Перейти к: навигация, поиск

Установка Postgresql из ppa

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

Для Ubuntu 16.04:

echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Для Ubuntu 14.04:

echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Для Ubuntu 12.04:

echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get update

Разрешить удаленное подключение к серверу

mcedit /etc/postgresql/9.1/main/postgresql.conf

listen_addresses = '*'

И в файле /etc/postgresql/9.1/main/pg_hba.conf:

#Тип    База            Пользователь    С каких адресов         Метод 
host    all             all             192.168.0.0/16          md5

Разрешить локальное подключение

local all all md5

Увеличиваем глубину стека выполнения

vi /etc/postgresql/9.1/main/postgresql.conf

max_stack_depth = 4MB

Добавление пользователя

# su postgres
$ psql
create user username with password 'userpassword';

Дать пользователю права администратора

ALTER USER username SUPERUSER;

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

Создание дампа базы:

sudo -u postgres -H pg_dump BASE_NAME | pbzip2 -p4 -c > FILE_NAME.bz2

Создание дампа всех баз:

sudo -u postgres -c pg_dumpall | pbzip2 -p10 > /tmp/postgresbackups

Восстановление дампа:

sudo -u postgres pbzip2 -p4 -d -c FILE_NAME.bz2 | psql -h localhost BASE_NAME USER_NAME -W

Или:

sudo -u postgres pbzip2 -p4 -d -c FILE_NAME.bz2 | pg_restore -d BASE_NAME

Удаление базы

sudo -u postgres -H dropdb basename

Оптимизация работы PostgreSQL

pgtune -i /etc/postgresql/9.1/main/postgresql.conf -o /etc/postgresql/9.1/main/postgresql.conf_new

Программа создает оптимизированный (по использованию памяти) конфиг. Подробнее в man.

Или онлайн калькулятор

Логирование медленных запросов

log_duration = on # Включает логирование запросов
log_min_duration_statement = 1 # Устанавливает минимальное время (в миллисекундах) выполнения запроса, который попадает в лог

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

alter user user_name with password 'new_password';

Сменить владельца объектов (рекурсивно)

REASSIGN OWNED BY old_owner TO new_owner;

Создать базу

Запросом:

CREATE DATABASE test_db OWNER test_user;

Из консоли:

createdb --encoding='utf-8' --lc-collate='ru_RU.UTF-8' --lc-ctype='ru_RU.UTF-8' --template=template0 bdname

Удалить подключения к базе (9.1)

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'condo-dev'
  AND procpid <> pg_backend_pid();

Удалить подключения к базе (9.3)

psql postgres -c "update pg_database set datallowconn = 'false' where datname = '$pg_db'; SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = '$pg_db'; "

Список активных подключений

SELECT * FROM pg_catalog.pg_stat_activity;

Список выполняющихся запросов

select * from pg_stat_activity where datname='tisnso_dev_pg' and usename='tisnso_dev_pg';

Чтобы запросы отображались полностью, необходимо увеличить значение track_activity_query_size (до, например, 16384 ) в /etc/postgresql/9.1/main/postgresql.conf

Чтобы выгрузить запросы в консоли, нужно запустить консоль с такими параметрами: psql -o /tmp/out -x

Дополнительные данные по работу с Postgres

Если требуется работа с локалью en_US.UTF-8, то нужно выполнить:

locale-gen en_US.UTF-8

Ссылки