ArticleDirectory
I. Software Packages
1. zlib-1.2.5.tar.gz
2. postgresql-9.0beta2.tar.gz
Ii. Installation Steps
1. Tar zxvf zlib-1.2.5.tar.gz
2. CD zlib-1.2.5
3../configure-Prefix =/usr/local/zlib make install
4. Tar zxvf postgresql-9.0beta2.tar.gz
5. CD postgresql-9.0beta2
6../configure -- With-libs =/usr/local/zlib/lib-with-provided des =/usr/local/zlib/include-without-Readline
7. gmake
8. Su
9. gmake install
10. adduser Postgres
11. mkdir/usr/local/pgsql/Data
12. chown Postgres/usr/local/pgsql/Data
13. Su-Postgres
14./usr/local/pgsql/bin/initdb-D/usr/local/pgsql/Data
15./usr/local/pgsql/bin/Postgres-D/usr/local/pgsql/data> logfile 2> & 1 &
16./usr/local/pgsql/bin/created Test
17./usr/local/pgsql/bin/Psql Test
18. The installation is complete and the test database is created.
3. Additional
1. Select datname from pg_database; Psql-l // display the database
2. Select spcname from pg_tablespace; // view the tablespace
3./Usr/local/pgsql/data/Pg_mirror.conf; // SDatabase Configuration File
4.Table 24-1.Standard Statistics View
View name |
Description |
Pg_stat_activity |
A row of each server process shows the database OID, database name, process ID, user OID, user name, the time when the current query and current query start to run, process start time, client address, and port number. Only when the stats_command_string parameter is enabled can various fields that report the information currently queried be obtained. In addition, unless the users who check these fields are superusers or have the same user with the process being reported, they are displayed as blank. (Note that due to the report delay of the collector, the current query only updates long-running queries in a timely manner .) |
Pg_stat_database |
A row of each database displays the database OID, Database Name, number of active server processes connected to the database, the total number of transactions committed, and the number of rollback tasks in the database, the total number of disk blocks read and the total number of buffer hits (that is, the required block has been found in the buffer, thus avoiding block reading ). |
Pg_stat_all_tables |
Each table row in the current database (including the toast table), table OID, mode and table name, the total number of sequential scans initiated, and the number of rows with raw data captured by sequential scans, the total number of index scans initiated (all indexes in the table), the number of raw data rows crawled by the index scan, and the total number of inserted, updated, and deleted rows. |
Pg_stat_sys_tables |
Like pg_stat_all_tables, only the system table is displayed. |
Pg_stat_user_tables |
Like pg_stat_all_tables, only the user table is displayed. |
Pg_stat_all_indexes |
For each index of the current database, enter the table where the index is located and the index OID, mode, table and index name, including the total number of index scans using the index, the number of index records returned by the index scan. Use the simple index of the index to scan the number of rows in a generated table. |
Pg_stat_sys_indexes |
Like pg_stat_all_indexes, only indexes displayed as system tables are contained. |
Pg_stat_user_indexes |
Like pg_stat_all_indexes, only indexes displayed as user tables are contained. |
Pg_statio_all_tables |
Each table in the current database contains a row (including the toast table), table OID, mode, and table name, including the total number of disk blocks read from the table, and number of buffer hit Times, the number of disk block reads and buffer hits for all indexes on the table, and the number of disk block reads and buffer hits on the secondary toast table (if any) of the table, as well as the total number of disk block reads and buffer hit for the toast table index. |
Pg_statio_sys_tables |
Like pg_statio_all_tables, only the system table is displayed. |
Pg_statio_user_tables |
Like pg_statio_all_tables, only the user table is displayed. |
Pg_statio_all_indexes |
Each index row in the current database contains the table and index OID, mode, table and index name, the number of disk block reads of the index, and the number of hits in the buffer zone. |
Pg_statio_sys_indexes |
Like pg_statio_all_indexes, only the system table is displayed. |
Pg_statio_user_indexes |
Like pg_statio_all_indexes, only the user table is displayed. |
Pg_statio_all_sequences |
A row of each sequence object in the current database, sequence OID, pattern and sequence name, number of sequential disk reads and buffer hits. |
Pg_statio_sys_sequences |
Like pg_statio_all_sequences, only the system sequence is displayed. (Currently, we have not defined the system sequence, so this view is always empty .) |
Pg_statio_user_sequences |
Like pg_statio_all_sequences, only the user sequence is displayed. |
Table 24-2.Statistics of access functions
Function |
Return type |
Description |
Pg_stat_get_db_numbackends (OID) |
Integer |
Number of active server processes that process the database. |
Pg_stat_get_db_xact_commit (OID) |
Bigint |
The number of committed transactions in the database. |
Pg_stat_get_db_xact_rollback (OID) |
Bigint |
Number of rollback transactions in the database |
Pg_stat_get_db_blocks_fetched (OID) |
Bigint |
Total number of disk block capture requests in the database |
Pg_stat_get_db_blocks_hit (OID) |
Bigint |
The total number of disk block capture requests that the database finds in the buffer zone |
Pg_stat_get_numscans (OID) |
Bigint |
If the parameter is a table, it is the number of sequential scans. If it is an index, it is the number of index scans. |
Pg_stat_get_tuples_returned (OID) |
Bigint |
If the parameter is a table, it is the number of tuples read by sequential scanning. If it is an index, it is the number of returned index tuples. |
Pg_stat_get_tuples_fetched (OID) |
Bigint |
If the parameter is a table, it is the number of table meta rows captured by bitmap scanning. If it is an index, it is the number of valid table tuples captured by simple index scanning. |
Pg_stat_get_tuples_inserted (OID) |
Bigint |
Number of inserted Table tuples |
Pg_stat_get_tuples_updated (OID) |
Bigint |
Number of updated tuples In the table |
Pg_stat_get_tuples_deleted (OID) |
Bigint |
Number of tuples deleted from the table |
Pg_stat_get_blocks_fetched (OID) |
Bigint |
Number of disk block capturing requests for tables or Indexes |
Pg_stat_get_blocks_hit (OID) |
Bigint |
Number of disk block requests for tables or indexes found in the buffer zone |
Pg_stat_get_backend_idset () |
Setof integer |
The number of active servers (from 1 to the number of active backend servers ). See the example in the text. |
Pg_backend_pid () |
Integer |
The ID of the server process that is attached to the current session. |
Pg_stat_get_backend_pid (integer) |
Integer |
The process Number of the server process. |
Pg_stat_get_backend_dbid (integer) |
Oid |
Database ID of the server process |
Pg_stat_get_backend_userid (integer) |
Oid |
User ID of the server process |
Pg_stat_get_backend_activity (integer) |
Text |
The current active query of the server process (null if the caller is not a superuser, or is not the same user as the session to be queried, or if stats_command_string is not enabled) |
Pg_stat_get_backend_activity_start (integer) |
Timestamp with Time Zone |
Specify the start time of the query currently being executed by the server process (if the current user is not a superuser, or is not the same user as the session to be queried, or if stats_command_string is not enabled, it is null) |
Pg_stat_get_backend_start (integer) |
Timestamp with Time Zone |
The server process start time. If the current user is not a super user, or the same user of the backend to be queried, This is the control. |
Pg_stat_get_backend_client_addr (integer) |
Inet |
The IP address of the client connecting to the given server. If the connection is established on a Unix domain socket, It is null. If the current user is not a superuser or the user of the queried session, it is empty. |
Pg_stat_get_backend_client_port (integer) |
Integer |
The IP port connecting to the client of the given server. If the connection is established on a Unix domain socket, it is-1. If the current user is not a superuser or is not the user of the queried session, it is null. |
Pg_stat_reset () |
Boolean |
Resets all statistics currently collected. |
Table 8-1.Data Type
Name |
Alias |
Description |
Bigint |
Int8 |
Signed 8-byte integer |
Bigserial |
Serial8 |
Auto-incrementing eight-character integer |
Bit [(N)] |
|
Fixed Length string |
Bit varying [(N)] |
Varbit |
Variable-length string |
Boolean |
Bool |
Logical Boolean (true/false) |
Box |
|
Rectangle in the plane |
Bytea |
|
Binary data ("byte array ") |
Character varying [(N)] |
Varchar [(N)] |
Variable-length string |
Character [(N)] |
Char [(N)] |
Fixed Length string |
CIDR |
|
IPv4 or IPv6 network address |
Circle |
|
Circle in the plane |
Date |
|
Calendar date (year, month, day) |
Double Precision |
Float8 |
Double Precision Floating Point Number |
Inet |
|
IPv4 or IPv6 network address |
Integer |
Int, int4 |
4-byte signed integer |
Interval [(P)] |
|
Interval |
Line |
|
Infinite Long line in a plane |
Lseg |
|
Line Segments in a plane |
Macaddr |
|
MAC address |
Money |
|
Currency amount |
Numeric [(P,S)] |
Decimal [(P,S)] |
Optional accurate numbers |
Path |
|
Geometric path in the plane |
Point |
|
Point in the plane |
Polygon |
|
Enclosed geometric path in the plane |
Real |
Float4 |
Single-precision floating point number |
Smallint |
Int2 |
Signed two-byte integer |
Serial |
Serial4 |
Auto-increment four-byte integer |
Text |
|
Variable-length string |
Time [(P)] [Without time zone] |
|
Time in a day |
Time [(P)] With Time Zone |
Timetz |
Time in a day, including the time zone |
Timestamp [(P)] [Without time zone] |
|
Date and Time |
Timestamp [(P)] With Time Zone |
Timestamptz |
Date and Time |