PG二进制安装

前言：

新公司有一套postgresql, 日夜作为数据仓库，跑一些报表及批处理等。所以，就得学习这PG， 学习的第一步，就是安装配置，从但节点开始：

系统：CentsOS6.8 x86 64bit

PG: postgresql-9.6.2-3-linux-x64-binaries.tar.gz ( 版本*.run 需要图形界面）

创建目录：

[root@limin-test ~]# groupadd -g 1300 postgres

[root@limin-test ~]# useradd -m -u 1301 -g postgres postgres

[root@limin-test ~]# id postgres

uid=1301(postgres) gid=1300(postgres) groups=1300(postgres)

[root@limin-test ~]#

[root@limin-test ~]# passwd postgres

加压 tar 包：

tar -xvf postgresql-9.6.2-3-linux-x64-binaries.tar.gz -C /usr/local/

授权：

[root@limin-test ~]# chown -R postgres:postgres /usr/local/pgsql-9.6.2

[root@limin-test ~]# chmod -R 755 /usr/local/pgsql-9.6.2

环境变量配置：

[postgres@limin-test ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export DATADIR=/data/postgres/posdb

export BASEDIR=/usr/local/pgsql-9.6.2

export PATH=$BASEDIR/bin:$PATH:$HOME/bin:$PATH

数据库初始化：

[postgres@limin-test bin]$ ./initdb -E utf8 -D /data/postgres/posdb/

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 text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/postgres/posdb ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... 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:

./pg_ctl -D /data/postgres/posdb/ -l logfile start

启动数据库：

[postgres@limin-test ~]$ nohup postgres -D /data/postgres/posdb/ > /tmp/postgres.log &

[1] 1891

[postgres@limin-test ~]$ ps -aux |grep pos

postgres 1891 0.0 0.3 309368 14360 pts/1 S 13:00 0:00 postgres -D /data/postgres/posdb/

postgres 1893 0.0 0.0 309368 1580 ? Ss 13:00 0:00 postgres: checkpointer process

postgres 1894 0.0 0.0 309368 2384 ? Ss 13:00 0:00 postgres: writer process

postgres 1895 0.0 0.0 309368 1360 ? Ss 13:00 0:00 postgres: wal writer process

postgres 1896 0.0 0.0 309812 2388 ? Ss 13:00 0:00 postgres: autovacuum launcher process

postgres 1897 0.0 0.0 164368 1540 ? Ss 13:00 0:00 postgres: stats collector process

连接数据库：

[postgres@limin-test ~]$ psql

psql.bin (9.6.2)

Type "help" for help.

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=# create database martinli;

CREATE DATABASE

连接数据库：

postgres=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

martinli | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

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=# \c martinli

You are now connected to database "martinli" as user "postgres".

martinli=#

可以任你发挥艺术创造了，DBA们，数据库架构师们，攻城狮们。。。

---------------------- The end ---------------------------