Postgresql
Перейти к навигации
Перейти к поиску
Установка Postgresql из официального репозитория
echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >> /etc/apt/sources.list apt-get install gnupg2 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - apt-get update apt-get install postgresql-11
Установка pgadmin4
apt-get install pgadmin4 pgadmin4-apache2
Разрешить удаленное подключение к серверу
mcedit /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*'
И в файле /etc/postgresql/11/main/pg_hba.conf:
#Подключение из подсети #Тип База Пользователь С каких адресов Метод host all all 192.168.0.0/16 md5 #Локальное подключение local all all md5
systemctl restart postgresql.service
Увеличиваем глубину стека выполнения
vi /etc/postgresql/11/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 pg_restore --dbname=zabbix --verbose zabbix
sudo -u postgres psql -U zabbix -d zabbix -f zabbix
Удаление базы
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;
Из консоли:
sudo -u postgres 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