Daily used SQL query statements for PostgreSQL DBA

DB=# help — total helpDB=# \h — SQL commands level helpDB=# \? — psql commands level helpShow by column, similar to MySQL GDB=# \x
find / -name initdb
find / -name postgresql.conf
cat $PGDATA/PG_VERSION
DB=# show server_version;DB=# select version();
pg_ctl status
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;
createdb database_nameDB=# \h create database — Help command to create databaseDB=# create database database_name
psql –d dbnameDB=# \c dbname
DB=# \cDB=# select current_database();
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
select * from pg_tablespace;
select * from pg_language;Query all schemas, must be executed under the specified databaseselect * from information_schema.schemata;SELECT nspname FROM pg_namespace;\dnS
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’;
DB=# \d tablenameDB=# select * from information_schema.columns where table_schema=’public’ and table_name=’XX’;
DB=# \diDB=# select * from pg_index;
DB=# \dvDB=# select * from pg_views where schemaname =’public’;DB=# select * from information_schema.views where table_schema =’public’;
DB=# select * from information_schema.triggers;
DB=# select * from information_schema.sequences where sequence_schema =’public’;
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’;
SELECT pg_size_pretty(pg_database_size(‘XX’)) As fulldbsize;
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_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;
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=’public’ order by pg_relation_size(relid) desc;
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname=’public’ order by pg_relation_size(relid) desc;
DB=# show config_file;DB=# show hba_file;DB=# show ident_file;
DB=# show all;
select * from pg_file_settings
DB=# show work_mem
DB=# alter system set work_mem=’8MB’
DB=# show archive_mode;
show logging_collector; — start log collectionshow log_directory; — Log output pathshow log_filename; — log file name
show log_statement; — Set the log record content
DB=# \cDB=# select current_user;View all usersDB=# select * from pg_user;DB=# select * from pg_shadow;
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
-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

--

--

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