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

In this issue, the editor will bring you about the commonly used SQL sentences in PostgreSQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Create a new user

Create a new user

CREATE USER WITH ENCRYPTED PASSWORD''

User authorization

Grant access to CONNECT

GRANT CONNECT ON DATABASE database_name TO username

Then grant the mode to use

GRANT USAGE ON SCHEMA schema_name TO username

Grant SELECT permission to a specific table

GRANT SELECT ON table_name TO username

Grant SELECT to multiple tables

# execution format

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username

# example

Grant select on all tables in schema public to user1

If you want to automatically grant access to the new table in the future, you must change the default value

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO username

Create a database

Create a database with utf-8 characters and create it with template0 as a template

CREATE DATABASE dbname WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8'

Grant all permissions to the specified database to the specified user

GRANT ALL PRIVILEGES ON DATABASE dbname to username

Prompt FATAL: role 'root' is not permitted to log in after performing the login operation.

Alter user "root" login

Database backup and recovery

Back up all databases

Pg_dumpall > db.out

Restore all databases

# it doesn't matter which database you connect to when executing this command, because the script created by pg_dumpall will contain the appropriate commands to create and connect to the database

Psql-f db.out postgres

Back up a single database

Pg_dump-h localhost-U postgres (user name) Database name (same user name by default) > / data/dum.sql

Restore a single database

Psql-U postgres (user name) Database name (same user name by default)

/ data/dum.sql.gz

Restore a single compressed database backup

Gunzip < / data/dum.sql.gz | psql-h localhost-U postgres (user name) Database name (same as user name by default)

Backup single table operation

Pg_dump-U postgres-h localhost-p 5432-t staff-f staff.sql yjl (for database name)

-U represents the user

-h represents the host

-p represents the port number

-t represents the table name

-f indicates the name of the backed-up sql file

-d indicates that you want to restore the database name

Restore data sheet table operation

Psql-U postgres-h localhost-p 5432-d product-f staff.sql

Query the current link

Query current number of connections

# count the current number of connections

Select count (1) from pg_stat_activity

# query details on the number of current connections

Select * from pg_stat_activity

Query maximum number of connections

Show max_connections

# the maximum number of connections can also be configured in the pg configuration file:

# set it in postgresql.conf:

Max_connections = 500,

Count the disk size occupied by the database

Count the disk size occupied by each database

SELECT d.datname AS Name, pg_catalog.pg_get_userbyid (d.datdba) AS Owner

CASE WHEN pg_catalog.has_database_privilege (d.datname, 'CONNECT')

THEN pg_catalog.pg_size_pretty (pg_catalog.pg_database_size (d.datname))

ELSE'No Access'

END AS SIZE

FROM pg_catalog.pg_database d

ORDER BY

CASE WHEN pg_catalog.has_database_privilege (d.datname, 'CONNECT')

THEN pg_catalog.pg_database_size (d.datname)

ELSE NULL

END DESC-nulls first

LIMIT 20

The disk size occupied by each table in the statistical database

# display only table name and occupied disk size

SELECT

Table_schema | |'. | | table_name AS table_full_name

Pg_size_pretty (pg_total_relation_size ('"| | table_schema | |'. | | table_name | |'") AS size

FROM information_schema.tables

ORDER BY

Pg_total_relation_size ('"'| | table_schema | |'.'| | table_name | |'"') DESC

# display each parameter in detail and sort by database occupancy size

SELECT *, pg_size_pretty (total_bytes) AS total

, pg_size_pretty (index_bytes) AS INDEX

, pg_size_pretty (toast_bytes) AS toast

, pg_size_pretty (table_bytes) AS TABLE

FROM (

SELECT *, total_bytes-index_bytes-COALESCE (toast_bytes,0) AS table_bytes FROM (

SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME

, c.reltuples AS row_estimate

, pg_total_relation_size (c.oid) AS total_bytes

, pg_indexes_size (c.oid) AS index_bytes

, pg_total_relation_size (reltoastrelid) AS toast_bytes

FROM pg_class c

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE relkind ='r'

) a

) an ORDER BY total_bytes desc

View the SQL that PostgreSQL is executing

SELECT

Procpid

Start

Now ()-start AS lap

Current_query

FROM

(SELECT

Backendid

Pg_stat_get_backend_pid (S.backendid) AS procpid

Pg_stat_get_backend_activity_start (S.backendid) AS start

Pg_stat_get_backend_activity (S.backendid) AS current_query

FROM

(SELECT pg_stat_get_backend_idset () AS backendid) AS S

) AS S

WHERE

Current_query''

ORDER BY

Lap DESC

# Parameter interpretation

Procpid: process id

Start: process start time

Lap: elapsed time

Current_query: sql in execution

# through the command:

= # select pg_cancel_backend (thread id)

To kill the specified SQL statement. This function can only be queried by kill Select, but updae,delete DML does not take effect.

# use

= # select pg_terminate_backend (pid int)

You can kill various DML (SELECT,UPDATE,DELETE,DROP) operations.

Although you can use kill-9 to force the deletion of user processes, it is not recommended.

Because: for the execution of update statements, kill drop process, may cause Postgres to enter the recovery mode; and under recovery mode, will lock the table, do not allow links to the database.

These are the commonly used SQL statements in PostgreSQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow 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.