CentOS6.5下tar包安装postgresql-9.6.1数据库_centos pg 9.6 tar安装部署-优快云博客

本文链接：https://blog.youkuaiyun.com/embracejava/article/details/53377351

1.下载安装包

从Postgres官方网站下载postgresql-9.6.1-1-linux-x64-binaries.tar.gz安装包

2.创建用户

创建Postgres用户：

useradd postgres                                                                                                                                                                         passwd postgres(postgres)

3.解压

为了保证我们使用postgres用户安装完成后其他用户也能使用，我们采用root用户解压安装包到/usr目录中，再将相应目录的权限改回postgres

su
cd /data/soft/postgres/
tar -zxvf postgresql-9.6.1-1-linux-x64-binaries.tar.gz -C /usr

4.更改目录权限

切换到/usr目录，找到刚才解压的pgsql文件夹，将pasql文件夹的所有者改回postgres

chown -R postgres:postgres /usr/pgsql

5.建立数据目录

在/opt目录下建立postgres的数据目录，并更改文件夹所有者为postgres

cd /opt 
mkdir postgres
cd postgres
mkdir 9.6
cd 9.6
mkdir data
cd /opt
chown -R postgres:postgres /opt/postgres

如果后期忘记了posgresql安装到什么目录了，可以通过查找pg_hba.conf,来定位postgresql的位置

切换到postgres用户下添加PG_HOME和PGDATA环境变量

vim ~/.bash_profile
export PG_HOME=/usr/pgsql
export PGDATA=/opt/postgres/9.6/data
export PATH=$PATH:$PG_HOME/bin

6.初始化数据库

切换到postgres用户，初始化数据库

find . -name initdb
su - postgres
/usr/pgsql/bin/initdb -D /opt/postgres/9.6/data

执行完成后结果如下：

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 /opt/postgres/9.6/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
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:

/usr/pgsql/bin/pg_ctl -D /opt/postgres/9.6/data -l logfile start

如果想对postgres进行配置，可以编辑如下文件：

cd /opt/postgres/9.6/data
vim postgresql.conf

7.启动数据库

先创建日志目录

cd /opt/postgres/9.6/
mkdir logs

启动数据库：

/usr/pgsql/bin/pg_ctl -D /opt/postgres/9.6/data -l /opt/postgres/9.6/logs/postgres.log start

查看服务是否启动成功

ps -ef | grep postgres

当看到类似下面的结果时，说明服务启动成功了

[postgres@hadoop1 9.6]$ ps -ef | grep postgres
root       3659   3644  0 09:46 pts/1    00:00:00 su - postgres
postgres   3660   3659  0 09:46 pts/1    00:00:00 -bash
root       4056   3751  0 10:15 pts/1    00:00:00 su - postgres
postgres   4057   4056  0 10:15 pts/1    00:00:00 -bash
postgres   4946      1  0 10:26 pts/1    00:00:00 /usr/pgsql/bin/postgres -D /opt/postgres/9.6/data
postgres   4948   4946  0 10:26 ?        00:00:00 postgres: checkpointer process                   
postgres   4949   4946  0 10:26 ?        00:00:00 postgres: writer process                         
postgres   4950   4946  0 10:26 ?        00:00:00 postgres: wal writer process                     
postgres   4951   4946  0 10:26 ?        00:00:00 postgres: autovacuum launcher process            
postgres   4952   4946  0 10:26 ?        00:00:00 postgres: stats collector process                
postgres   5560   4057  0 10:30 pts/1    00:00:00 ps -ef
postgres   5561   4057  0 10:30 pts/1    00:00:00 grep postgres

调用如下命令查看postgres服务：

service --status-all | grep postgres

然后发现并没有结果，因此需要将postgresql注册到服务列表

8.修改postgres用户的访问密码并测试建库建表

PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为’postgres’
直接在控制台输入以下命令：

su - postgres
psql
# ALTER USER postgres WITH PASSWORD 'postgres';
# select * from pg_shadow ;
# create database project;
# \c project

project=# create table person(id integer, name text);
project=# insert into person values (1, 'jimmy');
project=# select * from person

可以看到我们刚才插入的那条数据

9.将postgresql-9.6添加到服务列表

9.1CentOS6操作系统

su root
cd /etc/init.d/
touch postgresql-9.6
vim postgresql-9.6

添加以下内容(该文件参考postgres解压目录中的linux文件生成)：

#! /bin/sh

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems.  You should edit some of the variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
#   /etc/rc.d/rc0.d/K02postgresql
#   /etc/rc.d/rc1.d/K02postgresql
#   /etc/rc.d/rc2.d/K02postgresql
#   /etc/rc.d/rc3.d/S98postgresql
#   /etc/rc.d/rc4.d/S98postgresql
#   /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.

# Original author:  Ryan Kirkpatrick <pgsql@rkirkpat.net>

# contrib/start-scripts/linux

## EDIT FROM HERE

# Installation prefix
prefix=/usr/pgsql

# Data directory
PGDATA="/opt/postgres/9.6/data/"

# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="/opt/postgres/9.6/logs/postgres.log"

# It's often a good idea to protect the postmaster from being killed by the
# OOM killer (which will tend to preferentially kill the postmaster because
# of the way it accounts for shared memory).  To do that, uncomment these
# three lines:
#PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
#PG_MASTER_OOM_SCORE_ADJ=-1000
#PG_CHILD_OOM_SCORE_ADJ=0
# Older Linux kernels may not have /proc/self/oom_score_adj, but instead
# /proc/self/oom_adj, which works similarly except for having a different
# range of scores.  For such a system, uncomment these three lines instead:
#PG_OOM_ADJUST_FILE=/proc/self/oom_adj
#PG_MASTER_OOM_SCORE_ADJ=-17
#PG_CHILD_OOM_SCORE_ADJ=0

## STOP EDITING HERE

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster.  (If you want the script to wait
# until the server has started, you could use "pg_ctl start -w" here.
# But without -w, pg_ctl adds no value.)
DAEMON="$prefix/bin/postmaster"

# What to use to shut down the postmaster
PGCTL="$prefix/bin/pg_ctl"

set -e

# Only start if we can find the postmaster.
test -x $DAEMON ||
{
    echo "$DAEMON not found"
    if [ "$1" = "stop" ]
    then exit 0
    else exit 5
    fi
}

# If we want to tell child processes to adjust their OOM scores, set up the
# necessary environment variables.  Can't just export them through the "su".
if [ -e "$PG_OOM_ADJUST_FILE" -a -n "$PG_CHILD_OOM_SCORE_ADJ" ]
then
    DAEMON_ENV="PG_OOM_ADJUST_FILE=$PG_OOM_ADJUST_FILE PG_OOM_ADJUST_VALUE=$PG_CHILD_OOM_SCORE_ADJ"
fi


# Parse command line parameters.
case $1 in
  start)
    echo -n "Starting PostgreSQL: "
    test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
    su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
    echo "ok"
    ;;
  stop)
    echo -n "Stopping PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"
    echo "ok"
    ;;
  restart)
    echo -n "Restarting PostgreSQL: "
    su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"
    test -e "$PG_OOM_ADJUST_FILE" && echo "$PG_MASTER_OOM_SCORE_ADJ" > "$PG_OOM_ADJUST_FILE"
    su - $PGUSER -c "$DAEMON_ENV $DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
    echo "ok"
    ;;
  reload)
        echo -n "Reload PostgreSQL: "
        su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"
        echo "ok"
        ;;
  status)
    su - $PGUSER -c "$PGCTL status -D '$PGDATA'"
    ;;
  *)
    # Print help
    echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
    exit 1
    ;;
esac

exit 0

需要根据安装路径修改配置中相应的路径信息
接着就可以使用service postgresql-9.6 start/stop/status来操作postgresql的启停了

9.2CentOS7操作系统

su root
cd /lib/systemd/system/
vim postgres.service

添加以下内容：

[Unit]
Description=postgres
After=network.target

[Service]
User=postgres
Group=postgres
Type=forking
ExecStart=/usr/pgsql/bin/pg_ctl -D /opt/postgres/9.6/data -l /opt/postgres/9.6/logs/postgres.log start
ExecReload=
ExecStop=/usr/pgsql/bin/pg_ctl -D /opt/postgres/9.6/data stop
PrivateTmp=true

[Install]
WantedBy=multi-user.target

10.把postgresql加入自启动列表

10.1CentOS6操作系统

cd /etc/init.d
chkconfig --add postgresql-9.6

查看一下自启动列表

chkconfig --list

在这里可以看到postgresq-9.6已经在其中了。

10.2CentOS7操作系统

systemctl enable postgres.service

查看自启动列表中是否有postgres：

[root@hadoop2 postgres]# systemctl list-units --type=service |grep postgres
postgres.service                                      loaded active running postgres

11.配置postgresql允许远程访问

只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件：
pg_hba.conf：配置对数据库的访问权限；
postgresql.conf：配置PostgreSQL数据库服务器的相应的参数

11.1修改pg_hba.conf

vim /opt/postgres/9.6/data/pg_hba.conf

在IPV4中添加下面那一行内容

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0                   md5
#表示允许任意网段的任意机器通过密码验证的方式访问到该数据库

重新加载postgresql配置文件（可选）：

su - postgres
pg_ctl reload

11.2修改postgresql.conf

vim /opt/postgres/9.6/data/postgresql.conf

定位到listen_addresses，并将localhost改为*

listen_addresses = '*'           # what IP address(es) to listen on;

注：修改完配置后需要重新启动postgresql远程连接才能生效
至此，postgresql的安装和配置已经全部完成