0、安装PostgreSQL服务器
若有不明之处，参见官方手册：
1、安装
ubuntu直接：
apt-get install postgresql-9.4
Get:1 http://ftp.cn.debian.org/debian/ jessie/main libpq5 amd64 9.4.8-0+deb8u1 [123 kB]
Get:2 http://ftp.cn.debian.org/debian/ jessie/main postgresql-client-common all 165+deb8u1 [73.7 kB]
Get:3 http://ftp.cn.debian.org/debian/ jessie/main postgresql-client-9.4 amd64 9.4.8-0+deb8u1 [1,073 kB]
Get:4 http://ftp.cn.debian.org/debian/ jessie/main ssl-cert all 1.0.35 [20.9 kB]
Get:5 http://ftp.cn.debian.org/debian/ jessie/main postgresql-common all 165+deb8u1 [203 kB]
Get:6 http://ftp.cn.debian.org/debian/ jessie/main postgresql-9.4 amd64 9.4.8-0+deb8u1 [3,687 kB]
Fetched 5,181 kB in 4s (1,218 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libpq5:amd64.
(Reading database ... 34370 files and directories currently installed.)
Preparing to unpack .../libpq5_9.4.8-0+deb8u1_amd64.deb ...
Unpacking libpq5:amd64 (9.4.8-0+deb8u1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../postgresql-client-common_165+deb8u1_all.deb ...
Unpacking postgresql-client-common (165+deb8u1) ...
Selecting previously unselected package postgresql-client-9.4.
Preparing to unpack .../postgresql-client-9.4_9.4.8-0+deb8u1_amd64.deb ...
Unpacking postgresql-client-9.4 (9.4.8-0+deb8u1) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../ssl-cert_1.0.35_all.deb ...
Unpacking ssl-cert (1.0.35) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../postgresql-common_165+deb8u1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (165+deb8u1) ...
Selecting previously unselected package postgresql-9.4.
Preparing to unpack .../postgresql-9.4_9.4.8-0+deb8u1_amd64.deb ...
Unpacking postgresql-9.4 (9.4.8-0+deb8u1) ...
Processing triggers for man-db (2.7.0.2-5) ...
Processing triggers for systemd (215-17+deb8u1) ...
Setting up libpq5:amd64 (9.4.8-0+deb8u1) ...
Setting up postgresql-client-common (165+deb8u1) ...
Setting up postgresql-client-9.4 (9.4.8-0+deb8u1) ...
update-alternatives: using /usr/share/postgresql/9.4/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up ssl-cert (1.0.35) ...
Setting up postgresql-common (165+deb8u1) ...
Adding user postgres to group ssl-cert
Creating config file /etc/postgresql-common/createcluster.conf with new version
Creating config file /etc/logrotate.d/postgresql-common with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Setting up postgresql-9.4 (9.4.8-0+deb8u1) ...
Creating new cluster 9.4/main ...
config /etc/postgresql/9.4/main
data /var/lib/postgresql/9.4/main
locale en_US.UTF-8
Flags of /var/lib/postgresql/9.4/main set as -------------e-C
port 5432
update-alternatives: using /usr/share/postgresql/9.4/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for libc-bin (2.19-18+deb8u3) ...
Processing triggers for systemd (215-17+deb8u1) ...
2、修改用户密码
安装时会创建postgres用户，用户的home路径为安装路径下面的一个地方，例如
/var/lib/postgresql
初次使用这个用户，最好是用root修改一下密码：
echo postgres:postgres | chpasswd
用postgres用户登录，然后在其主目录执行：
vim ~/.bash_profile
增加如下内容：
export PGHOME=/usr/lib/postgresql/9.4
export PATH=$PGHOME/bin:$PATH
export PGDATA=$HOME/data
export LD_LIBRARY_PATH=$PGHOME/lib
MANPATH=/usr/share/postgresql/9.4/man:$MANPATH
export MANPATH
其中PGHOME是postgresql的bin和lib所在的目录，可以通过以下命令查看
whereis postgresql
显示：postgresql: /usr/lib/postgresql /etc/postgresql /usr/share/postgresql
第一个路径就是postgresql的安装路径。
注意PGDATA设置的是当前用户的home目录的data目录下，可以先创建这个目录：
mkdir -p ~/data
修改配置：
1.PostgresPlus\8.3\data\pg_hba.conf
# IPv4 local connections:
host all all 192.168.0.1/32 trust
这样 ip为192.168.0.1 就可以访问你的数据库了 。
如果还是不可以 ，关掉防火墙
2、更改postgresql.conf下
#listen_addresses = 'localhost' # what IP address(es) to listen on;
为
listen_addresses = '*'
记得去掉listen_addresses前的#号
4、然后执行初始化data空间
postgres@open-auth-tomcat:~$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D /var/lib/postgresql/data
or
pg_ctl -D /var/lib/postgresql/data -l logfile start
其他启动命令：
postgres -D /var/lib/postgresql/data >logfile 2>&1 &
关闭命令：
$ kill -INT `head -1 /var/lib/postgresql/data/postmaster.pid`
其中postmaster.pid的路径为data所在的路径下面
进入sql命令行：
postgres@open-auth-tomcat:~/data$ psql -U postgres -d postgres
psql (9.4.8)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | 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
(3 rows)
postgres=# \q
默认创建了postgres的数据库
可以创建新数据库：根据模板 template0创建数据库
CREATE DATABASE sonarqube TEMPLATE template0;
或者
$ createdb -T template0 sonarqube
修改数据库;
创建role：
CREATE ROLE sonarqube LOGIN;
ALTER ROLE sonarqube PASSWORD '123456';
注意：postgresql修改密码默认是用的MD5加密过，所以这样的123456的密码明文用不了，如果想设置成明文的123456如下：
ALTER ROLE sonarqube UNENCRYPTED PASSWORD '123456';
设置密码永久有效：ALTER ROLE sonarqube VALID UNTIL 'infinity';
1、安装客户端
安装：
pgadmin3-1.16.1.zip
下载地址http://www.postgresql.org/ftp/pgadmin3/release/v1.16.1/
2、登录报错Access to database denied
详细信息如下：
Access to database denied
The server doesn't grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host "170.12.3.8", user "dcm", database "dcm", SSL off
To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.
The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You'll probably want to add something like
host all all 192.168.0.0/24 md5
This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.
You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.
解决方案：
在PostgreSQL安装目录下，找到pg_hba.conf文件，例如：/opt/PostgresPlus/9.2AS/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 170.16.0.0/16 md5
host all all 170.16.3.8/32 md5
host all all 10.6.174.10/32 trust
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the replication privilege.
#local replication enterprisedb md5
#host replication enterprisedb 127.0.0.1/32 md5
#host replication enterprisedb ::1/128 md5
增加IP配置项：
host all all 170.26.3.0/24 md5
注意这个24不要乱写，24是子网掩码。
例如：
170.26.0.0/12
这个/12代表子网掩码：/12代表12个二进制1（11111111.11110000.00000000.00000000）
/8: 255.0.0.0
/12: 255.240.0.0
/16: 255.255.0.0
/24: 255.255.255.0
170.26.0.0/12能表示的范围为：
170.00010000.00000000.00000000（170.16.0.0 ）
170.000111111.11111111.11111111（170.31.255.255 ）
所以170.26.3.0/24代表：
170.26.3.0~170.26.3.255
可以使用 子网掩码计算器 来计算，地址如下：
http://www.ab126.com/goju/1840.html
再附一例：
知道IP地址和子网掩码如何计算可用IP地址范围和默认网关？
192.168.0.1/24
1：求可用IP地址范围
2：求子网掩码
3：求默认网关
答案解析：
/24就是子网掩码前24位位1，也就是11111111 11111111 11111111 00000000
也就是：255.255.255.0
子网掩码表明了这个子网的IP的前24位是网络号，后8位是主机号。
子网掩码理论范围：网络号不变，主机号由全0变化到全1.
也就是192.168.0.00000000-192.168.1.11111111
也就是192.168.0.0-192.168.0.255
实际可用IP范围要减去主机号位全0和全1这两个（全0为子网网络地址、全1位子网广播地址，这两个地址不能分配给主机）。
所以实际可用范围：192.168.0.1-192.168.0.254
网关顾名思义是网络的关口，通常是计算机所连接的路由器的LAN口上的IP，这个IP可能是子网范围内的任意IP，要看配置网络的人具体是怎么配置的。
md5代表认证方式。常用的有ident，md5，password，trust，reject。
ident是Linux下PostgreSQL默认的local认证方式，凡是能正确登录服务器的操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库。
password是以明文密码传送给数据库，建议不要在生产环境中使用。
md5是常用的密码认证方式，如果你不使用ident，最好使用md5。
trust是只要知道数据库用户名就不需要密码或ident就能登录，建议不要在生产环境中使用。
reject是拒绝认证。
修改后，最好再执行一下reload命令，使配置生效：
切换到安装目录下的bin目录。
执行：
./pg_ctl reload -D $PGDATA=/opt/PostgresPlus/9.2AS/data
（pg_ctl reload -D $PGDATA / service postgresql reload）
3、导出数据：
【其中dcm为用户名dcm_client为数据库名】
./pg_dump -U dcm dcm_client > /opt/back_data/dcm_client.sql
【导入的数据是copy格式的PostgreSQL才能恢复的数据格式】
导出insert式的SQL数据：
./pg_dump -U dcm --inserts > /opt/back_data/dcm_data.sql dcm_client
其中dcm为用户名dcm_client为数据库名
4、新增列
ALTER TABLE dcm_car_passenger ADD COLUMN etnumber character varying(20); -- --票号
ALTER TABLE dcm_car_passenger ADD COLUMN etissueddate character varying(16); -- --出票时间
ALTER TABLE dcm_car_passenger ADD COLUMN etissuanceoffice character varying(10); -- --出票office
ALTER TABLE dcm_car_passenger ADD COLUMN totalfare character varying(10); -- --总票价
COMMENT ON COLUMN dcm_car_passenger.etnumber IS '--票号';
COMMENT ON COLUMN dcm_car_passenger.etissueddate IS '--出票时间';
COMMENT ON COLUMN dcm_car_passenger.etissuanceoffice IS '--出票office';
COMMENT ON COLUMN dcm_car_passenger.totalfare IS '--总票价';
5、修改列
ALTER TABLE dcm_car_passenger
ALTER COLUMN foid TYPE character varying(30);
COMMENT ON COLUMN dcm_car_passenger.foid IS '---旅客证件号';
PostgreSQL的语法;