Вернуться назад

Команды и скрипты postgres

Создадим тестовую базу данных и тестового пользователя:

sudo -u postgres psql

postgres=# CREATE DATABASE test_database;
CREATE DATABASE
 
 
 
 
 
 
 
postgres=# CREATE USER test_user WITH password 'qwerty';
CREATE ROLE
 
 
 
 
 
 
 
postgres=# GRANT ALL privileges ON DATABASE test_database TO test_user;
GRANT
 
 
 
 
 
 
 

Для выхода из оболочки введите команду \q.

Теперь попробуем поработать с созданной базой данных от имени test_user:

psql -h localhost test_database test_user
 
 
 
 
 
 
 

Создадим новую таблицу:

test_database=> CREATE SEQUENCE user_ids;
CREATE SEQUENCE
test_database=> CREATE TABLE users (id INTEGER PRIMARY KEY DEFAULT NEXTVAL('user_ids'), login CHAR(64), password CHAR(64));
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "users_pkey" FOR TABLE "users"
CREATE TABLE
 
 
 
 
 
 
 

Удаление пользователя

Чтобы удалить пользователя необходимо передать его права другому, а затем только удалить

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
 
 
 
 
 
 
 

— повторить предыдущие команды для каждой базы в кластере

DROP ROLE doomed_role;

Скрипт по табличного бэкапа postgres.

Исполнять из под user’a — postgres. Если в кронах — то также из под юзера postgresql.

#!/bin/bash
DBNAMES="web";
USER="postgres";
DB_NAME="web";
NEW_OWNER="user_remote";
DATE_Y=`/bin/date '+%y'`
DATE_M=`/bin/date '+%m'`
DATE_D=`/bin/date '+%d'`
SERVICE="pgdump"
BACKUP_DIR="/var/backup_db/20${DATE_Y}/${DATE_M}/${DATE_D}"
mkdir -p $BACKUP_DIR;
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" ${DB_NAME}` \
           `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" ${DB_NAME}` \
           `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" ${DB_NAME}` ;
do
echo "Exporting table $tbl from db ${DB_NAME} to file tables3/$tbl.backup"
#pg_dump  --format p --verbose --table public.$tbl ${DB_NAME} > $BACKUP_DIR/$tbl
pg_dump  --format p --verbose --table public.$tbl ${DB_NAME} | gzip >  $BACKUP_DIR/$tbl
#pg_dump -a -d -t public.$tbl ${DB_NAME} > tables3/$tbl.sql
done
##################БЭКАП ФУНКЦИЙ POSTGRES
# Делаем dump базы без даты, для того что дальше извлечь их нее функции
pg_dump  -Fc -s -f $BACKUP_DIR/db_dump ${DB_NAME}
/bin/sleep 4;
# Создаем список функция
pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list
##Как восстановить функции
#########################
#pg_restore -h localhost -U username -d имя_базы -L function_list db_dump
########################
 
 
 
 
 
 
 

Скрипт по табличного бэкапа postgres. написан на perl

Исполнять из под user’a — postgres. Если в кронах — то также из под юзера postgresql.

#!/usr/bin/env perl
use strict;
use warnings;
my $database_name = 'book_library';
my $query = <<"EOT";
SELECT  n.nspname as table_schema,
        c.relname as table_name
    FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','')
        AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        AND n.nspname NOT LIKE '^pg_%'
    ;
EOT
$query =~ s/\n\s*/ /g;
my @results = `echo  "$query" | psql -At $database_name`;
foreach (@results) {
    chomp;
    my ($schema, $table) = split /\|/, $_;
    next unless ($schema && $table);
    my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name";
    system($cmd);
}
#If you wanted to restore only a single function:
##pg_restore -U $username --dbname=$dbname --function=$functionname(args)
#If you wanted to restore only a single table:
##pg_restore -U $username --dbname=$dbname --table=$tablename
 
 
 
 
 
 
 

Словари полнотекстового поиска в postgresql

Скачать с сайта

Бэкап базы с удаленной машины

 PGPASSWORD="PASSWORD" pg_dump   -h $HOSTNAME  -U databaseuser -Fc --verbose  'database.itc-life.ru' | gzip >  databasename.gz
 
 
 
 
 
 
 

Бекап и восстановление таблиц

В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall. pg_dump используется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем).

Создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb
 
 
 
 
 
 
 

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
 
 
 
 
 
 
 

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb
 
 
 
 
 
 
 

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

pg_dump -a -t table_name -f file_name database_name
 
 
 
 
 
 
 

Создание резервной копии с сжатием в gz

pg_dump -h localhost  -O -F p -c -U postgres mydb | gzip -c > mydb.gz
 
 
 
 
 
 
 

Список наиболее часто используемых опций:

-h host — хост, если не указан то используется localhost или значение из переменной окруженияPGHOST.

-p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.

-u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.

-a, --data-only — дамп только данных, по-умолчанию сохраняются данные и схема.

-b — включать в дамп большие объекты (blog’и).

-s, --schema-only — дамп только схемы.

-C, --create — добавляет команду для создания БД.

-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

-O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

-F, --format {c|t|p} — выходной формат дампа, custom, tar, или plain text.

-t, --table=TABLE — указываем определенную таблицу для дампа.

-v, --verbose — вывод подробной информации.

-D, --attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

pg_dumpall > all.sql
# проверка бекапа 
grep "^[\]connect" all.sql
\connect db1
\connect db2
 
 
 
 
 
 
 

В PostgreSQL есть две утилиты для восстановления базы из бекапа.

  • psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
  • pg_restore — восстановление сжатых бекапов (tar);

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

#pg_restore -v -e -d dbname dbname.dump
 
 
 
 
 
 
 

Восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql
 
 
 
 
 
 
 

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sql
 
 
 
 
 
 
 

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
 
 
 
 
 
 
 

Восстановление резервной копии БД, сжатой gz

gunzip mydb.gz
psql -U postgres -d mydb -f mydb
 
 
 
 
 
 
 

Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции --section

# создаем БД
CREATE DATABASE mydb2;
# восстанавливаем
pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup
 
 
 
 
 
 
 

Обслуживание таблицы

VACUUM ANALYZE table;
REINDEX DATABASE dbName;
REINDEX TABLE tabName;
 
 
 
 
 
 
 

Перенос директории с данным (data directory)

Узнать текущий путь

# способ 1
$ su - postgres
$ psql
psql > SHOW data_directory;
# способ 2
$ ps ax | grep 'postgres -D'
 
 
 
 
 
 
 

Создадим новую директорию, назначим пользователя и инициализируем

mkdir -p /pathto/postgresql/data
chown -R postgres:postgres /pathto/postgresql
su - postgres
initdb -D /pathto/postgresql/data
 
 
 
 
 
 
 

Теперь надо подправить файл с сервисом, который стартует postgresql

# под arch linux
sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service
Environment=PGROOT=/pathto/postgresql/
PIDFile=/pathto/postgresql/data/postmaster.pid
 
 
 
 
 
 
 

Очищение таблицы

Очищение таблицы tablename и обнуление счетчика с ID.

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;
 
 
 
 
 
 
 

CASCADE нужен на случай если tablename связана с другой таблицей.

Удаление NULL у поля

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;
 
 
 
 
 
 
 

Утилиты

pgcli утилита командной строки с авто-дополнениям и подсветкой синтаксиса.

Установка

pip install pgcli
 
 
 
 
 
 
 

Запуск

pgcli -U postgres -W dbname
 
 
 
 
 
 
 

 

Скрипт изменения владельца базы данных и таблиц postgresql

#!/bin/bash
usage()
{
cat << EOF
usage: $0 options
This script set ownership for all table, sequence and views for a given database
Credit: Based on https://stackoverflow.com/a/2686185/305019 by Alex Soto
        Also merged changes from @sharoonthomas
OPTIONS:
   -h      Show this message
   -d      Database name
   -o      Owner
EOF
}
DB_NAME="web"
NEW_OWNER="postgres"
while getopts "hd:o:" OPTION
do
    case $OPTION in
        h)
            usage
            exit 1
            ;;
        d)
            DB_NAME=$OPTARG
            ;;
        o)
            NEW_OWNER=$OPTARG
            ;;
    esac
done
if [[ -z $DB_NAME ]] || [[ -z $NEW_OWNER ]]
then
     usage
     exit 1
fi
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" ${DB_NAME}` \
           `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" ${DB_NAME}` \
           `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" ${DB_NAME}` ;
do
    psql -c "alter table \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
done
 
 
 
 
 
 
 

Отправка postgresql в rsyslog > fluentd > kibana

Макет для rsyslog.d

$ModLoad imfile
$InputFileName /var/log/postgresql/postgresql-9.4-main.log
$InputFileTag postgresql-9.4-main.log
$InputFileStateFile postgresql-9.4-main.log.state
$InputFileFacility local6
$InputRunFileMonitor
$template simple, " %msg%"
local6.* @;simple
 
 
 
 
 
 
 

Postgres pgpool2 presentation

pgpool-ii-3-5-presentation

Запуск pgbouncer

su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"
 
 
 
 
 
 
 

Отсоединить пользователей от базы данных

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb';`
 
 
 
 
 
 
 

Postgres перезагрузка конфига без рестарта

SELECT pg_reload_conf();


Взято тут https://itc-life.ru/komandy-postgresql/

igel
Дата: 08 августа 2019 г. в 18:24:30

Автор: igel