PostgreSQL DBA Handbook

How to connect with PostgreSQL-

sudo -i -u postgres

Grant the necessary privileges for the new user to connect to your
database:

Grant connect on database exampledb to user name;

Grant usage on schema public to user name;

How to Connect with PSQL Shell-

Psql

How to select version of PostgreSQL and Current Date-

Select Version ();

How to create Database in PostgreSQL –

Create database Employee;

How to Connect with Specific database in PostgreSQL-

\c database_name;

 

How many database are present in PostgreSQL Server-

\l+

How many tables are there in the PostgreSQL database-

\dt+

Create Table statement-

Create table empdetails (Id int primary key, Name (Text), Age (int), Address char (50), Salary (real));

Insert data in the Table-

Insert into empdetails (Id, Name, Age, Address, Salary) values (1, ‘Abhishek’, ‘25’, ‘LKO’ M ‘25000’);

Select Data from the Table-

Select * from empdetails;

Counting Rows statement-

Select count (*) from empdetails;

Structure table statement-

\d empdetails;

List all stored procedures and functions-

 \df+  or \df

 

Create user statement in PostgreSQL -

CREATE ROLE username Abhi LOGIN PASSWORD ‘secure@321’;

Change role for the current session to the new_role-

SET ROLE Abhi;

If you want to give all privileges to a newly created user, execute the following command-

GRANT ALL previleges

ON  table_name

TO  role_name; 

Check Table Privileges Using “\z” Command-

\z emp_info;

Drop user statement-

DROP USER 'account_name';  

Revoke Statement-

Revoke all privileages on table name from user name;

Show Users in PostgreSQL 

\du;

To log in without a password:

Psql –d dbname  -u username

 

Change PostgreSQL User Password Statement-

ALTER USER user_name WITH PASSWORD 'new_password';

PostgreSQL ALTER Table-

ADD a column in the table-

ALTER TABLE table_name

ADD COLUMN column_name datatype column_constraint;

Alter column in the table-

ALTER TABLE table_name  

Alter column_name column_definition  

[ FIRST | AFTER column_name ]; 

DROP column in table-

ALTER TABLE table_name

DROP COLUMN column_name;

 RENAME column in table-

ALTER TABLE table_name

RENAME COLUMN column_name

TO new_column_name;

RENAME table-

ALTER TABLE table_name  

RENAME TO new_table_name;  

Create Stored Procedure Syntax-

CREATE OR REPLACE FUNCTION my_stored_procedure()

LANGUAGE plpgsql;

AS

$$

BEGIN

    -- Your procedure logic goes here

    -- You can write SQL statements, control structures, etc.

    -- For example:

    RAISE NOTICE 'Hello, World!';

END;

$$

Let's break down the above example:

CREATE OR REPLACE FUNCTION: This is the statement to create a function or stored procedure in PostgreSQL. The OR REPLACE option allows you to modify the function if it already exists with the same name.

my_stored_procedure(): This is the name of the stored procedure. You can give it any name you prefer.

$$: This is the dollar-quoted string syntax that encloses the body of the stored procedure.

BEGIN and END: These keywords define the beginning and end of the procedure block, where you can write your procedure logic.

RAISE NOTICE 'Hello, World!';: This is an example of a statement inside the procedure. It raises a notice message that will be displayed when the procedure is executed.

LANGUAGE plpgsql;: This specifies the language used for the stored procedure. In this case, it's "plpgsql," which is the procedural language for PostgreSQL.

Create function in PostgreSQL Statement-

CREATE OR REPLACE FUNCTION get_total_rows(table_name text)

RETURNS bigint AS

$$

DECLARE

    total_rows bigint;

BEGIN

    EXECUTE format('SELECT COUNT(*) FROM %I', table_name) INTO total_rows;

    RETURN total_rows;

END;

$$

LANGUAGE plpgsql;

Create Trigger Statement-

CREATE TRIGGER trigger_name   

 {BEFORE | AFTER} { event }  

  ON table_name  

  [FOR [EACH] { ROW | STATEMENT }]  

  EXECUTE PROCEDURE trigger_function 

List sessions / active connections in PostgreSQL database-

select pid as process_id,

       usename as username,

       datname as database_name,

       client_addr as client_address,

       application_name,

       backend_start,

       state,

       state_change

from pg_stat_activity;

• process_id - process ID of this backend
• username - name of the user logged into this backend
• database_name - name of the database this backend is connected to
• client_address - IP address of the client connected to this backend
• application_name - name of the application that is connected to this backend
• backend_start - time when this process was started. For client backends, this is the time the client connected to the server.
• State - current overall state of this backend. Possible values are:

o   active

o   idle

o   idle in transaction

o   idle in transaction (aborted)

o   fastpath function call

o   disabled

• state change - time when the state was last changed

Finding blocked processes and blocking queries-

SELECT

    activity.pid,

    activity.usename,

    activity.query,

    blocking.pid AS blocking_id,

    blocking.query AS blocking_query

FROM pg_stat_activity AS activity

JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

Index Statement-

CREATE INDEX index_name ON table_name;

Single-Column Indexes-

CREATE INDEX index_name

ON table_name (column_name);

Multicolumn Indexes-

CREATE INDEX index_name

ON table_name (column1_name, column2_name);

Drop Primary Key-

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

PostgreSQL REINDEX syntax-

 REINDEX

  [ ( VERBOSE ) ]

  [ ( CONCURRENTLY [ boolean ] ) ]

  [ ( TABLESPACE new_tablespace ) ]

  { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;

Explanation:

• The VERBOSE keyword is not required. If this keyword is specified, progress will be shown while the index is rebuilt.
• The keyword CONCURRENTLY is optional. PostgreSQL will not halt any table operations while the index is rebuilt with it.
• TABLESPACE new_tablespace is not required. PostgreSQL will use it to rebuild indexes on the new tablespace.
• The index object to rebuild is specified by the term INDEX | TABLE | SCHEMA | DATABASE | SYSTEM.

 

·  REBUILD INDEX: Recreate the provided index.

·  TABLE: This command rebuilds all indexes in the chosen table.

·  SCHEMA: Rebuild all indexes in the schema supplied.

·  DATABASE: In the specified database, rebuild all indexes.

·  SYSTEM: Rebuild all indexes in the chosen database's system catalogue.

·  The name specifies the object's name.

Here are some examples of specific applications:

Use the INDEX keyword and the index name to recreate a single index:

REINDEX INDEX index_name; 

To rebuild all indexes in a table, use the TABLE keyword with the table name:

TABLE REINDEX table_name;

To rebuild all indexes in a schema, use the SCHEMA keyword and the schema name:

REINDEX SCHEMA schema_name;

Use the DATABASE keyword and the database name to rebuild all indexes in a database:

REINDEX DATABASE database_name;

Use the SYSTEM keyword and the database name to rebuild all indexes on the system catalogue in one data:

REINDEX SYSTEM database_name;

 

Exit psql shell-

\q