[PG Management] related to postgresql database management
-- single user initiates postgres-- single-D / pgdata/10/data postgres-- single-user action. When multi-user mode does not receive all commands, you can use the phase of single user to connect to the database initdb to repair the system table-- related parameters set select * from pg_settings;-- related parameter description (in select name,short_desc,unit from pg_settings limit 4) -- Parameter category-- internal: these parameters are read-only, some of which are written by the postgres program. -- postmaster: changing the values of these parameters requires restarting the instance. -- sighup: change these parameter values in the postgresql.conf file without restarting the database. You just need to send a SIGHUP signal to the postmater process to restart the loading configuration of the new parameter values. -- backend: similar to sighup, the change only applies to new links and is not valid in existing links. Select pg_reload_conf ();-- superuser: these parameters can be changed by super users using set, at session level. -- user: similar to supperuser. -check whether the parameter modification requires restarting the select name,context from pg_settings where name like 'wal_buffers';-- connection database. Parameter max_connections: default is 100. the maximum number of concurrent connections allowed to the database is superuser_reserved_connections: default is 3, which prevents ordinary users from consuming all connections and ensures that super users can connect to the database. -try to connect after idle for 180 seconds, connect once every 10 seconds, up to three times, and close the connection after failed tcp_keepalives_idle=180tcp_keepalives_interval=10tcp_keepalives_count=3-- memory configuration shared_buffers: usually set 25% of the system memory Twice the value of max_connections (MB) temp_buffers: for temporary tables work_mem: sort, hashmaintenance_work_mem: maximum memory used in maintenance operations synchronous_commit: whether to commit a transaction needs to wait for the wal log to be written to disk before returning. Default on-- log related logging_collector=on-- Open log _ min_duration_statement-- if a sql runs longer than how many milliseconds, log _ min_error_statement-- sql error messages can be recorded in the log log_statement-- whether to log ddl, dml and other operations-- commonly used management commands select version ();-- Database startup time select pg_postmater_start_time () -- View the last load configuration file time select pg_conf_load_time ();-- check the parameter configuration select current_settlings ('shared_buffers'); show shared_buffers;-- to view the walselect pg_xlogfile_name currently being written (pg_current_xlog_location ());-- see how many bytes in the buffer of the current wal are not written to the disk select pg_xlog_location_diff (pg_current_xlog_insert_location (), pg_current_xlog_location ()) -- View database status select pg_is_in_recovery ();-- View database size select pg_size_pretty (pg_relation_size ('ipdb2'));-- Table size select pg_size_pretty (pg_total_relation_size (' ipdb2'));-- Total size-- View tablespace size select pg_size_pretty (pg_tablespace_size ('pg_global')) -- kill running sql idselect pid,usename,query_start,query from pg_stat_activity;select pg_cancel_backend for a long time; select pg_terminate_backend