Download as ODP, PPTX
![Creating a cluster Initdb -D /path/to/data/dir [options] -U postgres [schristensen@small_db ~]$ initdb -U postgres -D cluster1/data The files belonging to this database system will be owned by user "schristensen". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english".](https://image.slidesharecdn.com/pgconfeast-postgres-for-sysadmins-presentation-101004145218-phpapp01/75/PostgreSQL-Administration-for-System-Administrators-26-2048.jpg)
Uploaded byCommand Prompt., Inc
PostgreSQL Administration for System Administrators
The document is a presentation by Spencer Christensen on PostgreSQL administration for Linux/Unix system administrators, focusing on management and monitoring aspects. It covers various topics, including creating database clusters, configuring postgresql.conf and pg_hba.conf, logging options, and tools for monitoring database performance. Key commands and examples for installation, starting/stopping the database, and managing connections are also provided.
PostgreSQL Administration for System Administrators
- 1. Postgres Administration forSysadmins Spencer Christensen Backcountry.com PostgreSQL Conference East, Philadelphia March 26, 2010
- 2. PostgreSQL How doyou pronounce “PostgreSQL”?
- 3. The Q andL are silent
- 4. How do youpronounce Thule? “Yakima”
- 5. Audience Linux/Unix systemadministrator
- 6.
- 7. Want to understandPostgres better
- 8. What we'll coverManagement
- 9.
- 10. What we'll coverManagement Creating a cluster
- 11.
- 12. Logging options, reviewof log output
- 13.
- 14.
- 15.
- 16. What we'll coverMonitoring top
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23. Management Install byrpm/deb/source
- 24.
- 25. Multiple instances (differentports, different ip addresses, etc)
- 26. Creating a clusterInitdb -D /path/to/data/dir [options] -U postgres [schristensen@small_db ~]$ initdb -U postgres -D cluster1/data The files belonging to this database system will be owned by user "schristensen". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english".
- 27. Postgresql.conf Located at$PGDATA/postgresql.conf
- 28.
- 29.
- 30. Some changes requirea restart (others can be picked up with a reload)
- 31.
- 32.
- 33.
- 34. statement_timeout = 0 # microseconds
- 35. Postgresql.conf port =5432 port = 54321 listen_addresses = 'localhost' listen_addresses = '*' max_connections = 10 max_connections = 100 statement_timeout = 0 # microseconds statement_timeout = 1800000
- 36. pg_ctl pg_ctl -D/path/to/data/dir \
- 37.
- 38. Run as ownerof data files (created by initdb)
- 39. -m [smart|fast|immediate] stopStart cluster1
- 40. Logging options log_destination= 'stderr' # or syslog
- 41.
- 42.
- 43.
- 44.
- 45.
- 46. log_statement = 'none' # or all, ddl, mod
- 47. Log output samplesLOG: connection received: host=192.168.56.1 port=44738 LOG: connection authorized: user=training database=training LOG: duration: 18.301 ms LOG: duration: 0.898 ms LOG: duration: 1428.301 ms LOG: duration: 2038.301 ms LOG: disconnection: session time: 0:00:12.543 user=training database=training host=192.168.56.1 port=44738
- 48. Log output samplesLOG: connection received: host=192.168.56.1 port=33185 LOG: duration: 23.469 ms LOG: connection authorized: user=training database=training LOG: statement: select * from emusic.sales_item LOG: duration: 0.706 ms LOG: statement: select * from emusic.stores LOG: duration: 0.447 ms LOG: disconnection: session time: 0:00:12.084 user=training database=training host=192.168.56.1 port=33185
- 49. pg_hba.conf HOST DATABASE USER CIDR-ADDRESS METHOD [OPTION] local all pgmonitor trust host all all 10.41.8.21/32 md5 host all all 10.12.12.0/24 md5 Need to reload to take effect H
- 50. Postgres Env vars$PGDATA – data directory
- 51. $PGPORT – port
- 52. $PGHOST – host
- 53. $PGUSER – user
- 54.
- 55.
- 56. psql psql [-h$host] [-p $port] [-U $user] $db \d – list tables, views, sequences \dt – list tables only \d my_awesome_table – describe table, view \q – exit out of psql \l – list databases in cluster \du – list users in cluster \h – help
- 57.
- 58. top Example ofrunning queries
- 59. Du Example ofdisk space usage
- 60.
- 61.
- 62. Example of runningpgtop
- 63.
- 64. More like systemtop Can view running queries, locks, EXPLAIN Example of running pg_top
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75. Log files Connections,disconnections, durations, statements Vacuum verbose output
- 76.
- 77. Wrap up Initdb,postgresql.conf, pg_hba.conf
- 78. pg_ctl -D $datadirstart|stop|reload|status
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
Editor's Notes
- #3 History of Postgres: Developed at UC Berkely, called Ingres Academic project Business Ingres Corp formed Forks: Informix, Sybase Then creator decided on different implimentation: Postgres Postgres95 (1994-1995) SQL became popular/standard PostgreSQL (1996-now)
- #22 Top line output for postgres: User, database, host connected from, PID, command type