📢 📢 📢 📣 📣 📣 hello! Hello, everyone, I am [IT Bond], known as Jeames007. I have 10 years of DBA working experience and am a member of China DBA Union (ACDU). I am currently engaged in DBA and program programming 😜😜😜. ❤️❤️❤️ Thank you all! ❤ ️ ❤ ️ ❤ ️

⛳ ️ 1. Introduction of PG

The official website link: www.postgresql.org

PostgreSQL is an open source, free relational database management system developed by the Department of Computer Science at the University of California, Berkeley. After more than 15 years of active development and continuous improvement, PostgreSQL has gained a relatively high reputation for reliability, stability, and data consistency. ● PostgreSQL runs on all major operating systems, including 34 platforms including Linux, AIX, HP-UX, Solaris, and Windows. ● PostgreSQL is a complete transaction security database with complete support for foreign keys, unions, views, triggers, and stored procedures (and support for developing stored procedures in multiple languages). ● PostgreSQL supports most of the SQL standard data types, including integer, numeric, Boolean, byte, character, date, time, and binary large objects (pictures, sounds, and videos). ● PostgreSQL stored procedures can be developed in a variety of programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and PL/pgSQL, which is similar to Oracle PL/SQL and has hundreds of built-in functions. Features range from basic arithmetic calculations and string processing to cryptographic logic calculations and high compatibility with Oracle. ● PostgreSQL has native programming interfaces for many advanced development languages, such as C/C++, Java,.NET, Perl, Python, Ruby, Tcl and ODBC, among others, as well as documentation. ● PostgreSQL has advanced features like Multi-version Concurrency Control (MVCC), point-in-time recovery (PITR), table Spaces, asynchronous replication, nested transactions, online hot backup, planning and optimization of complex queries, and write-ahead logging for fault tolerance. ● PostgreSQL supports international character sets, multi-byte encoding, sorting, case handling, and formatting in local languages. ● PostqreSQL provides a wide range of high-quality graphical PostgreSQL management tools, both open source and commercial. PostgreSQL source code is freely available under a very free open source license that allows users to use, modify, and distribute PostgreSQL source code in a variety of open and closed source projects. Users can modify and improve the source code in any way they wish. ● There are many business systems using PostgreSQL to manage more than 4 TERabytes of data in real production environments. ● PostgreSQL's rapid growth was fueled by MySQL's acquisition by Oracle and Microsoft's announced acquisition of PostgreSQL startup Citus Data in 2019.Copy the code

⛳ ️ 2. PG installation

PG can be installed in a variety of ways, similar to MySQL, giving users a large choice. For example: RPM package installation (online, offline), source code compilation and installation, binary installation (direct decompression, PG10 does not support Linux)

🐴 2.1 Windows Installation

Click on the blog: PostgreSQL installation and Deployment for Windows

🐴 2.2 Linux Installation

Install PostgreSQL for Linux

🐴 2.3 Docker

Warehouse: https://hub.docker.com/_/postgres\ [root@centos7 ~]# docker pull postgres:13.3 # Whitelist configuration cat << EOF > / TMP /pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 Trust host all All 0.0.0.0/0 MD5 host Replication all 0.0.0.0/0 MD5 local replication all trust EOF [root@centos7 ~]# docker ps -a --format "table {{undefined. {ID}} \ t undefined. {Names}} \ t {undefined. {Status}}"Copy the code

[root@centos7 ~]# docker exec -it jempg133 bash
root@jempg133:/# su - postgres
postgres@jempg133:~$ psql
postgres=# select version();
Copy the code

⛳️ 3.PG Client tool

The Navicat connection is as follows

PgAdmin4 connections are as follows:

PgAdmin4 is a graphical user interface (GPU) management tool based on the PostgreSQL database. PgAdmin3 is a desktop graphical user interface (GPU) management tool based on the PostgreSQL protocol. PgAdmin4 is open-source, free, and commercially available. PgAdmin4 is a Web application developed in Python. It can be accessed in Web mode through a browser or run independently in desktop mode. Download: https://www.pgadmin.org/download/Copy the code

⛳️ 4.PG data type

Check out the blog: PostgreSQL data types overview, family and children here

⛳️ 5.PG Architecture

Click on the blog: To understand PostgreSQL, you need to understand the architecture

🐴 5.1 Physical Structure

🐴 5.2 Memory Structure

⛳️ 6. Zabbix monitors PG

Click on the blog: Zabbix Surveillance PG, enterprise must-have, classic

⛳️ 7. PG Backup and restore

🐴 7.1 Logical Backup Restoration

Pg_dump supports specifying the objects to be backed up. Tables, schemas, and databases can be backed up separately. Pg_dumpall Supports exporting only full database data. Pg_dump can back up data to an SQL text file format, a customized compressed file format, or a TAR package format. PostgreSQL supports the following data restoration methods: 1. Use PSQL to restore data backup in SQL text format generated by PG_dump or PG_Dumpall. 2. Use the pg_restore tool to restore customized compressed format (-FC), TAR format (-ft), or directory format (-fd) backups generated by the pg_dump tool.Copy the code

🐴 7.2 PG Physical backup restoration

Pg_rman - pg_rman- is an open source PG backup software. It is a third-party backup software that copies files. It must be installed with the Server to support time-based recovery, support online full backup and add backup, and support WAL backup deletion after expirationCopy the code

Systematic mastery of PG backup recovery, www.bilibili.com/video/BV1af…

How to become a SQL master PostgreSQL from start to start

You can like, collect, pay attention to, comment on me, there are database related questions to contact me or exchange yo ~!