安装postgresql

安装postgresql数据库

1.下载安装包

wget https://ftp.postgresql.org/pub/source/v11.1/postgresql-11.1.tar.gz

2.创建postgres用户，并创建安装目录

useradd Postgres

mkdir -p /opt/pgsql/data
chown -R postgres:postgres /opt/pgsql 

3.解压安装包
tar -zxvf postgresql-11.1.tar.gz

4.编译、安装

cd postgresql-11.1
./configure --prefix=/opt/pgsql
make & make install

5.初始化数据库

su - postgres

[postgres@postgresql bin]$cd /opt/pgsql/bin
[postgres@postgresql bin]$ ./initdb -D /opt/pgsql/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/pgsql/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:

    ./pg_ctl -D /opt/pgsql/data/ -l logfile start


[postgres@postgresql bin]$./pg_ctl -D /opt/pgsql/data -l logfile start
waiting for server to start.... done
server started

#配置用户环境变量文件.bash.profile增加如下内容
export PATH=$PATH:/opt/pgsql/bin


[postgres@postgresql ~]$ psql
psql (11.1)
Type "help" for help.

postgres=#
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

安装9.2版本

系统环境说明
[root@slave1 ~]# cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core) 

[root@slave1 ~]# uname -r
3.10.0-693.el7.x86_64

[root@slave1 ~]# hostname -I
192.168.174.201 192.168.122.1

软件版本
psql (9.2.23)


安装
yum install postgresql-server

PostgreSQL安装将不会启用自动启动或自动初始化数据库。为了完成数据库安装，您需要执行以下两个步骤
service postgresql initdb
chkconfig postgresql on

PostgreSQL启动服务
service postgresql start

查看是否启动
netstat -a | grep PGSQ

连接数据库
切换到数据库用户
su - postgres

登录数据库
psql
psql 终端可以用\du 或\du+ 查看，也可以查看系统表 select * from pg_roles;

[root@slave1 ~]# su - postgres
上一次登录：四 4月 12 17:18:28 CST 2018pts/0 上
-bash-4.2$ psql
psql (9.2.23)
输入 "help" 来获取帮助信息.

# 查看信息
postgres=# \du
                        角色列表
 角色名称 |               属性                | 成员属于 
----------+-----------------------------------+----------
 postgres | 超级用户, 建立角色, 建立 DB, 复制 | {}
 
postgres=# 

修改用户postgres的密码
alter role postgres with password 'postgres'

退出
\q
exit

修改配置
修改监听地址
vi /var/lib/pgsql/data/postgresql.conf
#listen_addresses='localhost'

#将上面这行改成如下
listen_addresses='*'

设置所有网段IP可以访问
vi /var/lib/pgsql/data/pg_hba.conf
# IPv4 remote address connections:
host    all         all         0.0.0.0/0                 trust

解决psql: 致命错误: 用户 "postgres" Ident 认证失败
#vi /var/lib/pgsql/data/pg_hba.conf

这个配置文件中的认证 METHOD的ident修改为trust，可以实现用账户和密码来访问数据库

验证
service postgresql restart
su - postgres
psql -h 127.0.0.1 -U postgres -d postgres -W