PostgreSQL. HINTS
Расположение конфигурации
обычно:
/etc/postgresql/9.1/main/postgresql.conf
найти:
find / -iname postgresql.conf
locate postgresql.conf
Конфигуратор для тонкой настройки
>= 10
http://pgconfigurator.cybertec.at/
>= 9.2
https://pgtune.leopard.in.ua/#/
Оптимизация
https://serveradmin.ru/uskorenie-rabotyi-1s-s-postgresql-i-diagnostika-problem-proizvoditelnosti/
https://its.1c.ru/db/metod8dev#content:5866:hdoc
auto_increment в PostgreSQL
Тем, кто переходит на PostgreSQL с MySQL, наверное будет удивительно узнать
что специального свойства у поля auto_increment в новой СУБД нет.
Заменой автоинкрементов в PostgreSQL являются такие штуки как Sequences (счетчики).
Работают они так:
-- Создание счетчика
CREATE SEQUENCE auto_id_users;
-- Создание таблицы
CREATE TABLE users ("id" integer NOT NULL DEFAULT nextval('auto_id_users'), "name" text)
То есть счетчик взаимодействует с полем id через свойство DEFAULT в которой прописывается nextval от счетчика.
У этого подхода есть ряд несомненных плюсов:
- Можно очень быстро узнать значение счетчика, без нагрузки к БД и обращению к таблицам. SELECT last_value from auto_id_users;
- У счетчика функционал не ограничивается только увеличением на единицу, у него много возможностей.
Посмотреть список БД
\list
или
\l
Подключиться к БД
\c databasename
Отключиться от текущий БД и вернуться к умолчальной БД
\c postgres
Создать таблицу
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- минимальная температура дня
temp_hi int, -- максимальная температура дня
prcp real, -- уровень осадков
date date
);
Вывести список всех таблиц в БД
\dt
Вывести информацию о таблице
\d weather
Вставить строку в таблицу
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Тамбов', '12', '25', '4', '2019-09-09 09:00');
Получить все строки из таблицы
SELECT * FROM weather;
Postgresql позволяет определить порядок вывода запрошенных данных. Для этого используется конструкция ORDER BY, идущая после оператора SELECT
БАЗОВЫЙ СИНТАКСИС ВЫГЛЯДИТ ТАК: SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];
SELECT city,temp_lo FROM weather ORDER BY city;
Вывести значение конкретного столбца строки с конкретным именем
SELECT date FROM weather WHERE city='Тамбов';
Удалить таблицу
DROP TABLE <имя_таблицы>;
Создать пользователя с разрешением на вход в СУБД
CREATE ROLE username WITH PASSWORD 'user_password' LOGIN;
Изменить пароль пользователя
\password username;
Создание новой БД с присвоением владельца
CREATE DATABASE databasename WITH OWNER username;
Сменить владельца БД
ALTER DATABASE name OWNER TO new_username;
Дать права доступа на БД для пользователя
GRANT ALL ON DATABASE databasename TO username;
Переименовать БД
ALTER DATABASE name RENAME TO new_databasename ;
Посмотреть список запросов к БД
select * from pg_stat_activity where datname='YourDatabase';
Удалить процесс по pid
select pg_terminate_backend(pid) from pg_stat_activity where datname='YourDatabase';
Сделать дамп БД
pg_dump -U postgres database > dump_file.sql
Залить дамп БД
psql -U postgres database < dump_file.sql
Соотвественно работает связка
pg_dump -U postgres database | psql -U postgres database2
Сбросить все соединения к базе, удалить базу, создать базу
select pg_terminate_backend(pid) from pg_stat_activity where datname = 'db_name'; drop database db_name; create database db_name;
Удаленное выполнение команд на postgresql сервере (remote command run)
If you only want to connect
PGPASSWORD=<user-postgresql-password> psql -h <ip/url-of-the-remote-server> -U <user-name> -d <database-name>
If you want to execute a command remotely
PGPASSWORD=<user-postgresql-password> psql -h <ip/url-of-the-remote-server> -U <user-name> -d <database-name> -c "YOUR SQL COMMAND;"
If your SQL commands are in a .sql file and you want to execute that file on the remote server
PGPASSWORD=<user-postgresql-password> psql -h <ip/url-of-the-remote-server> -U <user-name> -d <database-name> < /path/to/your/local/file.sql
Формат имени файла лога (log filename pattern)
Умолчальный путь к логам
/var/lib/pgsql/13/data/log/
Шаблон имени файлов логов
nano /var/lib/pgsql/13/data/postgresql.conf
... log_filename = 'postgresql_%Y-%m-%d.log' ...