PostgreSQL 9.5 new features: IMPORT FOREIGN schema_php Tutorial

PostgreSQL 9.5 new features: IMPORT FOREIGN SCHEMA

This time in the form of a story about a person who does not follow the pace of the times, not active learning staff in the case of not understanding the latest features, how to lose the "old staff" advantage and leadership of the trust.

In a sunny, no haze in the morning, the technical department leader to call Xiao Ming to the office, asked: "Brother, I would like to query on the new system in the use of the production system data, there is no more rapid and convenient method?" Is there such a solution for the new PG database? ”

Xiaoming: "Lead, export the data from the production system, then import into the new system is not OK?" ”

Leader: "Is there a quicker, more convenient way?" ”

Xiaoming: "Lead, export the data from the production system, and then import it into the new system soon." ”

Leader: "Is there a faster, more convenient way?" ”

Xiaoming: "Lead, export the data from the production system, and then import the new system soon!" ”

The leader for Xiao Ming repetition machine-like answer some annoyed, according to withstand the impatience of heart, asked a sentence: "Import external table need to do what preparation work?" ”

Xiaoming said with confidence: "Leadership is to achieve the creation of a good DDL on the line." ”

The leader asked earnestly, "How many tables are there altogether?" ”

Xiao Ming is unsure: "200 photos?" ”

The leader was somewhat surprised: "So much?! ”

Xiao Ming suddenly think of something, whispered: "Leadership ... I just remember ... There is also a user, there are about 300 sheets ... "(-__-| | |) (The voice is so small that he can barely hear himself.)

The leader was a little too angry: "Get out!" ”

Xiao Ming Red Face said: "Leadership!" You hear me say ... "

The leader was completely ignited: "Get out!!!" ”

Xiao Ming silently in the outside to help the leader closed the door ...

The leader has been angry, the boss's phone has been called: "How?" Are you done? ”

Leader: "No problem, boss, wait a moment!" ”

Boss: "OK, good to say, good with customers to introduce our new development system, at least there is a query experience, waiting for your message!" Hold on. ”

The leader at this time think of the company's new colleague, Xiao Qiang, although Xiao Qiang usually looks more playful Shong lively, but the company has been a few companies to do database staff. The leader figured out how to explain to the boss how slow the development of the system is and how long it takes to delay the experience. Try to hold the mentality, summoned the cockroach, expressed in this situation under the demand.

Xiao Qiang: "Leadership, this problem, I do not promise to do a good job, I try." ”

Leader: "Usually see you lively exaggeration, today speak with reservations;" ”

Xiao Qiang: "leader, do not move the database what jokes I dare to open, real thing do things, do not come to those useless words." I saw PostgreSQL9.5 a new feature in the previous period, you can simply import external tables, import by mode, I think you can try. ”

Leader: "Well, do it, pay attention to the operation of caution, don't be careless." ”

Xiao Qiang: "Know, in half an hour to return your message, whether success or failure, are immediately reported to you." ”

When Xiao Qiang went out, the leader lit a cigarette, and was quiet to look "not reliable" small strong.

At the same time, the leadership is also thinking, Xiao Qiang said the method in theory sounds really good, do not know how to put in practice, he compared the plan of two employees:

Xiao Ming's plan:

Small and strong solutions:

Xiao Qiang back to the station, poured a glass of water, psychological some pressure, although he has just tested new features (import external mode), but the psychological is still very nervous, because he felt to the leadership of the trust responsible for the stability of the production system to run responsible, this is definitely not usually Dadanaonao, joking and laughing and other nonsense.

He opened the notebook, took a deep breath, and cautiously carried out the operation:

1. Determine the database version of the new environment:

-bash-3.2$psql

Psql (9.5BETA2)

Enter "Help" for information.

Postgres= #selectversion ();

Version

-------------------------------------------------------------------------------------------------------------

POSTGRESQL9.5BETA2ONX86_64-PC-LINUX-GNU,COMPILEDBYGCC (GCC) 4.1.220080704 (redhat4.1.2-55), 64-bit

(1 Line Records)

2. Create a user-based pattern:

Postgres=#\cmusic

You are now connected to the database "music", the user "Postgres".

Postgres= #createschemaericauthorizationeric;

Createschema

3. Install the POSTGRES_FDW plugin:

Music= #createextensionpostgres_fdw;

Createextension

4. Create an external server object:

music= #createservermusic_fdw_serverforeigndatawrapperpostgres_fdwoptions (Host ' 192.168.1.143 ', dbname ' music ', Port ' 5432 ');

Createserver

5. Create a user mapping object:

music= #createusermappingforericservermusic_fdw_serveroptions (user ' Eric ', password ' Gao ');

Createusermapping

Connect to the production library to see the table information for the production library:

Music=#\cmusiceric

Youarenowconnectedtodatabase "Music" Asuser "Eric".

Music=>\d

Listofrelations

schema| name| type| Owner

--------+---------+---------------+----------

Eric|summary|table|eric

...

(200rows)

6. Import the entire schema remotely:

Music= #importforeignschemaericfromservermusic_fdw_serverintoeric;

Importforeignschema

7. View the status of the new system table:

music=>\dsummary;

External table "Eric.summary" referenced

field | type | modifier | FDW Options

------+------------------------+--------+----------------------

id|integer| | (column_name ' id ')

Info|charactervarying (128) | | (column_name ' info ')

Server:music_fdw_server

Fdwoptions: (schema_name ' Eric ', table_name ' summary ')

8. Adjust the owner and permissions:

Music= #grantselectoneric. Summarytoeric;

GRANT

Music= #altertableeric. Summaryownertoeric;

Altertable

9. Verify the amount of data:

Music=>selectcount (*) fromsummary;

Count

-------

8

(1 Line Records)

The data has been imported into the new system, and Xiao Qiang immediately called the leader to inform.

Leader: "Good, Xiao Qiang, if the above requirements to shield off some sensitive table or only provide basic data good implementation?" ”

Xiao Qiang: "No problem, I give you my test report, recently just done." ”

Test report:

Create 2 new tables on the source side:

music=>createtableericgaoiasselect*fromsummary;

SELECT8

music=>createtableericgaoiiasselect*fromsummary;

SELECT8

Music=>\d

Listofrelations

schema| name| type| Owner

--------+-----------+---------------+----------

Eric|ericgaoi|table|eric

Eric|ericgaoii|table|eric

Eric|summary|table|eric

(5rows)

Exclude a table:

Music=>\cmusicpostgres

You are now connected to the database "music", the user "Postgres".

music= #IMPORTFOREIGNSCHEMAericEXCEPT (summary) fromservermusic_fdw_serverintoeric;

Importforeignschema

Look at the import effect:

Music=>\d

Association list

Schema Mode | name | type | owner

----------+-----------+--------------+----------

Eric|ericgaoi| refers to the appearance of |postgres

Eric|ericgaoii| refers to the appearance of |postgres

(2 Line Records)

Table summary has been ruled out.

Delete the table from the target library, and then test the usage that contains a table:

music=>dropforeigntableericgaoi;

Dropforeigntable

music=>dropforeigntableericgaoii;

Dropforeigntable

This time only some of the specified tables are imported:

music= #importforeignschemaericlimitto (summary) fromservermusic_fdw_serverintoeric;

Importforeignschema

Music=>\d

Association list

Schema Mode | name | type | owner

----------+---------+--------------+----------

Eric|summary| refers to the appearance of |postgres

(1 Line Records)

Leader: "Thank you, Xiao Qiang, get back to work."

Said, the leader dialed Xiao Ming's telephone, thinking or to maintain a good colleague relations, more than a network of contacts, many more opportunities, and also Xiao Ming is an old employee, just to his anger also some impulse.

Xiao Ming into the company, the leader smiled, said: "Xiao Ming, just a matter of urgency, some impatience, sorry." ”

Xiao Ming: "Leadership, it's okay, it is my technology is not fine, after a lot of learning!" ”

Leader: "Just now the cockroach to the table are all over, I see the query is a little slow, you give a look at the reason." ”

Xiao Ming: "Good leader, I collect information, the finished child immediately report to you!" ”

Xiaoming returned to the station and executed several commands to collect information on performance:

On the new system:

music=>analyzeeric.summary;

ANALYZE

music=>explainanalyzeselect*fromsummary;

Queryplan

----------------------------------------------------------------------------------------------------------

Foreignscanonsummary (cost=100.00..101.24rows=8width=28) (Actualtime=4.308..4.319rows=8loops=1)

Planningtime:0.076ms

Executiontime:8.308ms

(3 line Records)

On the server of the production system see:

music=>analyzeeric.summary;

ANALYZE

music=>explainanalyzeselect*fromsummary;

Queryplan

--------------------------------------------------------------------------------------------------

Seqscanonsummary (cost=0.00..1.08rows=8width=28) (Actualtime=0.003..0.004rows=8loops=1)

Planningtime:0.108ms

Executiontime:0.023ms

(3rows)

Xiao Ming put the above information to the leader, explained: "The leader, you see the information gathered to show that the remote table query is slow, it seems that the function is not reliable Ah, I suggest or use my plan, guide data, or can buy third-party software to synchronize data, real-time synchronization of data to local." ”

Leader: "Xiao Ming ... Teacher... Does the company pay you to give a report and tell the boss to pay for the new product? What else do you do with it? Synchronization data can be used Hotstandby, but also with third-party software? Do you have a guide to the data solution to eat all the items?! At present, this query speed customer is acceptable, not relative speed is not good?!! Want to check remote data and improve performance can you use materialized view?!! “

Xiao Ming: "leader, sorry ... Materialized view is ... What the? “

Leader: "Get out!!!" “

The external table still has a certain delay in the query speed, if you can accept the actual query speed in the project is OK. If you can't accept, you can try to use materialized views, of course, there are many solutions, the method is always more difficult ~ ~ ~

Materialized view here is not much wasted space, if interested, can refer to the following article, which has a materialized view of the performance test:

http://gaoqiangdba.blog.163.com/blog/static/245970045201510171821363/

http://www.bkjia.com/PHPjc/1093772.html www.bkjia.com true http://www.bkjia.com/PHPjc/1093772.html techarticle PostgreSQL 9.5 new features: IMPORT FOREIGN SCHEMA This time in the form of a story about a person who does not follow the pace of the times, not active learning staff in the case of not understanding the latest features, how to ...

