1. How to find the largest table
in the postgreSQL database?
test=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
relname | relpages
-----------------------------------+----------
pg_proc | 50
pg_attribute | 30
If you want only
the first biggest table in the postgres database then append the above query
with limit as:
# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
---------+----------
pg_proc | 50
(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.
Basic query will tell us the
"Top 10 Biggest Tables":
SELECT
table_name, pg_relation_size(table_name) as size FROM information_schema.
tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY
size DESC LIMIT 10;
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.
# SELECT pg_database_size('geekdb');
pg_database_size
------------------
63287944
(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.
# SELECT pg_size_pretty(pg_database_size('geekdb'));
pg_size_pretty
----------------
60 MB
(1 row)
SELECT pg_database_size(current_database());
However, this is limited to only the
current database. If you want to find out the size of all databases together,
then you'll need a query such as the following:
SELECT sum(pg_database_size(datname))
from pg_database;
3. How to calculate postgreSQL
table size in disk?
This is the
total disk space size used by the mentioned table including index and toasted
data. You may be interested in knowing only the size of the table excluding the
index then use the following command.
# SELECT pg_size_pretty(pg_total_relation_size('big_table'));
pg_size_pretty
----------------
55 MB
(1 row)
How
to find size of the postgreSQL table (not including index) ?
Use
pg_relation_size instead of pg_total_relation_size as shown below.
# SELECT pg_size_pretty(pg_relation_size('big_table'));
pg_size_pretty
----------------
38 MB
(1 row)
postgres=# \dt+ dailyattendance
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------+-------+----------+------------+-------------
public | dailyattendance | table | postgres | 8192 bytes |
(1 row)
4. How to view the indexes of an
existing postgreSQL table ?
Syntax: # \d table_name
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
pg_attribute has two btree indexes. By default, postgres uses btree index as it
good for most common situations.
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
Indexes:
"pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
"pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)
5. 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);
# CREATE INDEX test_index ON numbers using hash (num);
6. 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.
7. How to view execution plan used by the postgreSQL for a SQL
query?
# EXPLAIN query;
8. 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;
9. How to generate a series of
numbers and insert it into a table?
This insert
1,2,3 to 1000 as thousand rows in the table numbers.
# INSERT INTO numbers (num) VALUES (generate_series(1,1000));
10. 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;
Quick
estimate of the number of rows in a table by using
roughly the same calculation that the Postgres optimizer uses:
SELECT (CASE WHEN reltuples > 0
THEN pg_relation_size('mytable')/(8192*relpages/reltuples) ELSE 0 END)::bigint AS estimated_row_count
FROM pg_class WHERE oid = 'mytable'::regclass;
11. 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;
12. 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);
13. How to view the basic
available datatypes in postgreSQL?
Below is the
partial output that displays available basic datatypes and its size.
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
- typname – name of the datatype
- typlen – length of the datatype
14. 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
15. 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 (‘sathiya', 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, installl the postgresql-contrib-your-version package and execute the
following command in the postgreSQL prompt.
# \i /usr/share/postgresql/8.1/contrib/pgcrypto.sql
16. Use File as Input
Use \i
<filename> to use a file as input for commands:
mary=> \i /home/mary/myfile1.sql
|
psql
–f examples.sql
|
17. How to change PostgreSQL root user password?
$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD 'tmppassword';
$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)
Changing the
password for a normal postgres user is similar as changing the password of the
root user. Root user can change the password of any user, and the normal users
can only change their passwords as Unix way of doing.
# ALTER USER username WITH PASSWORD 'tmp password';
18. How to setup PostgreSQL SysV
startup script?
$ su - root
# tar xvfz postgresql-8.3.7.tar.gz
# cd postgresql-8.3.7
# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
# chmod a+x /etc/rc.d/init.d/postgresql
19. How to check whether PostgreSQL server is up and running?
$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]
$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]
20. How to start, stop and restart PostgreSQL database?
# service postgresql stop
Stopping PostgreSQL: server stopped
ok
# service postgresql start
Starting PostgreSQL: ok
# service postgresql restart
Restarting PostgreSQL: server stopped
Ok
On all platforms, there is a specific command to start the server, which is as follows:
UBUNTU/DEBIAN pg_ctlcluster 9.0
main reload
RED HAT/FEDORA pg_ctl -D
/var/lib/pgsql/data start
SOLARIS pg_ctl -D
/var/lib/pgsql/data start
MAC OS pg_ctl -D
/var/lib/pgsql/data start
FREEBSD pg_ctl -D
/var/lib/pgsql/data start
although on some
platforms, the service can be started in various ways such as:
RED HAT/FEDORA service
postgresql start
WINDOWS net start postgres
You can issue a
database server stop using fast mode as follows:
pg_ctl -D datadir -m fast stop
You must use -m fast if you wish to shut down as soon as possible.
Normal shutdown means "wait for all users to finish before we exit".
That can take a very long time, though all the while new connections are refused.
21. How do I find out what version of PostgreSQL I am running?
$ /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 version();
version
------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)
Some other ways of
checking the version number are as follows:
bash # psql ––version
psql (PostgreSQL) 9.0
However, be wary that
this shows the client software version number that might differ from the server
software version number. You check the server version directly using the
following:
bash # cat
$PGDATADIRECTORY/PG_VERSION
Although neither of these show the maintenance release number.
PostgreSQL server
version's format is Major.Minor.Maintenance
In some other software
products, the Major release
number is all you need to know, but with PostgreSQL the feature set and
compatibility relates to the Major.Minor release level. What that means is that 8.4
contains more additional features and compatibility changes than 8.3. There is
also a separate version of the manual, so if something doesn't work exactly the
way you think it should, you must consult the correct version of the
manual.
Maintenance software releases are identified by the full
three-part numbering scheme. 8.4.0 was the initial
release of 8.4, and 8.4.1 is a later maintenance release.
22. How to create a PostgreSQL
user ?
To create new users, you must either
be a superuser or have the createrole or createuser privilege.
There are two
methods in which you can create user.
Method
1: Creating the user in the PSQL prompt, with CREATE USER
command.
# CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE
Method
2: Creating the user in the shell prompt, with createuser
command.
$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
Checking roles of a user
pguser=# \du tim
List of roles
Role name | Attributes | Member
of
-----------+-------------+-----------
tim | Superuser | {}
: Create role
: Create DB
CREATE USER and CREATE GROUP
Starting
from Version 8.x, the commands CREATE USER and CREATE GROUP are actually
variations of CREATE ROLE.
No comments:
Post a Comment