Daily used SQL query statements for PostgreSQL DBA

PostgreSQL has been rated as the database of the year for two consecutive years, and it has been favored by many DBAs.

In this article, let’s understand what query statements are commonly used to learn PostgreSQL?

View help commands

DB=# help — total help
DB=# \h — SQL commands level help
DB=# \? — psql commands level help
Show by column, similar to MySQL G
DB=# \x

Expanded display is on.

View the DB installation directory (preferably root user execution)

find / -name initdb

See how many DB instances are running (preferably root user execution)

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 the DB instance

pg_ctl status

View all databases

1. psql — l — check how many DBs are under port 5432
psql — p XX — l — check how many DBs are under XX port
DB=# \l
DB=# select * from pg_database;

Create a database

createdb database_name
DB=# \h create database — Help command to create database
DB=# create database database_name

Enter a database

psql –d dbname
DB=# \c dbname

View the current database

DB=# \c
DB=# select current_database();

View database file directory

DB=# show data_directory;
cat $PGDATA/postgresql.conf |grep data_directory
cat /etc/init.d/postgresql|grep PGDATA=
lsof |grep 5432 gets the PID number in the second column and then ps –ef|grep PID

View table space

select * from pg_tablespace;

View language

select * from pg_language;
Query all schemas, 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 public table name 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 sequence

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,pg_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 data creation time of each database:

select datname,(pg_stat_file(format(‘%s/%s/PG_VERSION’,case when spcname=’pg_default’ then’base’ else’pg_tblspc/’||t2.oid||’/PG_11_201804061/’ end, t1. oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

View the size of all tables in order according to 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;

According to the size of the space, view the index size in order

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 ​​​​of the current session

DB=# show all;

View parameter values

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 the parameter work_mem

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

Note: 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, adding the mess you made after using many 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.

See if archive

DB=# show archive_mode;

Check the configuration of the operation log. The operation log includes Error information, slow location query SQL, database startup and shutdown information, and checkpoint too frequent alarm information.

show logging_collector; — start log collection
show log_directory; — Log output path
show log_filename; — log file name

show log_truncate_on_rotation; — when generating a new file, if the file name already exists, whether to overwrite the old file name with the same name

show log_statement; — Set the log record content

show log_min_duration_statement;-statements running for XX milliseconds will be recorded in the log, -1 means disable this function, 0 means record all statements, similar to the slow query configuration of mysql

View the configuration of the wal log, which is the redo redo log

Store 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 user XX authority must be executed under the specified database
select * from information_schema.table_privileges where grantee=’XX’;
Create user XX a
POSTGRESQL database export SQL statement
pg_dump — host hostname — port 5432 — username username -t testtable> /var/www/mytest/1.sql testdb
Command explanation:
pg_dump — host hostname — port 5432 — username username -t testtable > /var/www/mytest/1.sql testdb
Among them: the bold part means:
hostname : the name of the host;
5432 : The database uses the port, the default is 5432
username : the username to log in to the database;
testtable : the table whose data will be exported;
testdb: the database used

Usage:

pg_dump [options]… [database name]

general options:

-f, — file=FILENAME output file or directory name
-F, — format =c|d|t|p output file format (custom, directory, tar)
clear text (default))
-v, — verbose verbose mode
-V, — version output version information, then exit
-Z, — compress =0–9 Compression level of compressed format
 — lock-wait-timeout=TIMEOUT Operation failed after waiting for table lock timeout
-?, — help Display this help, and then exit the
control output options:
-a, — data -only Dump only data, excluding mode
-b, — blobs include large objects in dump
-c, — clean Before re-creating, first clear (delete) database objects
-C, — create in dump Include commands in order to create the database
-E, — encoding=ENCODING turn Store data encoded in ENCODING format
-n, — schema=SCHEMA only dump patterns with specified names
-N, — exclude-schema=SCHEMA do not dump named patterns
-o, — oids include OID in dump
-O, — no -owner Ignore the owner of the recovery object in the plain text format
-s, — schema-only only dump the mode, excluding data
-S, — superuser=NAME use the specified superuser name in the plain text format
-t,- -table=TABLE only dump the table with the specified name
-T, — exclude-table=TABLE does not dump the table with the specified name
-x, — no-privileges do not dump permissions (grant/revoke)
 — binary-upgrade Can only be used by the upgrade tool
 — column-inserts to dump data in the form of an INSERT command with column names
 — disable-dollar-quoting cancel dollar (symbol) quotes, use SQL standard quotes — exclude-table-data=TABLE Do not dump the data in the table with the specified name — inserts dump data in the form of INSERT command instead of COPY command
 — disable-triggers to disable triggers in the process of restoring data only
 — no-security-labels are not assigned a security tag dump
- -no-tablespaces Do not dump table space allocation information
 — no-unlogged-table-data Do not dump table data without logs
 — quote-all-identifiers All identifiers are quoted, even if they are not keywords
 — section=SECTION Back up named sections (before data, data, and after data)
 — serializable-deferrable wait until the backup can run without exception
 — use-set-session-authorization
use SESSION AUTHORIZATION command instead of
ALTER OWNER command to set ownership
connection option:
-h , — host=hostname database server hostname or socket directory
-p, — port=port number database server port number
-U, — username=name connect with the specified database user
-w, — no-password Never prompt for password
-W, — password Force password prompt (automatic)
 — role=ROLENAME Run SET ROLE before dumping

If no database name is provided, then use

the value of the PGDATABASE environment variable.

Thanks for reading, you like please follow me up here.