http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [195]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2917]
PostgreSQL [103]
기타정보 [151]
운영체제 [584]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: PostgreSQL

 회원가입

psql [ 8.4.20 , 8.4.22 ] 여러 Version 운영, upgrade 8.4.20 to 8.4.22 [ 뒤 Version 다르면 그냥 시작 ]
최길호 [LIST]   2021-11-12 10:19:37, 조회 : 116

# 요약
8.4.x patch 사례 찾기 힘들어서 테스트 진행
psql S/W 위치 변수는 없다. /usr/bin/psql, /psql/8.4.22/bin/psql 각각 $PATH 변경 하면 된다.
socket 위치는 psql -h /tmp
default PORT는 S/W 설치 할때 정해진다. [ 변경 export PGPORT=5408  ]

주의사항
https://postgresql.kr/blog/pg_upgrade.html

# S/W, DB 위치 변경
export PATH=/psql/8.4.22/bin:$PATH
export PGDATA=/psql/8.4.22/data
psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5408"? ==> # S/W PGPORT: 5408

# DB 설치
initdb -D /psql/8.4.22/data

# 주석 풀어서 정확한 Port 정의
grep port /psql/8.4.22/data/postgresql.conf
port = 5408

# 서비스 시작
pg_ctl -D /psql/8.4.22/data -l /psql/8.4.22/data/logfile start
psql
SELECT version();
SHOW server_version;

# 낮은 Version 연결 테스트
# 8.4.20 [ /usr/bin/psql ] connect to 8.4.22 [ /psql/8.4.22 ]
/usr/bin/psql -p 5408
or
export PGPORT=5408
/usr/bin/psql

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5408"?

# socket 로그인 help
/usr/bin/psql --help | grep socket
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")

/usr/bin/psql -p 5408 -h /tmp
psql (8.4.20, server 8.4.22)

ps -ef | grep bin/post
postgres  9585     1  0 09:14 pts/1    00:00:00 /psql/8.4.22/bin/postgres -D /psql/8.4.22/data
postgres 23534     1  0 Nov04 ?        00:00:11 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data


## upgrade
## psql stop
/psql/8.4.22/bin/pg_ctl stop
/usr/bin/pg_ctl stop

rm -rf /psql/8.4.22/data
cp -r /var/lib/pgsql/data /psql/8.4.22/
grep ^port /psql/8.4.22/data/postgresql.conf
port = 5432

# for 8.4.22
export PATH=/psql/8.4.22/bin:$PATH
export PGDATA=/psql/8.4.22/data
export PGPORT=5432

pg_ctl start


### grep ^[a-z] postgresql.conf
listen_addresses = '*'  # what IP interface(s) to listen on;
port = 5432
max_connections = 500
shared_buffers = 4GB            # min 16, at least max_connections*2, 8KB each
work_mem = 119837               # min 64, size in KB
maintenance_work_mem = 1GB      # min 1024, size in KB
max_stack_depth = 2048          # min 100, size in KB
fsync =  off                    # turns forced synchronization on or off
wal_buffers = 16MB              # min 4, 8KB each
checkpoint_segments = 32        # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.7      # in logfile segments, min 1, 16MB each
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
effective_cache_size = 12GB     # typically 8KB each
default_statistics_target = 100 # range 1-1000
log_destination = 'stderr'      # Valid values are combinations of stderr,
log_directory = 'pg_log'    # Directory where log files are written.
log_filename = 'postgresql-%a.log' # Log file name pattern.
log_truncate_on_rotation = true  # If true, any existing log file of the
log_rotation_age = 1440     # Automatic rotation of logfiles will happen after
log_rotation_size = 0       # Automatic rotation of logfiles will happen after
logging_collector=true
client_min_messages = error     # Values, in order of decreasing detail:
log_min_messages = error        # Values, in order of decreasing detail:
log_min_duration_statement = 0 # -1 is disabled, in milliseconds.
log_duration = on
log_line_prefix = '%m %d %p %r' # e.g. '<%u%%%d> '
autovacuum = on         # enable autovacuum subprocess?
autovacuum_naptime = 60    # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 1000  # min # of tuple updates before
autovacuum_analyze_threshold = 500  # min # of tuple updates before
autovacuum_vacuum_scale_factor = 0.4   # fraction of rel size before
autovacuum_analyze_scale_factor = 0.2  # fraction of rel size before
autovacuum_vacuum_cost_delay = -1   # default vacuum cost delay for
autovacuum_vacuum_cost_limit = -1   # default vacuum cost limit for
lc_messages = 'en_US.UTF-8'             # locale for system error message strings
lc_monetary = 'ko_KR.UTF-8'             # locale for monetary formatting
lc_numeric = 'ko_KR.UTF-8'              # locale for number formatting
lc_time = 'ko_KR.UTF-8'                 # locale for time formatting

3.239.112.140


  LIST

제목 작성자 작성일 조회
Admin  odbc_fdw test    최길호 2021/11/22 139
Admin  ERROR: cross-database references are not implemented / postgres_fdw / psql dblink    최길호 2021/11/15 185
Admin  CREATE EXTENSION postgres_fdw; [ source install ]    최길호 2021/11/15 292
Admin  psql [ 8.4.20 , 8.4.22 ] 여러 Version 운영, upgrade 8.4.20 to 8.4.22 [ 뒤 Version 다르면 그냥 시작 ]    최길호 2021/11/12 116
Admin  source make install [ 8 ~ 14 ]    최길호 2021/11/11 171
Admin  psql odbc make install    최길호 2021/11/09 135
Admin  oracle to psql dblink    최길호 2021/11/09 155
Admin  /usr/bin/initdb -D /var/lib/pgsql/test [ Instance 추가 ]    최길호 2021/11/05 107
Troubleshoot  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?    최길호 2021/11/04 168
Troubleshoot  ERROR: current transaction is aborted, commands ignored until end of transaction block; [ postgresql-odbc upgrade ]    최길호 2021/11/04 183
Troubleshoot  pg_ctl start $PGDATA/pg_log/ 한글/영어 메세지 [ $PGDATA/postgresql.conf lc_messages ]    최길호 2021/10/29 205
Admin  pg_dumpall pg_upgrade    최길호 2021/10/21 149
Admin  통계정보    최길호 2021/10/21 124
Admin  pg_dump / pg_restore[ psql (8.4.20) to psql (14.0) ]    최길호 2021/10/20 170
Admin  PostgreSQL 14 / pgadmin Install [ lc_collate 'ko_KR.UTF-8' order by 정상 ]    최길호 2021/10/17 235
Admin  psql dblink to oracle [ odbc test ] 한글    최길호 2021/04/19 2499
Admin  postgres , enterprisedb(EDB) / [ ERROR: type "number" does not exist ]    최길호 2020/09/01 273
Admin  install postgresql on docker centos:7    최길호 2020/08/31 382
Troubleshoot  psql: server closed the connection unexpectedly [ vi $PGDATA/postgresql.conf / listen_addresses='*' ]    최길호 2020/08/31 413

    목록보기   다음페이지 1 [2][3][4][5][6]
       

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
11/23
[ORACLE]
acfsutil size: ACFS-03006: sma....
by 최길호
11/07
[운영체제]
Job for sshd.service failed be....
by 최길호
11/04
[ORACLE]
jsp error 위치 확인 [ debug ].
by 최길호