[postgreSQL] postgreSQL 을 dbeaver 와 연결하기

SQL 인 postgreSQL로 데이터 삽입, 수정, 삭제 작업을 해보겠다.

 

 

우선 postgreSQL을 MacOS환경에서 brew로 설치해줬다.

 

 

brew install postgresql@14

14--version 이라고 정확하게 명시하지 않아서 그런지 나는 설치가 안돼서 @14로 명시해줬다.

 

 

==> Fetching dependencies for postgresql@14: krb5
==> Fetching krb5
==> Downloading https://ghcr.io/v2/homebrew/core/krb5/manifests/1.20.1
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/krb5/blobs/sha256:273a7b14a23c9
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sh
######################################################################## 100.0%
==> Fetching postgresql@14
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/14/manifests/14.7
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/14/blobs/sha256:adfc
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sh
######################################################################## 100.0%
==> Installing dependencies for postgresql@14: krb5
==> Installing postgresql@14 dependency: krb5
==> Pouring krb5--1.20.1.ventura.bottle.tar.gz
🍺  /usr/local/Cellar/krb5/1.20.1: 162 files, 4.9MB
==> Installing postgresql@14
==> Pouring postgresql@14--14.7.ventura.bottle.tar.gz
==> /usr/local/Cellar/postgresql@14/14.7/bin/initdb --locale=C -E UTF-8 /usr/loc
==> Caveats
This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgresql@14
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To restart postgresql@14 after an upgrade:
  brew services restart postgresql@14
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/postgresql@14/bin/postgres -D /usr/local/var/postgresql@14
==> Summary
🍺  /usr/local/Cellar/postgresql@14/14.7: 3,314 files, 44.5MB
==> Running `brew cleanup postgresql@14`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).
Warning: Calling plist_options is deprecated! Use service.require_root instead.
Please report this issue to the elastic/tap tap (not Homebrew/brew or Homebrew/homebrew-core), or even better, submit a PR to fix it:
  /usr/local/Homebrew/Library/Taps/elastic/homebrew-tap/Formula/elasticsearch-full.rb:68

==> Caveats
==> postgresql@14
This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgresql@14
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To restart postgresql@14 after an upgrade:
  brew services restart postgresql@14
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/postgresql@14/bin/postgres -D /usr/local/var/postgresql@14

설치하게 되면 해당 완료문이 나오게 되고

 

 

pg_ctl -D /usr/local/var/postgresql@14 start

위의 코드를 터미널에 입력하면

 

 

waiting for server to start....2023-03-29 15:32:36.703 KST [16953] LOG:  starting PostgreSQL 14.7 (Homebrew) on x86_64-apple-darwin22.1.0, compiled by Apple clang version 14.0.0 (clang-1400.0.29.202), 64-bit
2023-03-29 15:32:36.704 KST [16953] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-03-29 15:32:36.704 KST [16953] LOG:  listening on IPv6 address "::1", port 5432
2023-03-29 15:32:36.705 KST [16953] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-29 15:32:36.711 KST [16954] LOG:  database system was shut down at 2023-03-29 15:31:08 KST
2023-03-29 15:32:36.714 KST [16953] LOG:  database system is ready to accept connections
 done
server started

이런식으로 서버가 켜졌다는 안내문이 나온다.

 

 

psql postgres

위의 코드로 postgres를 시작하고

 

 

postgres=# \du

 

postgres=# 으로 postgres실행문 위에 위의 코드를 치면 

 

 

 

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 jujaehun  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

jujaehun이라는 Role name이 나온다. 복사해주자

 

 

이제 dbeaver를 실행하자

 

위의 추가 버튼을 눌러서

 

 

postgreSQL을 클릭하자

 

 

아까 복사해줬던 Role name을 여기에 복붙해주자

 

완료하면 연결은 성공한다.

 

간혹가다가 여기서 생성한 데이터베이스가 안보이는 문제가 생기는데

 

해결방법은 아래와 같다.

 

 

연결한 postgreSQL에 오른쪽 마우스를 누르면

 

Edit Connection 이 나타난다.

 

클릭하자

 

 

상단에 두개 체크 되어있는지 확인하고 확인버튼 누르고 다시 새로고침 하면 나타날것이다.

 

2023.03.29 - [𝐝𝐚𝐭𝐚𝐁𝐚𝐬𝐞/PostgreSQL] - [postgreSQL] 연결한 dbeaver로 sql문 사용하여 데이터 관리하기

[postgreSQL] 연결한 dbeaver로 sql문 사용하여 데이터 관리하기

 

이제 연결한 dbeaver로 데이터 관리하는 기본 sql문에 대해서 알아보자.