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

This article introduces the relevant knowledge of "what are the commonly used SQL query sentences in PostgreSQL DBA". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

View help commands

DB=# help-Total help

DB=#\ h-SQL commands level help

DB=#\?-psql commands level help

Display by column, similar to MySQL\ G

DB=#\ x

Expanded display is on.

View the DB installation directory (preferably root users)

Find /-name initdb

See how many DB instances are running (preferably root users)

Find /-name postgresql.conf

View DB version

Cat $PGDATA/PG_VERSION

Psql-version

DB=# show server_version

DB=# select version ()

View the running status of DB instance

Pg_ctl status

View all databases

Psql-l-check how many DB are under port 5432

Psql-p XX-l-check how many DB are under the XX port

DB=#\ l

DB=# select * from pg_database

Create a database

Createdb database_name

DB=#\ h create database-- help commands for creating databases

DB=# create database database_name

Access to a database

Psql-d dbname

DB=#\ c dbname

View the current database

DB=#\ c

DB=# select current_database ()

View the database file directory

DB=# show data_directory

Cat $PGDATA/postgresql.conf | grep data_directory

Cat / etc/init.d/postgresql | grep PGDATA=

Lsof | grep 5432 get the PID number of the second column and then ps-ef | grep PID

View tablespace

Select * from pg_tablespace

View Langua

Select * from pg_language

Query all schema, must be executed under the specified database

Select * from information_schema.schemata

SELECT nspname FROM pg_namespace

\ dnS

View table name

DB=#\ dt-you can only view the table name of public under the current database

DB=# SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE' sql_%' ORDER BY tablename

DB=# SELECT * FROM information_schema.tables WHERE table_name='ff_v3_ff_basic_af'

View table structure

DB=#\ d tablename

DB=# select * from information_schema.columns where table_schema='public' and table_name='XX'

View Index

DB=#\ di

DB=# select * from pg_index

View view

DB=#\ dv

DB=# select * from pg_views where schemaname = 'public'

DB=# select * from information_schema.views where table_schema = 'public'

View trigger

DB=# select * from information_schema.triggers

View sequenc

DB=# select * from information_schema.sequences where sequence_schema = 'public'

View constraints

DB=# select * from pg_constraint where contype ='p'

DB=# select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a <... > < title > constraint b where a.oid = b.conrelid and a.relname = 'cc'

View the size of the XX database

SELECT pg_size_pretty (pg_database_size ('XX')) As fulldbsize

View the size of all databases

Select pg_database.datname, pg_size_pretty (pg_database_size (pg_database.datname)) AS size from pg_database

View the creation time of each database:

Select datname, (pg_stat_file (format (when spcname='pg_default' then 'base' else' pg_tblspc/' | | t2.oid | |'/ PG_11_201804061/' end, t1.oid). * from pg_database T1 where t1.dattablespace=t2.oid pgpromotablespace T2 where t1.dattablespace=t2.oid

View the size of all tables according to the size of the space occupied

Select relname, pg_size_pretty (pg_relation_size (relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size (relid) desc

View the index size in order by the size of the space occupied

Select indexrelname, pg_size_pretty (pg_relation_size (relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size (relid) desc

View parameter file

DB=# show config_file

DB=# show hba_file

DB=# show ident_file

View the parameter values for the current session

DB=# show all

View parameter valu

Select * from pg_file_settings

View a parameter value, such as the parameter work_mem

DB=# show work_mem

Modify a parameter value, such as parameter work_mem

DB=# alter system set work_mem='8MB'

-- using the alter system command will modify the postgresql.auto.conf file instead of postgresql.conf, which can protect the postgresql.conf file very well. If you make a mess after using a lot of alter system commands, then you only need to delete postgresql.auto.conf, and then execute pg_ctl reload to load the postgresql.conf file to reload the parameters.

Check whether it is archived

DB=# show archive_mode

Check the relevant configuration of the running log, which includes Error information, slow query SQL, database startup and shutdown information, alarm information such as too frequent checkpoint, etc.

Show logging_collector;-- starts log collection

Show log_directory;-- log output path

Show log_filename;-- log file name

Show log_truncate_on_rotation;-- whether to overwrite the old file name with the same name if the file name already exists when generating a new file

Show log_statement;-- sets logging content

Statements that show log_min_duration_statement;-- runs XX for milliseconds are logged.-1 means to disable this feature, and 0 means to record all statements, similar to mysql's slow query configuration.

Check the configuration of wal log. Wal log is redo redo log.

Stored in the data_directory/pg_wal directory

View current user

DB=#\ c

DB=# select current_user

View all users

DB=# select * from pg_user

DB=# select * from pg_shadow

View all roles

DB=#\ du

DB=# select * from pg_roles

Query the permissions of user XX, which must be executed under the specified database

Select * from information_schema.table_privileges where grantee='XX'

Create the user XX and grant Super Admin privileges

Create user XXX SUPERUSER PASSWORD '123456'

Creating a role and giving login permission is tantamount to creating a user, which can be seen in pg_user

Create role "user1" superuser;--pg_roles has user1,pg_user and pg_shadow but no user1

Alter role "user1" login;--pg_user and pg_shadow also have user1.

Authorization

DB=#\ h grant

GRANT ALL PRIVILEGES ON schema schemaname TO dbuser

Grant ALL PRIVILEGES on all tables in schema fds to dbuser

GRANT ALL ON tablename TO user

GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser

Grant select on all tables in schema public to dbuser;-- reads all the tables under the schema public to the user.

GRANT create ON schema schemaname TO dbuser;-- grants users create permissions on schema, such as create table, create view, etc.

GRANT USAGE ON schema schemaname TO dbuser

Grant select on schema public to dbuser;-- error ERROR: invalid privilege type SELECT for schema

-- USAGE: for programming languages, functions are allowed to be created using the specified programming language; for Schema, objects under the Schema are allowed to be found; for sequences, currval and nextval functions are allowed; for external wrappers, external wrappers are allowed to create external servers; for external servers, external tables are allowed.

See which indexes and sizes exist on the table

Select relname,n.amname as index_type from pg_class mjournal pgendam n where m.relam = n.oid and m.oid in

(select b.indexrelid from pg_class a gaming index b where a.oid = b.indrelid and a.relname = 'cc')

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i

WHERE c.relname = 'cc' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname

View index definition

Select b.indexrelid from pg_class a journal pgfight index b where a.oid = b.indrelid and a.relname = 'cc'

Select pg_get_indexdef (b.indexrelid)

View procedure function definition

Select oid,* from pg_proc where proname = 'insert_platform_action_exist';-- oid = 24610

Select * from pg_get_functiondef (24610)

View table size (without index and other information)

Select pg_relation_size ('cc');-- 368640 byte

Select pg_size_pretty (pg_relation_size ('cc'))-- 360 kB

View the data file path and size corresponding to the table

SELECT pg_relation_filepath (oid), relpages FROM pg_class WHERE relname = 'empsalary'

Posegresql query current lsn

1. What methods are used:

Apple=# select proname from pg_proc where proname like 'pg_%_lsn'

Proname

-

Pg_current_wal_flush_lsn

Pg_current_wal_insert_lsn

Pg_current_wal_lsn

Pg_last_wal_receive_lsn

Pg_last_wal_replay_lsn

2. Query the current LSN value:

Apple=# select pg_current_wal_lsn ()

Pg_current_wal_lsn

-

0/45000098

3. Query the log files corresponding to the current lsn

Select pg_walfile_name ('0Universe 1732DE8')

4. Query the offset of current lsn in the log file

SELECT * FROM pg_walfile_name_offset (pg_current_wal_lsn ())

Toggle pg_wal Log

Select pg_switch_wal ()

Clean up the pg_wal log

Pg_archivecleanup / postgresql/pgsql/data/pg_wal 000000010000000000000005

Deletes all logs before 0000000100000000000005

-- pg_wal log does not set the parameter of retention period, that is, there is no parameter like mysql. Expire_logs_days,pg_wal log is permanently retained, unless the retention policy is set a few days before shell deletion or during pg-rman backup.

Which slot can be queried under any database, and the query results are all the same.

Select * from pg_replication_slots

This is the end of the content of "what are the commonly used SQL query sentences in PostgreSQL DBA?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.