sudo apt-get install postgresql
sudo apt-get install libpq-dev
# for hstore
sudo apt-get install postgresql-contrib
/etc/apt/sources.list.d/pgdg.list
# PostgreSQL repository
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update -y
sudo apt-get install postgresql-client-9.5 -y
sudo -u postgres psql template1
CREATE USER chideo WITH PASSWORD 'xxx';
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
psql -U user -d dbname -h xxx.com -p 5432
切换数据库,相当于mysql的use dbname
\c dbname
列举数据库，相当于mysql的show databases
\l
列举表，相当于mysql的show tables
\dt
查看表结构，相当于desc tblname,show columns from tbname
\d tblname
删除数据库
DROP DATABASE pg_database_3;
copy bitauth_userdata from '/home/chideo/db_backup/csv/userdata.csv';
导出mysql到csv
mysql -A service_db -h 199.115.165.194 -u chadmin -pch@dmin! -ss -e "SELECT * from bitauth_userdata;" > userdata.csv
用vi处理特殊字符
%s/~R/'/g
CTRL-K {char1} {char2}
~R P2
~S TS
~T CC
~V SG
~F SA
清空表中的数据，但保留表结构
TRUNCATE TABLE table_name;
如果该表有关联，则
TRUNCATE TABLE items,categories;
http://blog.sina.com.cn/s/blog_7c50857d01010d1e.html