Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you what the commands in PostgreSQL, I believe most people do not know how, so share this article for your reference, I hope you have a lot of harvest after reading this article, let us go to understand it together!

PostgreSQL features are still very rich, there are sequences, support db link, basic Oracle concepts it also has here, at present does not support package.

The style is also similar to Oracle, without the quick and convenient show create table statement in MySQL.

From my usage habits, I basically pay attention to the following aspects.

View the configuration of the database

You can directly use the\l option to list all databases. The character set and basic configuration are clear at a glance. It feels like show pdbs in Oracle 12c.

From the process point of view, PG is a multi-process multi-threaded architecture design.

To view the current database, you can use current_database().

postgres=# select current_database();

current_database

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

postgres

2. review the user information

You can use\dn to get information about the schema. There are still some differences between schema and user in PG. In other databases, schema is basically user.

postgres-# \dn

List of schemas

Name | Owner

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

public | postgres

We create a schema and use\dn to view it.

postgres=# create schema jeanron100;

CREATE SCHEMA

postgres=# \dn

List of schemas

Name | Owner

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

jeanron100 | postgres

public | postgres

Or use the pg_authid data dictionary to view it.

postgres=# select *from pg_authid;

For the schema concept, we can create a table test,

postgres=# create table test(id int);

CREATE TABLE

This is a public schema.

postgres=# \d

List of relations

Schema | Name | Type | Owner

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

public | test | table | postgres

If you use\d to view field information, the result is as follows:

postgres=# \d test

Table "public.test"

Column | Type | Modifiers

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

id | integer |

You can also use pg_users to see, for example, I create a user replica, there will be corresponding configuration.

select *from pg_user;

usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig

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

postgres | 10 | t | t | t | t | ******** | |

replica | 16384 | f | f | t | f | ******** | |

To view the current schema information, use current_schema()

3. View session connection information

To view session information in PG, select * from pg_stat_activity;

If a connection is added and a session is opened, there will be a shadow process on the server side. The corresponding session can be found based on pid. Including SQL execution can be seen, if there are multiple sessions, it is multiple records.

postgres=# select * from pg_stat_activity;

-[ RECORD 1 ]----+--------------------------------

datid | 13241

datname | postgres

pid | 20644

usesysid | 10

usename | postgres

application_name | psql

client_addr |

client_hostname |

client_port | -1

backend_start | 2018-03-25 05:38:16.988057+08

xact_start | 2018-03-25 05:48:08.113649+08

query_start | 2018-03-25 05:48:08.113649+08

state_change | 2018-03-25 05:48:08.113653+08

waiting | f

state | active

backend_xid |

backend_xmin | 1753

query | select * from pg_stat_activity;

Similar to 4.show tables

There is no shortcut to show tables in PG for the time being. Currently, there are two types of shortcuts.

One is to view through the data dictionary pg_tables, which is equivalent to all_tables in Oracle

Or use tables in information_schema to view it.

postgres=# select *from information_schema.tables;

postgres=# select *from pg_tables;

The information_schema in PG is quite special. It cannot be seen directly\l in the database, but it does exist. The data dictionary style is very similar to MySQL.

5. User's Rights View

View permissions can be accomplished using\dp, or the equivalent command\z.

postgres=# \dp

Access privileges

Schema | Name | Type | Access privileges | Column privileges | Policies

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

public | test | table | | |

6. tabulation statement

Create table statement, there is no such shortcut as show create table, but you can use pg_dump or concatenate it according to the information in the data dictionary.

7. tablespace information

The table space part is relatively clear and can be done directly using\db.

postgres=# \db

List of tablespaces

Name | Owner | Location

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

pg_default | postgres |

pg_global | postgres |

Or use pg_tablespace

postgres=# select *from pg_tablespace;

spcname | spcowner | spcacl | spcoptions

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

pg_default | 10 | |

pg_global | 10 | |

8. Object Storage Information

This information can be found in pg_tables, and some more details.

More details remain to be verified and discovered.

9. View lock information

Information about locks can be obtained using pg_locks.

postgres=# select *from pg_locks;

-[ RECORD 1 ]------+----------------

locktype | relation

database | 13241

relation | 11673

page |

tuple |

virtualxid |

transactionid |

classid |

objid |

objsubid |

virtualtransaction | 4/81

pid | 20644

mode | AccessShareLock

granted | t

fastpath | t

10. View database parameters

This part of the function is not very understanding, because did not find a very convenient way.

like checking cache settings.

postgres=# show shared_buffers;

-[ RECORD 1 ]--+------

shared_buffers | 128MB

Or check the parameters file postgresql.conf.

11. Display the operational status of the database

This information is undoubtedly recommended to view from pg_stats_activity.

12. View Data Dictionary information

This should be the focus of this section, as you can see by looking at the views, there are over 100 views.

postgres=# select count(*)from pg_views;

-[ RECORD 1 ]

count | 112

You can also supplement this with information from information_schema.

13. View indexed information

To view the index information, you can use\di to complete, very fast.

14. view the execution plan

The execution plan can generally be obtained by explaining, but there are several ways to format the result, such as converting it to json or xml format.

postgres=# explain select *from test;

-[ RECORD 1 ]------------------------------------------------------

QUERY PLAN | Seq Scan on test (cost=0.00.. 35.50 rows=2550 width=4)

Get the execution plan in json format.

postgres=# explain(format json) select *from test;

-[ RECORD 1 ]------------------------------

QUERY PLAN | [ +

| { +

| "Plan": { +

| "Node Type": "Seq Scan",+

| "Relation Name": "test",+

| "Alias": "test", +

| "Startup Cost": 0.00, +

| "Total Cost": 35.50, +

| "Plan Rows": 2550, +

| "Plan Width": 4 +

| } +

| } +

| ]

Or do some analysis to get more detailed implementation information.

postgres=# explain analyze select *from test;

QUERY PLAN

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

Seq Scan on test (cost=0.00.. 35.50 rows=2550 width=4) (actual time=0.001.. 0.001 rows=0 loops=1)

Planning time: 0.018 ms

Execution time: 0.009 ms

15. View Stored Procedures

View stored procedures is relatively thin. You can use pg_proc directly to get detailed information.

pg_proc

16. Scheduling Execution of Stored Procedures

There's no direct way to see it, and it doesn't feel strong enough.

17. Transaction isolation levels

According to the current situation of the company and the continuous expansion of business scale, in fact, the technology is constantly improved and accumulated, as is the handling of affairs. When the scale reaches a certain magnitude, the requirements of this part will be very clear. So many developers are interested in locking mechanisms.

View two SQL statements for transaction isolation levels.

postgres=# show default_transaction_isolation;

default_transaction_isolation

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

read committed

View the current transaction isolation level settings.

postgres=# show transaction_isolation;

transaction_isolation

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

read committed

The above is all the content of this article "What are the commands in PostgreSQL?" Thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to 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.