前言:
新公司有一套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 ---------------------------