Here are some more helpful postgresql commands that can help your out in your first few weeks of using Postgresql. Finding the current, time, which database you are in, the size of a database or a table, the database ipaddress and port, etc…
1. Select Current TimeStamp from Postgresql:
| uptimemadeeasy=> select current_time; timetz ——————– 02:34:20.582741+00 (1 row) |
2. Select the current database that you are using:
| uptimemadeeasy=> select current_database(); current_database —————— uptimemadeeasy (1 row) |
3. Show the User You Are Logged in as:
| uptimemadeeasy.com=# select current_user; current_user ————– postgres (1 row) |
4. Show the Version of Postgresql That You are Using:
| uptimemadeeasy=> select version(); version——————————————————————————– —————————— PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120 313 (Red Hat 4.4.7-3), 64-bit (1 row) |
5. Show the IP Address you used to connect to the Postgresql Database
| uptimemadeeasy=> select inet_server_addr(); inet_server_addr —————— 10.10.10.16 (1 row) |
6. Show the IP Port That the Postgresql Database is Listening On
| uptimemadeeasy=> select inet_server_port(); inet_server_port —————— 5432 (1 row) |
7. Select When Postgresql was Last Started
| uptimemadeeasy=> select pg_postmaster_start_time(); pg_postmaster_start_time ——————————- 2013-11-27 17:42:43.390377+00 (1 row) |
8. Select How Long Postgresql Has Been Up
| uptimemadeeasy=> select date_trunc(‘minute’, current_timestamp – pg_postmaster_start_time()) as “postgresql uptime”; postgresql uptime ——————- 7 days 08:59:00 (1 row) |
9. Get the Size of Your Postgresql Database
| uptimemadeeasy=> select pg_database_size(‘uptimemadeeasy’); pg_database_size —————— 9955512 (1 row) |
10. Find the Size of a Table, its Indexes, and Other Related Space
| uptimemadeeasy=> select pg_total_relation_size(‘awards’); pg_total_relation_size ———————— 49152 (1 row) |
The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on Google+ or LinkedIn at: LinkedIn or Twitter at: Twitter
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014
