Практикум
Физическая реплика ция
-
Удалите БД
monman_db,ts_dbи табличное пространствоts_lab. Удалите каталог табличного пространства:postgres=# DROP DATABASE monman_db;
DROP DATABASE
postgres=# DROP DATABASE ts_db;
DROP DATABASE
postgres=# DROP TABLESPACE ts_lab;
DROP TABLESPACE
postgres=# \q
[postgres@p620 ~]$ rm -rf *.log ts_data/ -
Создайте слот физической репликации:
[postgres@p620 ~]$ psql
postgres=# SELECT pg_create_physical_replication_slot('rslot');
pg_create_physical_replication_slot
-------------------------------------
(rslot, )
(1 row)
postgres=# SELECT * FROM pg_replication_slots \gx
-[ RECORD 1 ]-----------+--------
slot_name | rslot
plugin |
slot_type | physical
dataoid |
database |
temporary | f
active | f
active_pid |
xmin |
catalog_xmin |
restart_lsn |
comfirmed_flush_lsn |
wal_status |
safe_wal_size |
two_phase | f -
Проверьте настройку
wal_levelи достаточность процессов и слотов репликации:postgres=# \dconfig (max_(*send|repl)*|wal_level)
List of configuration parameters
Parameter | Value
-----------------------+---------
max_replication_slots | 10
max_wal_senders | 10
wal_level | replica
(3 rows) -
Создайте физическую копию, используя слот репликации, а также автоматически создав настройку для сервера-реплики:
[postgres@p620 ~]$ pg_basebackup -c fast -S rslot -R -D repl
[postgres@p620 ~]$ ls -l
total 8
drwx------ 25 postgres postgres 4096 Nov 11 13:58 repl -
Перейдите в каталог
repl. Проверьте настройки в postgres*.conf и наличие файлаstandby.signal:[postgres@p620 ~]$ cd repl/
[postgres@p620 repl]$ ls
backup_label log pg_hba.conf.orig pg_multixact pg_prep_stats pg_snapshots
pg_tblspc pg_xact PRODUCT_VERSION
backup_manifest pg_commit_ts pg_ident.conf pg_notify pg_quota.conf pg_stat
pg_twophase postgresql.auto.conf standby.signal
base pg_dynshem pg_integrity pg_perf_insights pg_replslot pg_stat_tmp
PG_VERSION postgresql.conf tracing
global pg_hba.conf pg_logical pg_pp_cache pg_serial pg_subtrans pg_wal
postgresql.conf.orig
[postgres@p620 repl]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres passfile=''/var/lib/postgres/.pgpass'' channel_binding=prefer
port=5432 client_encoding=UTF8 sslmode=prefer sslcompression=0 sslsni=1
ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'rslot' -
Настройте реплику на использование порта
7432:[postgres@p620 repl]$ grep -n ^#*port postgresql.conf
64:#port = 5432 # (change requires restart)
[postgres@p620 repl]$ sed -i.bak '64s/^.*$/port = 7432/' postgresql.conf
[postgres@p620 repl]$ diff postgresql.conf.bak postgresql.conf 64c64
< #port = 5432 # (change requires restart)
---
> port = 7432Команда
sedзаменила в 64 строке текст наport = 7432. Старое содержимое файла вpostgresql.conf.bak. -
Запустите реплику:
[postgres@p620 ~]$ pg_ctl start -D ~/repl/ -l repl_srv.log waiting for server to start...... done
server started -
Проверьте журнал отчета реплики:
[postgres@p620 ~]$ cat repl_srv.log
2024-11-11 14:17:13.816 MSK [66908] LOG: License type is "Trial"
2024-11-11 14:17:13.816 MSK [66908] LOG: Licensee is "Educational license for VM with Pangolin"
2024-11-11 14:17:13.816 MSK [66908] LOG: License expire date is "2025-01-31 23:59:59.816912+03"
2024-11-11 14:17:13.816 MSK [66908] LOG: License CPUs number allowed: 2
2024-11-11 14:17:13.816 MSK [66908] LOG: License allowed memory in bytes: unrestricted
2024-11-11 14:17:13.817 MSK [66908] WARNING: postgres: Could not init KMS connection. Error: -52
(failed to lock file)
2024-11-11 14:17:13.817 MSK [66908] LOG: configuration file "/var/lib/postgres/repl/pg_quota.conf"
contains no entries
2024-11-11 14:17:13.938 MSK [66908] LOG: product version: Platform V Pangolin 6.2.0
2024-11-11 14:17:13.938 MSK [66908] LOG: product build info: build 69 (13:53:00 27.04.2024) commit
f8acb7233c42e22cd2b0767c435018b32a43476f
2024-11-11 14:17:13.938 MSK [66908] LOG: starting PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-6), 64-bit
2024-11-11 14:17:13.938 MSK [66908] LOG: listening on IPv4 address "0.0.0.0", port 7432
2024-11-11 14:17:13.938 MSK [66908] LOG: listening on IPv6 address "::", port 7432
2024-11-11 14:17:13.946 MSK [66908] LOG: listening on Unix socket "/tmp/.s.PGSQL.7432"
2024-11-11 14:17:13.966 MSK [66912] LOG: database system was interrupted; last known up at 2024-11-
11 13:58:47 MSK
2024-11-11 14:17:13.970 MSK [66913] LOG: idle terminator started
2024-11-11 14:17:16.022 MSK [66912] LOG: entering standby mode
2024-11-11 14:17:16.052 MSK [66912] LOG: redo starts at 0/AC000028
2024-11-11 14:17:16.077 MSK [66912] LOG: consistent recovery state reached at 0/AC000130
2024-11-11 14:17:16.077 MSK [66908] LOG: database system is ready to accept read-only connections
2024-11-11 14:17:16.086 MSK [66908] LOG: Start integrity check launcher
2024-11-11 14:17:16.088 MSK [66918] LOG: Start of integrity check
2024-11-11 14:17:16.094 MSK [66919] LOG: License checker started
2024-11-11 14:17:16.103 MSK [66917] LOG: started streaming WAL from primary at 0/AD000000 on
timeline 1 -
Проверьте, что к реплике можно обращаться с читающими запросами:
[postgres@p620 ~]$ psql -p 7432 -c 'select pg_is_in_recovery()' pg_is_in_recovery
-------------------
t
(1 row) -
Выполните на реплике запрос, выдающий список БД (просто выполните метакоманду
\l- она выполняет необходимый запрос):[postgres@p620 ~]$ psql -p 7432 -l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-------------------+----------------+-----------------+-------------------+-------------------+------------------------+---------------------------+-----------------------
koi8_db | postgres | KOI8R | ru_RU.koi8r | ru_RU.koi8r | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
py_json_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
student | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + postgres=CRc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + postgres=CRc/postgres
(6 rows) -
Подключитесь к реплике и попробуйте удалить БД
koi8_db:[postgres@p620 ~]$ psql -p 7432 psql (15.5)
Type "help" for help.
postgres=# DROP DATABASE koi8_db;
ERROR: cannot execute DROP DATABASE in a read-only transaction postgres=#Подключитесь к мастеру и удалите БД
koi8_dbиpy_json_db:postgres=# DROP DATABASE koi8_db;
ERROR: cannot execute DROP DATABASE in a read-only transaction
postgres=# \c - - - 5432
You are now connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# DROP DATABASE koi8_db; DROP DATABASE
postgres=# DROP DATABASE py_json_db ; DROP DATABASE -
Cнова подключитесь к р еплике и проверьте результат:
[postgres@p620 ~]$ psql -p 7432 -l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-------------------+----------------+-----------------+-------------------+-------------------+------------------------+---------------------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
student | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + postgres=CRc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + postgres=CRc/postgres
(4 rows) -
Проверьте на мастере состояние слота репликации:
postgres=# SELECT * FROM pg_replication_slots \gx
-[ RECORD 1 ]-------+-----------
slot_name | rslot
plugin |
slot_type | physical
datoid |
database |
temporary | f
active | t
active_pid | 66920
xmin |
catalog_xmin |
restart_lsn | 0/AD002648
confirmed_flush_lsn |
wal_status | reserved
safe_wal_size |
two_phase | f -
Проверьте состояние репликации:
postgres=# SELECT * FROM pg_replication_slots \gx
-[ RECORD 1 ]-------+-----------
pid | 66920
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr |
client_hostname |
client_port | -1
backend_start | 2024-11-11 14:17:16.098048+03
backend_xmin |
state | streaming|
sent_lsn | 0/AD002648
write_lsn | 0/AD002648
flush_lsn | 0/AD002648
replay_lsn | 0/AD002648
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2024-11-11 14:33:22.328724+03 -
Подключитесь к реплике. Повысьте реплику до мастера:
postgres=# \c - - - 7432
You are now connected to database "postgres" as user "postgres" via socket in "/tmp" at port "7432".
postgres=# SELECT pg_promote();
pg_promote
------------
t
(1 row)
postgres=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row) -
Подключившись к мастеру, проверьте состояние слота и репликации:
postgres=# SELECT * FROM pg_replication_slots \gx
-[ RECORD 1 ]-------+-----------
slot_name | rslot
plugin |
slot_type | physical
datoid |
database |
temporary | f
active | f
active_pid |
xmin |
catalog_xmin |
restart_lsn | 0/AD002648
confirmed_flush_lsn |
wal_status | reserved
safe_wal_size |
two_phase | f
postgres=# SELECT * FROM pg_stat_replication \gx
(0 rows)Репликация разорвана, бывшая реплика сейчас отдельный мастер. Старый мастер тоже в строю.
Логическая репликация
-
Подключитесь к серверу на порт 5432 и измените уровень
wal_levelнаlogical:postgres=# ALTER SYSTEM SET wal_level = logical ; ALTER SYSTEM -
Рестартуйте сервер:
[student@TERM1 ~]$ sudo systemctl restart postgresql -
Подключитесь к БД
postgres, создайте БДlrep_dbи подключитесь к ней, создайте в ней схемуstudent, принадлежащую ролиstudent:student=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE DATABASE lrepl_db; CREATE DATABASE
postgres=# \c lrepl_db
You are now connected to database "lrepl_db" as user "postgres".
lrepl_db=# CREATE SCHEMA student AUTHORIZATION student; CREATE SCHEMA -
Подключитесь ролью
student, удалите все таблицы и подчиненные объекты, принадлежащие ему:student=> SELECT schemaname, tablename FROM pg_tables WHERE tableowner = 'student';
schemaname | tablename
------------+-----------
public | stab
student | stab
public | fatab
(3 rows)
student=> DROP TABLE stab;
DROP TABLE
student=> DROP TABLE fatab;
DROP TABLE
student=> DROP TABLE stab;
DROP TABLE -
Создайте таблицу и опубликуйте ее:
student=> CREATE TABLE tab2lr(id int PRIMARY KEY, msg text);
CREATE TABLE
student=> CREATE PUBLICATION stud_pub FOR TABLE student.tab2lr;
CREATE PUBLICATION
student=> \x \dRp+ \x
Expanded display is on.
Publication stud_pub
-[ RECORD 1 ]-------
Owner | student
All tables | f
Inserts |t
Updates |t
Deletes |t
Truncates | t
Via root |f
Tables:
"student.tab2lr"
Expanded display is off. -
Заполните таблицу какими-либо данными:
student=> INSERT INTO tab2lr VALUES (1, 'Первая запись.'),(2, 'Вторая.'),(3, 'Теперь - третья.'); INSERT 0 3 -
Подключитесь суперпользователем на порт 5432 и выполните резервное копирование только схемы
studentв БДstudentна сервере, прослушивающем порт 7432:student=> \c - postgres /tmp 5432
You are now connected to database "student" as user "postgres" via socket in "/tmp" at port "5432".
student=# \! pg_dump -d student -n student -s | psql -d student -p 7432 SET
SET
SET
SET SET
set_config
------------
(1 row)
SET
SET
SET
SET
ERROR: schema "student" already exists
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE -
Подключитесь на порт 7432 и проверьте наличие пустой таблицы:
student=> \c - postgres /tmp 7432
You are now connected to database "student" as user "postgres" via socket in "/tmp" at port "7432".
student=# \d *.tab2lr
Table "student.tab2lr"
Column | Type | Collation | Nullable | Default
-------+---------+-----------+----------+---------
id | integer | | not null |
msg | text | | |
Indexes:
"tab2lr_pkey" PRIMARY KEY, btree (id)
student=# SELECT count(*) FROM student.tab2lr; count
------- 0
(1 row) -
Создайте подписку:
student=# CREATE SUBSCRIPTION stud_sub
student-# CONNECTION 'host=/tmp port=5432 dbname=student user=postgres' student-# PUBLICATION stud_pub;
NOTICE: created replication slot "stud_sub" on publisher
CREATE SUBSCRIPTION
student=# \x \dRs+ \x
Expanded display is on.
List of subscriptions
-[ RECORD 1 ]------+-------------------------------------------------
Name | stud_sub
Owner | postgres
Enabled | t
Publication | {stud_pub}
Binary | f
Streaming | f
Two-phase commit | d
Disable on error | f
Synchronous commit | off
Conninfo | host=/tmp port=5432 dbname=student user=postgres
Skip LSN | 0/0
Expanded display is off. -
Проверьте, выполнилась ли начальная синхронизация:
student=# SELECT * FROM student.tab2lr; id | msg
----+------------------
1 | Первая запись.
2 | Вторая.
3 | Теперь - третья.
(3 rows) -
Добавьте еще одну строку на сервере 5432 и проверьте на 7432:
student=# \! psql -d student -p 5432 -c "INSERT INTO student.tab2lr VALUES(4, 'Четвертая. Ну и хватит, пожалуй.')"
INSERT 0 1
student=# SELECT * FROM student.tab2lr; id | msg
--+----------------------------------
1 | Первая запись.
2 | Вторая.
3 | Теперь - третья.
4 | Четвертая. Ну и хватит, пожалуй.
(4 rows) -
Удалите подписку:
student=# DROP SUBSCRIPTION stud_sub;
NOTICE: dropped replication slot "stud_sub" on publisher DROP SUBSCRIPTION -
Удалите публикацию:
student=# \c - postgres /tmp 5432
You are now connected to database "student" as user "postgres" via socket in "/tmp" at port "5432".
student=# DROP PUBLICATION stud_pub ; DROP PUBLICATION
student=# \q -
Остановите сервер, прослушивающий порт 7432:
[postgres@p620 ~]$ pg_ctl stop -D ~/repl/ -l repl_srv.log waiting for server to shut down.... done
server stopped