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

 회원가입

PostgreSQL 14 / pgadmin Install [ lc_collate 'ko_KR.UTF-8' order by 정상 ]
최길호 [LIST]   2021-10-17 23:36:39, 조회 : 235

#PostgreSQL
https://www.postgresql.org/
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

# 설치 파일 download
wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y pgdg-redhat-repo-latest.noarch.rpm
yumdownloader --resolve postgresql14-server
rpm -Uvh postgresql14-libs-14.0-1PGDG.rhel7.x86_64.rpm postgresql14-14.0-1PGDG.rhel7.x86_64.rpm postgresql14-server-14.0-1PGDG.rhel7.x86_64.rpm

# 방화벽 설정
systemctl status firewalld.service
firewall-cmd --permanent --zone=public --add-port=5432/tcp
firewall-cmd --permanent --zone=public --add-port=80/tcp
firewall-cmd --reload
firewall-cmd --list-ports

# pgadmin
https://www.pgadmin.org/
rpm -e pgadmin4-redhat-repo
rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
# Install for both desktop and web modes.
yum install pgadmin4
# Install for desktop mode only. # yum install pgadmin4-desktop
# Install for web mode only.     # yum install pgadmin4-web
/usr/pgadmin4/bin/setup-web.sh
Email address:
Password:
Retype password:
The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4

# /usr/pgsql-14/bin  PATH추가
su - postgres
echo export PATH=/usr/pgsql-14/bin:\$PATH >> .bash_profile

. .bash_profile

vi $PGDATA/postgresql.conf
listen_addresses = '*'
vi $PGDATA/pg_hba.conf
host    all             all             0.0.0.0/0               md5
pg_ctl reload
# 재시작 해야 적용
pg_ctl stop
pg_ctl start

psql [dbname] [user] -h hostname -p 5432
psql admin admin -h localhost
admin=> \conninfo
You are connected to database "admin" as user "admin" on host "localhost" (address "::1") at port "5432".

psql admin
admin=#
postgres=# \c admin
You are now connected to database "admin" as user "postgres".
admin=# \conninfo
You are connected to database "admin" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

--
psql
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
admin     | admin    | UTF8     | ko_KR.UTF-8 | en_US.UTF-8 |
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
postgres=# select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

\l

\h create user
create user admin createdb createrole password 'pwd';

\h create database
create tablespace admin location '/data/pgsql/admin';
create database admin template template0
lc_collate 'ko_KR.UTF-8'
tablespace admin owner admin;

\c admin

create table 한글 ( 이름 varchar(10));
ERROR:  invalid byte sequence for encoding "UTF8": 0xc7 0xd1
set client_encoding ='uhc';
select '안녕' ;

-- 한글 order by
drop table test;
create table test ( 이름 varchar(10));
insert into test values('가나');
insert into test values('다라');
insert into test values('1가');
insert into test values('1하');
insert into test values('a가');
insert into test values('a하');
insert into test values('가1');
insert into test values('가2');
insert into test values('하1');
insert into test values('하a');
select * from test order by 1;

en_US.UTF-8
이름
------
가나
다라
가1
하1
1가
1하
가2 [ en_US.UTF-8  order by 기본 발생 / => order by 1 collate "ko_KR.utf8" 정상 작동, https://sungtae-kim.tistory.com/68 ]
하a
a가
a하

ko_KR.UTF-8
이름
------
1가
1하
a가
a하
가1
가2
가나
다라
하1
하a

drop table test
ERROR:  must be owner of table test
SQL state: 42501
create table test
ERROR:  relation "test" already exists
SQL state: 42P07
insert into test values('가나');
ERROR:  permission denied for table test
SQL state: 42501
delete test;
ERROR:  syntax error at or near "test"
SQL state: 42601

admin=# select * from pg_tables where tablename='test';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public     | test      | postgres   |            | f          | f        | f           | f

admin=> alter table test owner to admin;
ERROR:  must be owner of table test

psql admin
admin=# [ #=> superuser ]
admin=# alter table test owner to admin;
ALTER TABLE

admin=# select * from pg_tables where tablename='test';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public     | test      | admin      |            | f          | f        | f           | f


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 186
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 117
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 206
Admin  pg_dumpall pg_upgrade    최길호 2021/10/21 149
Admin  통계정보    최길호 2021/10/21 125
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 최길호