Basic Understanding:
PostgreSQL: POSTGRES is an object-relational database management system (ORDBMS) based on version 4.2 developed by the Department of Computer Science, University of California, Berkeley. PostgreSQL supports most SQL standards and provides many other modern features: complex queries, foreign keys, triggers, views, transaction integrity, MVCC. Similarly, PostgreSQL can be extended in many ways, for example, by adding new data types, functions, operators, aggregation functions, indexes. Free to use, modify, and distribute PostgreSQL for personal, commercial, and academic use.
Features and Advantages:
PostgreSQL is a free object-relational database server (database management system) distributed under a flexible BSD-style license. It provides an alternative to other open source database systems, such as MySQL and Firebird, and proprietary systems, such as Oracle, Sybase, IBM’s DB2, and Microsoft SQL Server.
PostgreSQL’s unusual name has caused some readers to stop and try to spell it, especially those who spell SQL as “sequel”. The PostgreSQL developers spell it as “post-ress-Q-l”. It is also often shortened to “postgres”.
In fact, PostgreSQL features both SQL-2/SQL-92 and SQL-3/SQL-99. First, PostgreSQL includes arguably the most extensive data types in the world, some of which are not available in commercial databases, such as IP types and geometric types. PostgreSQL is a full-featured free software database. For a long time, PostgreSQL was the only free software database management system that supported transactions, subqueries, multiversion parallel control systems (MVCC), data integrity checks, and more. Vendors such as Inprise’s InterBase and SAP broke the exclusiveness when they opened up their previously proprietary software as free software. Finally, PostgreSQL has a very active development team, and the quality of PostgreSQL is improving thanks to the efforts of many hackers.
Technically, PostgreSQL uses the classic C/S (client/server) architecture, where one client corresponds to one server daemon. This daemon analyzes query requests from the client and generates a planning tree. The data is retrieved and the results are eventually formatted and output back to the client. In order to facilitate the programming of the client, the database server provides a unified client C interface. PostgreSQL supports all types of database client interfaces, including ODBC, JDBC, Python, Perl, Tcl, C/C++, AND ESQL. This is one of PostgreSQL’s greatest strengths.
architecture
One reason PostgreSQL is strong is because of its architecture. Like commercial databases, PostgreSQL can be used in C/S(client/server) environments. This has many benefits for users and developers.
The PostgreSQL installation core is the database server process. It allows on a separate server. Applications that need to access data stored in the database must go through the database process. These clients cannot access the data directly, even though they are on the same machine as the server.
Teaching courses: PostgreSQL database teaching
(Lecturer: Zhou Zhengzhong (Dege), one of the founders of PostgreSQL China User Association, ali Cloud senior technical expert)
The syllabus
课时 1:1 PostgreSQL system overview and compilation installation 29:36
课时 2:2 PostgreSQL physics, logic, process structure and system table system functions 80:53
课时 3:3 PostgreSQL PSQL usage,SQL syntax, data types, recursive SQL usage 90:33
课 堂 4:4 PostgreSQL Index, Full text Retrieval, Fuzzy matching, Approximate degree matching 63:08
Class 5:5 PostgreSQL execution plan, cost formula explanation, cost factor calibration, automatic tracking SQL execution plan 75:37
Lesson 6:6 PostgreSQL connection pooling, local cache, remote cache 93:38
课时 7:7 PostgreSQL physical backup and restore, logical backup and restore 52:26
课 程 8:8 PostgreSQL stream replication setup master/slave environment, synchronous and asynchronous interpretation, stress testing, master/slave role switching
课 程 9:9 PostgreSQL Point-to-point multi-master table-level replication – Triggers 64:14
PostgreSQL table level copy – materialized view, support remote, heterogeneous, such as Oracle to PG materialized view 62:34
课 hour 11:11 PostgreSQL table level replication -Londiste3 installation and Use 85:47
课 hour 12:12 PostgreSQL table level replication -Londiste3 One-to-many replication and DDL errors 38:55
Lesson 13:13 PostgreSQL table Replication -Londiste3 Cascading Replication and Provider Switching 69:55
Class 14:14 PostgreSQL table level replication -Londiste3 Hash partition replication 61:53
课 hour 15:15 PostgreSQL table level Replication -Londiste3 Data synchronization from multiple nodes to a single node 79:19
PostgreSQL local partition table usage and optimization 36:11
课时 17:17 PostgreSQL shared nothing
Class 18:18 PostgreSQL Transaction Isolation Level implementation and Multi-version concurrency control 55:58
PostgreSQL lock type, lock mode, lock conflict, deadlock detection introduction
课时 20:20 PostgreSQL monitoring 1 statistical process and interpretation of statistical information 74:25
PostgreSQL Monitoring 2 Trend monitoring data collection and analysis, Nagios real-time monitoring deployment and custom monitoring 106:05
课时 22:22 monitoring PostgreSQL 3 usage of PostgreSQL performance snapshot and graphical analysis tool pg_statsinfo
课 hour 23:23 PostgreSQL monitoring 4 Dynamic kernel tracking staP 37:12
课 hour 24:24 PostgreSQL daily maintenance and inspection 63:57
Lesson 25:25 PostgreSQL database security Certification 19:37
Class 26:26 PostgreSQL object security control 41:13
课 程 27:27 PostgreSQL Modeling, Stress Test 137:2
Course objectives
Master the use of PostgreSQL database
Suits the crowd
DBA
Official website of Ali Yun University (Official website of Ali Yun University, Innovative Talent Workshop under cloud Ecology)