アーカイブモードの確認
デフォルトでは archive_mode は OFF となっている
show archive_mode;
実行例
[postgres@hellomyworld data]$ psql -U hellouser hellodb
psql (12.6)
Type "help" for help.
hellodb=> show archive_mode;
archive_mode
--------------
off
(1 row)アーカイブモードの有効化
設定ファイルの編集
/var/lib/pgsql/12/data/postgresql.conf ファイルを編集する必要がある。
以下二つのパラメタを設定し、必要であればアーカイブ先のディレクトリも mkdir する。
archive_mode
archive_command
実行例
[postgres@hellomyworld data]$ pwd
/var/lib/pgsql/12/data
[postgres@hellomyworld data]$ vim postgresql.conf
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /var/lib/pgsql/12/arc/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
[postgres@hellomyworld data]$ mkdir /var/lib/pgsql/12/arc/
DBの再起動
設定を反映させるためには再起動が必須。
pg_ctl restart
実行例
[postgres@hellomyworld ~]$ /usr/pgsql-12/bin/pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-02-15 14:17:12.607 CST [16964] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-02-15 14:17:12.607 CST [16964] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-02-15 14:17:12.607 CST [16964] LOG: listening on IPv6 address "::", port 5432
2023-02-15 14:17:12.609 CST [16964] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-02-15 14:17:12.614 CST [16964] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-02-15 14:17:12.629 CST [16964] LOG: redirecting log output to logging collector process
2023-02-15 14:17:12.629 CST [16964] HINT: Future log output will appear in directory "log".
done
server started
[postgres@hellomyworld arc]$ psql -U postgres
psql (12.6)
Type "help" for help.
postgres=# show archive_mode;
archive_mode
--------------
on ★
(1 row)設定後確認
WALファイルのスイッチをしながら、ファイルが問題なく出力されるかを確認する。
SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());
SELECT pg_switch_wal();
実行例
postgres=# SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());
pg_walfile_name | pg_walfile_name
--------------------------+--------------------------
000000010000000000000004 | 000000010000000000000004
(1 row)
postgres=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/5000078
(1 row)
postgres=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/5000000
(1 row)
postgres=# SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());
pg_walfile_name | pg_walfile_name
--------------------------+--------------------------
000000010000000000000006 | 000000010000000000000007
(1 row)
[postgres@hellomyworld arc]$ ls -ltr
total 32768
-rw------- 1 postgres postgres 16777216 Mar 22 18:18 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Mar 22 18:18 000000010000000000000005
[postgres@hellomyworld arc]$ ls -ltr
total 65536
-rw------- 1 postgres postgres 16777216 Mar 22 18:18 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Mar 22 18:18 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Mar 22 18:18 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Mar 22 18:18 000000010000000000000007
