PostgreSQL 9.5 new features: IMPORT FOREIGN SCHEMA_PHP tutorial

New features of PostgreSQL 9.5: IMPORT FOREIGN SCHEMA

This time, a story is told about a man who does not keep up with the times and How do employees who do not take the initiative to learn lose the advantage of "old employees" and the trust of leaders without understanding the latest features.

On a sunny morning with no smog, the leader of the technical department called Xiao Ming to the office and asked: "Brother, I want to check the production system in use on the new system. Is there a faster and more convenient method for the new PG database? "

Xiao Ming: "Leader, export the data from the production system. , and then importing it into the new system will be OK?”

Leader: “Is there a faster and more convenient way?”

Xiao Ming: "Leader, it will be quick to export the data from the production system and then import it into the new system."

Leader: "Is there a faster and more convenient way?"

Xiao Ming: “Boss, it’s very fast to export the data from the production system and then import it into the new system!” The answer was a bit irritating. I suppressed my impatience and asked: "What preparations are needed to import external tables?"

Xiao Ming confidently said: "Leadership, just create a good ddl."

The leader asked seriously: "How many tables are there in total?"

Xiao Ming was a little unsure: "200?"

The leader was a little surprised: "So many?!"

Xiao Ming suddenly remembered something again and whispered: "Boss... I just remembered... there is another user with about 300 tables..." (- __-|||) (The voice was so low that he could hardly hear it)

The leader was a little out of breath: "Get out!"

Xiao Ming blushed and said: "Leader! Listen to me..."

The leader was completely ignited this time: "Get out!!! "

Xiao Ming silently closed the door for the leader outside...

The leader was already angry No, the boss has called: "How is it? Are you done?"

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

Boss: "Okay, let's tell you when it's done so that we can introduce our newly developed system to our customers. At least we have a query experience. We are waiting for your news! Hurry up."

At this time, the leader thought of Xiaoqiang, a new colleague in the company. Although Xiaoqiang usually looks funny and lively, Xiaoqiang is already one of the few employees in the company who works on databases. The leader has thought about how to explain to the boss that the system development progress is slow and the experience time is delayed. With the mentality of giving it a try, I summoned Xiaoqiang and expressed his needs in this situation.

Xiaoqiang: "Leader, I can't guarantee that I can do a good job on this issue. I will try my best."

Leader: "I usually see you I am lively and exaggerated, but I am reserved in what I say today. Tell me, what are your thoughts?” Let’s get down to business and stop talking about useless things. I saw a new feature in PostgreSQL 9.5 some time ago that allows you to simply import external tables by schema. I thought I’d give it a try.”

Leader: “Okay, let’s do it, be careful and don’t be careless.”

Xiaoqiang: “I know, I’ll get back to you within half an hour, regardless of success. If it still fails, we will report it to you immediately.”

After Xiaoqiang went out, the leader lit a cigarette and waited for Xiaoqiang, who usually seemed "unreliable".

At the same time, the leader was also thinking that Xiaoqiang’s method sounded good in theory, but he didn’t know how it would work in practice. He compared the two employees’ plans:

Xiao Ming’s plan:

Xiaoqiang’s plan:

Xiaoqiang returned to his work station and poured a glass of water. He felt a little stressed, even though he had just tested it. New function (importing external mode), but he is still very nervous because he feels that he must be responsible for the trust of the leader and the stable operation of the production system. This is definitely not the usual slapstick, joking, etc. Nonsense.

He opened the notebook, took a deep breath, and operated cautiously:

1. Determine the database version of the new environment:

-bash-3.2$psql

psql(9.5beta2)

Enter "help "To get help information.

postgres=#selectversion();

version

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

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 schema:

postgres=#cmusic

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

postgres=#createschemaericauthorizationeric;

CREATESCHEMA

3. Install postgres_fdw plug-in:

music=#createextensionpostgres_fdw;

CREATEEXTENSION

4. Create external server object:

music=#createservermusic_fdw_serverforeigndatawrapperpostgres_fdwoptions(host'192.168.1.143',dbname'mus ic' ,port'5432');

CREATESERVER

5. Create user mapping object:

music=#createusermappingforericservermusic_fdw_serveroptions(user'eric',password 'gao');

CREATEUSERMAPPING

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

music=#cmusiceric

Youarenowconnectedtodatabase"music"asuser"eric".

music=>d

Listofrelations

Schema|Name|Type|Owner

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

eric|summary|table|eric

...

(200rows)

6. Remotely import the entire schema:

music=#importforeignschemaericfromservermusic_fdw_serverintoeric;

IMPORTFOREIGNSCHEMA

7. View the new system table Status:

music=>dsummary;

Referenced external table "eric.summary"

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 owners and permissions:

music= #grantselectoneric.summarytoeric;

GRANT

music=#altertableeric.summaryownertoeric;

ALTERTABLE

9. Verify the amount of data:

music=>selectcount(*)fromsummary;

count

------

8

(1 line of record)

The data has been imported into the new system, Xiaoqiang Call the leader immediately to inform him.

Leader: "Yes, Xiaoqiang, if the above requires blocking certain sensitive tables or providing only basic data, is it easy to implement?"

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

Test report:

Create 2 new tables on the source:

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", user "postgres".

music=#IMPORTFOREIGNSCHEMAericEXCEPT(summary)FROMSERVERmusic_fdw_serverINTOeric;

IMPORTFOREIGNSCHEMA

Check the import effect:

music=>d

Association list

Architecture pattern|name|type|owner

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

eric|ericgaoi|referenced appearance|postgres

eric|ericgaoii|Referenced appearance|postgres

(2 lines)

Table summary has been excluded.

Delete the table in the target library, and then test the usage of a certain table:

music=>dropforeigntableericgaoi;

DROPFOREIGNTABLE

music=>dropforeigntableericgaoii;

DROPFOREIGNTABLE

This time only import some specified tables:

music=#importforeignschemaericlimitto( summary)fromservermusic_fdw_serverintoeric;

IMPORTFOREIGNSCHEMA

music=>d

Association List

Architecture Schema|Name|Type|Owner

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

eric|summary|Referenced appearance|postgres

(1 line record)

Leader: "Thank you, Xiaoqiang, let's go back to work."

As he said that, the leader dialed Xiao Ming's phone number, thinking that he still needed to maintain a good relationship with colleagues. , more connections, more opportunities, and Xiao Ming is also an old employee, so I was a little impulsive in getting angry with him just now.

Xiao Ming entered the office, and the leader smiled and said: "Xiao Ming, there was an emergency just now and I was a little impatient. I'm sorry." I’m not very skilled, so I’ll learn more in the future!”

Leader: “Xiaoqiang just sorted out the tables. I see that the query is a bit slow. Please tell me why.”

Xiao Ming: "Okay, leader, let me collect the information and report back to you immediately!"

Xiao Ming returned to work I executed a few commands and collected some performance information:

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.308 ms

(3 lines of record)

In production See the system server:

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 submitted the above information to the leader and explained: "Leader, look what we have collected The information shows that the remote table query is slow. It seems that this function is still unreliable. I suggest you use my solution to import the data, or you can buy third-party software to synchronize the data and synchronize the data to the local in real time. "

Leader: "Xiao Ming...Teacher...did the company pay you to come here and ask you to give a report and then tell the boss to spend money to buy new products? What else do you need to do? Can I use HotStandby to synchronize data, or use third-party software? Do you want to introduce a data import plan to cover all projects? ! The current query speed is acceptable to customers. Does it mean that it is relatively slow or not? ! ! If you want to check remote data and improve performance, can you use materialized views? ! ! ! "

Xiao Ming: "Leader, I'm sorry... What is a materialized view? "

Leader: "Get out! ! ! "

There is still a certain delay in the query speed of external tables. If you can accept the actual query speed in the project, it's OK. If you can't, you can try to use materialized views. Of course, there are many solutions, and the methods are always more difficult than difficult~~~

Materialization I won’t waste too much space on views here. If you are interested, you can refer to the following article, which contains performance testing of materialized views:

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

www.bkjia.com