[PostgreSQL] 예제 - 접속 및 확인

PostgreSQL

Oracle 촌놈의 PostgreSQL 경험..

psql

Examples for psql connection - 예제

• 특정 User로 접속

    psql --username postgres

  OR

    psql -U postgres

• DB명과 함꼐

    psql DBNAME USERNAME

• psql 종료 (빠져나오기)

  • [CTRL] + [Z]로 빠져나온다.

Examples - 예제

• Select DB Instance - DB 인스턴스 확인

    select oid as database_id,
           datname as database_name,
           datallowconn as allow_connect,
           datconnlimit as connection_limit
    from pg_database
    order by oid;

• Select Tables - Tables 확인

    select * from pg_tables;

• Default Schema 확인

    SHOW search_path

• SESSION상에서만 Default Schema 변경

    SET search_path = new_schema

• 영구히 Default Schema 변경 (적용후 psql 재접속하여 확인했을 때 변경됨)

    ALTER USER postgres SET search_path = scott, new_schema, public, "UPPER_USERNAME";

• postgresql.conf 파일에서 Default Schema 변경

    search_path = ‘”$user”,public,”dbSys”‘ # schema names

Reference - 참고

• Postgres Command Line Connection Guide | pSQL Commands: https://www.a2hosting.com/kb/developer-corner/postgresql/connect-to-postgresql-from-the-command-line
• List databases on PostgreSQL instance: https://dataedo.com/kb/query/postgresql/list-databases