postgresql 常用命令

yum install postgresql-server postgresql


su - postgres
initdb -D /var/lib/pgsql/data


bash-4.1$ service postgresql start
Starting postgresql service: /etc/init.d/postgresql: line 114: echo: write error: Permission denied
bash-4.1$ exit
exit


[root@cdh2 local]# service postgresql start
Starting postgresql service:                               [  OK  ]
[root@cdh2 local]# 
[root@cdh2 local]# su - postgres
-bash-4.1$ 
-bash-4.1$ service postgresql status
postmaster (pid  9745) is running...
-bash-4.1$ 


http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
http://blog.chinaunix.net/uid-26184465-id-3527130.html
http://blog.chinaunix.net/uid-26184465-id-3527130.html
http://blog.chinaunix.net/uid-22920230-id-3493064.html
http://blog.chinaunix.net/uid-22920230-id-3493064.html






cat /var/liinitdb -D /var/lib/pgsql/data^C
-bash-4.1$ service postgresql status
postmaster (pid  9745) is running...
-bash-4.1$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres)
-bash-4.1$ vim /var/lib/pgsql/data/postgresql.conf
-bash-4.1$ cat /var/lib/pgsql/data/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the
# PostgreSQL documentation for a complete description
# of this file.  A short synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain socket,
# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", a database name, or
# a comma-separated list thereof.
#
# USER can be "all", a user name, a group name prefixed with "+", or
# a comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names from
# a separate file.
#
# CIDR-ADDRESS specifies the set of hosts the record matches.
# It is made up of an IP address and a CIDR mask that is an integer
# (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies
# the number of significant bits in the mask.  Alternatively, you can write
# an IP address and netmask in separate columns to specify the set of hosts.
#
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", "krb5",
# "ident", "pam", "ldap" or "cert".  Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE. The available options depend on the different authentication
# methods - refer to the "Client Authentication" section in the documentation
# for a list of which options are available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other special
# characters must be quoted. Quoting one of the keywords "all", "sameuser" or
# "samerole" makes the name lose its special character, and just match a
# database or username with that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can use
# "pg_ctl reload" to do that.


# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL listen
# on a non-local interface via the listen_addresses configuration parameter,
# or via the -i or -h command line switches.
#


# CAUTION: Configuring the system for local "trust" authentication allows
# any local user to connect as any PostgreSQL user, including the database
# superuser. If you do not trust all your local users, use another
# authentication method.




# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD


# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
#host    all         all         127.0.0.1/32          trust
host    all         all          0.0.0.0/0            trust
# IPv6 local connections:
host    all         all         ::1/128               trust
-bash-4.1$ service postgresql restart
Stopping postgresql service:                               [FAILED]
Starting postgresql service: /etc/init.d/postgresql: line 114: echo: write error: Permission denied
touch: cannot touch `/var/lock/subsys/postgresql': Permission denied
/etc/init.d/postgresql: line 122: /var/run/postmaster.5432.pid: Permission denied


-bash-4.1$ exit
logout
[root@cdh2 local]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
[root@cdh2 local]# su - postgres
-bash-4.1$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres)
-bash-4.1$ psql -U postgres -W
Password for user postgres: 
psql (8.4.20)
Type "help" for help.


postgres=# CREATE USER <user> WITH PASSWORD '<password>';
ERROR:  syntax error at or near "<"
LINE 1: CREATE USER <user> WITH PASSWORD '<password>';
                    ^
postgres=# CREATE USER postUser WITH PASSWORD 'postPwd';
CREATE ROLE
postgres=#  CREATE DATABASE test OWNER postUser ENCODING 'UTF8';


CREATE DATABASE
postgres=# 
postgres=# psql -U kongxx -d test
postgres-# psql -U postUser -d test
postgres-# psql -U postUser -d test
postgres-# id
postgres-#  psql --version
postgres-# exit
postgres-# help
postgres-# "help"
postgres-# psql -h localhost -U postgres -d test
postgres-# ls/
postgres-# CREATE DATABASE test1 OWNER postgres ENCODING 'UTF8'
postgres-# psql -U postgres -d test1
postgres-# ^Z
[1]+  Stopped                 psql -U postgres -W
-bash-4.1$ psql -U postgres -d test1
psql: FATAL:  database "test1" does not exist
-bash-4.1$ psql -U postUser -d test
psql: FATAL:  role "postUser" does not exist
-bash-4.1$ CREATE USER postUser WITH PASSWORD 'postPwd';
-bash: CREATE: command not found
-bash-4.1$ psql -U postgres -W
Password for user postgres: 
psql (8.4.20)
Type "help" for help.


postgres=# CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UTF8';
ERROR:  database "test" already exists
postgres=# CREATE DATABASE test2 WITH OWNER = postgres ENCODING = 'UTF8';
CREATE DATABASE
postgres=# /psql -h localhost -U postgres -d test2
postgres-# psql -h localhost -U postgres -d test2
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 test      | postuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)


postgres-# psql --version
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 test      | postuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)


postgres-# select * from pg_database;
ERROR:  syntax error at or near "/"
LINE 1: /psql -h localhost -U postgres -d test2
        ^
postgres=# select * from pg_database;
  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | dat
config |               datacl                
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+----
-------+-------------------------------------
 template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |    
       | {=c/postgres,postgres=CTc/postgres}
 template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         11563 |          648 |          1663 |    
       | {=c/postgres,postgres=CTc/postgres}
 postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |    
       | 
 test      |  16384 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |    
       | 
 test2     |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |    
       | 
(5 rows)


postgres=# select * from pg_database;
  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | dat
config |               datacl                
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+----
-------+-------------------------------------
 template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |    
       | {=c/postgres,postgres=CTc/postgres}
 template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         11563 |          648 |          1663 |    
       | {=c/postgres,postgres=CTc/postgres}
 postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |    
       | 
 test      |  16384 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |    
       | 
 test2     |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |    
       | 
(5 rows)


postgres=# \dt
No relations found.
postgres=# \c test
Password for user postgres: 
psql (8.4.20)
You are now connected to database "test".
test=# \dt
No relations found.
test=# CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
CREATE TABLE
test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | user_tbl | table | postgres
(1 row)


test=# SELECT * FROM user_tbl;
 name | signup_date 
------+-------------
(0 rows)


test=# INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
INSERT 0 1
test=# SELECT * FROM user_tbl;
 name | signup_date 
------+-------------
 张三 | 2013-12-22
(1 row)


test=# commit;
WARNING:  there is no transaction in progress
COMMIT
test=# UPDATE user_tbl set name = '李四' WHERE name = '张三';
UPDATE 1
test=# SELECT * FROM user_tbl;
 name | signup_date 
------+-------------
 李四 | 2013-12-22
(1 row)


test=# ALTER TABLE user_tbl ADD email VARCHAR(40);
ALTER TABLE
test=# desc user_tbl
test-# ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
ERROR:  syntax error at or near "desc"
LINE 1: desc user_tbl
        ^
test=# ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
ALTER TABLE
test=# ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
ALTER TABLE
test=# ALTER TABLE user_tbl RENAME TO backup_tbl;
ALTER TABLE
test=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | backup_tbl | table | postgres
(1 row)


test=# \du
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 postgres  | Superuser   | {}
           : Create role   
           : Create DB     
 postuser  |             | {}


test=# \conninfo
Invalid command \conninfo. Try \? for help.
test=# \conninfo
Invalid command \conninfo. Try \? for help.
test=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | backup_tbl | table | postgres
(1 row)


test=# \d backup_tbl 
         Table "public.backup_tbl"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 name   | character varying(20) | 
 signup | date                  | not null
 email  | character varying(40) | 


test=# ALTER TABLE user_tbl RENAME COLUMN signup TO signup_todate;
ERROR:  relation "user_tbl" does not exist
test=# ALTER TABLE user_tbl RENAME COLUMN signup TO signup_todate;
ERROR:  relation "user_tbl" does not exist
test=# ALTER TABLE backup_tbl RENAME to user_tbl;
ALTER TABLE
test=# \d backup_tbl 
Did not find any relation named "backup_tbl".
test=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | user_tbl | table | postgres
(1 row)


test=# \d user_tbl 
          Table "public.user_tbl"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 name   | character varying(20) | 
 signup | date                  | not null
 email  | character varying(40) | 


test=# ALTER TABLE user_tbl RENAME COLUMN signup TO signup_todate;
ALTER TABLE
test=# \d user_tbl 
              Table "public.user_tbl"
    Column     |         Type          | Modifiers 
---------------+-----------------------+-----------
 name          | character varying(20) | 
 signup_todate | date                  | not null
 email         | character varying(40) | 


test=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql


Query Buffer
  \e [FILE]              edit the query buffer (or file) with external editor
  \ef [FUNCNAME]         edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file


Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)


Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[+]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S]  [PATTERN]      list conversions
  \dC     [PATTERN]      list casts
  \dd[S]  [PATTERN]      show comments on objects
  \dD[S]  [PATTERN]      list domains
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
--More--




test=# SELECT version();
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.20 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 32-bit
(1 row)


test=#