DBMS: PostgreSQL 14.9 |
[목차여기]
초기생성 시 wal 경로 지정
initdb -D /pg_data -E'UTF8' --locale=en_US.utf8 --waldir=/pg_wal
또는 이미 생성된 기존 경로 변경 시
아래와 같은 순서로 작업 진행
1. pg_ctl stop
2. 신규 경로 디렉토리 생성(/pg_wal) 및 권한 부여
3. 기존 경로 파일 이동($PGDATA/pg_wal -> /pg_wal)
4. 심볼릭 링크 생성
4.1 기존 디렉토리 삭제 (rm -fr $PGDATA/pg_wal)
4.2 심볼릭 링크 생성 (ln -s /pg_wal $PGDATA/pg_wal)
5. pg_ctl start
초기 생성 시 WAL 경로 지정
initdb 명령으로 초기 생성 시 --waldir 옵션을 사용하여 WAL 경로를 지정할 수 있습니다
기본 경로인 $PGDATA/pg_wal 은 심볼릭 링크로 생성됩니다
[postgres@svr1 pg_wal]$ initdb -D /pg_data -E'UTF8' --locale=en_US.utf8 --waldir=/pg_wal
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pg_data ... ok
fixing permissions on existing directory /pg_wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /pg_data -l logfile start
[postgres@svr1 pg_wal]$
[postgres@svr1 pg_wal]$ pg_ctl start
waiting for server to start....2024-01-23 22:29:41.687 KST [11121] LOG: redirecting log output to logging collector process
2024-01-23 22:29:41.687 KST [11121] HINT: Future log output will appear in directory "log".
done
server started
[postgres@svr1 pg_wal]$ psql
psql (14.9)
Type "help" for help.
postgres=# select count(*) from pg_tables ;
count
-------
66
(1 row)
postgres=# exit
[postgres@svr1 pg_wal]$ cd $PGDATA
[postgres@svr1 pg_data]$ ls -al
total 76
drwx------ 19 postgres postgres 4096 Jan 23 22:29 .
dr-xr-xr-x. 21 root root 4096 Jan 23 19:38 ..
-rw------- 1 postgres postgres 3 Jan 23 22:29 PG_VERSION
drwx------ 5 postgres postgres 41 Jan 23 22:29 base
-rw------- 1 postgres postgres 30 Jan 23 22:29 current_logfiles
drwx------ 2 postgres postgres 4096 Jan 23 22:29 global
drwx------ 2 postgres postgres 32 Jan 23 22:29 log
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_commit_ts
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_dynshmem
-rw------- 1 postgres postgres 4789 Jan 23 22:29 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jan 23 22:29 pg_ident.conf
drwx------ 4 postgres postgres 68 Jan 23 22:29 pg_logical
drwx------ 4 postgres postgres 36 Jan 23 22:29 pg_multixact
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_notify
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_replslot
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_serial
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_snapshots
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_stat
drwx------ 2 postgres postgres 25 Jan 23 22:29 pg_stat_tmp
drwx------ 2 postgres postgres 18 Jan 23 22:29 pg_subtrans
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_tblspc
drwx------ 2 postgres postgres 6 Jan 23 22:29 pg_twophase
lrwxrwxrwx 1 postgres postgres 7 Jan 23 22:29 pg_wal -> /pg_wal <-- 심볼릭 링크로 생성됨
drwx------ 2 postgres postgres 18 Jan 23 22:29 pg_xact
-rw------- 1 postgres postgres 88 Jan 23 22:29 postgresql.auto.conf
-rw------- 1 postgres postgres 28760 Jan 23 22:29 postgresql.conf
-rw------- 1 postgres postgres 27 Jan 23 22:29 postmaster.opts
-rw------- 1 postgres postgres 90 Jan 23 22:29 postmaster.pid
[postgres@svr1 pg_data]$
기존 WAL 경로를 신규 경로로 변경
현재 WAL 경로 확인
postgresql.conf 파일 또는 pg_settings를 조회하여 data_directory 값을 확인합니다
예제처럼 /pg_data라면 /pg_data/pg_wal 경로가 WAL 경로입니다
postgres=# select name, setting from pg_settings where name like 'data_directory' ;
name | setting
----------------+----------
data_directory | /pg_data
WAL 경로의 파일들 확인
[postgres@svr1 ~]$ cd $PGDATA
[postgres@svr1 pg_data]$ ls -al
total 456
drwx------ 20 postgres postgres 4096 Jan 23 19:36 .
dr-xr-xr-x. 20 root root 4096 Jan 23 11:23 ..
-rw------- 1 postgres postgres 3 Jan 23 2024 PG_VERSION
-rw------- 1 postgres postgres 382776 Jan 23 19:36 autoprewarm.blocks
drwx------ 8 postgres postgres 84 Jan 30 2024 base
-rw------- 1 postgres postgres 37 Jan 23 16:36 current_logfiles
drwx------ 2 postgres postgres 4096 Jan 23 16:36 global
drwx------ 2 postgres postgres 143 Jan 23 16:36 log
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_commit_ts
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_dynshmem
-rw------- 1 postgres postgres 4926 Jan 30 2024 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jan 23 2024 pg_ident.conf
drwx------ 4 postgres postgres 68 Jan 23 19:37 pg_logical
drwx------ 4 postgres postgres 36 Jan 23 2024 pg_multixact
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_notify
drwx------ 2 postgres postgres 6 Jan 23 15:59 pg_replslot
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_serial
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_snapshots
drwx------ 2 postgres postgres 6 Jan 23 16:36 pg_stat
drwx------ 2 postgres postgres 151 Jan 23 19:39 pg_stat_tmp
drwx------ 2 postgres postgres 18 Jan 29 2024 pg_subtrans
drwx------ 2 postgres postgres 19 Jan 24 2024 pg_tblspc
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_twophase
drwx------ 3 postgres postgres 4096 Jan 23 19:37 pg_wal <-- 여기가 현재 WAL 경로
drwx------ 2 postgres postgres 18 Jan 23 2024 pg_xact
-rw------- 1 postgres postgres 88 Jan 23 2024 postgresql.auto.conf
-rw------- 1 postgres postgres 28983 Jan 23 16:27 postgresql.conf
-rw------- 1 postgres postgres 27 Jan 23 16:36 postmaster.opts
-rw------- 1 postgres postgres 81 Jan 23 16:36 postmaster.pid
-- pg_wal 디렉토리 조회
[postgres@svr1 pg_data]$ ls pg_wal
000000010000000600000085.00000028.backup 00000001000000060000008E 000000010000000600000097 0000000100000006000000A0
000000010000000600000086 00000001000000060000008F 000000010000000600000098 0000000100000006000000A1
000000010000000600000087 000000010000000600000090 000000010000000600000099 0000000100000006000000A2
000000010000000600000088 000000010000000600000091 00000001000000060000009A 0000000100000006000000A3
000000010000000600000089 000000010000000600000092 00000001000000060000009B 0000000100000006000000A4
00000001000000060000008A 000000010000000600000093 00000001000000060000009C archive_status
00000001000000060000008B 000000010000000600000094 00000001000000060000009D
00000001000000060000008C 000000010000000600000095 00000001000000060000009E
00000001000000060000008D 000000010000000600000096 00000001000000060000009F반응형
1. postgres stop
[postgres@svr1 pg_data]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
2. 신규 경로 디렉토리 생성(/pg_wal) 및 권한 부여
신규 디렉토리 생성 후 기존과 동일하게 권한 부여 합니다
[root@svr1 ~]# mkdir /pg_wal
[root@svr1 ~]# chown postgres:postgres /pg_wal
[root@svr1 ~]# cd /
[root@svr1 /]# ls -al
합계 40
dr-xr-xr-x. 21 root root 4096 1월 23 19:40 .
dr-xr-xr-x. 21 root root 4096 1월 23 19:40 ..
-rw-r--r-- 1 root root 0 1월 19 10:51 .autorelabel
lrwxrwxrwx. 1 root root 7 10월 9 2021 bin -> usr/bin
dr-xr-xr-x. 5 root root 4096 1월 19 08:45 boot
drwxr-xr-x 19 root root 3340 1월 23 19:38 dev
drwxr-xr-x. 144 root root 8192 1월 23 11:31 etc
drwxr-xr-x. 4 root root 35 1월 19 09:03 home
lrwxrwxrwx. 1 root root 7 10월 9 2021 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 10월 9 2021 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 10월 9 2021 media
drwxr-xr-x. 2 root root 6 10월 9 2021 mnt
drwxr-xr-x. 2 root root 6 10월 9 2021 opt
drwx------ 20 postgres postgres 4096 1월 23 19:39 pg_data
drwxr-xr-x 2 postgres postgres 6 1월 19 10:52 pg_logs
drwx------ 3 postgres postgres 29 1월 24 2024 pg_temp
drwxr-xr-x 2 postgres postgres 6 1월 23 19:40 pg_wal
dr-xr-xr-x 292 root root 0 1월 23 13:49 proc
dr-xr-x---. 16 root root 4096 1월 23 14:14 root
drwxr-xr-x 41 root root 1220 1월 23 18:07 run
lrwxrwxrwx. 1 root root 8 10월 9 2021 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 10월 9 2021 srv
dr-xr-xr-x 13 root root 0 1월 23 13:49 sys
drwxrwxrwt. 13 root root 4096 1월 23 19:39 tmp
drwxr-xr-x. 14 root root 174 1월 19 10:11 usr
drwxr-xr-x. 21 root root 4096 1월 19 10:51 var
3. 기존 경로 파일 이동($PGDATA/pg_wal -> /pg_wal)
[postgres@svr1 ~]$ cd $PGDATA/pg_wal
[postgres@svr1 pg_wal]$ ls
000000010000000600000085.00000028.backup 00000001000000060000008E 000000010000000600000096 00000001000000060000009E
000000010000000600000087 00000001000000060000008F 000000010000000600000097 00000001000000060000009F
000000010000000600000088 000000010000000600000090 000000010000000600000098 0000000100000006000000A0
000000010000000600000089 000000010000000600000091 000000010000000600000099 0000000100000006000000A1
00000001000000060000008A 000000010000000600000092 00000001000000060000009A 0000000100000006000000A2
00000001000000060000008B 000000010000000600000093 00000001000000060000009B 0000000100000006000000A3
00000001000000060000008C 000000010000000600000094 00000001000000060000009C 0000000100000006000000A4
00000001000000060000008D 000000010000000600000095 00000001000000060000009D archive_status
[postgres@svr1 pg_wal]$ mv * /pg_wal/
[postgres@svr1 pg_wal]$ ls
[postgres@svr1 pg_wal]$ cd ..
[postgres@svr1 pg_data]$ ls -al
total 448
drwx------ 20 postgres postgres 4096 Jan 23 19:39 .
dr-xr-xr-x. 21 root root 4096 Jan 23 19:40 ..
-rw------- 1 postgres postgres 3 Jan 23 2024 PG_VERSION
-rw------- 1 postgres postgres 382776 Jan 23 19:39 autoprewarm.blocks
drwx------ 8 postgres postgres 84 Jan 30 2024 base
-rw------- 1 postgres postgres 37 Jan 23 16:36 current_logfiles
drwx------ 2 postgres postgres 4096 Jan 23 16:36 global
drwx------ 2 postgres postgres 143 Jan 23 16:36 log
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_commit_ts
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_dynshmem
-rw------- 1 postgres postgres 4926 Jan 30 2024 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jan 23 2024 pg_ident.conf
drwx------ 4 postgres postgres 68 Jan 23 19:39 pg_logical
drwx------ 4 postgres postgres 36 Jan 23 2024 pg_multixact
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_notify
drwx------ 2 postgres postgres 6 Jan 23 15:59 pg_replslot
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_serial
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_snapshots
drwx------ 2 postgres postgres 153 Jan 23 19:39 pg_stat
drwx------ 2 postgres postgres 6 Jan 23 19:39 pg_stat_tmp
drwx------ 2 postgres postgres 18 Jan 29 2024 pg_subtrans
drwx------ 2 postgres postgres 19 Jan 24 2024 pg_tblspc
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_twophase
drwx------ 2 postgres postgres 6 Jan 23 19:40 pg_wal
drwx------ 2 postgres postgres 18 Jan 23 2024 pg_xact
-rw------- 1 postgres postgres 88 Jan 23 2024 postgresql.auto.conf
-rw------- 1 postgres postgres 28983 Jan 23 16:27 postgresql.conf
-rw------- 1 postgres postgres 27 Jan 23 16:36 postmaster.opts
[postgres@svr1 pg_data]$
4. 심볼릭 링크 생성
기존 디렉토리 제거
-- 기존 WAL 디렉토리 제거
[postgres@svr1 pg_data]$ rm -fr $PGDATA/pg_wal
[postgres@svr1 pg_data]$ ls -al
total 448
drwx------ 19 postgres postgres 4096 Jan 23 19:45 .
dr-xr-xr-x. 21 root root 4096 Jan 23 19:40 ..
-rw------- 1 postgres postgres 3 Jan 23 2024 PG_VERSION
-rw------- 1 postgres postgres 382776 Jan 23 19:39 autoprewarm.blocks
drwx------ 8 postgres postgres 84 Jan 30 2024 base
-rw------- 1 postgres postgres 37 Jan 23 16:36 current_logfiles
drwx------ 2 postgres postgres 4096 Jan 23 16:36 global
drwx------ 2 postgres postgres 143 Jan 23 16:36 log
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_commit_ts
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_dynshmem
-rw------- 1 postgres postgres 4926 Jan 30 2024 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jan 23 2024 pg_ident.conf
drwx------ 4 postgres postgres 68 Jan 23 19:39 pg_logical
drwx------ 4 postgres postgres 36 Jan 23 2024 pg_multixact
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_notify
drwx------ 2 postgres postgres 6 Jan 23 15:59 pg_replslot
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_serial
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_snapshots
drwx------ 2 postgres postgres 153 Jan 23 19:39 pg_stat
drwx------ 2 postgres postgres 6 Jan 23 19:39 pg_stat_tmp
drwx------ 2 postgres postgres 18 Jan 29 2024 pg_subtrans
drwx------ 2 postgres postgres 19 Jan 24 2024 pg_tblspc
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_twophase
drwx------ 2 postgres postgres 18 Jan 23 2024 pg_xact
-rw------- 1 postgres postgres 88 Jan 23 2024 postgresql.auto.conf
-rw------- 1 postgres postgres 28983 Jan 23 16:27 postgresql.conf
-rw------- 1 postgres postgres 27 Jan 23 16:36 postmaster.opts
신규 경로(/pg_wal)를 기존 경로($PGDATA/pg_wal)에 심볼릭 링크로 생성합니다
[postgres@svr1 pg_data]$ ln -s /pg_wal $PGDATA/pg_wal
[postgres@svr1 pg_data]$ ls -al
total 448
drwx------ 19 postgres postgres 4096 Jan 23 19:45 .
dr-xr-xr-x. 21 root root 4096 Jan 23 19:40 ..
-rw------- 1 postgres postgres 3 Jan 23 2024 PG_VERSION
-rw------- 1 postgres postgres 382776 Jan 23 19:39 autoprewarm.blocks
drwx------ 8 postgres postgres 84 Jan 30 2024 base
-rw------- 1 postgres postgres 37 Jan 23 16:36 current_logfiles
drwx------ 2 postgres postgres 4096 Jan 23 16:36 global
drwx------ 2 postgres postgres 143 Jan 23 16:36 log
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_commit_ts
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_dynshmem
-rw------- 1 postgres postgres 4926 Jan 30 2024 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jan 23 2024 pg_ident.conf
drwx------ 4 postgres postgres 68 Jan 23 19:39 pg_logical
drwx------ 4 postgres postgres 36 Jan 23 2024 pg_multixact
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_notify
drwx------ 2 postgres postgres 6 Jan 23 15:59 pg_replslot
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_serial
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_snapshots
drwx------ 2 postgres postgres 153 Jan 23 19:39 pg_stat
drwx------ 2 postgres postgres 6 Jan 23 19:39 pg_stat_tmp
drwx------ 2 postgres postgres 18 Jan 29 2024 pg_subtrans
drwx------ 2 postgres postgres 19 Jan 24 2024 pg_tblspc
drwx------ 2 postgres postgres 6 Jan 23 2024 pg_twophase
lrwxrwxrwx 1 postgres postgres 7 Jan 23 19:45 pg_wal -> /pg_wal <-- 심볼릭 링크
drwx------ 2 postgres postgres 18 Jan 23 2024 pg_xact
-rw------- 1 postgres postgres 88 Jan 23 2024 postgresql.auto.conf
-rw------- 1 postgres postgres 28983 Jan 23 16:27 postgresql.conf
-rw------- 1 postgres postgres 27 Jan 23 16:36 postmaster.opts
[postgres@svr1 pg_data]$ ls pg_wal
000000010000000600000085.00000028.backup 00000001000000060000008E 000000010000000600000096 00000001000000060000009E
000000010000000600000087 00000001000000060000008F 000000010000000600000097 00000001000000060000009F
000000010000000600000088 000000010000000600000090 000000010000000600000098 0000000100000006000000A0
000000010000000600000089 000000010000000600000091 000000010000000600000099 0000000100000006000000A1
00000001000000060000008A 000000010000000600000092 00000001000000060000009A 0000000100000006000000A2
00000001000000060000008B 000000010000000600000093 00000001000000060000009B 0000000100000006000000A3
00000001000000060000008C 000000010000000600000094 00000001000000060000009C 0000000100000006000000A4
00000001000000060000008D 000000010000000600000095 00000001000000060000009D archive_status
5. pg_ctl start
[postgres@svr1 pg_data]$ pg_ctl start
waiting for server to start....2024-01-23 19:45:32.346 KST [8709] LOG: redirecting log output to logging collector process
2024-01-23 19:45:32.346 KST [8709] HINT: Future log output will appear in directory "log".
done
server started
[postgres@svr1 pg_data]$ psql
psql (14.9)
Type "help" for help.
postgres=# select count(*) from pg_tables ;
count
-------
70
(1 row)
-- 현재 WAL 파일
postgres=# select pg_walfile_name(pg_current_wal_lsn()) ;
pg_walfile_name
--------------------------
000000010000000600000087
(1 row)
-- WAL 경로 파일들 확인
postgres=# select * from pg_ls_waldir() ;
name | size | modification
------------------------------------------+----------+------------------------
000000010000000600000085.00000028.backup | 341 | 2024-01-23 19:33:30+09
000000010000000600000087 | 16777216 | 2024-01-23 19:45:47+09
000000010000000600000088 | 16777216 | 2024-01-30 20:21:25+09
000000010000000600000089 | 16777216 | 2024-01-30 20:21:26+09
00000001000000060000008A | 16777216 | 2024-01-30 20:21:27+09
00000001000000060000008B | 16777216 | 2024-01-30 20:21:27+09
00000001000000060000008C | 16777216 | 2024-01-30 20:21:28+09
00000001000000060000008D | 16777216 | 2024-01-30 20:21:29+09
00000001000000060000008E | 16777216 | 2024-01-30 20:21:29+09
00000001000000060000008F | 16777216 | 2024-01-30 20:21:30+09
000000010000000600000090 | 16777216 | 2024-01-30 20:21:31+09
000000010000000600000091 | 16777216 | 2024-01-30 20:21:33+09
000000010000000600000092 | 16777216 | 2024-01-30 20:21:36+09
000000010000000600000093 | 16777216 | 2024-01-30 20:21:40+09
000000010000000600000094 | 16777216 | 2024-01-30 20:21:41+09
000000010000000600000095 | 16777216 | 2024-01-30 20:21:41+09
000000010000000600000096 | 16777216 | 2024-01-30 20:21:42+09
000000010000000600000097 | 16777216 | 2024-01-30 20:21:43+09
000000010000000600000098 | 16777216 | 2024-01-30 20:21:44+09
000000010000000600000099 | 16777216 | 2024-01-30 20:21:45+09
00000001000000060000009A | 16777216 | 2024-01-30 20:21:49+09
00000001000000060000009B | 16777216 | 2024-01-30 20:21:50+09
00000001000000060000009C | 16777216 | 2024-01-30 20:21:53+09
00000001000000060000009D | 16777216 | 2024-01-30 20:21:54+09
00000001000000060000009E | 16777216 | 2024-01-30 20:21:56+09
00000001000000060000009F | 16777216 | 2024-01-30 20:21:58+09
0000000100000006000000A0 | 16777216 | 2024-01-30 20:22:01+09
0000000100000006000000A1 | 16777216 | 2024-01-30 20:22:05+09
0000000100000006000000A2 | 16777216 | 2024-01-30 20:21:24+09
0000000100000006000000A3 | 16777216 | 2024-01-30 20:22:10+09
0000000100000006000000A4 | 16777216 | 2024-01-30 20:23:34+09
(31 rows)'DBMS > PostgreSQL' 카테고리의 다른 글
| [PostgreSQL] ERROR: current transaction is aborted 에러 해결 법 (0) | 2024.04.07 |
|---|---|
| [PostgreSQL] pg_dump 파일에서 테이블명 변경하여 복구 (0) | 2024.03.12 |
| [PostgreSQL] auto_explain 사용/미사용 성능 테스트 (0) | 2024.02.29 |
| [PostgreSQL] 기본 권한 설정 default privilege(권한 자동 부여) (0) | 2024.02.15 |
| [PostgreSQL] pg_hba.conf 우선순위 (2) | 2024.02.01 |
댓글