log_statement: Choices of none, ddl, mod, all.It may not be obvious what the PID is useful at first, but it can be vital for trying to troubleshoot problems in the future so better to put in the logs from the start. A good generic recommendation is ' : %t=timestamp, %u=db user name, %r=host connecting from, %d=database connecting to, %p=PID of connection. log_line_prefix: Appends information to the start of each line.log_min_duration_statement: Not necessary for everyday use, but this can generate logs of 'slow queries' on your system.should be the default on recent versions. log_min_error_statement: You should probably make sure this is at least on error, so that you will see any SQL commands which cause an error.Note that in some cases, the init script starting your database may be customizing the log destination in the command line used to start the database, overriding what's in the configuration files (and making it so you'll get different behavior if you run pg_ctl manually instead of using the init script). Best practice would be to try and make this as similar as possible across your servers. log_destination & log_directory (& log_filename): What you set these options to is not as important as knowing they can give you hints to determine where your database server is logging to.You should investigate the documentation on all of the options, but here are some tips & tricks to get you started: There are many things you can log that may or may not be important to you. pgMustard provides tuning advice based on EXPLAIN ANALYZE output.PgBadger analyse PostgreSQL logs to generate performance reports.The script can analyze the configuration and make tuning recommendations.dbForge Studio for PostgreSQL helps to identify productivity bottlenecks, and provides PostgreSQL performance tuning.SELECT * FROM pg_settings will label session specific changes as locally modified.SHOW ALL, SHOW will show you the current value of the setting.For a new database, this will mean the setting is using the default, but on running systems this may not hold true! Changes to the configuration files do not take effect without a reload/restart, so it's possible for the system to be running something different from what is in the file. Lines with # are comments and have no effect.It will generally be $PGDATA/nf ( SHOW data_directory), but watch out for symbolic links, postmaster.opts and other trickiness You can figure out the nf location with SHOW config_file.If the same setting is listed multiple times, the last one wins.Command line options override settings override nf settings.Important notes about configuration files You can tell which type of parameter a setting is by looking at the 'context' field in the pg_settings view. Most of the time you'll only use the first of these, but the second can be useful if you have a server you don't want to take down, while the user session settings can be helpful for some special situations.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |