PostgreSQL Database Administration

How to connect to postgreSQL database?

psql -U postgres

output:

C:\Users\FEROZ>psql -U postgres

Password for user postgres:

psql (13.6)

WARNING: Console code page (437) differs from Windows code page (1252)

         8-bit characters might not work correctly. See psql reference

         page "Notes for Windows users" for details.

Type "help" for help.


How to check postgreSQL version?

select version();

output:

postgres=# select version();

                          version

------------------------------------------------------------

 PostgreSQL 13.6, compiled by Visual C++ build 1914, 64-bit

(1 row)


psql --version

psql (PostgreSQL) 13.6



How to check server uptime?

Generally postgres stores the start time, and we can see the details by running the below command:

SELECT pg_postmaster_start_time();


output:

postgres=# SELECT pg_postmaster_start_time();

     pg_postmaster_start_time

----------------------------------

 2022-03-18 04:28:40.398544+05:30

(1 row)


Then, we can get the server up time by running the SQL:

SELECT current_timestamp - pg_postmaster_start_time();

output:

postgres=# SELECT current_timestamp - pg_postmaster_start_time();

    ?column?

-----------------

 00:41:40.736566

(1 row)


Finally, formatting the output:

select date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

output

postgres=# select date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

  uptime

----------

 00:35:25

(1 row)


How to create a database?

CREATE DATABASE <database_name>;

createdb <database_name>


Output:

postgres=# create database devdb;

CREATE DATABASE

postgres=# select datname from pg_database;

   datname

-------------

 postgres

 ibm

 template1

 template0

 devdb

(5 rows)


[postgres@pgdb-localdomain log]$ createdb prodb
[postgres@pgdb-localdomain log]$ psql
psql (13.6)
Type "help" for help.

postgres=# select datname from pg_database;
   datname
-------------
 postgres
 ibm
 template1
 template0
 devdb
 prodb
(6 rows)


How to list the all databases?

\l

select datname from pg_database;

Output:

postgres=# \l

                                         List of databases

   Name    |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges

-----------+----------+----------+--------------------+--------------------+-----------------------

 devdb       | postgres | UTF8     | English_India.1252 | English_India.1252 |

 postgres  | postgres | UTF8     | English_India.1252 | English_India.1252 |

 template0 | postgres | UTF8     | English_India.1252 | English_India.1252 | =c/postgres          +

           |          |          |                    |                    | postgres=CTc/postgres

 template1 | postgres | UTF8     | English_India.1252 | English_India.1252 | =c/postgres          +

           |          |          |                    |                    | postgres=CTc/postgres

(5 rows)


postgres=# select datname from pg_database;

  datname

-----------

 postgres

 devdb

 template1

 template0

(4 rows)


How to connect to a specific database?

\c <database name>

Output:

postgres=# \c devdb

You are now connected to database "ibm" as user "postgres".


How to check which database you connected to or current database?

select current_database();

Output

devdb=# select current_database();

 current_database

------------------

 devdb

(1 row)


How to check which user you connected to or current user?

select current_user;

Output:

devdb=# select current_user;

 current_user

--------------

 postgres

(1 row)


How to check IP address and port number of the database?

select inet_server_addr(),inet_server_port();

Output:

devdb=# select inet_server_addr(),inet_server_port();

 inet_server_addr | inet_server_port

------------------+------------------

 ::1              |             5432

(1 row)


To get all all the details in one command like , Ip address, port number, user, and database connected to, use the below command:

\conninfo

Output:

devdb=# \conninfo

You are connected to database "ibm" as user "postgres" on host "localhost" (address "::1") at port "5432".


How to change the Postgres password?

\password

Output:

devdb=# \password

Enter new password for user "postgres":

Enter it again:

OR

devdb=# ALTER USER postgres PASSWORD 'xxxx';

ALTER ROLE


How to list all tables in the database?

select table_catalog,table_schema,table_name,table_type from information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog');

OR

\d


Output:

devdb=# select table_catalog,table_schema,table_name,table_type from information_schema.tables WHERE table_schema NOT IN ('information_schema','pg_catalog');

 table_catalog | table_schema | table_name | table_type

---------------+--------------+------------+------------

 devdb           | public       | emp        | BASE TABLE

(1 row)


devdb=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)


How to check the database size?

SELECT pg_database_size(current_database());
select datname,sum(pg_database_size(datname)) from pg_database group by datname;
select sum(pg_database_size(datname)) from pg_database;

Output:

devdb=# SELECT pg_database_size(current_database());
 pg_database_size
------------------
          8335919
(1 row)

devdb=# select datname,sum(pg_database_size(datname)) from pg_database group by datname;
   datname   |   sum
-------------+---------
 template0   | 8159747
 template1   | 8159747
 production  | 8159747
 ibm         | 8335919
 postgres    | 8335919
 development | 8159747
(6 rows)

devdb=# select sum(pg_database_size(datname)) from pg_database;
   sum
----------
 49310826
(1 row)
 

How to check the table size?

select pg_relation_size('table_name');  ---- only table size
select pg_total_relation_size('emp');    ----- includes table, index size

Output:

devdb=# select pg_relation_size('emp');
 pg_relation_size
------------------
             8192
(1 row)

devdb=# select pg_total_relation_size('emp');
 pg_total_relation_size
------------------------
                  24576

How to create a cluster 


How to stop and start mongodb instance?


You can't start/stop individual databases. you have do at cluster level only.


Comments

Post a Comment

Popular posts from this blog

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

rs.stepDown() in MongoDB replication

It instructs the primary of the replica set to become a secondary. After the primary steps down,the eligible secondaries will hold an election for primary. Generally, the rs.stepDown() does not immediately step down the primary. If no electable secondaries are up to date with the primary, the primary waits up to secondaryCatchUpPeriodSecs (by default 10 seconds)  for a secondary to catch up. Once an electable secondary is available, the method steps down the priamry.  Once stepped down, the original primary becomes a secondary and is ineligible from becoming primary again for the remainder of time specified by stepDownSecs. The rs.stepDown() method attempts to terminate long-running user's operations that block the primary from stepping down, such as an index build, a write operation, or a map-reduce job. Syntax: rs.stepDown(stepDownSecs, secondaryCatchUpPeriodSecs)
Read more

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more