centos 7 安装

https://www.postgresql.org/download/linux/redhat/ 安装教程

https://www.postgresql.org 官网地址

对照安装初始化完成后 postgresql10 在Linux的安装，默认创建了postgres用户，无需再次创建，直接su 即可。 PostgreSQL 数据库默认会创建一个linux 系统用户postgres，通过passwd 命令设置系统用户的密码为123456。

# passwd postgres

[rootpylixm-web ~]# su - postgres
bash-4.2$ psql
psql (10.0)
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=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#

/var/lib/pgsql/10/data/pg_hba.conf 权限相关配置: 远端可以通过账号密码登陆
host    all             all             0.0.0.0/0               password
/var/lib/pgsql/10/data/postgresql.conf 数据库相关配置:
listen_addresses = '*'
posrt = 5432
监听任意IP, 允许任意ip连接数据库

修改密码
# ALTER USER postgres WITH PASSWORD '123456';
# select * from pg_shadow ;  查看

创建测试数据库
# create database fangzhen;

切换到test 数据库
# \c test

创建测试表
# create table test (id integer, name text);
# insert into test values (1,'david');
# select * from test ;

查看当前数据库占用的线程

select * from pg_stat_activity;

关闭 卡住的事务线程

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname='db'
  AND state = 'idle in transaction'

postgresql 的配置

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
host    all     all     0.0.0.0/0       md5   #这里配置所有ip都能远程连接通过账号密码登陆

 vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = '*'
port = 5432 
max_connections =300
password_encryption = on
tcp_keepalives_idle = 100
tcp_keepalives_interval = 100
tcp_keepalives_count = 10
shared_buffers = 128MB
dynamic_shared_memory_type = posix
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on 
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '< %m > '
log_timezone = 'PRC'
idle_in_transaction_session_timeout = 20000
datestyle = 'iso, ymd'
timezone = 'PRC'
lc_messages = 'zh_CN.UTF-8'                    # locale for system error message
                                        # strings
lc_monetary = 'zh_CN.UTF-8'                    # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8'                      # locale for number formatting
lc_time = 'zh_CN.UTF-8'                        # locale for time formatting
default_text_search_config = 'pg_catalog.simple'

如果配置文件失败  数据库无法启动  可以删除初始化的数据库  重新初始化 然后修改配置文件
rm -rf /var/lib/pgsql/9.6/data    删除数据库
/usr/pgsql-9.6/bin/postgresql96-setup initdb  初始化数据库