[TIL] Postgres/PostgreSQL 명령어 정리

[psql DB 접속]
$ psql -h localhost -U <username> -d <dbname>

[psql shell 진입]
$ psql <schemaname>

기본적으로 psql에서는 postgres 라는 유저가 생성되어 있다.

그래서 $ psql -U postgres -h localhost 를 입력하고 psql 설치시 입력한 비밀번호를 입력하면

아래와 같이 postgres(는 db가 아니지만)명의 psql 쉘이 열리는 것을 볼 수 있다.

psql 에 postgres 유저로 접근한 경우

(원래는 psql 쉘이 열릴 때 나오는 =# 앞부분은 DB name이다.)

 

 

---

< psql shell 안에서의 명령어 >

1️⃣ USER

- postgres 계정(USER) 목록 조회 방법

=# SELECT * FROM PG_USER;
=# \du                     <- 위의 명령어와 같은 의미
=# \du+                    <- 보다 상세조회

 

- 유저 생성 방법

1. superuser의 계정으로 psql shell에 접속한다. [참고: docs]
2. 간단하게 생성
   =# CREATE USER <username>;
3. 옵션까지 추가해서 생성
   =# CREATE ROLE <username> PASSWORD '<password: str>' <권한>;
   • [참고] CREATE ROLE 대신 CREATE USER를 사용해도 똑같이 user는 만들어진다.
     둘의 차이점
     CREATE ROLE의 경우: 로그인을 한번 더 해줘야 유저로 인식된다.
     CREATE USER의 경우: 로그인을 안해도 유저로 인식된다.

create user

- 유저의 정보(권한/비밀번호 등) 업데이트 하는 방법 (이미 유저 생성 이후에)

• 권한 업데이트
  =# ALTER USER <username> WITH <permissions>;
• 비밀번호 업데이트(설정)
  =# ALTER ROLE <username> LOGIN password '<password: str>';
• 해당 유저에게 특정 권한만 주고 싶을 때
  =# GRANT <permissions> ON DATABASE <dbname> TO <username>;

- 유저 삭제 방법

=# DROP ROLE <username>;

drop user

 

2️⃣ DATEBASE

- postgres 데이터베이스 목록 조회 방법

=# \l
=# \list

- 데이터베이스 생성 방법

• 간단하게 생성
  =# CREATE DATABASE <dbname>;
• 소유자 지정해서 생성
  =# CREATE DATABASE <dbname> OWNER <owner_username>;

- 데이터베이스  소유자 변경 방법 (이미 생성 후에, 업데이트 방법)

=# ALTER DATABASE <dbname> OWNER TO <owner_username>;

- 데이터베이스 삭제 방법

=# DROP DATABASE <dbname>;

💫 [참고] ERROR:  database "gbzrdb" is being accessed by other users 발생시

               ➡️ https://scriptcrunch.com/there-is-1-other-session

💫 [참고] [ERROR] ERROR: syntax error at or near "-" 발생시

               ➡️ https://kkangsg.tistory.com/106

 

 

3️⃣ TABLE

- postgres 테이블 목록 조회 방법

=# SELECT * FROM pg_tables;
=# \dt                       <- 위의 명령어와 같은 의미
=# \dt+                      <- 보다 상세조회

 

테이블 생성 전에 데이터베이스에 접속해야함!!!!!!

- 데이터베이스 접속 방법

=# \c <database_name> <db_user>

(위와 같은 명령어로 접속하면 psql 쉘 표현이 postgres=# 에서 <db_name>=# 으로 바뀔 것이다.)

gbzrdb =# 쉘

\fastapi-practice=# 쉘로 바뀜

- 테이블 생성 방법 (예시)

• DDL(Data Definition Language)을 이용해서 생성할 것이다.
• 보통은, 사용하는 framework의 models을 만들고 migration을 하면 ORM을 통해서 자동으로 table이 생성된다.

=# CREATE TABLE 테이블이름 (컬럼명1 데이터타입, 컬럼명2 데이터타입);

[예시]
=# CREATE TABLE ACCOUNT_TEST
(
	USER_ID SERIAL PRIMARY KEY,
	USERNAME VARCHAR(50) UNIQUE NOT NULL,
	PASSWORD VARCHAR(50) NOT NULL,
	EMAIL VARCHAR(355) UNIQUE NOT NULL,
	CREATED_ON TIMESTAMP NOT NULL,
	LAST_LOGIN TIMESTAMP
);

- 테이블 생성 여부 확인 방법

1. =# \dt로 전체 테이블을 조회해서 내가 만든 '테이블 이름'이 있는지 확인한다.
2. =# SELECT * FROM 'table_name';을 통해서 내가 생성한 테이블의 컬럼들이 데이터타입에 맞춰서 잘 생성되었는지 확인한다.

- 테이블 삭제 방법

=# DROP TABLE <table_name>;

 

 

4️⃣ SCHEMA

스키마 또한 테이블과 마찬가지로, 조회하기 전에 DB에 연결을 먼저 해야한다!

- postgres 스키마 목록 조회 방법

=# \dn
=# \dn+

- 현재 DB에서 지정한 스키마 삭제 방법

=# DROP SCHEMA <schema_name:PUBLIC> CASCADE;

 

- 현재 DB에서 지정한 스키마 생성 방법

(일반적으로 db를 생성하면 스키마는 이미 생성되어있다)

=# CREATE SCHEMA <schema_name:PUBLIC>;

 

 

💫 [참고] IN my CASE,

나의 경우, django/fastapi 프로젝트에서 model을 변경하면 migration을 했지만
잘못된 스키마를 생성했거나 기존의 모델과 비교했을 때 변경을 많이해서, 아예 migration파일을 날리고 처음부터 시작하고 싶은 경우 아래와 같이 했다.  [참고 블로그]

• migration파일 날리기
• =# DROP SCHEMA PUBLIC CASCADE; (지금까지의 스키마 삭제)
  =# CREATE SCHEMA PUBLIC; (스키마 새롭게 생성)
• 또는 migrate으로 생성된 파일 직접 수정하기 (이건 모델 변화가 크지 않았을 때 추천!)