New Feature of PostgreSQL 9.5: IMPORT FOREIGN SCHEMA

New Feature of PostgreSQL 9.5: IMPORT FOREIGN SCHEMA

This story tells the story of an employee who does not keep up with the pace of the times and does not actively learn the latest features, how to Lose the advantages of "old employees" and the trust of leaders.

On a morning without smog, the technical department leader called James to the office and asked, "Brother, I want to query the data of the production system in use on the new system, is there a fast and convenient way? Is there such a solution for the new PostgreSQL database ?"

JAMES: "lead, it's okay to export data from the production system and import the data to the new system ?"

Lead: "Is there a fast and convenient way ?"

JAMES: "Leaders, export data from the production system, and import the data to the new system soon ."

Lead: "Is there a quicker and more convenient way ?"

JAMES: "Leaders, export data from the production system, and then import the data to the new system soon !"

The leader was a little annoyed with Xiao Ming's reply, and was impatient. He asked, "what preparations do I need to prepare for importing External tables ?"

James said with confidence: "The leader is to create ddl ."

The leaders seriously asked: "How many tables are there ?"

James is not sure: "200 sheets ?"

The leaders were surprised: "So much ?!"

James suddenly remembered something and whispered, "lead ...... I just remembered... another user, which has about 300 tables... "(-_-|) (he cannot even hear his own voice)

Leaders are a little overwhelmed: "Get out !"

James said with a red face: "lead! You heard me say ..."

The leaders were completely ignited this time: "Get out !!!"

James quietly closed the door for the leaders ......

The manager is already angry. At this time, the boss's phone number has been called: "How is it? OK ?"

Lead: "No problem, boss. Wait a moment !"

Boss: "Okay, let's say a good job. Let's talk to the customer about our new system. At least we have a query experience. Wait for your message! Hurry up ."

At this time, the leaders remembered the new company's colleague Xiaoqiang. Although Xiaoqiang seems to be very funny and lively at ordinary times, Xiaoqiang is already one of the few employees in the company who work as databases. The leader figured out how to explain to the boss the slow development progress of the system and the delay in experience time. With a try-on mentality, Xiao Qiang was inspired to express his needs in this situation.

Xiao Qiang: "leaders, I cannot guarantee that this problem can be solved. I will do my best ."

Leaders: "I think you are lively and exaggerated at ordinary times. I have reserved my speech today. What are your ideas ?"

Xiaoqiang: "Leaders, don't move the database any joke. I dare to open it. It's just a matter of fact, it's useless. I saw a new feature in PostgreSQL some time ago. You can simply import External tables and import them by mode. I think you can try it ."

Lead: "Well, do it. Be careful when operating. Don't be careless ."

Xiaoqiang: "I understand. I will send you a message within half an hour. I will report it to you immediately, whether it succeeds or fails ."

After Xiaoqiang went out, the leaders ordered a cigarette and waited for the seemingly unreliable Xiaoqiang.

At the same time, the leader was also thinking that the method mentioned by Xiaoqiang sounds really good in theory and does not know how to put it in practice. He compared the two employees' solutions:

James's solution:

Xiaoqiang's solution:

Xiaoqiang went back to the work station and poured a cup of water, causing some psychological pressure. Although he had just tested the new function (imported to the external mode), he was still very nervous, he felt that he was responsible for the trust of the leaders and the stable operation of the production system. This was definitely not a joke.

He opened his notebook, took a deep breath, and operated with caution:

1. Determine the database version of the new environment:

-Bash-3.2 $ psql

Psql (9.5beta2)

Enter "help" to obtain help information.

S = # selectversion ();

Version

Bytes -------------------------------------------------------------------------------------------------------------

PostgreSQL9.5beta2onx86 _ 64-pc-linux-gnu, compiledbygcc (GCC) 4.1.220080704 (RedHat4.1.2-55), 64-bit

(1 line record)

2. Create a user-based mode:

Postgres = # \ cmusic

You have now connected to the database "music", user "s ".

Postgres = # createschemaericauthorizationeric;

CREATESCHEMA

3. Install the postgres_fdw plug-in:

Music = # createextensionpostgres_fdw;

CREATEEXTENSION

4. Create an external server object:

Music = # createservermusic_fdw_serverforeigndatawrapperpostgres_fdwoptions (host '192. 168.1.143 ', dbname 'music', port '123 ');

CREATESERVER

5. Create a user ing object:

Music = # createusermappingforericservermusic_fdw_serveroptions (user 'Eric ', password 'gao ');

CREATEUSERMAPPING

Connect to the production database and check the table information of the production database:

Music = # \ cmusiceric

Youarenowconnectedtodatabase "music" asuser "eric ".

Music => \ d

Listofrelations

Schema | Name | Type | Owner

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

Eric | summary | table | eric

...

(200 rows)

6. Remotely import the entire schema:

Music = # importforeignschemaericfromservermusic_fdw_serverpoliceric;

IMPORTFOREIGNSCHEMA

7. view the status of the new system table:

Music => \ dsummary;

Referenced External table "eric. summary"

FIELD | type | modifier | FDW Option

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

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 data volume:

Music => selectcount (*) fromsummary;

Count

-------

8

(1 line record)

The data has been imported into the new system, and Xiaoqiang immediately calls the leaders to inform them.

Lead: "Good, Xiaoqiang. If the above requirement is to block some sensitive tables or only provide basic data, can this be achieved ?"

Xiaoqiang: "No problem. I will give you my test report. I just did it recently ."

Test Report:

Create two new tables at the source end:

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

(5 rows)

Exclude a table:

Music => \ cmusicpostgres

You have now connected to the database "music", user "s ".

Music = # IMPORTFOREIGNSCHEMAericEXCEPT (summary) FROMSERVERmusic_fdw_serverINTOeric;

IMPORTFOREIGNSCHEMA

Check the import results:

Music => \ d

Association list

Architecture mode | Name | type | Owner

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

Eric | ericgaoi | the referenced External table | postgres

Eric | ericgaoii | the referenced External table | postgres

(2 rows of Records)

The table summary has been excluded.

Delete the table in the target database and test the usage of a table:

Music => dropforeigntableericgaoi;

DROPFOREIGNTABLE

Music => dropforeigntableericgaoii;

DROPFOREIGNTABLE

This time, only some specified tables are imported:

Music = # importforeignschemaericlimitto (summary) fromservermusic_fdw_serverintoeric;

IMPORTFOREIGNSCHEMA

Music => \ d

Association list

Architecture mode | Name | type | Owner

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

Eric | summary | the referenced External table | postgres

(1 line record)

Lead: "Thank you, Xiaoqiang. Go back to work"

Said, the leader called James and thought that he would maintain a good relationship with his colleagues. He had more contacts and opportunities. Besides, James was also an old employee and he was angry with him just now.

Xiao Ming entered the company and the manager smiled and said, "James, something urgent just now. I'm a little impatient. Sorry ."

JAMES: "leaders, it's okay. It's true that I am not technically competent. I will learn more in the future !"

Lead: "Xiaoqiang has integrated all the tables. I think the query is slow. Let's see why ."

JAMES: "Good leaders. I will collect the information and report it to you immediately !"

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

On the new system:

Music => analyzeeric. summary;

ANALYZE

Music => explainanalyzeselect * fromsummary;

QUERYPLAN

Bytes ----------------------------------------------------------------------------------------------------------

ForeignScanonsummary (cost = 100. 00 .. 101.24 rows = 8 width = 28) (actualtime = 4. 308 .. 4.319 rows = 8 loops = 1)

Planningtime 0.076 ms

Executiontime: 8.308 ms

(3 rows of Records)

In the production system server:

Music => analyzeeric. summary;

ANALYZE

Music => explainanalyzeselect * fromsummary;

QUERYPLAN

Bytes --------------------------------------------------------------------------------------------------

SeqScanonsummary (cost = 0. 00 .. 1.08 rows = 8 width = 28) (actualtime = 0. 003 .. 0.004 rows = 8 loops = 1)

Planningtime 0.108 ms

Executiontime: 0.023 ms

(3 rows)

James submitted the above information to the leader and explained: "leaders, you can see that the collected information shows that remote table queries are slow. It seems that this function is still unreliable, I suggest using my solutions to export data, or buy third-party software to synchronize data and synchronize the data locally in real time."

Lead: "James... teacher... is the company asking you to give a report and tell the boss to spend money on new products? What else do you do? Can I use HotStandby or third-party software to synchronize data? Do you want to use all projects in a data import solution ?! Currently, this query speed is acceptable to customers. Isn't it a relatively slow query speed or not ?!! Can I use materialized views to check remote data and improve performance ?!!! "

JAMES: "lead, sorry... materialized view is... what? "

Lead: "get out !!! "

The External table still has a certain latency in query speed. If the project can accept the actual query speed, it will be OK. If you cannot accept it, you can try to use the materialized view. Of course there are still many solutions, and the methods are always more difficult ~~~

Materialized views are not a waste of space here. If you are interested, refer to the following article, which provides performance tests for materialized views:

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