PostgreSQL 관련 팁 모음

1. DBMS 샘플데이터 2배수로 생성하기

insert into table_name (col_a, col_b, col_c, col_n) (select col_a, col_b, col_c, col_n from table_name);

이리하면 테스트용 자료를 금방 생성할 수 있지요..

2. psql 콘솔창에서 데이터베이스 선택하기

postgres=# \c 데이터베이스명
접속정보: 데이터베이스="데이터베이스명", 사용자="postgres".
데이터베이스명=#

3. database 목록 조회하기..

select datname from pg_database;
datname
----------------
template1
template0
postgres
.
.
.
(n개 행)

하거나 psql 콘솔창에서 \l 타이핑하시면 아래와 같이 결과가 나옵니다.

postgres=# \l
                                          데이터베이스 목록
       이름        |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
-------------------+----------+--------+------------------+------------------+-----------------------
 postgres          | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
.
.
.
(n개 행)
postgres=#

4. 테이블 목록 조회하기..

psql 콘솔창에서 \dt입력
postgres=# \dt
          릴레이션(relation) 목록
 스키마 |     이름      |  형태  |  소유주
--------+---------------+--------+----------
 public | tab_a         | 테이블 | postgres
 public | tab_b         | 테이블 | postgres
 public | .....         | 테이블 | postgres
(n개 행)

postgres=#

혹은....

postgres=# select * from pg_catalog.pg_tables where schemaname != 'pg_catalog' and schemaname !='information_schema';
 schemaname |   tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+---------------+------------+------------+------------+----------+-------------+-------
------
 public     | table_a       | postgres   |            | t          | f        | t           | f
 public     | table_b       | postgres   |            | t          | f        | t           | f
 public     | .....         | postgres   |            | t          | f        | t           | f
(n개 행)

postgres=#

5. 테이블 명세(?)하기...

psql 콘솔창에서 \d 테이블명 입력
dvdrental-# \d city
                                        "public.city" 테이블
   필드명    |            형태             |                        기타 조건

-------------+-----------------------------+--------------------------------------------------------
-
 city_id     | integer                     | Null 아님 기본 값 nextval('city_city_id_seq'::regclass)
 city        | character varying(50)       | Null 아님
 country_id  | smallint                    | Null 아님
 last_update | timestamp without time zone | Null 아님 기본 값 now()
인덱스들:
    "city_pkey" PRIMARY KEY, btree (city_id)
    "idx_fk_country_id" btree (country_id)
Foreign-key 제약 조건:
    "fk_city" FOREIGN KEY (country_id) REFERENCES country(country_id)
다음에서 참조됨:
    TABLE "address" CONSTRAINT "fk_address_city" FOREIGN KEY (city_id) REFERENCES city(city_id)
트리거들:
    last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE PROCEDURE last_updated()
dvdrental-#

혹은.... information_schema에서 아래와 같이 조회할 수 있다..

dvdrental-# select * from information_schema.columns where table_name = 'city';
....결과.....블라....블라.....
dvdrental-# 

6. postgres 기본 암호 변경

$ sudo -u postgres psql
[sudo] password for 리눅스계정:
암호:
psql (9.5.1)
도움말을 보려면 "help"를 입력하십시오.

postgres=# ALTER USER postgres PASSWORD 'newPassword';
ALTER ROLE
postgres=# \q

만약 postgres is not in the sudoers file. this incident will be reported. 라는 에러가 뜨면 postgres 계정의 시스템계정 암호가 필요하니 아래와 같이 암호 설정

$ sudo passwd postgres
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully

psql: FATAL: password authentication failed for user "postgres" 라는 에러메시지가 뜨면 /etc/postgresql/9.5/main/pg_hba.conf의 설정 중 local all postgres peer의 주석을 제거하고 peer를 md5로 변경한 다음 저장 후 postgresql서비스 재시작해야한다.

$ sudo vi /etc/postgresql/9.5/main/pg_hba.conf
....
# Database administrative login by Unix domain socket
local   all             postgres                                md5
.....
$ sudo service postgresql restart
$ psql -U postgres -W -h 호스트명

정작 유저의 암호를 변경하려고 alter user 유저명 password '새암호'; 한건데, 접속과정 내용이 기네요..

7.....