ostgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 |en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | dave=CTc/postgres
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
(4 rows)
?
?
--重置postgres账户密码：
postgres=# alter user postgres withpassword 'dave';
ALTER ROLE
?
--修改用户认证配置文件pg_hba.conf：
postgres@dave:/etc/postgresql/9.1/main$ pwd
/etc/postgresql/9.1/main
postgres@dave:/etc/postgresql/9.1/main$ ls
environment pg_ctl.conf pg_hba.conf pg_ident.conf postgresql.conf start.conf
?
# Database administrative login by Unixdomain socket
local all postgres trust
?
# TYPE DATABASE USER ADDRESS METHOD
?
# "local" is for Unix domainsocket connections only
local all all md5
?
--重新加载postgresql：
postgres@dave:/etc/postgresql/9.1/main$/etc/init.d/postgresql reload
?
postgres@dave:~$ psql -d cndba -U dave
Password for user dave:
psql (9.1.15)
Type "help" for help.
?
cndba=> selectcurrent_user;
current_user
--------------
dave
(1 row)
?
?
--表的基本操作：
?
postgres-> \c cndba
You are now connected to database"cndba" as user "dave".
cndba->
?
cndba=> create table cndba(namevarchar(20),signupdate date);
CREATE TABLE
cndba=> insert into cndba(name, signupdate)values('dave', '2015-02-11');
INSERT 0 1
cndba=> select * from cndba;
name| signupdate
------+------------
dave| 2015-02-11
(1 row)
?
cndba=> update cndba set name ='tianlesoftware' where name = 'dave';
UPDATE 1
cndba=> alter table cndba add emailvarchar(40);
ALTER TABLE
cndba=> alter table cndba alter columnsignupdate set not null;
ALTER TABLE
cndba=> alter table cndba rename columnsignupdate to signup;
ALTER TABLE
cndba=> alter table cndba drop columnemail;
ALTER TABLE
cndba=> alter table cndba rename todave;
ALTER TABLE
cndba=> drop table if exists dave;
DROP TABLE
cndba=>
?
?
4 postgresql 查看数据库,表,索引,表空间以及大小
?
postgres=# select pg_database.datname,pg_database_size(pg_database.datname) AS size from pg_database;
datname | size
-----------+---------
template1 | 6030136
template0 | 6030136
postgres | 6030136
cndba | 6038328
(4 rows)
?
--以KB，MB，GB的方式来查看数据库大小
postgres=# selectpg_size_pretty(pg_database_size('cndba'));
pg_size_pretty
----------------
5897kB
(1 row)
?
postgres=# create table cndba(namevarchar(20),signupdate date);
CREATE TABLE
?
--查看多表：
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | cndba | table | postgres
(1 row)
?
--查看单表：
postgres=# \d cndba
Table "public.cndba"
Column | Type | Modifiers
------------+-----------------------+-----------
name | character varying(20) |
signupdate | date |
?
?
--查看表大小
postgres=# select pg_relation_size('cndba');
pg_relation_size
------------------
8192
(1 row)
?
--以KB，MB，GB的方式来查看表大小
postgres=# selectpg_size_pretty(pg_relation_size('cndba'));
pg_size_pretty
----------------
8192bytes
(1 row)
?
?
--查看索引信息：
postgres=# create index idx_cndba oncndba(name);
CREATE INDEX
?
postgres=# \di
List of r