PostgreSQL- Daily used SQL Query Statement for PostgreSQL DBA Beginners - PostgreSQL Query Help!
In this video, let’s understand what query statements are commonly used to learn PostgreSQL?
PostgreSQL has been most rated as the database of the year for four consecutive years, and it has been favored by many DBAs by now.
View help commands:
postgres=# help — total help
postgres=# \h — SQL commands level help
postgres=# \? — psql commands level help
Show by column, similar to MySQL G
postgres=# \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
postgres=# show server_version;
postgres=# 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
postgres=# \l
postgres=# select * from pg_database;
Create a database
createdb database_name
postgres=# \h create database — Help command to create database
postgres=# create database database_name
Enter a database
psql –d dbname
postgres=# \c dbname
View the current database
postgres=# \c
postgres=# select current_database();
View database file directory
postgres=# 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 tablespace
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
postgres=# \dt — You can only view the public table name under the current database
postgres=# SELECT tablename FROM pg_tables WHERE tablename NOT LIKE’pg%’ AND tablename NOT LIKE’sql_%’ ORDER BY tablename;
postgres=# SELECT * FROM information_schema.tables WHERE table_name=’ff_v3_ff_basic_af’;
View table structure
postgres=# \d tablename
postgres=# select * from information_schema.columns where table_schema=’public’ and table_name=’XX’;
View index
postgres=# \di
postgres=# select * from pg_index;
View view
postgres=# \dv
postgres=# select * from pg_views where schemaname =’public’;
postgres=# select * from information_schema.views where table_schema =’public’;
View trigger
postgres=# select * from information_schema.triggers;
View sequence
postgres=# select * from information_schema.sequences where sequence_schema =’public’;
View constraints
postgres=# select * from pg_constraint where contype =’p’
postgres=# 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
postgres=# show config_file;
postgres=# show hba_file;
postgres=# show ident_file;
View the parameter values of the current session
postgres=# show all;
View parameter values
select * from pg_file_settings
View a parameter value, such as the parameter work_mem
postgres=# show work_mem
Modify a parameter value, such as the parameter work_mem
postgres=# 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
postgres=# 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
postgres=# \c
postgres=# select current_user;
View all users
postgres=# select * from pg_user;
postgres=# select * from pg_shadow;
View all roles
postgres=# \du
postgres=# 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 xxxx 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.
Hope this helps!
Cheers!
Ramesh.
Post a Comment: