📢📢📢📣📣📣
Hello! Hello, I'm [IT bond], known as jeames007 in Jianghu, with 10 years of DBA working experience
A highly motivated [big data blogger]! 😜😜😜
Member of China DBA Alliance (ACDU), currently engaged in DBA and program programming, lecturer in station B and Tencent classroom, with a live broadcast volume of more than 10W
Good at mainstream data Oracle, MySQL, PG operation and maintenance development, backup and recovery, installation and migration, performance optimization, fault emergency treatment, etc.
✨ If there are [cute] who are interested in [database], please pay attention to [IT bond] 💞💞💞
❤️❤️❤️ Thank you, big and small! ❤️❤️❤️

⛳ ️1. Introduction to PostgreSQL

PostgreSQL advertises itself as the world's most advanced open source database.
Some PostgreSQL fans say it is comparable to Oracle,
And there is no such expensive price and arrogant customer service.
It has a long history and was first developed at the University of California, Berkeley, in 1985
After more than 15 years of active development and continuous improvement,
PostgreSQL has gained a relatively high reputation in the industry in terms of reliability, stability and data consistency.

The database ranking in 2022 is as calm as in February of previous years. There is no change in the top ten ranking compared with last month, and PG ranks fourth

What are the advantages of PostgreSQL database?

PostgreSQL Database is a powerful open source database,
It supports rich data types (e.g JSON and JSONB Type, array type), and custom type.

PostgreSQL The database provides rich interfaces, which can easily expand its functions,
If you can GiST Implement your own index type under the framework and support the use of C Language to write custom functions and triggers,
It also supports writing custom functions in popular programming languages.

PostgreSQL Database has the following advantages:

PostgreSQL Database is the most powerful open source database at present,
It is closest to industrial standards SQL92 Query language,
At least SQL:2011 160 of the 179 main functions required in the standard
(Note: at present, no database management system can be fully implemented SQL:2011 All major functions in the standard).

Stable and reliable: PostgreSQL It is the only open source database that can achieve zero data loss.
At present, it is reported that some banks at home and abroad use it PostgreSQL database

Open source saves money: PostgreSQL The database is open source and free,
And it uses classes BSD There are basically no restrictions on the use and secondary development of the protocol.

Wide range of support: PostgreSQL The database supports a large number of mainstream development languages,
include C,C++,Perl,Python,Java,Tcl as well as PHP Wait.

PostgreSQL Active community: PostgreSQL Basically, a patch version is launched every three months,
This means known Bug It will be repaired soon, and the needs of application scenarios will be responded in time.

⛳ ️2. Advantages of PostgreSQL over MySQL

The standard implementation of SQL is more perfect than MySQL, and the function implementation is more rigorous;
The function support of stored procedure is better than that of MySQL, and it has the ability to locally cache the execution plan;
The table connection support is relatively complete, the function of the optimizer is relatively complete, many index types are supported, and the complex query ability is strong;
PG main table is stored in heap table, and MySQL uses index to organize tables, which can support a larger amount of data than mysql.
The active / standby replication of PG belongs to physical replication. Compared with the logical replication based on MySQL binlog, the data consistency is more reliable, the replication performance is higher, and the impact on the host performance is less.
The plug-in mechanism of MySQL's storage engine has the problem that the complex locking mechanism affects concurrency, but PG does not exist.

⛳ ️3. Comparison of PG, Oracle and MySQL

⛳ ️4.PG Learning Guidelines

1. Recommended reading< PostgreSQL The nine story pagoda of learning:
https://mp.weixin.qq.com/s/i7b6FvY3PYC2JENCgiVxjQ
2.Self regulated learning and reading PG Application management basic documents,
This part is mainly about China PG Branch training certification PGCA Selected courses,
Related links: http://www.postgresqlchina.com/tecdoc
3.PG The main site of learning
PG International community: https://www.postgresql.org/
1) PG Summary: https://www.postgresql.org/about/
2) PG Online help documents (English version, multiple) PG Version:
https://www.postgresql.org/docs/
3)Also available through PG The Chinese manual can be accessed at:
http://www.postgres.cn/docs/10/；
http://www.postgres.cn/docs/11/
4)Installation media download address:
https://www.postgresql.org/download/
There are mainly two ways: binary and source code compilation and installation. Binary installation media correspond to different operating systems.
There is also an exception based on PG Installation of the product release version of,
The installation media and installation methods can be obtained through the company's official website of the product,
For example, Ali POLARDB,AsiaInfo  ANTDB,tencent TBase,Huawei GaussDB,Han Gao HGDB Wait.
5)Online learning resources
https://www.postgresql.org/docs/online-resources/
It contains rich tutorials and hands-on practice resources

⛳ ️5.PostgreSQL tools

PostgreSQL tools can be divided into the following categories:

Backup and recovery tools
Monitoring tools
Logical and trigger based replication tools
Multi master replication tool
High availability and failover tools
Connection pool tool
Table partitioning tool
Migration tools

🐴 5.1 backup and recovery tools

1. Barman
Barman (Backup and Recovery Manager-Backup and Recovery Manager) 
It's a Python Language implemented PostgreSQL Disaster recovery management tools,
It consists of the second quadrant company(2ndQuadrant)Open source and maintain. It allows us to perform remote backups in business critical environments,
Provide effective data assurance for database administrators in the recovery phase.
Barman The best features include metadata backup, incremental backup
 Retention policy, remote reply WAL File archiving, compression and backup.

2. EDB BART
EDB BART(Backup and Recovery Tool -Backup and recovery tools)It is enterprise level PostgreSQL Key components of data management strategy.
BART For large-scale deployment PostgreSQL The service provides the implementation of retention policy and point in time recovery.
BART 2.0 Version provides block level incremental backups.

3. PgBackRest
pgBackRest The main purpose of the tool is to make a simple and reliable backup and recovery tool,
To seamlessly access to large-scale databases and workloads. pgBackRest Abandoned other traditional backup tools tar and rsync The usual routine,
Its backup function is realized from the inside of the software, and uses the client protocol to interact with the remote server. Removed pair tar and rsync Dependence on,
Enable it to better meet the backup challenges for specific databases. Client remote protocols are more flexible,
The protocol can limit the connection type as required to ensure a safer backup process.

🐴 5.2 monitoring tools

1. PoWA
PoWA(PostgreSQL Workload Analyzer)yes PostgreSQL Workload analysis tools,
It collects performance data and provides real-time icon and image display to help us monitor and tune PostgreSQL The server.
It and Oracle AWR perhaps SQL Server MDW Very similar.

2. PgCluu
pgCluu It's a PostgreSQL Performance monitoring and audit tools.
It shows you in the form of a view PostgreSQL All statistics collected by the database cluster.
It can display a completed database cluster information and system utilization information.

3. Pgwatch2
Pgwatch2 It's surveillance PostgreSQL One of the easiest to use database tools.
It is based on Grafana And for PostgreSQL The database provides out of the box monitoring function.
Because it has been integrated into the container, we don't have to worry about various dependencies and complex installation steps,
The monitoring can be set up in a few minutes, and everything has been configured in advance.
We only need to configure the database connection to the monitoring to run the normal monitoring operation.

🐴 5.3 logic and trigger based replication tools

1. pgLogical
pglogical Is adopted PostgreSQL A logical replication tool implemented in the form of an extension.
Perfect integration, without using any triggers and external programs. The plug-in acts as a substitute for physical replication,
Adopt publishing in selective replication/Subscription model is an effective way to copy data.

2. Slony-I
Slony-I yes PostgreSQL The implementation of one master multi-slave replication system supports cascade replication.
development Slony-I The main purpose of is to realize master-slave replication,
The replication system includes all the features and capabilities required for the reasonable configuration of slave systems in large database systems.

Slony-I It is mainly designed for the scenario of data center and backup site,
In this scenario, all nodes are usually required to be available.

3. Bucardo
Bucardo It's a PostgreSQL Asynchronous replication system, allowing configuration of multi master and multi slave operations.
It is http://Backcountry. Developed by Jon Jensen and Greg Sabino of com.

🐴 5.4. Multi master copy tool

BDR
Postgres-BDR(Bi-Directional Replication for PostgreSQL)
It is the first open source in the world PostgreSQL Multi master replication system, which aims to strengthen the production environment.
From the second quadrant(2ndQuadrant)Open source and maintenance of the company, BDR Specially designed for geographically distributed cluster environments,
The funny asynchronous logical replication mode is used to support the distribution of 2 to more than 48 nodes in different regions.

🐴 5.5 high availability and failover tools

1. Repmgr
repmgr Is an open source, for PostgreSQL A tool for server cluster replication management and failover.
It extends PostgreSQL Built in hot-standby Ability,
You can set up a hot backup server, monitor replication, and perform management tasks(Failover, manual switching, etc). 
repmgr It's the second quadrant( 2ndQuadrant)Developed by the company.

2. PAF
PAF(PostgreSQL Automatic Failover-Automatic fault transfer tool)yes OCF Resource agents contribute to PostgreSQL of
 Its initial purpose is to Pacemaker Management and PostgreSQL Clarify the rules and make things simple, documented and effective.
If your PostgreSQL The cluster has enabled internal stream replication,
PAF Exposed to Pacemaker Every current PostgreSQL Status of instance node:
Which is the master, which is the slave, which has stopped, which is chasing the replication state, and so on. If the primary node fails,
Pacemaker By default, the failed master node is restored first. If the failure cannot be recovered,
PAF Will select the best one from the nodes(Closest to failed master node data)Promote to a new master node.

3. Patroni
Patroni Is a template that uses Python Provide you with a customized, highly available solution for
 For maximum availability, its configuration information is stored in the image ZooKeeper, etcd perhaps Consul Yes.
If DBAs，DevOps Engineer or SRE Looking for a high availability for rapid deployment in the data center PostgreSQL Programme,
Or other uses, Patroni Can help.

4. Stolon
Stolon It's a cloud native of PostgreSQL Highly available management tools.
The reason why it is cloud native Because it can be inside the container PostgreSQL Provide high availability(Kubernetes integrate)，
And other types of infrastructure support(For example: cloud IaaS，Old style infrastructure, etc)

🐴5.6,Connection Pooling Tools

1. PgBouncer
PgBouncer yes Skype The developer of the connection pool in 2007.
In the years since then, the project has been improved by many developers, but no matter how it changes,
Its reduction PostgreSQL The role of connection costs has not changed.
PgBouncer allow PostgreSQL A database operation provides client access that is greater than the maximum number of connections it can provide.
In essence, it only tracks each client connection, and then based on the configuration information,
Create some client connections and serve client access based on the principle of first in first service.

2. PgPool-II
pgpool-II It is also a connection pool, which we usually call it pgpool. 
It is another popular connection broker, which predates PgBouncer It will be released in about a year(2006 Released in the second half of). 
pgpool The application scope of is very relevant. The functions provided include query based replication, connection pool function, load balancing, parallel query, etc,
pgpool An important specific is connection pooling. If we have two PostgreSQL Server, we want to use virtual IP，
In this way, the client will not feel the impact of master database switching. Sometimes, in order to move between servers IP Address,
First, you need to send the data from the main database server IP Remove it and rebuild it on another machine, which will break the active link,
Resulting in temporary service unavailability. use pgpool You can cache the server until another server is promoted,
pgpool It will handle failover internally. From the perspective of application and client, it seems that the database has never been offline.

🐴 5.7 table partition tool

1. Pg_Partman
pg_partman yes PostgreSQL An extension to create and manage time-based or sequence based table partitions.
Multi level sub partitions are also supported. Child tables and triggers are managed by the extension itself. Tables that already have data can also easily add fine-grained partitions.
Optional retention policies can automatically delete partitions that are no longer needed.
Background worker process(BGW)It can automatically run partition maintenance and execute tasks regularly without relying on linux cron And other procedures are maintained from the outside.

2. pg_Pathman
pg_pathman yes PostgreSQL Pro The company's open source extension can provide optimized partition solutions for large distributed databases. use pg_pathman It can partition large databases without downtime, speed up partition table query, dynamically manage and add partitions,
Add external tables for partitions, operate joint partitions, etc.

🐴 5.8 migration tools

Ora2pg
Ora2Pg Is a Oracle or MySQL Database migration to PostgreSQL Free tools.
It can connect to Oracle Database, and then automatically scan and export the table structure or data on the source side,
Translate into PostgreSQL database SQL script. Ora2Pg Can be regarded as Oracle The reverse engine of the database,
For large enterprise database migration or Oracle Copy data to PostgreSQL Database and other scenarios.
It is easy to use and does not require any Oracle Database background, all you need to do is establish and Oracle Just a connection to the database.

Everyone likes, collects, pays attention to and comments 👇🏻👇🏻👇🏻 WeChat official account 👇🏻👇🏻👇🏻