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 helpDB=# \h — SQL commands level helpDB=# \? — psql commands level helpShow by column, similar to MySQL GDB=# \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 5432psql — p XX — l — check how many DBs are under XX portDB=# \lDB=# select * from pg_database;

Create a database

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

Enter a database

psql –d dbnameDB=# \c dbname

View the current database

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

View database file directory

DB=# show data_directory;cat $PGDATA/postgresql.conf |grep data_directorycat /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 databaseselect * 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 databaseDB=# 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 tablenameDB=# select * from information_schema.columns where table_schema=’public’ and table_name=’XX’;

View index

DB=# \diDB=# select * from pg_index;

View view

DB=# \dvDB=# 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 collectionshow log_directory; — Log output pathshow 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=# \cDB=# select current_user;View all usersDB=# select * from pg_user;DB=# select * from pg_shadow;

View all roles

DB=# \duDB=# select * from pg_roles;Query user XX authority must be executed under the specified databaseselect * from information_schema.table_privileges where grantee=’XX’;Create user XX aPOSTGRESQL database export SQL statementpg_dump — host hostname — port 5432 — username username -t testtable> /var/www/mytest/1.sql testdbCommand explanation:pg_dump — host hostname — port 5432 — username username -t testtable > /var/www/mytest/1.sql testdbAmong them: the bold part means:hostname : the name of the host;5432 : The database uses the port, the default is 5432username : 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 thecontrol 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-authorizationuse SESSION AUTHORIZATION command instead ofALTER OWNER command to set ownershipconnection 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.

--

--

--

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Upgrading our house with QR codes

Automatically send gmail with AWS

tech stack

Power Management & Performance

Building a game engine!

[docker] Basic command

Topological Sort — A naive approach

The 3 Mindsets to Avoid as a Senior Software Developer

A young man looking out of an apartment window.

The Best Resources for R Programming

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

More from Medium

Azure Cosmos DB API Services

Azure SQL Database Tools Part 3: dbForge Studio

Azure SQL Database Tools Part 3: dbForge Studio

SQL Queries For Mere Mortals: Creating a Simple Query

Column Values in Reverse Order in sql