Pg Admin에서 가상서버 postgres db 접속 테스트

Local PC Pg Admin ||| 에서 가상서버 POSTGRES DATABASE 가상서버 접속 테스트

1) vi postgresql.conf
listen_addresses = '*'로 수정
[postgres@localhost data]$ vi postgresql.conf
[postgres@localhost data]$ pwd
/home/postgres/pgsql/data

2) vi pg_hba.conf
0.0.0.0/0으로 변경해 모두 접속으로 변경

3) 재시작
[postgres@localhost bin]$ cd /home/postgres/pgsql/bin
[postgres@localhost bin]$ ./pg_ctl restart -D $PGDATA

 

4) 접속 테스트

접속 성공

POSTGRES 외부 접속하다 발생한 에러들

| Listen 에러

Server doesn't listen
The server doesn't accept connections: the connection library reports 
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

 

| Listen 에러 해결
5432 Port OPEN

   
 [root@localhost home]# vi /etc/sysconfig/iptables
[root@localhost home]# service network restart
인터페이스 eth0 (을)를 종료 중:   장치 상태: 3 (연결 끊겼음)
                                                           [  OK  ]
loopback 인터페이스 종료 중:                               [  OK  ]
loopback 인터페이스 활성화중 입니다:                       [  OK  ]
eth0 인터페이스 활성화중 입니다:  활성화된 연결 상태: 활성화중
활성화된 연결 경로: /org/freedesktop/NetworkManager/ActiveConnection/1
상태: 활성화됨
연결이 활성화되었습니다
                                                           [  OK  ]

| 에러 해결

| Server connecting 에러
Error connecting to the server: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.

 에러 해결

- Postgresql.conf에서 listen_addresses를 허용한다.
[postgres@localhost ~]$ pwd
/home/postgres

- Postgresq.conf 파일 찾기
[postgres@localhost ~]$ find ./ -name postgresql.conf
./pgsql/data/postgresql.conf

- 재시작
[postgres@localhost bin]$ ./pg_ctl restart -D /home/postgres/pgsql/data
2021-08-24 15:26:40.104 KST [7402] LOG:  received fast shutdown request
waiting for server to shut down....2021-08-24 15:26:40.115 KST [7402] LOG:  aborting any active transactions
2021-08-24 15:26:40.117 KST [7402] LOG:  worker process: logical replication launcher (PID 7409) exited with exit code 1
2021-08-24 15:26:40.117 KST [7404] LOG:  shutting down
2021-08-24 15:26:40.148 KST [7402] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2021-08-24 15:26:40.243 KST [7574] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-08-24 15:26:40.244 KST [7574] LOG:  listening on IPv6 address "::", port 5432
2021-08-24 15:26:40.321 KST [7574] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-08-24 15:26:40.412 KST [7575] LOG:  database system was shut down at 2021-08-24 15:26:40 KST
2021-08-24 15:26:40.423 KST [7574] LOG:  database system is ready to accept connections
 done
server started

| Access to database denied 에러
 

FATAL: no pg_hba.conf entry for host "10.0.2.2", user "postgres", database "postgres"

[postgres@localhost data]$ vi pg_hba.conf
[postgres@localhost data]$ pwd

[변경전]

[변경후]

2021-08-24 16:13:41.646 KST [2548] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

| Password authentication failed for user 에러

| 에러 해결

- md5에서 trust로 변경
[postgres@localhost data]$ vi pg_hba.conf

 

그리고 아래와 같이 에러가 뜨는데 계속 OK 누르면 접속이 되긴한다.

function pg_last_xlog_receive_location() does not exist

LINE 1: ...E NULL END as inrecovery, CASE WHEN usesuper THEN pg_last_xl...