[postgres] (본인 기준) 자주사용되는 psql 사용 예제 모음집

PSQL 명령어 모음집

[명령어 모음집] 다양한 명령어를 모아두는 아카이브

포스트 목적 명령어들을 모아두는 포스트 입니다. psql 명령어 일반 \copyright PostgreSQL 사용법 및 저작권 정보 표시 \crosstabview [칼럼들] 쿼리를 실행하고, 피봇 테이블 형태로 자료를 보여줌 \errverbos

duklook.tistory.com

특정 테이블만 백업해서 복원하기

특정 테이블만 백업하려면, 기존 백업 방식에서 -t <테이블명> 형식만 추가로 적어주면 된다. 예를 들어 pg_dump 를 사용해서 백업 시 다음과 같이 명시하면 해당 사용자의 데이터베이스에서 tablename 에 해당하는 테이블만 백업되어 .sql 파일로 저장된다.

pg_dump -U username -d dbname -t tablename > tablename_backup.sql

그 후 해당 파일을 복원하고자 할 때도 동일하다. 기존 복원 명령어에서 -t <테이블명> 을 추가로 명시해주면 해당 테이블에 설정된 제약조건과 컬럼, 로우 들이 복원된다.

pg_restore -U username -d dbname -t tablename tablename_backup.sql

테이블 이름을 잘못 작명해서 수정하고자 한다면

만일 테이블 이름을 잘못 지정하여 생성 후 이를 다른 이름으로 수정하고자 한다면 ALTER TABLE ~ RENAME TO ~ 를 이용한다. 여기서 quote-card 를 보면 - 는 원래라면 테이블 작명 시 사용할 수 없으나, psql 를 이용해 생성하면 생성이 된다. 이는 테이블 조회 등에서도 동일하게 동작하며, 정상적으로 조회 및 수정하고자 한다면 " "으로 감싸서 특수문자를 일반 문자로 인식될 수 있도록 해주어야 한다.

ALTER TABLE "quote-card" RENAME TO new_table_name;

데이터베이스 내 테이블 전체 삭제 하기

DO $$ DECLARE
    cur_table RECORD;
BEGIN
    FOR cur_table IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(cur_table.tablename) || ' CASCADE';
    END LOOP;
END $$;

여기서 DO $$ ... END $$; 구문은 PL/pgSQL 블록을 시작하고 끝내는데 사용된다. 이는 여러 SQL 문을 담을 수 있는 블록을 만들어주는 예약어 이다.

DECLARE 구문은 변수 선언을 시작한다. 여기서는 레코드 변수 cur_table을 선언한다. 이는 pg_tables 라는 해당 데이터베이스 내에 생성되어 있는 테이블 정보를 담고 있는 테이블을 순회할 때 각 테이블이 임시적으로 할당되는 변수이다.

BEGIN과 END 사이에는 실제로 실행될 코드가 위치한다. 이 코드는 루프를 통해 모든 테이블을 순회하고 각 테이블에 대해 DROP TABLE IF EXISTS 문을 실행한다.

FOR cur_talble IN (...) LOOP ... END LOOP; 구문은 지정된 쿼리 결과에 따라 루프를 실행한다. 여기서는 pg_tables 시스템 카탈로그에서 현재 스키마에 속한 모든 테이블을 선택한다.

EXECUTE 문은 동적 SQL을 실행한다. 여기서는 각 테이블을 삭제하는 DROP TABLE 문을 실행한다. quote_ident() 함수는 테이블 이름을 안전하게 인용 부호로 감싸는 역할을 한다.

CASCADE 키워드는 해당 테이블이 참조하는 모든 객체를 함께 삭제하도록 해준다.

데이터베이스 백업하기(with pg_dump)

postgrsql 에서 데이터베이스를 백업하는 방법은 여러 가지가 있는데, 그 중 일부를 정리해본다.

pg_dump 를 평문 형식(.sql) 으로 백업하기

데이터베이스를 평문 형식으로 백업하려면 다음과 같이 사용한다.

pg_dump -U 사용자명 데이터베이스명 > 백업파일이름.sql

만일 명시적으로 백업할 파일을 지정하고자 한다면 > 대신에 -f 를 사용하면 된다. 물론 이 둘은 모두 동일하게 동작한다.

pg_dump -U 사용자명 데이터베이스명 -f 백업파일이름.sql # 명시적으로 백업파일을 지정

위 형식을 참고하여 백업을 진행 해보면, 지정한 파일명과 확장자를 가진 백업 파일이 생성되는 것을 확인할 수 있다.

참고로 위 작업들을 처리할 때, 아래 옵션들이 주요 사용된다.

psql 내에서 외부 스크립트 파일에 접근하여 복원하기( /i )

데이터베이스를 복원할 때 psql에 직접 접속하지 않고 복원할 수도 있고, psql 접속 후 외부 스크립트 파일에 접근하여 데이터베이스를 복원할 수 있다.

이 중 psql 내에서 외부 스크립트 파일에 접근하여 복원하고자 한다면 /i 를 이용하면 된다.

예를 들어 psql 접속 후

# psql 접속
psql -U [사용자명] -d [데이터베이스명]

다음과 같은 형식으로 입력하면 해당 경로에 있는 sql 파일을 접속한 사용자의 데이터베이스에 복원한다.

# psql 접속 후
\i /home/admin.sql;

복원 이후 데이터베이스 테이블 목록을 조회하면 정상적으로 복원된 것을 확인할 수 있다.

현재 데이터베이스의 모든 테이블에 지정된 외래키 확인

SELECT
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE 
    constraint_type = 'FOREIGN KEY';

데이터베이스 사용자 생성 및 데이터베이스 생성 권한 부여

우선 기본적으로 pg_user 테이블을 조회하면 현재 데이터베이스 사용자 목록을 확인할 수 있다.

SELECT * FROM pg_user;

여기서 새로운 사용자를 생성 시 슈퍼유저 로서 역할을 부여하려면 다음 형식을 따른다.

CREATE USER admin PASSWORD 'admin' SUPERUSER;

이 상태에서 다시 조회를 해보면 admin 이라는 유저가 새롭게 추가 되었으며, usesuper 가 t 로 되어 있는 것을 볼 수 있다.

그리고 admin 유저에게 새로운 권한을 부여하고자 하는데, 데이터베이스 생성 권한을 부여해보고자 한다. 이를 위해서 다음 형식을 따르면 된다.

 ALTER USER admin CREATEDB;

그러면 다음과 같이 ALTER ROLE 이 나오면 성공적으로 부여된 것이다.

그 후 \du 를 입력해 보면, 생성된 사용자와 기존 사용자의 역할 목록을 요약해서 볼 수 있다. admin 유저의 역할에 DB 만들기가 추가된 것을 확인하면 정상적으로 작업이 된 것이다

데이터베이스 생성

데이터베이스 사용자를 생성하였다면, 이번에는 해당 사용자에게 배정할 데이터베이스를 생성해볼 것이다.

쉘 에서 생성하는 방법

우선 psql 에서 나온 뒤 다음 형식으로 입력한다.

createdb 데이터베이스명

그러면 다음과 같이 암호를 입력 하라는 칸이 뜨는데, 사용자를 생성할 때 지정한 번호를 입력한다.

psql 접속 후 생성하는 방법

원래 데이터베이스를 생성할 수 있는 권한을 superuser 라는 역할을 부여 받는 사용자만 가능하다.

따라서 psql 접속 시 postgres 라는 루트 관리자 계정으로 접속하고, 이 계정으로 데이터베이스를 생성해야 한다. 이 때 해당 데이터베이스의 주인(오너)으로 추가로 생성했던 admin 사용자이름을 입력하면 된다.

CREATE DATABASE [데이터베이스이름] OWNER [사용자명];

그러면 다음과 같이 CREATE DATABSAE 라는 문자가 보일텐데, 그러면 생성이 성공한 것이다.

현재 데이터베이스 인코딩 확인하기

psql 내에서 현재 접속한 데이터베이스의 인코딩을 확인하려면 다음 쿼리를 입력하면 된다.

SHOW SERVER_ENCDOING;

그러면 다음과 같이 지정되어 있는 인코딩 형식이 표시되는 것을 볼 수 있다.

현재 데이터베이스 인코딩 변경하기

그럼 반대로 기본적으로 설정된 인코딩 형식을 변경하려면 어떻게 해야할까? 가장 기본적인 방식은 모든 데이터베이스의 기본 인코딩 방식을 지정하는 postgresql.conf 파일에 직접 접근하여 다음과 같이 디폴트 인코딩 값을 지정해주는 것이다.

해당 파일은 운영체제 마다 접근하는 경로는 조금씩 차이는 있으나, 마지막에 도달하는 경로는 /data 내부로 동일하기 때문에 해당 폴더의 위치를 찾아서 접근하면 된다. 리눅스 환경에서는 vi 이나 vim 편집 패키지를 사용하면 더 수월하다.

생성된 데이터베이스의 관리자 목록 확인

다음 쿼리를 조회하면 현재 생성되어 있는 모든 데이터베이스의 관리자를 데이터베이스와 맵핑하여 테이블 형태로 조회할 수 있습니다.

SELECT datname AS database_name, rolname AS owner
FROM pg_database
JOIN pg_roles ON pg_database.datdba = pg_roles.oid;

실제 psql 내부에서 해당 쿼리를 조회해보면 다음과 같은 화면을 보실 수 있습니다.

데이터베이스 권한

생성권한 부여

your_username 에게 데이터베이스 생성 권한을 부여

ALTER USER your_username CREATEDB;

특정 권한 제거

예제는 myuser 로 부터 mydatabase 에 대한 조회(select) 권한을 제거하는 것

REVOKE SELECT ON DATABASE mydatabase FROM myuser;

특정 테이블에 대한 권한 제거

특정 권한을 제거할 때는 앞서와 같이 REVOKE 를 사용한다.

REVOKE privilege_type ON table_name FROM username;

이 때, 특정 테이블에 대한 삽입 권한을 제거한다면, INSERT ON 을 privilege_type 자리에 넣는다.

REVOKE INSERT ON mytable FROM myuser;

데이터베이스 연결 권한 제거

만일 특정 사용자가 데이터베이스에 접근하는 것 자체를 막으려면, CONNECT 를 넣어준다.

REVOKE CONNECT ON DATABASE mydatabase FROM myuser;

슈퍼 권한 제거

보통 postgres 가 루트 사용자일 때 SUPERUSER 권한을 가지고 있다. 이 때, 해당 권한을 제거할 때 사용한다.

ALTER USER username WITH NOSUPERUSER;

사용자에게 데이터베이스 생성 권한을 제거하려면

ALTER USER username WITH NOCREATEDB;

참고 쿼리

-- 테이블 생성 시 외래키 제약조건 설정
CREATE TABLE user_bookmarks (
	bookmark_id serial PRIMARY KEY,
	user_id INT NOT NULL,
	user_quote_id INT NOT NULL,
	quote_url varchar(200) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
	FOREIGN KEY (user_quote_ID) REFERENCES user_quotes(user_quote_id) ON DELETE CASCADE
);

Coding Life