Sysadmins Most Used PostgreSQL Commands with Examples-Part 2

In this article we will show you how to manage postgresql database by using the most used commands used by every sysadmins when dealing with postgresql databases.

Some of the open source application comes with postgreSQL database. To maintain those application, companies may not hire a full time postgreSQL DBA. Instead they may request the existing Oracle DBA, or Linux system administrator, or programmers to maintain the potgreSQL. In this article let discuss some advanced practical postgresql database commands which will be useful to both DBA and expert psql users.

We will run our commands against postgreSQL 9.4 database on different families of Linux systems.

1. How to find the largest table in the postgreSQL database?

$ psql -U postgres -d core_production
psql (9.4.8)
Type "help" for help.

core_production=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
 relname | relpages 
--------------------------------------------------------------+----------
 photos | 9809
 topic_translations | 6579
 accounts | 5635
 tvguides | 5609
 photoables | 4921

If you want only the first biggest table in the postgres database then append the above query with limit as:

core_production=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages 
---------+----------
 photos | 9809
(1 row)

• relname – name of the relation/table.
• relpages – relation pages ( number of pages, by default a page is 8kb )
• pg_class – system table, which maintains the details of relations
• limit 1 – limits the output to display only one row.

2. How to calculate postgreSQL database size in disk ?

pg_database_size is the function which gives the size of mentioned database. It shows the size in bytes.

core_production=# select pg_database_size('core_production'); 
 pg_database_size 
------------------
 1017769748
(1 row)

If you want it to be shown pretty, then use pg_size_pretty function which converts the size in bytes to human understandable format.

core_production=# SELECT pg_size_pretty(pg_database_size('core_production'));
 pg_size_pretty 
----------------
 971 MB
(1 row)

3. How to calculate the actual postgreSQL table size in disk ?

This is the total disk space size used by the mentioned table including index and toasted data. Use  pg_total_relation_size as shown below.

core_production=# SELECT pg_size_pretty(pg_total_relation_size('photos'));
 pg_size_pretty 
----------------
 101 MB
(1 row)

4. How to find size of the postgreSQL table ( not including index ) ?

Use pg_relation_size instead of pg_total_relation_size to show the table size no including index as shown below.

core_production=# SELECT pg_size_pretty(pg_relation_size('photos'));
 pg_size_pretty 
----------------
 77 MB
(1 row)

5. How to view the indexes of an existing postgreSQL table ?

Syntax: # \d+ table_name

\d –> describe table, view, sequence, or index,( + )–> gives additional detail like “Storage etc..”

As shown in the example below, at the end of the output you will have a section titled as indexes, if you have index in that table. In the example below, table photos has four btree indexes. By default postgres uses btree index as it good for most common situations.

core_production=# \d+ photos
 Table "public.photos"
 Column | Type | Modifiers | Storage | Stats target | 
Description 
-----------------------+-----------------------------+-----------------------------------------------------+----------+--------------+-
------------
 id | integer | not null default nextval('photos_id_seq'::regclass) | plain | | 
 user_id | integer | | plain | | 
 category | smallint | | plain | | 
 is_published | boolean | not null default false | plain | | 
 is_exclusive | boolean | not null default false | plain | | 
 is_by_mail | boolean | not null default false | plain | | 
 copyrights | smallint | not null default 11 | plain | | 
 metadata | hstore | not null default ''::hstore | extended | | 
 original | character varying | | extended | | 
 remote_original | character varying | default ''::character varying | extended | | 
 file | character varying | | extended | | 
 remote_file | character varying | default ''::character varying | extended | | 
 year | integer | | plain | | 
 created_at | timestamp without time zone | | plain | | 
 updated_at | timestamp without time zone | | plain | | 
 photo_by_email_log_id | integer | | plain | | 
Indexes:
 "photos_pkey" PRIMARY KEY, btree (id)
 "index_photos_on_category" btree (category)
 "index_photos_on_is_published" btree (is_published)
 "index_photos_on_user_id" btree (user_id)

6. How to specify postgreSQL index type while creating a new index on a table ?

By default the indexes are created as btree. You can also specify the type of index during the create index statement as shown below.

Syntax: CREATE INDEX name ON table USING index_type (column);

core_production=# CREATE INDEX test_index ON photos using hash (user_id);

7. How to work with postgreSQL transactions ?

How to start a transaction ?

# BEGIN -- start the transaction.

How to rollback or commit a postgreSQL transaction ?

All the operations performed after the BEGIN command will be committed to the postgreSQL database only you execute the commit command. Use rollback command to undo all the transactions before it is committed.

# ROLLBACK -- rollbacks the transaction.
 # COMMIT -- commits the transaction.

8. How to view execution plan used by the postgreSQL for a SQL query ?

# EXPLAIN query;

9. How to display the plan by executing the query on the server side ?

This executes the query in the server side, thus does not shows the output to the user. But shows the plan in which it got executed.

# EXPLAIN ANALYZE query;

10. How to generate a series of numbers and insert it into a table ?

This inserts 1,2,3 to 1000 as thousand rows in the table numbers.

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

11. How to count total number of rows in a postgreSQL table ?

This shows the total number of rows in the table.

# select count(*) from table;

Following example gives the total number of rows with a specific column value is not null.

# select count(col_name) from table;

Following example displays the distinct number of rows for the specified column value.

# select count(distinct col_name) from table;

12. How can I get the second maximum value of a column in the table ?

First maximum value of a column

# select max(col_name) from table;

Second maximum value of a column

# SELECT MAX(num) from number_table where num < ( select MAX(num) from number_table );

13. How can I get the second minimum value of a column in the table ?

First minimum value of a column

# select min(col_name) from table;

Second minimum value of a column

 # SELECT MIN(num) from number_table where num > ( select MIN(num) from number_table );

14. How to view the basic available datatypes in postgreSQL ?

Below is the partial output that displays available basic datatypes and it’s size.

core_production=# 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
 int4 | 4
 regproc |

• typname – name of the datatype
• typlen – length of the datatype

15. How to redirect the output of postgreSQL query to a file?

# \o output_file
 # SELECT * FROM pg_class;

The output of the query will be redirected to the “output_file”. After the redirection is enabled, the select command will not display the output in the stdout. To enable the output to the stdout again, execute the \o without any argument as mentioned below.

# \o

As explained in our earlier article, you can also backup and restore postgreSQL database using pg_dump and psql.

16. Storing the password after encryption.

PostgreSQL database can encrypt the data using the crypt command as shown below. This can be used to store your custom application username and password in a custom table.

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

PostgreSQL crypt function Issue:

The postgreSQL crypt command may not work on your environment and display the following error message.

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.

PostgreSQL crypt function Solution:

To solve this problem, install the postgresql-contrib-your-version package and execute the following command in the postgreSQL prompt.

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

Summary

In this article we showed you the most common advanced used postgresql command examples that you must know to handle the postgreql database. Simple commands to facilitate your work when managing postgresql database.

I hope this article is good enough for you.
See you in other articles