Supplement: How to determine the path of the psql configuration file
①Switch to the psql user, here is thunisoft.
② There are many ways to determine the path, here are some commonly used methods.
<1>ps -ef |grep base In the output result, the data directories after -D.
<2>After switching the thunisoft user, it will default to thunisoft’s home directory /home/thunisoft. Use the installation package provided by the company to automatically generate a start or stop script in the home directory when psql is automatically installed.
Use the command more startup.sh or stop.sh. You can see the following. The -D specifies the data directory.
<3>The third one-step method: directly use the tool to connect to the database for execution. You can see that the sourcefile corresponds to two different configuration files. They are postgresql.conf and postgresql.auto.conf, the latter has a higher priority. Before changing the configuration, you need to confirm the corresponding configuration file in the database. For example, our automatically installed abase will have postgresql.auto.conf, and max_connections is configured here.
One: How to make the modified configuration item take effect?
1. Checking the postgresql.conf configuration file, you can see that there is # (change requires restart) after the port configuration, which means that the reload configuration does not make the configuration effective, and the database needs to be restarted.
Do all configurations need to be restarted to take effect? How to determine what operation can make the configuration take effect?
2. How the configuration takes effect can be found in pg_settings. There are seven different context fields as follows.
|
context |
Operation |
|
backend |
You can check in postgresql.conf These settings are changed without restarting the server. However, the new configuration values will only appear in subsequent connections. In existing connections, these values will not change. |
|
user |
This type of parameter means that ordinary users can change the configuration value of the parameter through the set command. |
|
internal |
This type of parameter is an internal parameter, that is, it cannot be modified unless it is re-initdb. |
|
postmaster |
After changing the configuration items of this type of parameter, you need to restart the PostgreSQL instance to take effect. |
|
superuser |
This type of parameter can be changed by the super user. When it is changed, it will only affect its own session, and will not affect other users. |
|
sighup |
Changing this type of parameter in the postgresql.conf configuration file does not need to restart the instance. You only need to send a SIGHUP signal to the postmaster process to read the configuration file again. After the postmaster process receives the signal, it will also send a SIGHUP signal to other child processes, so that the new parameter values will also take effect in other child processes. This type of parameter is different from the backend type parameter. |
|
superuser-backend |
Such parameters can be changed by the super user, and these settings can be changed in postgresql.conf without restarting the server. However, the new configuration values will only appear in subsequent connections. In existing connections, these values will not change. |
Supplement:
1. sighup means that the configuration can be reloaded without restarting the server. The method of reloading the configuration is as follows
①Execute the command: pg_ctl reload [-D DATADIRT]
②Use sql in the database: select pg_reload_conf();
2, postmaster. Need to restart the database.
For example, modify max_connections (the maximum number of connections)