Tips for PostgreSQL (dblink, SQL administrator, dbsize )-

(1) dblink functions similar to Oracle
Anyone who has used Oracle knows that Oracle has a very advanced function called dblink, which can be used in a database
A remote database, such as a database in Beijing, China, and a database in Shanghai, China.
This database is built on a dblink to the Shanghai database, and then the database in Shanghai can be faced on the Beijing database.
Query, update, or delete. This advanced feature is available in the original PostgreSQL code: contrib/dblink
And you can compile and install it to our database like this.
# Cd contrib/dblink
# Make
# Make install
Suppose our PostgreSQL is installed in:/home/pgsql.
After make install, there will be a dblink. So file in/home/pgsql/lib. This is required to use dblink.
Function file. In addition, there is a dblink. SQL file in/home/pgsql/share/contrib. This is the installation of dblink. So
The SQL statement required by the function.
You can install all the functions of dblink as follows:
# Cat dblink. SQL | Psql
[Pgsql @ Webtrends contrib] $ cat dblink. SQL | Psql
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create Function
Create type
Create Function
Create Function
Create Function
Create Function
Create Function
[Pgsql @ Webtrends contrib] $
This indicates that our function is successfully installed.
All the advanced functions of dblink can be used below.
Let's take a look at some function declarations in dblink. SQL to better understand their functions.

Enter Psql below:
Pgsql = # select dblink_connect ('host = localhost user = pgsql Password = ');
Dblink_connect
----------------
OK
(1 row)
This function is used to establish a connection to a remote database.

We can insert a record in a remote database as follows:
Pgsql = # select dblink_exec ('insert into student values (/'linux _ prog/',/'2017 /')');
Dblink_exec
-------------------
Insert 22516276 1
(1 row)

Now we can retrieve the insert record:
Pgsql = # select * From dblink ('select * from student ') as student (name varchar (100), pass varchar (100 ));
Name | pass
------------ + -------
Linux_prog | 12345
(1 row)

How is it? The insert record is already in.

Dblink is very powerful. I listed it as its simplest application. You can refer to the source code of postgresql below:
Contrib/dblink/SQL/dblink. SQL take a closer look.

(2) identify and optimize SQL statements with poor system performance
What we often do when doing ORACLE System Management is:
First, let's take a look at which SQL statements in the system have the worst performance. Run the Linux Command: top-C to find the PID of the first several Oracle processes, and then go to the relevant Oracle View
Find out these SQL statements, then look at the Execute Plan of these SQL statements, and then perform related optimization.
PostgreSQL also provides such advanced functions.
First, enable stats_command_string = true in PostgreSQL. conf to enable the stats collector process of postgresql to monitor each session.
SQL statement.
Compile related scripts:
Viewsql. sh:
#! /Bin/sh

######################################## ##############
# Viewsql. Sh #
# Author: linux_prog #
# Use to show all active sessions's SQL in PostgreSQL .#
######################################## ##############

If test-Z $1; then
Echo "Usage: $0 PID"
Exit 10
Fi

Echo "select * from (select pg_stat_get_backend_pid (S. backendid) as procpid, pg_stat_get_backend_activity (S. backendid) as current_query from (select pg_stat_get_backend_idset () as backendid) as s) as querystring where procpid = $1; "| Psql

This script displays the SQL statement currently being executed by the Session of the specified PID.
For example:
I use top-C and the result is:
3665 pgsql 15 0 124 m 124 m 122 M r 30.0 0: 04 s: pgsql [local] Insert

As you can see, the PID 3665 is displayed in the first one, indicating that its SQL efficiency may be relatively low.
[Pgsql @ Webtrends bin] $./viewsql. Sh 3665
Procpid | current_query
--------- + ---------------------------------------------------
3665 | insert into access_log select * From access_log;
(1 row)

We can see the SQL statements that are being executed, and then we can optimize the performance of these SQL statements.
If the execution speed of a SELECT statement is too slow, we can use explain to check whether the Execute plan has a proper index or
Tables have not been analyzed for a long time, and so on.

In addition, you can provide a script to kill a session. For example, if a session occupies too many resources and is not killed, the system may be down.
Killsession. sh:
#! /Bin/sh
######################################## ########
# Author: linux_prog #
# Use to kill one session .#
######################################## ########
If test-Z $1; then
Echo "Usage: $0 PID"
Exit 10
Fi

SID = $1
Echo "select pg_cancel_backend ($ Sid);" | Psql

For example, execute:
[Pgsql @ Webtrends bin] $./killsession. Sh 3665
Pg_cancel_backend
-------------------
1
(1 row)

The current SQL operation of the session that occupies resources was canceled by cancel.
Psql 3665 displays:
Pgsql = # insert into access_log select * From access_log;
Error: canceling query due to user request

(3) clearly know the size of each table or index
Every DBA should know that the bottleneck of Io is the bottleneck of all databases. Therefore, when designing the table structure, We must minimize the size of each field,
Only in this way can the table size be as small as possible.
Also, when we make SQL adjustments, the first thing we do is definitely performance turnning for large tables.
Therefore, it is necessary to know the disk size occupied by each table or index. You can directly access the view of dba_segments in Oracle.
To know the size of each table or index.
PostgreSQL contrib/dbsize also has such a module.
You can install dbsize. So as you have installed dblink above.
View the table: access_log size as follows:
Pgsql = # select relation_size ('Access _ log')/1024/1024 | 'M' as dbsize;
Dbsize
--------
332 m
(1 row)

View the size of index: test_idx as follows:
Pgsql = # select relation_size ('test _ idx')/1024/1024 | 'M' as dbsize;
Dbsize
--------
0 m
(1 row)

First, we can write a script to perform this operation (for everyone to do it ).

 

The above content is quite practical. If you can understand it deeply, you will be able to manage the database on a daily basis.
Can not be underestimated.

 

 

How does PostgreSQL query across databases?

Dblink

Select * From dblink ('select ID, name from table2')

A (ID integer, name character varying (32) Inner join cc_table B on B. ID = A. ID