PostgreSQL数据库安装教程

互联网安装

一、安装

1、安装EPEL存储库（如果未安装）

• sudo yum install -y epel-release

2、安装PostgreSQL

•  sudo yum install -y postgresql-server postgresql-contrib

3、初始化数据库

• sudo postgresql-setup initdb

4、启动PostgreSQL服务

• sudo systemctl start postgresql

5、查看pgsql版本

•  psql --version

6、设置开机启动

• sudo systemctl enable postgresql

7、检查PostgreSQL服务状态

• sudo systemctl status postgresql

二、设置

1、以默认用户postgres身份进入PostgreSQL（两边都有空格！）

• su - postgres

2、启动psql

• -bash-4.2$ psql

3、查看数据库

• -bash-4.2$ \l

4、修改PostgreSQL数据库用户密码

• postgres=# ALTER USER postgres WITH PASSWORD '123456'; ALTER ROLE #设置成功

5、配置PostgreSQL远程访问

vim /var/lib/pgsql/data/postgresql.conf 配置文件

listen_addresses = 'localhost' 改为 listen_addresses = '*'

并且把注释去掉

6、修改/var/lib/pgsql/data/pg_hba.conf文件，新增以下两行：

• vim /var/lib/pgsql/data/pg_hba.conf

• host all all 127.0.0.1/32 ident host all all 0.0.0.0/0 md5

7、重启PostgreSQL服务

• systemctl restart postgresql.service

8、远程连接PostgreSQL数据库

• psql -h192.168.1.128 -Upostgres -W

[root@jeven ~]# psql -h192.168.1.128 -Upostgres -W Password: 123456

9、退出

返回：\q

退出：exit

10、关闭防火墙

• systemctl stop firewalld

11、telnet查看是否能通

离线版安装

软件安装步骤：gcc、zlib、readline-devel、postgres

报错说明：

1、configure: error: readline library not found If you have readline already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support.

2、configure: error: zlib library not found

If you have zlib already installed, see config.log for details on the

failure. It is possible the compiler isn't looking in the proper directory.

Use --without-zlib to disable zlib support.

1、安装gcc

• ./configure –prefix=/usr/local
  
  make
  
  make install

2、安装readline-devel

• yum install -y readline-devel
  
  yum install -y zlib-devel

或者去相关网站下载离线包

3、安装postgresql（如果有：gcc和readline-devel就不会报错）

• tar -zxvf postgresql-10.18.tar.gz

• cd postgresql-10.18 ./configure --prefix=/usr/local/pgsql

4、继续报错安装

• make && make install echo PG_LIBRARY_PATH=/usr/local/pgsql/lib PATH=/usr/local/pgsql/bin:$PATH MANPATH=/usr/local/pgsql/man:$MANPATH export PATH PG_LIBRARY_PATH MANPATH>>/etc/profile

5、创建用户

• groupadd postgres useradd postgres -g postgres mkdir -p /home/postgres chown -R postgres:postgres /home/postgres chown -R postgres:postgres /usr/local/pgsql

• passwd postgres

• 123456

6、创建数据库集群

• su - postgres

• /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

7、修改/usr/local/pgsql/data/postgresql.conf

将listen_addresses = 'localhost' 修改为 listen_addresses = '*'

修改/usr/local/pgsql/data 下pg_hba.conf【IPV4 local connections配置】

• host　　all　　all　　192.168.1.128/32　　md5

下面添加

• host　　all　　all　　0.0.0.0/0　　 md5

8、启动和停止数据库服务

• /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start /usr/local/pgsql/bin/pg_ctl -D 

• /usr/local/pgsql/data stop

或

启动服务

• sudo systemctl start postgresql

停止服务

• sudo systemctl stop postgresql

9、注册service服务（可跳过）

• cp /data/postgresql/postgresql-10.18/contrib/start-scripts/linux /etc/init.d/postgresql chmod 755 /etc/init.d/postgresql

10、设置PG开机启动（可跳过）

• systemctl enable postgresql

11、设置密码访问

• cd /usr/local/pgsql/bin/
  
  ./psql -h localhost -p 5432 -Upostgres -W

• ALTER user postgres WITH PASSWORD '123456';

• SELECT pg_reload_conf();

报错：psql: error: connection to server on socket “/usr/local/pgsql/data/.s.PGSQL.5432“ failed

问题分析：

查看查看参数unix_socket_directories[postgres@localhost ~]$ cat /usr/local/pgsql/data/postgresql.conf | grep unix_socket_directories

# unix_socket_directories = '/tmp'

# comma-separated list of directories

解决办法：
修改/usr/local/pgsql/data/postgresql.conf文件中的unix_socket_directories参数，修改如下：unix_socket_directories='/usr/local/pgsql/data'
或者在psql中修改也可以

psql -h 127.0.0.1
alter system set unix_socket_directories to '/usr/local/pgsql/data';
重新postgreSQL服务，问题解决。