centos7 postgres9.4 安装设置

一键初使化

安装，设置自启动，启动

yum install -y http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
yum install -y postgresql94-server postgresql94-contrib
/usr/pgsql-9.4/bin/postgresql94-setup initdb
systemctl enable postgresql-9.4.service
systemctl start postgresql-9.4.service

修改postgres用户密码,添加新用户，数据库

su postgres
psql -U postgres
ALTER USER postgres WITH PASSWORD 'P0stgres'
CREATE USER lucky_draw WITH PASSWORD 'fOejcf3Jd';
CREATE DATABASE lucky_draw OWNER lucky_draw;
\q

远程访问

vim /var/lib/pgsql/9.4/data/pg_hba.conf

添加如下一行：
host all all 0.0.0.0/0 md5

导入出数据

• 同用户，数据库，密码简易方法

pg_dump -C -h xx.xx.xx.xx -U lucky_draw lucky_draw | psql -h xx.xx.xx.xx -U lucky_draw lucky_draw

• 导出数据到文件

pg_dump -C -h xx.xx.xx.xx -U lucky_draw lucky_draw>lucky_draw.sql

• 从文件导入数据库

psql -U lucky_draw -f lucky_draw.sql

修改字段长度

ALTER TABLE "user" ALTER COLUMN avator TYPE character varying(500);

修改字段名

ALTER TABLE TABLE_NAME rename column OLD_FIELD to NEW_FIELD;

attention

• 字段和表名用双引号
• 数据用单引号

reference

http://www.cnblogs.com/tjpanda88/p/4306149.html