Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to use MYSQL or ORACLE methods to manage POSTGRESQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

The reason for writing this article is that many DB people devote themselves to learning PG database and encounter some difficulties. In fact, when I put forward this topic, I was also thinking that each database has its own suitable management method, some of which are actually helpless. Recently, someone asked POSTGRESQL whether the method used was closer to ORACLE or MYSQL.

Why did you bring up such a topic?

1 before using PG, the database used or managed by the questioner has gone deep into the bone marrow and is willing to use the original management method to manage the new database, which is very normal. We are all willing to apply the existing experience to new transactions to speed up the understanding and use of new things.

2 underestimate the conflict between new things and old experience, such as going abroad to take a bus, if you do not press the STOP button, the bus will not stop at the station, but such a situation will not happen in China, what is more interesting is that if you press the wrong button, you also have to get off, because sorry, don't ask me how I know.

So today's topic is a bit interesting, a little discussion, three pieces of data and user experience to a MIX is a very interesting topic.

1 there is no concept of DATABASE in ORACLE (similar to MYSQL SQL SERVER), but there is the concept of SCHEMA in ORACLE. In the world of ORACLE, it can be regarded as a SCHEMA is a DATABASE.

2 there is no concept of SCHEMA in MYSQL, it divides logical or physical data information through different DATABASE.

3 databases such as POSTGRESQL and SQL SERVER belong to comparison, no matter what, both of which have the concept of SCHEMA and DATABASE. It's OK in any way you want to split it. However, for historical reasons of SQL SERVER, it is common to use DATABASE to split.

Speaking of which, the question is what to do with PG. The concepts of SCHEMA and ORACLE in PG are the same, and unfortunately, his concept of DATABASE is the same as that of MYSQL. Looks like PG belongs to the one who stepped on two boats.

When we use PG, we prefer to cram all the tables into one database and manage them with SCHEMA + user + permissions.

Or use MYSQL or SQL SERVER to create multiple databases in an INSTANCE way, each DATABASE has a different user way to manage, more in line with the character of PG.

If we manage PG as ORACLE, creating multiple SCHEMA in one DATABASE, that is, abandoning PG's multi-DATABASES concept and using only one database, we will encounter another problem, autovacuum worker, according to the following official text

If all the tables are crammed into a database, can I infer that even if multiple WORKS are set up, only one cleanup thread can be used in a database (if this is misunderstood, please let me know).

This leads to a different concept. There are UNDO LOG and cleaning threads in ORACLE, but the principle of PG is that there is no REDO UNDO LOG. If you implement it through the table itself, you will have the meta-ancestor of DEAD. Failure to clean up the dead Yuanzu in time will cause a series of problems.

So for the time being, I can only understand that if you want to use ORACLE to manage the PG database, it is best not to have too many tables and not too many tables.

To put it another way, I use MYSQL to manage it, and I can always avoid the above worries, but PG's access to the data of other databases is not as simple as MYSQL, select * from library name. Table name

You can query across libraries, but use dblink to connect different databases in the same INSTANCE (the official name of PG should be called cluster). This is MYSQL database administrator can not understand, and also feel more troublesome.

At this point, I fell into a mode of thinking that PG is not easy to use. It is different from ORALCE, MYSQL and SQL SERVER. In the established mode, I can't understand why this PG is so weird. Not standing, not even sitting.

In fact, I do not think so, to learn a new thing, must not hold the original mode of thinking to learn, so that everything may feel that it is not as convenient as mine. But in fact, if you go deep into the study of PG, you will find that in addition to such things, the scalability and polymorphism of PG is also incomparable to other databases.

So now that we have a certain understanding of the above problems, we can avoid some possible problems. For example, I can use ORACLE to manage PG and build multiple SCHEMA, but if one set of tables is unrelated to the other, then I will create a new database on the CLUSTER of PG, put these illogical tables into another DATABASE, or I can create a cross-library VIEW. To solve the problem of needing DBLINK, manage him in a way that suits PG, forget to use ORACLE or MYSQL to manage PG, because PG is PG a different kind of fireworks.

The above is the editor for you to share how to use MYSQL or ORACLE method to manage POSTGRESQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.