Практикум

Логическая копия

1. Получите резервную копию таблицы fatab БД monman_db в формате по умолчанию:

   postgres=# \q
   [postgres@p620 ~]$ pg_dump -d monman_db -t fatab > /tmp/fatab.sql

2. Проверьте, включены ли ограничения целостности для таблицы в логическую копию и есть ли информация об индексе:

   [postgres@p620 ~]$ egrep -in '(index|constraint)' /tmp/fatab.sql
   100082:-- Name: fatab fatab_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
   100086: ADD CONSTRAINT fatab_pkey PRIMARY KEY (id);
   100090:-- Name: fatab_nme_idx; Type: INDEX; Schema: public; Owner: postgres
   100093:CREATE INDEX fatab_nme_idx ON public.fatab USING btree (nme);

3. Сделайте резервную копию этой же таблицы в формате custom. Сравните размеры файлов резервных копий:

   [postgres@p620 ~]$ ls -lh /tmp/fatab.*
   -rw-r--r-- 1 postgres postgres 671K Nov 11 10:44 /tmp/fatab.dump -rw-r--r-- 1 postgres postgres 1.9M Nov 11 10:39 /tmp/fatab.sql

4. Определите, какая роль в логической копии установлена для таблицы и подчиненных объектов в качестве владельца:

   [postgres@p620 ~]$ egrep -in 'owner to' /tmp/fatab.sql 35:ALTER TABLE public.fatab OWNER TO postgres; 50:ALTER TABLE public.fatab_id_seq OWNER TO postgres;

5. Задача восстановить таблицу fatab в БД student, осуществив восстановление ролью student:

   [student@p620 ~]$ sed 's/^\(.*OWNER TO postgres;$\)/-- \1/' /tmp/fatab.sql | psql SET
   SET
   SET
   SET
   SET
   set_config
   ------------
   (1 row)
   SET
   SET
   SET
   SET
   SET
   SET
   CREATE TABLE
   CREATE SEQUENCE
   ALTER SEQUENCE
   ALTER TABLE
   COPY 100000
   setval
   --------
   100000
   (1 row)
   ALTER TABLE
   CREATE INDEX

   Поскольку логическая копия в формате по умолчанию - это просто текстовый скрипт SQL, то в нем вполне допускается производить правки с помощью текстовых утилит. Например, можно закомментировать команды установки владельцев на объекты, как это сделано в примере с помощью sed, где указанная команда ставит комментарии (--) перед строками, содержащими строку OWNER TO postgres; .

   Справедливости ради, следует сказать, что восстановление прошло бы и без обработки текста. Просто были бы получены сообщения об ошибке при выполнении команд установки владельца, но таблица и данные были бы восстановлены.

6. Сделайте резервную копию всей БД monman_db в каталожном формате с двумя параллельными процессами:

   [postgres@p620 ~]$ pg_dump -cC -d monman_db -Fd -j2 -f /tmp/monman.dump
   
   [postgres@p620 ~]$ ls -ld /tmp/monman.dump
   drwx------ 2 postgres postgres 4096 Nov 11 11:03 /tmp/monman.dump
   
   [postgres@p620 ~]$ ls -l /tmp/monman.dump
   total 1128
   -rw-r--r-- 1 postgres postgres 682752 Nov 11 11:03 4398.dat.gz
   -rw-r--r-- 1 postgres postgres 118507 Nov 11 11:03 4399.dat.gz
   -rw-r--r-- 1 postgres postgres 100 Nov 11 11:03 4400.dat.gz
   -rw-r--r-- 1 postgres postgres 333365 Nov 11 11:03 4401.dat.gz
   -rw-r--r-- 1 postgres postgres 37 Nov 11 11:03 4402.dat.gz
   -rw-r--r-- 1 postgres postgres 6478 Nov 11 11:03 toc.dat
   [postgres@p620 ~]$ du -sh /tmp/monman.dump
   1.2M /tmp/monman.dump

   В команде pg_dump были установлены опции:

   • -c - удалять объекты БД перед их восстановлением;
   • -C - включить в логическую копию команду создания БД;
   • -d - БД для резервного копирования;
   • -Fd - формат резервной копии в виде каталога;
   • -j2 - два процесса резервного копирования в параллель;
   • -f - имя целевого каталога для запись в него резервной копии.

   Команда ls -l в примере использовалась для демонстрации того, что результатом такого копирования стал каталог с содержимым - резервной копией.

7. Получите список содержимого логической копии:

   [postgres@p620 ~]$ pg_restore -l /tmp/monman.dump ;
   ; Archive created at 2024-11-11 11:03:29 MSK
   ;     dbname: monman_db
   ;     TOC Entries: 25
   ;     Compression: -1
   ;     Dump Version: 1.14-0
   ;     Format: DIRECTORY
   ;     Integer: 4 bytes
   ;     Offset: 8 bytes
   ;     Dumped from database version: 15.5
   ;     Dumped by pg_dump version: 15.5
   ;
   ;
   ; Selected TOC Entries:
   ;
   2; 3079 16392 EXTENSION - plpython3u
   4409; 0 0 COMMENT - EXTENSION plpython3u
   240; 1259 16457 TABLE public fatab postgres
   239; 1259 16456 SEQUENCE public fatab_id_seq postgres
   4410; 0 0 SEQUENCE OWNED BY public fatab_id_seq postgres
   243; 1259 16488 TABLE public pgbench_accounts postgres
   244; 1259 16491 TABLE public pgbench_branches postgres
   241; 1259 16482 TABLE public pgbench_history postgres
   242; 1259 16485 TABLE public pgbench_tellers postgres
   4236; 2604 16460 DEFAULT public fatab id postgres
   4398; 0 16457 TABLE DATA public fatab postgres
   4401; 0 16488 TABLE DATA public pgbench_accounts postgres
   4402; 0 16491 TABLE DATA public pgbench_branches postgres
   4399; 0 16482 TABLE DATA public pgbench_history postgres
   4400; 0 16485 TABLE DATA public pgbench_tellers postgres
   4411; 0 0 SEQUENCE SET public fatab_id_seq postgres
   4239; 2606 16462 CONSTRAINT public fatab fatab_pkey postgres
   4243; 2606 16503 CONSTRAINT public pgbench_accounts pgbench_accounts_pkey postgres
   4245; 2606 16499 CONSTRAINT public pgbench_branches pgbench_branches_pkey postgres
   4241; 2606 16501 CONSTRAINT public pgbench_tellers pgbench_tellers_pkey postgres
   4237; 1259 16475 INDEX public fatab_nme_idx postgres

8. Удалите БД monman_db (командой DROP DATABASE monman_db) и восстановите ее из копии:

   [postgres@p620 ~]$ pg_restore -d postgres -C -Fd -j2 /tmp/monman.dump
   [postgres@p620 ~]$ psql monman_db
   psql (15.5)
   Type "help" for help.
   monman_db=# \d
                  List of relations
   Schema  |       Name       |   Type   |  Owner
   --------+------------------+----------+----------
   public  | fatab            | table    | postgres
   public  | fatab_id_seq     | sequence | postgres
   public  | pgbench_accounts | table    | postgres
   public  | pgbench_branches | table    | postgres
   public  | pgbench_history  | table
   public  | pgbench_tellers  | table
   (6 rows)
   monman_db=# \q [postgres@p620 ~]$

   База данных восстановлена.

Физическая копия

1. Предоставьте права на выполнение любых команд с таблицей fatab в базе данных monman_db роли student:

   monman_db=# GRANT ALL ON fatab TO student ;
   GRANT
   monman_db=# \dp+ fatab
                                    Access privileges
   Schema  | Name  | Type  |     Access privileges     | Column privileges | Policies
   --------+-------+-------+---------------------------+-------------------+----------
   public  | fatab | table | postgres=arwdDxt/postgres+|                   |
           |       |       | student=arwdDxt/postgres  |                   |
   (1 row)

2. В сеансе student запустите скрипт, изменяющий данные в таблице fatab. Например, можно с помощью метакоманды \watch раз в 10 секунд выполнять массовое обновление в таблице. Завершить работу \watch можно нажатием Ctrl+C:

   [student@TERM1 ~]$ psql monman_db
   psql (15.5)
   Type "help" for help.
   
   student@monman_db=> UPDATE fatab SET dte = current_date + id;
   UPDATE 100000
   
   student@monman_db=> \p
   UPDATE fatab SET dte = current_date + id;
   
   student@monman_db=> \watch 10

3. В сеансе пользователя ОС postgres выполните физическое копирование кластера:

   [postgres@p620 ~]$ pg_basebackup -c fast --tablespace-mapping=${PWD}/ts_data=${PWD}/ts_new -D ~/base_backup
   
   [postgres@p620 ~]$ ls -l
   total 12
   drwx------ 25 postgres postgres 4096 Nov 11 12:19 base_backup
   drwx------ 3 postgres postgres 4096 Nov 10 08:39 ts_data
   drwx------ 3 postgres postgres 4096 Nov 11 12:19 ts_new

   В команде pg_basebackup выполнена быстрая контрольная точка, что задано опцией -c fast. Также выполнено отображение каталога табличного пространства ts_data на ts_new. Резервная копия представлена каталогами base_backup и ts_new - табличное пространство для копии.

4. Остановите работу \watch и остановите кластер:

   ^C
   student@monman_db=> \q
   [student@TERM1 ~]$ sudo systemctl stop postgresql

5. Проверьте наличие символьной ссылки на каталог копии табличного пространства:

   [postgres@p620 ~]$ ls -l base_backup/pg_tblspc/
   total 0
   lrwxrwxrwx 1 postgres postgres 24 Nov 11 12:19 16431 -> /var/lib/postgres/ts_new

6. Проверьте работоспособность физической копии:

   [postgres@p620 ~]$ pg_ctl start -D ~/base_backup/ -l backup_srv.log waiting for server to start.... done
   server started
   [postgres@p620 ~]$ psql monman_db psql (15.5)
   Type "help" for help.
   monman_db=# SELECT count(*) FROM fatab; count
   --------
   100000
   (1 row)
   monman_db=# \q
   [postgres@p620 ~]$ pg_ctl stop -D ~/base_backup/ -l backup_srv.log waiting for server to shut down.... done
   server stopped

7. Удалите копию и запустите исходный кластер. В сеансе пользователя ОС postgres:

   [postgres@p620 ~]$ ls
   backup_srv.log base_backup ts_data ts_new [postgres@p620 ~]$ rm -rf base_backup/ ts_new/

   В сеансе student:

   [student@TERM1 ~]$ sudo systemctl start postgresql
   [student@TERM1 ~]$ psql -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                      |
       monman_db      | postgres       | UTF8            | en_US.UTF-8       | en_US.UTF-8       |                        | 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
           ts_db      | postgres       | UTF8            | en_US.UTF-8       | en_US.UTF-8       |                        | libc                      |
   (8 rows)