Fifteen postgresql database utility commands are Shared

  • 2020-05-06 11:55:15
  • OfStack

Originally, I was looking for the blog database space command found in this blog, and found several
provided in it A few commands are useful (but there are also a few that feel like they're loaded =. =), and translated it. In addition, this article is from 2009, so its contents may be a little outdated. I collected useful comments from the original article and put them at the end.

A number of open source software now use postgreSQL as their database system. But the company may not hire some full-time postgreSQL DBA to maintain it. Instead, some, such as Oracle DBA or Linux system administrators or programmers, are left to maintain. In this article we will introduce 15 postgresql database commands that are useful for both psql old bird and DBA.

1. How do I find the largest table in the postgreSQL database?


$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit test=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
              relname              | relpages
-----------------------------------+----------
 pg_proc                           |       50
 pg_proc_proname_args_nsp_index    |       40
 pg_depend                         |       37
 pg_attribute                      |       30

If you only want the largest table, you can limit the number of results with the limit parameter, like this:


# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)

1.relname - relationship name/table name
2.relpages - relationship pages (one page size is 8kb by default)
3.pg_class - system table, which maintains
details for all relations 4.limit 1 - limits the return result to only one row of

2. How do I calculate the disk size of the postgreSQL database?

pg_database_size this method is specifically designed to query the database size, and it returns the result in bytes (bytes). :


# SELECT pg_database_size('geekdb');
pg_database_size
------------------
         63287944
(1 row)

If you want to make the results more intuitive, use the **pg_size_pretty** method, which converts the number of bytes into a more friendly and readable format.

# SELECT pg_size_pretty(pg_database_size('geekdb'));
 pg_size_pretty
----------------
 60 MB
(1 row)

3. How do I calculate the size of the hard disk occupied by the postgreSQL table?

The following command, , looks up the table size with the index and toasted data. If you are interested in just the table size without the index, you can use the following command.


# SELECT pg_size_pretty(pg_total_relation_size('big_table'));
 pg_size_pretty
----------------
 55 MB
(1 row)

How do I query the size of postgreSQL table without indexes?

Use the **pg_relation_size** instead of **pg_total_relation_size** method.


# SELECT pg_size_pretty(pg_relation_size('big_table'));
 pg_size_pretty
----------------
 38 MB
(1 row)

4. How do I view the indexes of postgreSQL tables?

Syntax: # \d table_name

Let's take a look at the following example. Notice that if your table has an index, you will find a heading Indexes at the end of the output of the command. In this case, the pg_attribut table has two indexes of type btree.

test=# \d pg_attribute
   Table "pg_catalog.pg_attribute"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 attrelid      | oid      | not null
 attname       | name     | not null
 atttypid      | oid      | not null
 attstattarget | integer  | not null
 attlen        | smallint | not null
 attnum        | smallint | not null
 attndims      | integer  | not null
 attcacheoff   | integer  | not null
 atttypmod     | integer  | not null
 attbyval      | boolean  | not null
 attstorage    | "char"   | not null
 attalign      | "char"   | not null
 attnotnull    | boolean  | not null
 atthasdef     | boolean  | not null
 attisdropped  | boolean  | not null
 attislocal    | boolean  | not null
 attinhcount   | integer  | not null
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

5. How do I create an index of the specified type?

indexes are of type btree by default, but you can specify the type of the new index in the following way.


Syntax: CREATE INDEX name ON table USING index_type (column); # CREATE INDEX test_index ON numbers using hash (num);

6. How do I use transactions in postgreSQL?

How does
start a transaction?


# BEGIN -- Start the transaction

How do I commit or roll back a transaction?

Only when you call the COMMIT command will all the actions you did after the BEGIN command actually be committed to the postgreSQL database. You can also use the ROLLBACK command to roll back and forth all the operations you do in a transaction.


# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
0

7. How do I view the postgreSQL database's execution plan for an SQL query?


# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
1

8. How do I display an execution plan by executing a query on the server side?

The following command from
executes the query on the server side, but instead of giving the query result to the user, it returns its actual execution plan.

# EXPLAIN ANALYZE query;


9. How do I generate a sequence of Numbers and insert them into a table?

The following command for
will generate a thousand Numbers from 1 to 1000 and insert them into the numbers table.

# INSERT INTO numbers (num) VALUES ( generate_series(1,1000));

How do I count the rows in the postgreSQL table?

The command looks up the number of records in the table.


# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
4
This command looks up the number of rows in the table where the value of the specified column is not null.

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
5
This command queries the total number of rows in the table after the specified column value has been deduplicated.

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
6

11. How do I query the value of the second largest ** in a column ** in a table?

queries for the maximum value
for a column


# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
7
Query for the second largest value in a column,

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
8

12. How do I query the value of the second smallest ** in a column in the table?

queries for the smallest value of a column,


# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)
9
Query for the second-smallest value of a column,

# SELECT pg_database_size('geekdb');
pg_database_size
------------------
         63287944
(1 row)
0

13. How do I list the basic data types in the postgreSQL database?

This command shows the available data types and the number of bytes they consume.


test=# SELECT typname,typlen from pg_type where typtype='b';
    typname     | typlen
----------------+--------
 bool           |      1
 bytea          |     -1
 char           |      1
 name           |     64
 int8           |      8
 int2           |      2
 int2vector     |     -1

1.typname - name of type
2.typlen - size of type

14. How do I save the results of a particular query as a file?

# \o output_file
# SELECT * FROM pg_class;

The results of the above query will be saved to "output_file". When the redirect is activated, all subsequent queries will no longer print the results on the screen. To open the screen output again, you need to execute the o command again without any arguments.

# \o

15. Stores the encrypted password

The
PostgreSQL database can use the following crypt command to encrypt the data. This is handy for saving your username and password.


# SELECT crypt ( 'sathiya', gen_salt('md5') );

Possible problems with the PostgreSQL crypt method:

crypt may not work in your environment, and provide the following error information.


ERROR:  function gen_salt("unknown") does not exist
HINT:  No function matches the given name and argument types.
         You may need to add explicit type casts.

Solution:

To solve this problem, you need to install the postgresql-contrib-version package and then execute the following command in psql.


# \i /usr/share/postgresql/8.1/contrib/pgcrypto.sql

Comments after original

In the 13th command, what does that typtype='b' mean?

typtype='b' means basetype. b = = basetype

PostgreSQL has several data types: composite types, domains, and pseudo-types.

http://developer.postgresql.org/pgdocs/postgres/extend-type-system.html

gets the second largest/smallest value efficiency problem

It is much faster to query for the second-smallest value of a column in a table:


# SELECT pg_database_size('geekdb');
pg_database_size
------------------
         63287944
(1 row)
7
If the m column has an index.

COUNT(*) efficiency problems

Performing count(*) on a large table can have significant efficiency problems


Related articles: