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

This article mainly introduces how POSTGRESQL accesses the library with instance. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

As a matter of fact, when it comes to this question, some students will have questions. Is it so difficult to visit people with instance? It is estimated that students who have used SQL SERVER and MYSQL will ask such questions, while students of ORACLE will ask what multiple libraries of the same instance, really?

In fact, SQL SERVER itself is a database where multiple databases and schema coexist, and so is POSTGRESQL. At this time, it is estimated that the corners of the mouth of SQL SERVER students will rise, our function is the most complete, how can it be so troublesome to access multiple databases of the same INSTANCE.

In fact, I have different opinions for the following reasons

Multiple databases under the INSTANCE of the same database can be accessed without obstacles, and whether the disadvantages or advantages outweigh the disadvantages, it is hard to say, especially now when MYSQL is used in development, there are already separate databases and tables. Of course, it is not a bad thing that multiple databases can visit each other under the same INSTANCE. But you have seen N databases hanging under an INSTANCE, and then the library and the library boast about each other. How troublesome it is to open the database. In addition, from the current software development point of view, if it is still a project, such cross-library access should be reduced or even prohibited.

Of course, there are not so many projects that are not projects. PG can also be accessed across libraries, which is commonly used in the second way.

1 dblink

2 postgres_fdw

The following operations are based on PG version 11 and above

1 dblink

Dblink students who are familiar with the term ORACLE SQL SERVER usually access the data table of another physical database. POSTGRESQL can access another database or remote database in this example in this way.

1 establish a connection

Select dblink_connect ('test','host=192.168.198.100 port=5432 user=test password=test dbname=test')

2 query directly

Select * from dblink ('test','select * from actor limit 10') ast (actor_id int,first_name varchar (45), last_name varchar (45), last_update timestamp)

3 how to add conditions? is it written on the inside or on the outside

Select * from dblink ('test','select * from actor limit 10') ast (actor_id int,first_name varchar (45), last_name varchar (45), last_update timestamp) where first_name = 'Penelope'

The above example is written outside, but conditionally, it should be a cross-database query of the local database.

Then why not write it inside? in fact, it involves the problem of transfer characters, which is troublesome to write, so it is written on the outside.

Most of the articles are over in the end, in fact, this is the end, how to query how many I have dblink

How to clean up dblink, even G similar knowledge has not found a clear explanation.

In fact, DBLINK is a set of functions. Let's take a look at the common functions of DBLINK.

problem

1 to set up dblink, it is established through dblink_connect, but if you have built too many dblink, how to check how many dblink are currently built?

SELECT dblink_get_connections ()

In the above way, you can see the name and quantity of the specific local library that has been enabled with db_link.

2 remove dblink

Dblink_disconnect ()

3 determine whether the current BDLINK is in use

Dblink_is_busy

In addition, DBLINK also supports asynchronous calls, sending statements to remote's database and then waiting for the information to be retrieved. In fact, if an analogy is found in other databases, it may be similar to SQL SERVER SERVICE BROKE, but only similar.

In use, it should be similar to a handle, send the information to the remote database server, and then retrieve the information through the judgment handle. However, the returned information will be saved in the local memory, so it is not recommended to obtain information with a large amount of data. It is recommended to use the standard way to obtain data, that is, the top way.

At the same time, it is suggested to increase the size of work_mem and temporary table.

There should be some people complaining about the above way of visiting. It is troublesome. PG also provides another way, POSTGRES_FDW.

As a matter of fact, POSTGRES_FDW is not troublesome to do. 4 steps to OK.

1 create a create extension on the destination library

2 connections created in the destination library

3 create an account mapping between local users and remote connections

4 create an external table

Create extension postgres_fdw

Create server foreign_server_t foreign data wrapper postgres_fdw options (host '192.168.198.100century journal port' 5432century database 'test')

Create user mapping for postgres server foreign_server_t options (user 'test',password' test')

CREATE FOREIGN TABLE foreign_table (

Actor_id integer NOT NULL

First_name varchar (45)

Last_name varchar (45)

Last_update timestamp

SERVER foreign_server_t

OPTIONS (schema_name 'public', table_name' actor')

There is no difference between query and local database, which is the second way to solve the cross-database access of tables in two databases in local library.

The above is all the contents of the article "how POSTGRESQL accesses the library of the same instance". Thank you for reading! Hope to share the content to help you, more related knowledge, 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.