Postgres: различия между версиями

Материал из wiki.nntc.nnov.ru
Перейти к навигации Перейти к поиску
(Создадим 30 пользователей в формате userX)
(Пример подключения к базе данных с любого хоста)
 
(не показано 8 промежуточных версий этого же участника)
Строка 36: Строка 36:
 
docker run \
 
docker run \
 
--name postgres-srv-$i \
 
--name postgres-srv-$i \
 +
--restart always \
 
-e POSTGRES_USER="dbuser${i}" \
 
-e POSTGRES_USER="dbuser${i}" \
 
-e POSTGRES_PASSWORD="dbpass${i}" \
 
-e POSTGRES_PASSWORD="dbpass${i}" \
Строка 71: Строка 72:
 
docker rm postgres-srv-$i
 
docker rm postgres-srv-$i
 
done
 
done
 +
docker volume prune -f
 
</pre>
 
</pre>
  
Строка 110: Строка 112:
  
 
далее нужно будет ввести пароль
 
далее нужно будет ввести пароль
 +
 +
==pg_dump==
  
 
=Список из 30 хостов=
 
=Список из 30 хостов=
Строка 144: Строка 148:
 
psql -h localhost -p 54349 -U dbuser29 database29 # password=dbpass29
 
psql -h localhost -p 54349 -U dbuser29 database29 # password=dbpass29
 
psql -h localhost -p 54350 -U dbuser30 database30 # password=dbpass30
 
psql -h localhost -p 54350 -U dbuser30 database30 # password=dbpass30
 +
</pre>
 +
 +
=Ubuntu 22.04=
 +
 +
==Конфиги==
 +
 +
===Раз===
 +
 +
<pre>
 +
mv /etc/postgresql/14/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf.orig
 +
 +
cat << 'EOF' > /etc/postgresql/14/main/pg_hba.conf
 +
# Database administrative login by Unix domain socket
 +
local  all            postgres                                trust
 +
 +
# TYPE  DATABASE        USER            ADDRESS                METHOD
 +
 +
# "local" is for Unix domain socket connections only
 +
local  all            all                                    peer
 +
# IPv4 local connections:
 +
host    all            all            127.0.0.1/32            md5
 +
host    all            all            0.0.0.0/0 md5
 +
 +
# IPv6 local connections:
 +
#host    all            all            ::1/128                scram-sha-256
 +
# Allow replication connections from localhost, by a user with the
 +
# replication privilege.
 +
 +
local  replication    all                                    peer
 +
host    replication    all            127.0.0.1/32            scram-sha-256
 +
host    replication    all            ::1/128                scram-sha-256
 +
EOF
 +
 +
</pre>
 +
 +
===Два===
 +
<pre>
 +
mv /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf.orig
 +
 +
cat << 'EOF' > /etc/postgresql/14/main/postgresql.conf
 +
data_directory = '/var/lib/postgresql/14/main'          # use data in another directory
 +
hba_file = '/etc/postgresql/14/main/pg_hba.conf'        # host-based authentication file
 +
ident_file = '/etc/postgresql/14/main/pg_ident.conf'    # ident configuration file
 +
external_pid_file = '/var/run/postgresql/14-main.pid'  # write an extra PID file
 +
listen_addresses = '*' # what IP address(es) to listen on;
 +
max_connections = 100 # (change requires restart)
 +
shared_buffers = 128MB # min 128kB
 +
dynamic_shared_memory_type = posix # the default is usually the first option
 +
max_wal_size = 1GB
 +
min_wal_size = 80MB
 +
log_destination = 'stderr' # Valid values are combinations of
 +
logging_collector = on # Enable capturing of stderr, jsonlog,
 +
log_directory = 'log' # directory where log files are written,
 +
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
 +
log_file_mode = 0600 # creation mode for log files,
 +
log_rotation_size = 10MB # Automatic rotation of logfiles will
 +
log_min_messages = warning # values in order of decreasing detail:
 +
log_checkpoints = off
 +
log_connections = off
 +
log_disconnections = off
 +
log_timezone = UTC
 +
datestyle = 'iso, mdy'
 +
timezone = UTC
 +
default_text_search_config = 'pg_catalog.english'
 +
EOF
 +
 +
</pre>
 +
 +
==Перезапуск systemd-сервиса postgresql==
 +
 +
systemctl restart postgresql
 +
 +
и не только...
 +
 +
systemctl status postgresql
 +
 +
systemctl stop postgresql
 +
 +
systemctl start postgresql
 +
 +
systemctl enable postgresql
 +
 +
systemctl disable postgresql
 +
 +
==Подготовить кодировки в базах данных==
 +
 +
<pre>
 +
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='postgres';";
 +
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template0';";
 +
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template1';";
 +
</pre>
 +
 +
 +
==Создать базу, пользователя, выдать привилегии==
 +
 +
<pre>
 +
psql -U postgres -c "CREATE USER app_user WITH ENCRYPTED PASSWORD 'app_passQwE123';"
 +
psql -U postgres -c "CREATE DATABASE app_db;"
 +
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='app_db';";
 +
psql -U postgres -c "GRANT ALL ON DATABASE app_db TO app_user;"
 +
psql -U postgres -c "ALTER DATABASE app_db OWNER TO app_user;"
 +
</pre>
 +
 +
==Создать доп. схему в базе, выдать привилегии пользователю на неё==
 +
 +
Важно!
 +
 +
Подключаемся пользователем postgres, но указываем базу данных, в которой хотим создать схему (app_db). И только из этого контекста выполняем запросы внутри параметра (-c "SQL запрос;").
 +
 +
<pre>
 +
psql -U postgres app_db -c "CREATE SCHEMA myschema1;"
 +
psql -U postgres app_db -c "GRANT ALL PRIVILEGES ON SCHEMA myschema1 TO pguser;"
 
</pre>
 
</pre>

Текущая версия на 10:17, 8 июня 2024

Развёртываем 30 контейнеров на Альт Рабочая станция 10

Обновим систему и установим docker

apt-get update
apt-get dist-upgrade
update-kernel
apt-get install docker-engine
systemctl enable --now docker

Включим ssh сервер

systemctl enable sshd
systemctl start sshd

Создадим 30 пользователей в формате userX

for i in `seq 1 30`; do adduser user$i; echo "user$i" | passwd --stdin user$i; done

Создадим скрипты развёртывания и уничтожения

cat /opt/create-x30-containers.sh
#!/bin/bash
rm /tmp/cred
port=54320

for i in `seq -w 1 30`; do

port=$((port+1))

docker run \
--name postgres-srv-$i \
--restart always \
-e POSTGRES_USER="dbuser${i}" \
-e POSTGRES_PASSWORD="dbpass${i}" \
-e POSTGRES_DB="database${i}" \
-p $port:5432 \
-d \
postgres:11
echo "psql -h localhost -p ${port} -U dbuser${i} database${i} # password=dbpass${i}" >> /tmp/cred
done

echo "Credentials: "
echo "-----"
cat /tmp/cred
echo "-----"


#install postgresql 14 client on alt workstation 10
#apt-get install postgresql14

#connect example from dockerhost
#psql -h localhost -p 54321 -U dbuser01 database01

#connect example from some host (where 172.16.228.138 -- remote address of dockerhost)
#psql -h 172.16.228.138 -p 54321 -U dbuser01 database01


cat /opt/destroy-x30-containers.sh 
#!/bin/bash
for i in `seq -w 1 30`; do
docker stop postgres-srv-$i
docker rm postgres-srv-$i
done
docker volume prune -f

Развёртывание и уничтожение

Создать 30 контейнеров

Войти на докерхост и выполнить команду

/opt/create-x30-containers.sh

В результате поднимутся 30 контейнеров с postgres сервером 14 версии. В файле /tmp/cred будут примеры команды для подключения для всех 30 штук (см. ниже раздел "Список из 30 хостов")

Сломать все контейнеры (полное уничтожение)

Войти на докерхост и выполнить команду (осторожно, она сразу сделает то, что вы хотели!)

/opt/destroy-x30-containers.sh

В результате все 30 контейнеров будут уничтожены. Вместе с данными обучающихся :-))))))

Примеры

Установить только клиент для postgresql 14 для Альт Рабочая станция 10 (или любого дистрибутива на базе P10)

apt-get install postgresql14

Пример подключения к базе данных непосредственно с docker хоста

psql -h localhost -p 54321 -U dbuser01 database01

далее нужно будет ввести пароль

Пример подключения к базе данных с любого хоста

Представим, что наш докерхост имеет ip адрес 172.16.228.138, тогда на машине, с которой мы собираемся подключаться, для подключения к базе данных команда будет такая:

psql -h 172.16.228.138 -p 54321 -U dbuser01 database01

далее нужно будет ввести пароль

pg_dump

Список из 30 хостов

psql -h localhost -p 54321 -U dbuser01 database01 # password=dbpass01
psql -h localhost -p 54322 -U dbuser02 database02 # password=dbpass02
psql -h localhost -p 54323 -U dbuser03 database03 # password=dbpass03
psql -h localhost -p 54324 -U dbuser04 database04 # password=dbpass04
psql -h localhost -p 54325 -U dbuser05 database05 # password=dbpass05
psql -h localhost -p 54326 -U dbuser06 database06 # password=dbpass06
psql -h localhost -p 54327 -U dbuser07 database07 # password=dbpass07
psql -h localhost -p 54328 -U dbuser08 database08 # password=dbpass08
psql -h localhost -p 54329 -U dbuser09 database09 # password=dbpass09
psql -h localhost -p 54330 -U dbuser10 database10 # password=dbpass10
psql -h localhost -p 54331 -U dbuser11 database11 # password=dbpass11
psql -h localhost -p 54332 -U dbuser12 database12 # password=dbpass12
psql -h localhost -p 54333 -U dbuser13 database13 # password=dbpass13
psql -h localhost -p 54334 -U dbuser14 database14 # password=dbpass14
psql -h localhost -p 54335 -U dbuser15 database15 # password=dbpass15
psql -h localhost -p 54336 -U dbuser16 database16 # password=dbpass16
psql -h localhost -p 54337 -U dbuser17 database17 # password=dbpass17
psql -h localhost -p 54338 -U dbuser18 database18 # password=dbpass18
psql -h localhost -p 54339 -U dbuser19 database19 # password=dbpass19
psql -h localhost -p 54340 -U dbuser20 database20 # password=dbpass20
psql -h localhost -p 54341 -U dbuser21 database21 # password=dbpass21
psql -h localhost -p 54342 -U dbuser22 database22 # password=dbpass22
psql -h localhost -p 54343 -U dbuser23 database23 # password=dbpass23
psql -h localhost -p 54344 -U dbuser24 database24 # password=dbpass24
psql -h localhost -p 54345 -U dbuser25 database25 # password=dbpass25
psql -h localhost -p 54346 -U dbuser26 database26 # password=dbpass26
psql -h localhost -p 54347 -U dbuser27 database27 # password=dbpass27
psql -h localhost -p 54348 -U dbuser28 database28 # password=dbpass28
psql -h localhost -p 54349 -U dbuser29 database29 # password=dbpass29
psql -h localhost -p 54350 -U dbuser30 database30 # password=dbpass30

Ubuntu 22.04

Конфиги

Раз

mv /etc/postgresql/14/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf.orig

cat << 'EOF' > /etc/postgresql/14/main/pg_hba.conf
# Database administrative login by Unix domain socket
local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0		md5

# IPv6 local connections:
#host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.

local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
EOF

Два

mv /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf.orig

cat << 'EOF' > /etc/postgresql/14/main/postgresql.conf
data_directory = '/var/lib/postgresql/14/main'          # use data in another directory
hba_file = '/etc/postgresql/14/main/pg_hba.conf'        # host-based authentication file
ident_file = '/etc/postgresql/14/main/pg_ident.conf'    # ident configuration file
external_pid_file = '/var/run/postgresql/14-main.pid'   # write an extra PID file
listen_addresses = '*'					# what IP address(es) to listen on;
max_connections = 100					# (change requires restart)
shared_buffers = 128MB					# min 128kB
dynamic_shared_memory_type = posix			# the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'				# Valid values are combinations of
logging_collector = on					# Enable capturing of stderr, jsonlog,
log_directory = 'log'					# directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'		# log file name pattern,
log_file_mode = 0600					# creation mode for log files,
log_rotation_size = 10MB				# Automatic rotation of logfiles will
log_min_messages = warning				# values in order of decreasing detail:
log_checkpoints = off
log_connections = off
log_disconnections = off
log_timezone = UTC
datestyle = 'iso, mdy'
timezone = UTC
default_text_search_config = 'pg_catalog.english'
EOF

Перезапуск systemd-сервиса postgresql

systemctl restart postgresql

и не только...

systemctl status postgresql
systemctl stop postgresql
systemctl start postgresql
systemctl enable postgresql
systemctl disable postgresql

Подготовить кодировки в базах данных

psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='postgres';";
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template0';";
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='template1';";


Создать базу, пользователя, выдать привилегии

psql -U postgres -c "CREATE USER app_user WITH ENCRYPTED PASSWORD 'app_passQwE123';"
psql -U postgres -c "CREATE DATABASE app_db;"
psql -U postgres -c "UPDATE pg_database SET encoding = pg_char_to_encoding('UTF8'), datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='app_db';";
psql -U postgres -c "GRANT ALL ON DATABASE app_db TO app_user;"
psql -U postgres -c "ALTER DATABASE app_db OWNER TO app_user;"

Создать доп. схему в базе, выдать привилегии пользователю на неё

Важно!

Подключаемся пользователем postgres, но указываем базу данных, в которой хотим создать схему (app_db). И только из этого контекста выполняем запросы внутри параметра (-c "SQL запрос;").

psql -U postgres app_db -c "CREATE SCHEMA myschema1;"
psql -U postgres app_db -c "GRANT ALL PRIVILEGES ON SCHEMA myschema1 TO pguser;"