RedHat PostgreSQL Installation Details

ArticleDirectory

• I. Software Packages

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