PostgreSQL Architecture:

PostgreSQL instance consists of set of Process and Memory.

PostgreSQL uses a simple “process per-user” client/server model.

The major processes a

1. The ‘postmaster’ which is: supervisory daemon process,

 ‘postmaster’ is attached to shmmem segment but refrains from accessing to it.

 always running waiting for connection requests

2. Utility processes

( bgwriter, walwriter, syslogger, archiver, statscollector, checkpointer and autovacuum

launcher ) and

3. User Backend process ( postgres process itself, Server Process )

When a client request for connection to the database, firstly request is hit to Postmaster

daemon process. After performing authentication and authorization it forks one new backend

server process (postgres).

Henceforth, the frontend process and the backend server communicate directly without

intervention by the postmaster. The postmaster is always running, waiting for connection

requests, whereas frontend and backend processes come and go. The libpq library allows a

single frontend to make multiple connections to backend processes.

However, each backend process is a single-threaded process that can only execute one query at

a time; so the communication over any one frontend-to-backend connection is single-threaded.

Postmaster and postgres servers run with the user ID of the PostgreSQL “superuser”.

One postgres process exists for every open database session. Once authenticated with user

connection, it directly connects (with who and for what purpose) with shared memory.

POSTGRESQL SHARED MEMORY

Shared Buffers:
Sets the amount of memory the database server uses for shared memory buffers. The default is

typically 32MB. Larger settings for shared_buffers usually require a corresponding increase

in checkpoint_segments, in order to spread out the process of writing large quantities of new or

changed data over a longer period of time. Below 3 parameters should be discussed:

 bgwriter_delay

 bgwriter_lru_maxpages

 bgwriter_lru_multiplier

WAL Buffers

The amount of shared memory used for WAL data that has not yet been written to disk. The

default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers, but not less

than 64kB nor more than the size of one WAL segment, typically 16MB. This value can be set

manually if the automatic choice is too large or too small, but any positive value less than 32kB

will be treated as 32kB. This parameter can only be set at server start. The contents of the WAL

buffers are written out to disk at every transaction commit, so extremely large values are

unlikely to provide a significant benefit. However, setting this value to at least a few megabytes

can improve write performance on a busy server where many clients are committing at once.

The auto-tuning selected by the default setting of -1 should give reasonable results in most

cases.

CLOG Buffers:
$PGDATA/pg_clog contains a log of transaction metadata. This log tells PostgreSQL which

transactions completed and which did not. The clog is small and never has any reason to become

bloated, so you should never have any reason to touch it.

POSTGRESQL PER BACKEND MEMORY

work_mem

Specifies the amount of memory to be used by internal sort operations and hash tables before

writing to temporary disk files. Default is 1M. Note that for a complex query, several sort or hash

operations might be running in parallel; each operation will be allowed to use as much memory

as this value specifies before it starts to write data into temporary files. Also, several running

sessions could be doing such operations concurrently. Therefore, the total memory used could

be many times the value of work_mem; it is necessary to keep this fact in mind when choosing

the value.

temp_buffers

Sets the maximum number of temporary buffers used by each database session. Default is 8M.

The setting can be changed within individual sessions, but only before the first use of temporary

tables within the session; subsequent attempts to change the value will have no effect on that

session.

maintenance_work_mem

Specifies the maximum amount of memory to be used by maintenance operations, such as

VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Default is 16M. Since only one of
these operations can be executed at a time by a database session, and an installation normally

doesn’t have many of them running concurrently, it’s safe to set this value significantly larger

than work_mem.

UTILITY PROCESSES:

Mandatory processes: We cannot Enable/Disable these processes.

 BGWriter

 WAL Writer

 Checkpointer

Optional Processes:

 Stats-collector

 Autovacuum launcher

 Archiver

 Syslogger
 WAL Sender

 WAL Receiver

BGWriter

The function of background writer is to issue writes of “dirty” (new or modified) shared buffers.

It writes shared buffers so that server processes handling user queries seldom or never need to

wait for a write to occur. However, the background writer does cause a net overall increase in

I/O load, because while a repeatedly-dirtied page might otherwise be written only once per
checkpoint interval, the background writer might write it several times as it is dirtied in the same

interval. Below parameters can be used to tune the behavior for local needs.

bgwriter_delay: Specifies the delay between activity rounds for the background writer. Default is

200ms.

bgwriter_lru_maxpages: In each round, no more than this many buffers will be written by the

background writer. Setting this to zero disables background writing (except for checkpoint

activity). The default value is 100 buffers.

bgwriter_lru_multiplier: The number of dirty buffers written in each round is based on the

number of new buffers that have been needed by server processes during recent rounds. The

default is 2.0.

Smaller values of bgwriter_lru_maxpages and bgwriter_lru_multiplier reduce the extra I/O load

caused by the background writer, but make it more likely that server processes will have to issue

writes for themselves, delaying interactive queries.

[Link]

WAL Writer

WAL writer process writes and fsync WAL at convenient Intervals. WAL buffers holds the

changes made to the database in the transaction logs, in order to guarantee transaction security.

WAL buffers are written out to the disk at every transaction commit, as WAL writer process is
responsible to write on to the disk. WAL_WRITER_DELAY parameter for invoking the WAL Writer

Process, however there are other parameters which also keeps the WAL Writer busy.

Stats Collector

Stats collector process will collect the information about the server activity. It count number of

access to the tables and indexes in both disk-block and individual row items. It also tracks the

total number of rows in each table, and information about VACUUM and ANALYZE actions for

each table. Collection of statistics adds some overhead to query execution, whether to collect or

not collect information. Some of the parameter in the [Link] file will control the

collection activity of the stats collector process. Click here to know more about the stats

collector process and its related parameters.

Autovacuum Launcher

For automating the execution of VACUUM and ANALYZE command, Autovacuum Launcher is a

daemon process consists of multiple processes called autovacuum workers. Auto-vacuum

launcher is a charge of starting autovacuum worker processes for all databases. Launcher will
distribute the work across time, attempting to start one worker on each database for every

interval, set by the parameter autovacuum_naptime. One worker will be launched for each

database, set by the parameter autovacuum_max_workers. Each worker process will check each

table within its database and execute VACUUM or ANALYZE as needed. Following will brief about

the AUTOVACUUM LAUNCHER PROCESS parameters.

Click here to know more about AUTOVACUUM LAUNCHER PROCESS parameters

Syslogger Process / Logger Process:

Syslogger Process

As per the figure, it is clearly understood that all the utility process + User backends +

Postmaster Daemon are attached to the syslogger process for logging the information about

their activities. Every process information is logged under $PGDATA/pg_log with the file .log.
Debugging more on the process information will cause overhead on the Server. Minimal tuning

is always recommended, however, increasing the debug level when required. Below link will

brief further on logging parameters.

[Link]

Archiver

Archiver process is optional process, default is OFF.

Setting up the database in Archive mode means to capture the WAL data of each segment file

once it is filled and save that data somewhere before the segment file is recycled for reuse.

1. On Database Archivelog mode, once the WAL data is filled in the WAL Segment, that filled

segment named file is created under PGDATA/pg_xlog/archive_status by the WAL Writer

naming the file as “.ready”. File naming will be “[Link]”.

2. Archiver Process triggers on finding the files which are in “.ready” state created by the WAL

Writer process. Archiver process picks the ‘segment-file_number’ of .ready file and copies

the file from $PGDATA/pg_xlog location to its concerned Archive destination given in

‘archive_command’ parameter([Link]).

3. On successful completion of copy from source to destination, archiver process renames the

“[Link]” to “[Link]”. This completes the archiving

process.
It is understood that, if any files named “[Link]” found

in $PGDATA/pg_xlog/archive_status. They are the pending files still to be copied to Archive

destination.

For more information on parameters and

Archiving, [Link]

There are three major elements in a PostgreSQL database: the postmaster, the front end
(client), and the back end. The client sends requests to the postmaster with information such as
IP protocol and which database to connect to. The postmaster authenticates the connection
and passes it to the back-end process for further communication. The back-end process
executes the query and sends results directly to the front end (client).
A PostgreSQL instance is based on a multiprocess model instead of a multithreaded model. It
spawns multiple processes for different jobs, and each process has its own functionality. The
major processes include the client process, the WAL writer process, the background writer
process, and the checkpointer process:

 When a client (foreground) process sends read or write requests to the PostgreSQL
instance, it doesn't read or write data directly to the disk. It first buffers the data in shared
buffers and write-ahead logging (WAL) buffers.
 A WAL writer process manipulates the content of the shared buffers and WAL buffers to
write into the WAL logs. WAL logs are typically transaction logs of PostgreSQL and are
sequentially written. Therefore, to improve the response time from the database,
PostgreSQL first writes into the transaction logs and acknowledges the client.
 To put the database in a consistent state, the background writer process checks the
shared buffer periodically for dirty pages. It then flushes the data onto the data files that are
stored on NetApp volumes or LUNs.
 The checkpointer process also runs periodically (less frequently than the background
process) and prevents any modification to the buffers. It signals to the WAL writer process
to write and flush the checkpoint record to the end of WAL logs that are stored on the
NetApp disk. It also signals the background writer process to write and flush all dirty pages
to the disk.
Initialization parameters

You create a new database cluster by using the initdb program. An initdb script creates the data
files, system tables, and template databases (template0 and template1) that define the cluster.
The template database represents a stock database. It contains definitions for system tables,
standard views, functions, and data types. pgdata acts as an argument to the initdb script that
specifies the location of the database cluster.
All the database objects in PostgreSQL are internally managed by respective OIDs. Tables and
indexes are also managed by individual OIDs. The relationships between database objects and
their respective OIDs are stored in appropriate system catalog tables, depending on the type of
object. For example, OIDs of databases and heap tables are stored in pg_database and
`pg_class, respectively. You can determine the OIDs by issuing queries on the PostgreSQL client.
Every database has its own individual tables and index files that are restricted to 1GB. Each
table has two associated files, suffixed respectively with _fsm and _vm. They are referred to as
the free space map and the visibility map. These files store the information about free space
capacity and have visibility on each page in the table file. Indexes only have individual free
space maps and don't have visibility maps.
The pg_xlog/pg_wal directory contains the write-ahead logs. Write-ahead logs are used to
improve database reliability and performance. Whenever you update a row in a table,
PostgreSQL first writes the change to the write-ahead log, and later writes the modifications to
the actual data pages to a disk. The pg_xlog directory usually contains several files, but initdb
creates only the first one. Extra files are added as needed. Each xlog file is 16MB long.
There are several PostgreSQL tuning configurations that can improve performance.

The most commonly used parameters are as follows:

 max_connections = <num>: The maximum number of database connections to have at

one time. Use this parameter to restrict swapping to disk and killing the performance.
Depending on your application requirement, you can also tune this parameter for the
connection pool settings.
 shared_buffers = <num>: The simplest method for improving the performance of your
database server. The default is low for most modern hardware. It is set during deployment
to approximately 25% of available RAM on the system. This parameter setting varies
depending on how it works with particular database instances; you might have to increase
and decrease the values by trial and error. However, setting it high is likely to degrade
performance.
 effective_cache_size = <num>: This value tells PostgreSQL's optimizer how much
memory PostgreSQL has available for caching data and helps in determining whether to use
an index. A larger value increases the likelihood of using an index. This parameter should be
set to the amount of memory allocated to shared_buffers plus the amount of OS cache
available. Often this value is more than 50% of the total system memory.
 work_mem = <num>: This parameter controls the amount of memory to be used in sort
operations and hash tables. If you do heavy sorting in your application, you might need to
increase the amount of memory, but be cautious. It isn't a system wide parameter, but a
per-operation one. If a complex query has several sort operations in it, it uses multiple
work_mem units of memory, and multiple back ends could be doing this simultaneously.
This query can often lead your database server to swap if the value is too large. This option
was previously called sort_mem in older versions of PostgreSQL.
 fsync = <boolean> (on or off): This parameter determines whether all your WAL pages
should be synchronized to disk by using fsync() before a transaction is committed. Turning it
off can sometimes improve write performance and turning it on increases protection from
the risk of corruption when the system crashes.
 checkpoint_timeout: The checkpoint process flushes committed data to disk. This
involves a lot of read/write operations on disk. The value is set in seconds and lower values
decrease crash recovery time and increasing values can reduce the load on system
resources by reducing the checkpoint calls. Depending on application criticality, usage,
database availability, set the value of checkpoint_timeout.
 commit_delay = <num> and commit_siblings = <num>: These options are used together
to help improve performance by writing out multiple transactions that are committing at
once. If there are several commit_siblings objects active at the instant your transaction is
committing, the server waits for commit_delay microseconds to try to commit multiple
transactions at once.
  max_worker_processes / max_parallel_workers: Configure the optimal number of
workers for processes. Max_parallel_workers correspond to the number of CPUs available.
Depending on application design, queries might require a lesser number of workers for
parallel operations. It is better to keep the value for both parameters the same but adjust
the value after testing.
 random_page_cost = <num>: This value controls the way PostgreSQL views non-
sequential disk reads. A higher value means PostgreSQL is more likely to use a sequential
scan instead of an index scan, indicating that your server has fast disks Modify this setting
after evaluating other options like plan-based optimization, vacuuming, indexing to altering
queries or schema.
 effective_io_concurrency = <num>: This parameter sets the number of concurrent disk
I/O operations that PostgreSQL attempts to execute simultaneously. Raising this value
increases the number of I/O operations that any individual PostgreSQL session attempts to
initiate in parallel. The allowed range is 1 to 1,000, or zero to disable issuance of
asynchronous I/O requests. Currently, this setting only affects bitmap heap scans. Solid-
state drives (SSDs) and other memory-based storage (NVMe) can often process many
concurrent requests, so the best value can be in the hundreds.
See the PostgreSQL documentation for a complete list of PostgreSQL configuration parameters.
cd C:\Program Files\PostgreSQL\14\bin

pg_ctl -D C:\Postgrecluster start

psql -p 9999 -d postgres -U bhasker

psql Meta-Commands:

The meta-commands are small commands written/prefixed with the backslash. These
commands are used to perform different operations like managing users, databases, tables, etc.
There is a long list of meta-commands provided by the psql and a few of them are listed below:

\l - Lists All the Databases

The \l meta-command lists all the databases in the system. Let's see how it works:

\l
Important: The \l command must be executed without a semi-colon; otherwise, you wouldn't

get the desired results.

\c<database_name> - Connect to a Specific Database

The \c<database_name> command is used to get connected to the database whose name is
specified in the command:

\c test_db
The output demonstrates that running the "\c" command connects us to the specified
database, i.e., "test_db".

\d - Display Objects

The \d meta-command displays all the objects. These objects include a table, view, index,
schema, and database. We will implement the \d command with different options to display
different database objects.

 \dt - Display Tables

This meta-command displays all the tables in the database when it is used as “\dt”:

\dt

The above enlisted are the tables available in my default database.

 \dv - Display Views

This meta-command will give us all the views present in our database. In my case, I have never
created any view so the command will find no such relation. The \dm is used
to display the materialized view. The same will be the output for this case:

\dv;
\dm;
  \di - Display Indexes

To display indexes, we use the \di command as follows:

\di

 \dn - Display Schemas

The “\dn” command will give the list of schemas present in the system:

\dn

 \dT - Display the Data Types

The \dT command displays the user-defined data types:

\dT

Note that the “\dt” is different from “\dT”. The \dt displays tables.
  \ds - Display the Sequences

The “\ds” command displays all the sequences of a database.

 \dtS - Displays System Catalog Tables

The system catalog tables consist of the tables and views and describe the structure of the
database. These can be displayed in the psql command line using the \dtS command:

\dtS
  \du - Displays all the Roles in the Database

This meta-command gives all the user roles defined in the system:

\du
In my case, the only user role defined is the Postgres.

\x - Set the Expanded Display

We can toggle the expanded display using the "\x" command. This is useful for large tables. We
can set it to auto or can be toggled on or off:

\x

\set - Display Internal Variables

The \set command is used to list all the internal variables, as shown below:

\set
\unset <name_of_variable>- Delete an Internal Variable

This command will delete the internal variable from the list.

\echo<text> - Print Message

This command is used to get the text/message printed to the console:

\echo Hello From Command Prompt!

\? - get help

This command is used to get help from the psql, particularly regarding meta-commands:

\?

\a - Toggles Alignment of the Output Format

The “\a” command aligns and unaligns the output format, as demonstrated in the following
snippet:

\a

\C<caption> - Sets the Title/Caption of a Table

The “\C<caption>” command sets the title for the database table:
\C hello

\r - Resets the Query Buffer

This command resets the query buffer:

\r

\z - Lists All the Tables with Their Revoked Permissions

The “\z” command displays the list of tables with their objects as Access Control Lists i.e. ACLs:

\z
\q - Quit psql

By running the “\q” query you can quit the psql, as illustrated in the following snippet:

\q

Now by pressing any key, you can simply exit from the psql.

Additional Information
You can execute multiple meta-commands at the same time to perform the desired operation
like this:
In the above example, we have used three commands to display the table caption, and the
table, and toggle the alignment respectively and simultaneously.

We can add a “+” sign to every command to get some additional technical information about
whatever is the output. For example; running the “\dt” gives:
While if we execute the “\dt+” command. There is some additional information displayed.

PostgreSQL Databases :

A database in Postgres is the topmost hierarchical level for managing database objects, like
tables, views, indexes, etc. We can perform various operations on a database, such as create,
alter, drop, etc. For this purpose, different commands are used.

Create Database

To create a new database in Postgres, use the CREATE DATABASE command followed by the
database name to be created:
CREATE DATABASE db_name;

Access/Connect Database

To connect to a database, execute the “\c” command (from psql) followed by the database
name:

\c db_name;

Drop Database

To drop a database, use the DROP DATABASE command, as follows:

DROP DATABASE db_name;

Alter Database

To alter a database, use the ALTER DATABASE command. Using this command, you can change
the database owner, rename the database, change database attributes, etc.

Change Database Owner

To change the database owner, users need to execute the ALTER DATABASE command as
follows:

--To Change Database Owner

ALTER DATABASE db_name
OWNER TO new_owner_name | current_user |current_role | session_user;

--To Rename a Database

ALTER DATABASE old_db_name
RENAME TO new_db_name;

--To Alter a Tablespace

ALTER DATABASE db_name
SET TABLESPACE new_tablespace_name;

-- To Alter Database Attributes

ALTER DATABASE db_name
WITH option;
-- To Alter Configuration Variables
ALTER DATABASE db_name
SET configuration_parameter = value;

-- To RESET Configuration Parameter

ALTER DATABASE db_name
RESET configuration_parameter;

-- To List all Databases

\l

PostgreSQL Schemas

Postgres provides different commands to work with schemas, such as CREATE SCHEMA, DROP
SCHEMA, etc.

Create Schema

To create a schema, use the CREATE SCHEMA statement as follows:

CREATE SCHEMA [IF NOT EXISTS] new_schema;

Drop Schema

To drop a schema, run the DROP SCHEMA statement with one of the following options:

DROP SCHEMA schema_name

[RESTRICT | CASCADE];

Alter Schema

The ALTER SCHEMA command is used in Postgres to modify the schema’s definition. This
command/statement allows us to rename a schema, change the schema’s owner, etc.

--To Rename a Schema

ALTER SCHEMA schema_name
RENAME TO new_schema_name;

--To Change Schema Owner

ALTER SCHEMA schema_name
OWNER TO{ new_owner_name | SESSION_USER | CURRENT_USER};

--TO Show All Available Schemas

\dn

Default schema of postgres is public , we have chance to change the default schema as well ,
and we can have a chance to keep two schemas as default ,

If any table is there in two schemas , which is there as a default , it will pickup , it will take as
per search path

set search_path=app,public;

grant create,usage on schema app to user3;

set --- session level

alter user user1 set search_path=app,public;

alter database db1 set search_path=app;

Catalog tables :
PostgreSQL Tables:

Postgres offers several commands to perform different table operations, such as creating a
table, dropping a table, selecting a table’s data, etc.
Create Table

To create a new table, run the CREATE TABLE statement followed by the table name to be
created, as follows:

CREATE TABLE [IF NOT EXISTS] tab_name (

column_name DATATYPE column_contraint,
);

IF NOT EXISTS” is an optional clause that creates a new table only if it doesn’t exist already.
Specify the column names followed by their respective data types and constraints.

Drop Table

To drop a table in Postgres, run the DROP TABLE statement with the following syntax:

DROP TABLE [IF EXISTS] tab_name

[RESTRICT | CASCADE];

IF EXISTS”, “RESTRICT”, and “CASCADE” are optional clauses. The “IF EXISTS” option drops a
table if it already exists, the RESTRICT option denies the table deletion in case some objects
depend on it, while the CASCADE option deletes a table along with its dependent objects.

Alter Table

To modify the table’s definition in Postgres, run the ALTER TABLE command as follows:

--To Rename a Table

ALTER TABLE tab_name
RENAME TO new_tab_name;

--To Add new Columns

ALTER TABLE tab_name
ADD COLUMN new_col_name data_type constraint;

--To Drop a Table Column

ALTER TABLE tab_name
DROP COLUMN col_name;

--To Add a Constraint to a Table

ALTER TABLE tab_name
ALTER COLUMN col_name SET constraint_name;
--To Drop/Remove a Constraint
ALTER TABLE tab_name
ALTER COLUMN col_name DROP constraint_name;

--To Alter Column Data Type

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

--To Rename Table Columns

ALTER TABLE tab_name
RENAME COLUMN old_col_name TO new_col_name;

--To Add a Primary Key

ALTER TABLE tab_name
ADD PRIMARY KEY col_name;

--To Remove a Primary Key

ALTER TABLE tab_name
DROP CONSTRAINT primary_key_constraint_name;

Fetch the Table’s Data

To fetch the table’s data, use the SELECT command, as follows:

--To Fetch All Columns

SELECT * FROM tab_name;

--To Fetch Specific Columns

SELECT col_1, col_2, ...
FROM tab_name;

Insert Data Into a Table

To insert data into a table, use the INSERT INTO query as follows:

INSERT INTO tab_name(col_list)

VALUES (value_list);

Delete Table Records

To delete records from a table, run the following query:

--To Delete all Records

DELETE FROM tab_name;

--To Delete Specific Records

DELETE FROM tab_name
WHERE condition;

List Tables

To list all available tables, run the “\dt” command from SQL Shell, like this:

\dt

PostgreSQL Views:

In PostgreSQL views are the virtual tables that are used to simplify complex queries. Postgres
supports different types of views, such as materialized views, recursive views, etc. We can
create, alter, or remove a view using different commands.

Create a View

To create a new view in Postgres, run one of the following CREATE VIEW statements:

--To Create a Standard View

CREATE OR REPLACE viewName AS
query;

--To Create a Recursive View

CREATE RECURSIVE VIEW viewName(col_list) AS
SELECT col_list;

--To Create a Materialized View

CREATE MATERIALIZED VIEW viewName
AS
query
WITH <NO> DATA;

--To refresh a Postgres MATERIALIZED view

REFRESH MATERIALIZED VIEW CONCURRENTLY viewName;
Drop a View

To drop a view run the below-given commands:

DROP VIEW <IF EXISTS> viewName;

--To drop a materialized view
DROP MATERIALIZED VIEW viewName;

Rename a View

To rename a Postgres view, execute the following command:

ALTER VIEW viewName RENAME TO new_viewName;

PostgreSQL Users:

PostgreSQL users manage the database access and privileges. We can create new users, alter
them when needed, or even drop them (when no longer needed).

Create User

To create a user in PostgreSQL, use the CREATE ROLE or CREATE USER command followed by
the role/user name to be created:

CREATE USER use_name

WITH option;

Any privilege of your choice, such as SUPERUSER, LOG-IN, CREATEDB, CREATEROLE,

CONNECTION LIMIT, PASSWORD, VALID UNTIL, etc., can replace the option.

Create Superuser

To create a superuser, run the CREATE USER statement along with the SUPERUSER attribute, as
follows:

CREATE USER user_name

WITH SUPERUSER;

Drop User
To drop any particular user in Postgres, specify the DROP USER statement followed by the user
name to be dropped:

DROP USER [IF EXISTS] user_name;

Find Users

To find the users in PostgreSQL, fetch the “usename” column of the “pg_user” table using
the SELECT query, as follows:

SELECT usename, usesysid

FROM pg_user;

Find Logged in Users

To find the currently logged-in Postgres users, use the SELECT query with a system view
named “pg_stat_activity”, as shown below:

SELECT DISTINCT usename, usesysid

FROM pg_stat_activity;

List Users

Execute the “\du” or “\du+” commands from the SQL Shell to find the list of users:

\du;

Alter Users

To change the user’s definition, use the ALTER USER command with the suitable clause, as seen
in the following snippet:

--To Rename a User

ALTER USER user_name
RENAME TO new_user_name;

--To Change User Password

ALTER USER user_name
WITH PASSWORD 'updated_password';
--To Change Password Validity Date
ALTER USER user_name
WITH PASSWORD 'updated_password'
VALID UNTIL 'expiry_date_time';

--To Change a Normal User to a Superuser

ALTER USER existing_user_name
WITH SUPERUSER;

--To Alter User Permissions

ALTER USER user_name
WITH user_privileges;

PostgreSQL Indexes:

Indexes improve the efficiency of the PostgreSQL database by finding and retrieving a table
record quickly. To create an index in Postgres, simply employ the following syntax:

CREATE [UNIQUE] INDEX indexName

ON tableName (column_list);

Similarly, you can drop/remove an index using its name, as shown below:

DROP INDEX indexName;

C-6
User creation :
C-8
Generate_series
9
PG_HBA.CONF
Host based authentication File