목차

PostgreSQL 환경설정

set AUTOCOMMIT OFF in psqlrc not having affect (~/.psqlrc)

  1. I have \set AUTOCOMMIT OFF in my .psqlrc and it seems psql seems to not have any effect.
    Am I missing something? I've been googling and googling and seem a bit lost at this point.
  2. ~/.psqlrc 에 아래의 내용이 존재하여야 한다.
    $ cat ~/.psqlrc
    \set AUTOCOMMIT OFF
    \echo 'AUTOCOMMIT is' :AUTOCOMMIT
    \set PROMPT1 'host.%/%R%# '
    \timing
  3. 시작은 BEGIN으로 시작해야 하며, COMMIT, ROLLBACK 으로 DDL 명령을 실행한다.
    $ psql
    Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
    
    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help on internal slash commands
           \g or terminate with semicolon to execute query
           \q to quit
    
    AUTOCOMMIT is OFF
    Timing is on.
    host.domain=# commit;
    WARNING:  there is no transaction in progress
    COMMIT
    host.domain=# update foo set foo_type_id = 3 where foo_id=200000234;
    UPDATE 1
    host.domain=# commit;
    WARNING:  there is no transaction in progress
    COMMIT
    host.domain=# update foo set foo_type_id = 3 where foo_id=200000234;
    UPDATE 1
    host.domain=# rollback;
    WARNING:  there is no transaction in progress
    ROLLBACK
    host.domain=# begin;
    BEGIN
    host.domain=# update foo set foo_type_id = 3 where foo_id=200000234;
    UPDATE 1
    host.domain=# rollback;
    ROLLBACK
    host.domain=# begin;
    BEGIN
    host.domain=# update foo set foo_type_id = 3 where foo_id=200000234;
    UPDATE 1
    host.domain=# rollback;
    ROLLBACK
    host.domain=# \q

The Password File(~/.pgpass)

  1. The file ‘.pgpass’ in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise).
  2. On Microsoft Windows the file is named ‘%APPDATA%\postgresql\pgpass.conf’ (where ‘%APPDATA%’ refers to the Application Data subdirectory in the user's profile).
  3. This file should contain lines of the following format:
    hostname:port:database:username:password
  4. Each of the first four fields may be a literal value, or *, which matches anything.
  5. The password field from the first line that matches the current connection parameters will be used.
    (Therefore, put more-specific entries first when you are using wildcards.)
  6. If an entry needs to contain : or \, escape this character with \. A host name of localhost matches both TCP (hostname localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine.
  7. The permissions on ‘.pgpass’ must disallow any access to world or group;
    achieve this by the command chmod 0600 ~/.pgpass.
  8. If the permissions are less strict than this, the file will be ignored.
    (The file permissions are not currently checked on Microsoft Windows, however.)