2.postgresql基础配置

使用pg_ctl方式进行管理

1.数据库的启动，查看状态，关闭

[root@localhost ~]# su - postgres
Last login: Tue Oct 12 23:34:33 EDT 2021 on pts/1
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ start
waiting for server to start....2021-10-13 00:40:19.174 EDT [25652] LOG:  listening on IPv6 address "::1", port 1921
2021-10-13 00:40:19.174 EDT [25652] LOG:  listening on IPv4 address "127.0.0.1", port 1921
2021-10-13 00:40:19.176 EDT [25652] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-10-13 00:40:19.189 EDT [25653] LOG:  database system was shut down at 2021-10-12 23:36:10 EDT
2021-10-13 00:40:19.191 EDT [25652] LOG:  database system is ready to accept connections
 done
server started
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ status
pg_ctl: server is running (PID: 25652)
/opt/pg10/bin/postgres "-D" "/pgdata/10/data"
[postgres@localhost ~]$ /opt/pgsql/bin/pg_isready -p 1921
/tmp:1921 - accepting connections
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ -ms stop　　#smart（-ms）,fast(-mf),immediate(-mi)三种关闭模式
waiting for server to shut down....2021-10-13 00:43:51.783 EDT [25652] LOG:  received smart shutdown request
2021-10-13 00:43:51.784 EDT [25652] LOG:  worker process: logical replication launcher (PID 25659) exited with exit code 1
2021-10-13 00:43:51.785 EDT [25654] LOG:  shutting down
2021-10-13 00:43:51.791 EDT [25652] LOG:  database system is shut down
 done
server stopped

2.配置开机启动

1.配置服务脚本，将linux脚本拷贝到/etc/init.d/中，将脚本重命名为postgresql-10并赋予执行权限

[root@localhost ~]# ls postgresql-10.0/contrib/start-scripts/
freebsd  linux  osx
[root@localhost ~]# cp postgresql-10.0/contrib/start-scripts/linux /etc/init.d/postgresql-10
[root@localhost ~]# chmod +x /etc/init.d/postgresql-10
[root@localhost ~]# ls -lh /etc/init.d/postgresql-10
-rwxr-xr-x. 1 root root 3.5K Oct 13 00:48 /etc/init.d/postgresql-10

使用

chkconfig --list查看postgresql是否开机启动

设置开机启动，或关闭chkconfig postgresql-10 on/off

3.数据库配置基础

postgresql中两个重要的全局配置文件postgresql.conf(文件配置，资源限制，集权设置)和pg_hba.conf（客户端连接和认证）都存于初始化目录中

全局配置的修改方法

• 修改postgresql.cong文件
• 在库中通过alter system set 命令修改全局配置（会产生一个postgresql.auto.conf文件，数据库启动时会加载此文件，并覆盖postgresql.conf中配置，不要修改它）

非全局修改方法

• 设置database级别
• alter database name set configparameter 
• 设置session级别配置
• 通过set命令设置当前session配置

查询配置　　show all

使配置生效：

select pg_reload_conf();
或者
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data reload
server signaled
2021-10-13 01:31:28.393 EDT [25748] LOG:  received SIGHUP, reloading configuration files

允许数据库远程访问，需修改监听地址，pg_hba.conf文件

 vi /pgdata/10/data/postgresql.conf
找到#listen_addresses = 'localhost'改为
listen_addresses = '*'
重启数据库：　 /opt/pgsql/bin/pg_ctl -D /pgdata/10/data stop/start
echo "host mydb pguser 0.0.0.0/0 md5" >> /pgdata/10/data/pg_hba.conf
reload生效

/opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ reload

　　

配置默认环境，设置完后执行source ~/.bashrc

vi ~/.bashrc
export PG_HOME=/opt/pgsql
export PATH=$PG_HOME/bin:$PATH
PGUSER=postgres
PGHOST=127.0.0.1
PGDATABASE=postgres
PGPORT=1921