Команда для входа под суперпользователем
sudo -u postgres psqlСоздать базу test_database
CREATE DATABASE test_database;Создаём пользователя test_user с паролем qwerty:
CREATE USER test_user WITH password 'qwerty';Даём права на базу созданному пользователю:
GRANT ALL privileges ON DATABASE test_database TO test_user;Для выхода в систему вводим \q
Чтобы удалить пользователя, надо сначала передать его права другому пользователю:
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;повторить предыдущие команды для каждой базы в кластере
DROP ROLE doomed_role;
Создание пользователя и базы (из консоли, под root, пользователь dbuser, база dbase, после первой команды будет предложение установить пароль пользователя dbuser ):
# sudo -u postgres createuser --pwprompt dbuser
# sudo -u postgres createdb -O dbuser dbaseИмпорт дампа базы server.sql.gz в существующую базу (пользователь dbuser, в базу dbase):
zcat /home/server.sql.gz | sudo -u dbuser psql dbase
psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.
psql -d dbname -H -c «SELECT * FROM my» -o my.html — вывод результата запроса в html-файл.Создание бекапа базы mydb, в сжатом виде
pg_dump -h localhost -p 5440 -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Бекап всех баз данных используя команду pg_dumpall.
pg_dumpall > all.sqlpsql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore — восстановление сжатых бекапов (tar);
Восстановление всего бекапа с игнорированием ошибок
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Выполняется под суперпользователем
#!/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
########################Выполняется под суперпользователем
#!/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 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
Смена пароля пользователя
ALTER USER user_name WITH PASSWORD 'new_password';Если ошибка доступа к базе "permission denied for schema public"
GRANT ALL ON DATABASE mydb TO admin;или
ALTER DATABASE mydb OWNER TO admin;
Список наиболее часто используемых опций:
-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 с списком имен свойств.
Пример скрипта добавления записи в базу через bash:
#!/bin/bash
# Параметры подключения к PostgreSQL
DB_NAME="email"
DB_USER="emailu" # замените на ваше имя пользователя
DB_PASS="emailp" # замените на ваш пароль
DB_HOST="localhost" # замените на хост, если нужно
DB_PORT="5432" # замените на порт, если нужно
# Функция для выполнения запроса к PostgreSQL
exec_psql() {
PGPASSWORD=$DB_PASS psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "$1"
}
# Выводим существующих пользователей
echo "Существующие ящики:"
exec_psql "SELECT \"Email\" FROM users;"
# Запрашиваем данные у пользователя
read -p "Введите название нового ящика: " username
read -sp "Введите пароль: " password
echo
# Хешируем пароль с помощью SHA-512
hashed_password=$(openssl passwd -6 "$password")
# Вставляем новую запись
insert_query="INSERT INTO users (\"DomainId\", \"Password\", \"Email\") VALUES (1, '$hashed_password', '$username');"
exec_psql "$insert_query"
# Проверяем результат
if [ $? -eq 0 ]; then
echo "Пользователь успешно добавлен!"
echo "Обновленный список пользователей:"
exec_psql "SELECT \"Email\" FROM users;"
else
echo "Ошибка при добавлении пользователя!"
fi
Бекап всех баз данных используя команду pg_dumpall.
pg_dumpall > all.sql
# проверка бекапа
grep
"^[\]connect" all.sql
\
connect
db1
\
connect
db2