安装

sudo apt-get install postgresql
sudo apt-get install libpq-dev
# for hstore
sudo apt-get install postgresql-contrib

配置用户

sudo -u postgres psql template1
CREATE USER chideo WITH PASSWORD 'Wagner01'; 
alter user chideo with superuser;
\q

修改 /etc/postgresql/9.3/main/pg_hba.conf 权限控制,本地 TCP 访问设置为完全信任

#host    all             all             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            trust

修改后要重启

sudo service postgresql restart

###备份

Uncompressed:

$ pg_dump -h IP_ADDRESS -p 5432 -U app -N postgis -N topology -d DATABASE_NAME > your_file_name.sql
Compressed:

$ pg_dump -h IP_ADDRESS -p 5432 -U app -a -N postgis -N topology -Fc -d DATABASE_NAME > your_file_name.dump
The variables

IP_ADDRESS = The IP address of your database server

DATABASE_NAME = The database name of your server (found on the Database tab of your app)

The flags

-h = Host
-p = Port
-U = User
-d = Database name
-N = Exclude schema (in particular, exclude the PostGIS and topology schema if you aren’t using any of their geographic functionality)
-Fc = Format compressed
Optional flags

-a = Data only
-c = Clean

导入

psql -h IP_ADDRESS -p 5432 -U app -d databasename -f your_file_name.sql

连接数据库, 默认的用户和数据库是postgres

psql -U user -d dbname

切换数据库,相当于mysql的use dbname

\c dbname

列举数据库,相当于mysql的show databases

\l

列举表,相当于mysql的show tables

\dt

查看表结构,相当于desc tblname,show columns from tbname

\d tblname