EasyReliableDBA: Postgresql Commands For Beginner

How to connect postgreSQL Database

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

C:\Users\admin>cd C:\Program Files (x86)\PostgreSQL\10\bin

C:\Program Files (x86)\PostgreSQL\10\bin>psql -U postgres

Password for user postgres:

psql (10.15, server 13.1)

WARNING: psql major version 10, server major version 13.

Some psql features might not work.

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.

postgres=#

postgres=# select setting from pg_settings where name='data_directory';

setting

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

C:/Program Files/PostgreSQL/13/data

(1 row)

postgres=#

Postgres-# \q

To check Database Status

C:\Program Files (x86)\PostgreSQL\10\bin>pg_ctl -D "C:/Program Files/PostgreSQL/13/data" status

pg_ctl: no server running

C:\Program Files (x86)\PostgreSQL\10\bin>

[root@easypostgresvm ~]#

[root@easypostgresvm ~]# sudo su - postgres

Last login: Mon Mar 7 14:00:48 UTC 2022 on pts/1

[postgres@easypostgresvm ~]$

[postgres@easypostgresvm ~]$ psql

psql (13.1)

Type "help" for help.

postgres=# SHOW data_directory;

data_directory

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

/var/lib/pgsql/13/data

(1 row)

postgres=#

Use \q to quit.

postgres-# \q

To check Status of Postgres Cluster

[postgres@easypostgresvm ~]$ systemctl status postgresql-13

● postgresql-13.service - PostgreSQL 13 database server

Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)

Active: active (running) since Tue 2022-03-08 04:04:53 UTC; 58min ago

Docs: https://www.postgresql.org/docs/13/static/

Main PID: 1377 (postmaster)

Tasks: 8 (limit: 26213)

Memory: 28.6M

CGroup: /system.slice/postgresql-13.service

├─1377 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/

├─1528 postgres: logger

├─1547 postgres: checkpointer

├─1548 postgres: background writer

├─1549 postgres: walwriter

├─1550 postgres: autovacuum launcher

├─1551 postgres: stats collector

└─1552 postgres: logical replication launcher

[postgres@easypostgresvm ~]$ systemctl stop postgresql-13

==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====

Authentication is required to stop 'postgresql-13.service'.

Authenticating as: root

Password:

[postgres@easypostgresvm ~]$ exit

logout

[root@easypostgresvm ~]# systemctl stop postgresql-13

[root@easypostgresvm ~]#

[root@easypostgresvm ~]# systemctl status postgresql-13

● postgresql-13.service - PostgreSQL 13 database server

Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)

Active: inactive (dead) since Tue 2022-03-08 05:04:45 UTC; 28s ago

Docs: https://www.postgresql.org/docs/13/static/

Process: 1377 ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)

Main PID: 1377 (code=exited, status=0/SUCCESS)

Mar 08 04:04:52 easypostgresvm systemd[1]: Starting PostgreSQL 13 database server...

Mar 08 04:04:53 easypostgresvm postmaster[1377]: 2022-03-08 04:04:53.314 UTC [1377] LOG: redirecting log output to logging collector process

Mar 08 04:04:53 easypostgresvm postmaster[1377]: 2022-03-08 04:04:53.314 UTC [1377] HINT: Future log output will appear in directory "log".

Mar 08 04:04:53 easypostgresvm systemd[1]: Started PostgreSQL 13 database server.

Mar 08 05:04:45 easypostgresvm systemd[1]: Stopping PostgreSQL 13 database server...

Mar 08 05:04:45 easypostgresvm systemd[1]: postgresql-13.service: Killing process 1528 (postmaster) with signal SIGKILL.

Mar 08 05:04:45 easypostgresvm systemd[1]: Stopped PostgreSQL 13 database server.

[root@easypostgresvm ~]#

#Getting started

Getting started

Switch and connect

$ sudo -u postgres psql

List all databases

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

postgres=# \l

Connect to the database named postgres

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

postgres=# \c postgres

Disconnect

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

postgres=# \q

postgres=#

psql commands

Option Example Description

[-d] <database> psql -d mydb Connecting to database

-U psql -U john mydb Connecting as a specific user

-h -p psql -h localhost -p 5432 mydb Connecting to a host/port

-U -h -p -d psql -U admin -h 192.168.1.5 -p 2506 -d mydb Connect remote PostgreSQL

-W psql -W mydb Force password

-c psql -c '\c postgres' -c '\dt' Execute a SQL query or command

-H psql -c "\l+" -H postgres > database.html Generate HTML report

-l psql -l List all databases

-f psql mydb -f file.sql Execute commands from a file

-V psql -V Print the psql version

Getting help

\h Help on syntax of SQL commands

\h DELETE DELETE SQL statement syntax

\? List of PostgreSQL command

Run in PostgreSQL console

#PostgreSQL Working

Show version

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

SHOW SERVER_VERSION;

Show system status

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

\conninfo

Show environmental variables

SHOW ALL;

List users

SELECT rolname FROM pg_roles;

Show current user

SELECT current_user;

Show current user's permissions

\du

Show current database

SELECT current_database();

Show all tables in database

\dt

List functions

\df <schema>

Databases

List databases

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

Connect to database

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

\c <database_name>

Show current database

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

SELECT current_database();

Create database

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

CREATE DATABASE <database_name> WITH OWNER <username>;

CREATE DATABASE easyDB WITH OWNER EasyAdmin;

family=# CREATE DATABASE easyDB WITH OWNER EasyAdmin;

ERROR: role "easyadmin" does not exist

family=# CREATE ROLE EasyAdmin WITH LOGIN PASSWORD 'Babubigha@123';

CREATE ROLE

family=# CREATE DATABASE easyDB WITH OWNER EasyAdmin;

CREATE DATABASE

family=#

Drop database

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

DROP DATABASE IF EXISTS <database_name>;

Rename database

ALTER DATABASE <old_name> RENAME TO <new_name>;

Tables

List tables, in current db

\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

List tables, globally

\dt *.*.

SELECT * FROM pg_catalog.pg_tables

List table schema

\d <table_name>

\d+ <table_name>

SELECT column_name, data_type, character_maximum_length

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = '<table_name>';

Create table

CREATE TABLE <table_name>(

<column_name> <column_type>,

<column_name> <column_type>

);

CREATE TABLE Student (

name varchar,

role int );

Create table, with an auto-incrementing primary key

CREATE TABLE <table_name> (

<column_name> SERIAL PRIMARY KEY

);

Delete table

DROP TABLE IF EXISTS <table_name> CASCADE;

Permissions

Become the postgres user, if you have permission errors

sudo su - postgres

psql

Grant all permissions on database

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

Grant connection permissions on database

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

Grant permissions on schema

GRANT USAGE ON SCHEMA public TO <user_name>;

Grant permissions to functions

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

Grant permissions to select, update, insert, delete, on a all tables

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

Grant permissions, on a table

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

Grant permissions, to select, on a table

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

Columns

Add column

ALTER TABLE <table_name> IF EXISTS

ADD <column_name> <data_type> [<constraints>];

Update column

ALTER TABLE <table_name> IF EXISTS

ALTER <column_name> TYPE <data_type> [<constraints>];

Delete column

ALTER TABLE <table_name> IF EXISTS

DROP <column_name>;

Update column to be an auto-incrementing primary key

ALTER TABLE <table_name>

ADD COLUMN <column_name> SERIAL PRIMARY KEY;

Insert into a table, with an auto-incrementing primary key

INSERT INTO <table_name>

VALUES (DEFAULT, <value1>);

INSERT INTO <table_name> (<column1_name>,<column2_name>)

VALUES ( <value1>,<value2> );

Data

[Select](http://www.postgresql.org/docs/current/static/sql-select.html] all data

SELECT * FROM <table_name>;

Read one row of data

SELECT * FROM <table_name> LIMIT 1;

Search for data

SELECT * FROM <table_name> WHERE <column_name> = <value>;

Insert data

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

Update data

UPDATE <table_name>

SET <column_1> = <value_1>, <column_2> = <value_2>

WHERE <column_1> = <value>;

Delete all data

DELETE FROM <table_name>;

Delete specific data

DELETE FROM <table_name>

WHERE <column_name> = <value>;

Users

List roles

SELECT rolname FROM pg_roles;

Create user

CREATE USER <user_name> WITH PASSWORD '<password>';

Drop user

DROP USER IF EXISTS <user_name>;

Alter user password

ALTER ROLE <user_name> WITH PASSWORD '<password>';

Schema

List schemas

\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

Create schema

CREATE SCHEMA IF NOT EXISTS <schema_name>;

Drop schema

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

#PostgreSQL Commands

Tables

\d <table> Describe table

\d+ <table> Describe table with details

\dt List tables from current schema

\dt *.* List tables from all schemas

\dt <schema>.* List tables for a schema

\dp List table access privileges

\det[+] List foreign tables

Query buffer

\e [FILE] Edit the query buffer (or file)

\ef [FUNC] Edit function definition

\p Show the contents

\r Reset (clear) the query buffer

\s [FILE] Display history or save it to file

\w FILE Write query buffer to file

Informational

\l[+] List all databases

\dn[S+] List schemas

\di[S+] List indexes

\du[+] List roles

\ds[S+] List sequences

\df[antw][S+] List functions

\deu[+] List user mappings

\dv[S+] List views

\dl List large objects

\dT[S+] List data types

\da[S] List aggregates

\db[+] List tablespaces

\dc[S+] List conversions

\dC[+] List casts

\ddp List default privileges

\dd[S] Show object descriptions

\dD[S+] List domains

\des[+] List foreign servers

\dew[+] List foreign-data wrappers

\dF[+] List text search configurations

\dFd[+] List text search dictionaries

\dFp[+] List text search parsers

\dFt[+] List text search templates

\dL[S+] List procedural languages

\do[S] List operators

\dO[S+] List collations

\drds List per-database role settings

\dx[+] List extensions

S: show system objects, +: additional detail

Connection

===============

\c [DBNAME] Connect to new database

\encoding [ENCODING] Show or set client encoding

\password [USER] Change the password

\conninfo Display information

Formatting

\a Toggle between unaligned and aligned

\C [STRING] Set table title, or unset if none

\f [STRING] Show or set field separator for unaligned

\H Toggle HTML output mode

\t [on|off] Show only rows

\T [STRING] Set or unset HTML <table> tag attributes

\x [on|off] Toggle expanded output

Input/Output

\copy ... Import/export table

See also: Copy

===========================

• \dv: Meta command to get the list of views

• \di: List of indexes

• \d: Table description

======================performance Monitoring====================

turn off paging (less/more)

psql> \pset pager off

Pager usage is off.

-- find an object name by id

SELECT OID, relname

FROM pg_class

WHERE oid = 1234;

oid | relname

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

(0 rows)

-- find config info/pending restarts by name

SELECT name, setting, pending_restart

FROM pg_settings

WHERE name LIKE '%wal%';

name | setting | pending_restart

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

max_wal_senders | 8 | f

max_wal_size | 1024 | f

min_wal_size | 80 | f

wal_block_size | 8192 | f

wal_buffers | 2048 | f

wal_compression | off | f

wal_consistency_checking | | f

wal_keep_segments | 0 | f

wal_level | replica | f

wal_log_hints | on | f

wal_receiver_status_interval | 10 | f

wal_receiver_timeout | 300000 | f

wal_retrieve_retry_interval | 5000 | f

wal_segment_size | 2048 | f

wal_sender_timeout | 60000 | f

wal_sync_method | fdatasync | f

wal_writer_delay | 200 | f

wal_writer_flush_after | 128 | f

(18 rows)

-- reload config changes dynamically

SELECT pg_reload_conf();

pg_reload_conf

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

(1 row)

databases_tables.sql

-- Get Databases with their size

SELECT

pg_database.datname as database,

pg_size_pretty(pg_database_size(pg_database.datname)) AS size

FROM pg_database

WHERE datistemplate=false

AND pg_database_size(pg_database.datname) > 0;

database | size

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

postgres | 7613 kB

psql_db | 7637 kB

pg_db_1 | 7757 kB

pg_db_2 | 7757 kB

test11 | 8797 kB

(5 rows)

-- Get tables with their size (excluding index size)

SELECT

table_name,

pg_size_pretty(pg_total_relation_size(table_name)) as table_size

FROM information_schema.tables

WHERE table_schema not in ('pg_catalog', 'information_schema')

AND table_type='BASE TABLE'

ORDER BY pg_total_relation_size(table_name) DESC;

table_name | table_size

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

tabl3 | 1088 kB

tbl_1 | 16 kB

(2 rows)

-- Get tables with their size (including indexs, TOAST, map etc.) and approximate number of visible rows

/* As per postgres docs: The number of rows is only an estimate used by the planner.

* It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. */

SELECT

table_name,

pg_size_pretty(pg_total_relation_size(table_name)) as table_size,

pg_size_pretty(pg_total_relation_size(table_name) - pg_relation_size(table_name)) as index_toast_size,

(SELECT cast(reltuples as bigint) AS approximate_row_count FROM pg_class WHERE relname = table_name) as approximate_rows

FROM information_schema.tables

WHERE table_schema not in ('pg_catalog', 'information_schema')

AND table_type='BASE TABLE'

ORDER BY pg_total_relation_size(table_name) DESC;

table_name | table_size | index_toast_size | approximate_rows

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

tabl3 | 1088 kB | 296 kB | 4741

tbl_1 | 16 kB | 16 kB | 10

(2 rows)

-- Get object sizes (including materialized views)

SELECT relname AS objectname

, relkind AS objecttype

, reltuples AS entries

, pg_size_pretty(pg_table_size(oid)) AS size

FROM pg_class

WHERE relkind IN ('r', 'i', 'm')

ORDER BY pg_table_size(oid) DESC LIMIT 5;

objectname | objecttype | entries | size

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

deduped_reviews | m | 6.90744e+06 | 9171 MB

reviews_2018 | r | 6.12466e+06 | 5076 MB

reviews_2017 | r | 4.18376e+06 | 3342 MB

i_deduped_reviews_id_location_id_ts | i | 6.89427e+06 | 2436 MB

i_deduped_reviews_location_id | i | 6.89427e+06 | 2281 MB

(5 rows)

-- Get table's vacuum/analyze status

SELECT psut.relname,

to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,

to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,

to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,

to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,

to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)

+ (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)

* pg_class.reltuples), '9G999G999G999') AS av_threshold,

CASE

WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)

+ (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)

* pg_class.reltuples) < psut.n_dead_tup

THEN '*'

ELSE ''

END AS expect_av

FROM pg_stat_user_tables psut

JOIN pg_class on psut.relid = pg_class.oid

ORDER BY 1;

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

deduped_reviews | 2018-12-04 05:10 | 2018-11-29 12:52 | 6,841,022 | 4,644,265 | 1,368,254 | *

reviews_2016 | 2018-12-04 00:06 | | 1,620,157 | 266,186 | 324,081 |

reviews_2017 | 2018-12-04 00:08 | | 4,183,278 | 826,730 | 836,706 |

(3 rows)

postgres_connections.sql

-- Get current session info:

SELECT

datname as database ,usename as user ,client_addr,state, count(*) as total_connections,query

FROM pg_stat_activity

WHERE pid<>pg_backend_pid()

GROUP BY usename,client_addr,datname,state,query;

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

test11 | dba | 192.168.2.1 | idle | 1 | delete from tabl3 where id in (select id from tabl3 LIMIT 2);

| postgres | | | 1 |

| slave | 127.0.0.1 | active | 1 |

(4 rows)

-- Get current session info with Duration

SELECT

datname as database ,usename as user ,client_addr,state,

now() - backend_start as duration, count(*) as total_connections,query

FROM pg_stat_activity

WHERE state <> 'idle'

AND pid<>pg_backend_pid()

GROUP BY usename,client_addr,datname,state, now() - backend_start, query

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

postgres | datadog | 127.0.0.1 | idle in transaction | 00:00:12.81569 | 1 | select split_part(split_part(version(), 'PostgreSQL ', 2), ' ', 1)

(2 rows)

-- Get db connection count by user

SELECT

datname as database ,usename as user ,client_addr,state, count(*) as total_connections

FROM pg_stat_activity

WHERE pid<>pg_backend_pid()

GROUP BY usename,client_addr,datname,state;

database | user | client_addr | state | total_connections

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

| | | | 4

| slave | 10.10.48.81 | active | 1

beacon | beacon_service | 10.10.17.23 | idle | 6

postgres | datadog | 127.0.0.1 | idle | 1

| postgres | | | 1

postgres | datadog | 127.0.0.1 | idle in transaction | 1

beacon | beacon_service | 10.10.17.24 | idle | 36

(7 rows)

-- Active sessions by state

SELECT

datname as database ,usename as user ,client_addr,state, count(*) as total_connections,query

FROM pg_stat_activity

WHERE state <> 'idle'

AND pid<>pg_backend_pid()

GROUP BY usename,client_addr,datname,state,query;

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

test11 | dba | 192.168.2.1 | active | 1 | insert into tabl3 (cool_int,cool_json) values ( 113,'{"name":"testdata","ts": "var_created", "userId": "1037" , "username": "dba"}' );

| slave | 127.0.0.1 | active | 1 |

(3 rows)

-- Queries running for greater than x ms

SELECT

(now() - query_start) as query_time_taken,

datname as database ,usename as user,

query

FROM pg_stat_activity

WHERE xact_start IS NOT NULL

AND (now() - query_start) > interval '300 ms';

query_time_taken | database | user | query

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

00:00:06.432377 | test11 | app_level3 | SELECT pg_sleep(20);

(1 row)

-- Get blocking queries

SELECT pid,

usename,

pg_blocking_pids(pid) AS blocked_by,

query AS blocked_query

FROM pg_stat_activity

WHERE cardinality(pg_blocking_pids(pid)) > 0;

pid | usename | blocked_by | blocked_query

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

(0 rows)

/* References:

* https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

* http://bajis-postgres.blogspot.com/2014/01/handy-queries-of-postgresql-let-us.html

replication.sql

-- View current replication status (on Primary)

SELECT usename as user, application_name, client_addr, client_port, state, sent_lsn, write_lsn, flush_lsn, replay_lsn

FROM pg_stat_replication;

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

slave | walreceiver | 127.0.0.1 | 47050 | streaming | 0/3B35F70 | 0/3B35F70 | 0/3B35F70 | 0/3B35F70

(1 row)

-- View replication lag (on Secondary): Version 10 and up

SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()

THEN 0

ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())

END AS log_delay;

-- View replication lag (on Secondary): Version less than 10

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()

THEN 0

ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())

END AS log_delay;

/* References:

* https://severalnines.com/blog/postgresql-streaming-replication-deep-dive

* https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-REPLICATION-VIEW

users_roles.sql

-- View all roles with attributes and parent role using psql command

postgres=# \du

List of roles

Role name | Attributes | Member of

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

app | Create DB | {}

backups | Replication | {}

datadog | | {mon}

dba | Superuser, Create role, Create DB | {}

dev | | {}

devadmin | Create DB | {}

mon | | {}

mozdba | Superuser | {dba}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

psql_app | | {app}

repl | Cannot login, Replication | {}

slave | Replication | {repl}

-- View all roles with attributes and parent role using query

* equivalent query for /du command, found using psql -E

SELECT r.rolname, r.rolsuper, r.rolinherit,

r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,

r.rolconnlimit, r.rolvaliduntil,

ARRAY(SELECT b.rolname

FROM pg_catalog.pg_auth_members m

JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)

WHERE m.member = r.oid) as memberof

, r.rolreplication

, r.rolbypassrls

FROM pg_catalog.pg_roles r

WHERE r.rolname !~ '^pg_'

ORDER BY 1;

List of roles

Role name | Attributes | Member of

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

app | Create DB | {}

backups | Replication | {}

datadog | | {mon}

dba | Superuser, Create role, Create DB | {}

dev | | {}

devadmin | Create DB | {}

mon | | {}

mozdba | Superuser | {dba}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

psql_app | | {app}

repl | Cannot login, Replication | {}

slave | Replication | {repl}

-- View all roles a user/role belongs to (recursively)

WITH RECURSIVE cte AS (

SELECT oid, rolname FROM pg_roles WHERE rolname = 'app_level3'

UNION ALL

SELECT a.roleid, pg_get_userbyid(a.roleid)

FROM cte

JOIN pg_auth_members a ON a.member = cte.oid

)

SELECT * FROM cte;

oid | rolname

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

16505 | app_level3

16390 | psql_app

16385 | app

(3 rows)

-- View the recursive hierarchy of all roles/users across all schemas

WITH RECURSIVE

cte1 as (

SELECT b.oid, b.rolname, m.roleid as parentid

FROM pg_catalog.pg_auth_members m

RIGHT OUTER JOIN pg_catalog.pg_roles b ON (m.member = b.oid)

WHERE b.rolname !~ '^pg_'

cte2 as (

SELECT oid, rolname, parentid, CAST(rolname AS varchar(100)) AS inheritance_map

FROM cte1

WHERE parentid IS NULL

UNION ALL

SELECT c1.oid, c1.rolname, c1.parentid,

CAST(c2.inheritance_map || '->' || c1.rolname AS varchar(100)) AS inheritance_map

FROM cte1 c1 INNER JOIN cte2 c2

ON (c1.parentid = c2.oid)

)

SELECT * FROM cte2;

oid | rolname | parentid | inheritance_map

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

16389 | repl | | repl

16386 | mon | | mon

10 | postgres | | postgres

16394 | backups | | backups

16388 | devadmin | | devadmin

16387 | dev | | dev

16384 | dba | | dba

16385 | app | | app

16393 | slave | 16389 | repl->slave

16392 | datadog | 16386 | mon->datadog

16391 | mozdba | 16384 | dba->mozdba

16390 | psql_app | 16385 | app->psql_app

16505 | app_level3 | 16390 | app->psql_app->app_level3

-- View object ownership for all roles

SELECT relowner as userid,

pg_get_userbyid(relowner) as username,

CASE

WHEN relkind in ('r','t' ,'f','p') THEN 'table'

WHEN relkind in ('m', 'v') THEN 'view'

WHEN relkind='S' THEN 'sequence'

WHEN relkind='i' THEN 'index'

WHEN relkind='c' THEN 'composite_type'

END as type,

array_agg(relname) as owned_objs

FROM pg_class

WHERE relnamespace IN (

SELECT oid

FROM pg_namespace

WHERE nspname NOT LIKE 'pg_%'

AND nspname != 'information_schema'

)

GROUP BY 1,2,3

ORDER BY 2;

userid | username | type | owned_objs

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

16505 | app_level3 | index | {app_table_pkey,app_table1_pkey}

16505 | app_level3 | sequence | {app_table_id_seq,app_table1_id_seq}

16505 | app_level3 | table | {app_table,app_table1}

16384 | dba | table | {tabl3}

16384 | dba | index | {tabl3_pkey}

16384 | dba | sequence | {tabl3_id_seq}

(6 rows)

/* References:

* https://www.postgresql.org/docs/10/catalog-pg-class.html

* http://bajis-postgres.blogspot.com/2014/01/handy-queries-of-postgresql-let-us.html

* https://dba.stackexchange.com/questions/56096/how-to-get-all-roles-that-a-user-is-a-member-of-including-inherited-roles

====================

To Check if the Postgresql service is running via OS(linux):

ps -ef | grep postgres

To Check the RAM / CPU status via OS(linux):

top

To Check the Disk Status via OS(linux9:

df -h

To Check the Number of the Connection Count:

You can find the following information with the following script.

How many connections in the database?

What is Transaction Count?

Write to disk,

Read from cache,

What is the number of rows inserted, deleted etc?

SELECT * FROM pg_stat_database;

However, to have this table, after installing the Postgresql9.6-contrib package, it is necessary to create the following extension by connecting to the database with psql.

create extension pg_stat_statements;

Long Running Queries:

SELECT total_time,query FROM pg_stat_database;

Table and index access statistics:

select * from pg_stat_user_indexes;

select * from pg_stat_activity;

select * from pg_stat_user_indexes;

select * from pg_stat_activity;

How long have the existing queries been working:

SELECT pid,datname,usename, now() – query_start AS runtime, state,query FROM pg_stat_activity;

To stop a query:

select pg_cancel_backend(<pid of the process>)

To kill if the query does not stop using the above method:

select pg_terminate_backend(<pid of the process>)

To see locked objects:

SELECT * FROM pg_locks;

To see the size of a database:

SELECT pg_size_pretty(pg_database_size(‘database_name’));

After connecting with psql, you can see the sizes of all databases with l+ command.

To see the size of a table:

After connecting to database with psql;

SELECT pg_size_pretty( pg_total_relation_size(‘table_name’));

postgres=# \l

List of databases

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

(3 rows)

postgres=#

create role user2_remote with login password 'password';

postgres=# select rolname from pg_roles;

rolname

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

pg_monitor

pg_read_all_settings

pg_read_all_stats

pg_stat_scan_tables

pg_read_server_files

pg_write_server_files

pg_execute_server_program

pg_signal_backend

postgres

(9 rows)

postgres=#

psql -U user2_remote -W postgres -h 35.200.133.249

postgres@postgres-tutorial:~$

postgres@postgres-tutorial:~$ psql -U user2_remote -W postgres -h 35.200.133.249

Password:

psql: error: could not connect to server: Connection timed out

Is the server running on host "35.200.133.249" and accepting

TCP/IP connections on port 5432?

postgres@postgres-tutorial:~$

======================troubleshoot=================

oot@postgres-tutorial:/etc/postgresql/13/main#

root@postgres-tutorial:/etc/postgresql/13/main# dpkg -l | grep postgres

ii postgresql 13+225 all object-relational SQL database (supported version)

ii postgresql-13 13.7-0+deb11u1 amd64 The World's Most Advanced Open Source Relational Database

ii postgresql-client 13+225 all front-end programs for PostgreSQL (supported version)

ii postgresql-client-13 13.7-0+deb11u1 amd64 front-end programs for PostgreSQL 13

ii postgresql-client-common 225 all manager for multiple PostgreSQL client versions

ii postgresql-common 225 all PostgreSQL database-cluster manager

ii postgresql-contrib 13+225 all additional facilities for PostgreSQL (supported version)

-----------log file-----------

root@postgres-tutorial:/etc/postgresql/13/main#

/var/log/postgresql/postgresql-9.6-main.log

root@postgres-tutorial:/var/log# cd postgresql

root@postgres-tutorial:/var/log/postgresql# ls -ltr

total 12

-rw-r----- 1 postgres adm 9649 Jun 9 12:48 postgresql-13-main.log

root@postgres-tutorial:/var/log/postgresql# pwd

/var/log/postgresql

root@postgres-tutorial:/var/log/postgresql#

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

root@assaf-desktop:/home/assaf# ps axf | grep postgres

14338 ? S 0:00 /opt/djangostack-1.3-0/postgresql/bin/postgres -D /opt/djangostack-1.3-0/postgresql/data -p 5432

14347 ? Ss 0:00 \_ postgres: writer process

14348 ? Ss 0:00 \_ postgres: wal writer process

14349 ? Ss 0:00 \_ postgres: autovacuum launcher process

14350 ? Ss 0:00 \_ postgres: stats collector process

15139 pts/1 S+ 0:00 \_ grep --color=auto postgres

root@assaf-desktop:/home/assaf# netstat -nltp | grep 5432

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 14338/postgres

tcp6 0 0 ::1:5432 :::* LISTEN 14338/postgres

root@assaf-desktop:/home/assaf#

---------

tail 10f /var/log/messages

----------Monitoring script========

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;

-Cluster List----

s9s cluster --list --long

DBA Life Easy

pg_terminate_backend() AND pg_cancel_backend()

1) kill all connections of a specified database name.

-- List all available databases

postgres=>\l

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE datname = 'DATABASE NAME'

AND pid <> pg_backend_pid();

2) kill all connections of a specified user name.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE usename = 'testuser'

AND pid <> pg_backend_pid();

3) kill all sessions of current database.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE datname = current_database()

AND pid <> pg_backend_pid();

4) kill a connection of a specified pid.

postgres=> SELECT pg_terminate_backend(pid);

5) kill a running SQL of a session, but does not kill the session.

postgres=> SELECT pg_cancel_backend( pid );

It is useful when a running SQL is taking long time than expected.

=================

select name,setting from pg_setting where name like '%auto%';

select * from pg_settings where name like '%log%';

show log_destination;

=============Monitoring PostgreSQL VACUUM processes================

https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/

SELECT relname, n_dead_tup FROM pg_stat_user_tables;

SELECT relname AS "table_name", pg_size_pretty(pg_table_size(C.oid)) AS "table_size"

FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')

ORDER BY pg_table_size(C.oid)

DESC LIMIT 1;

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

ps -axww | grep autovacuum

SELECT name, setting FROM pg_settings WHERE name='autovacuum';

SELECT name, setting FROM pg_settings WHERE name='track_counts';

SELECT reloptions FROM pg_class WHERE relname='my_table';

ALTER TABLE my_table SET (autovacuum_enabled = true);

SELECT relname, reloptions FROM pg_class;

SELECT * from pg_settings where category like 'Autovacuum';

SELECT xact_start, state, usename FROM pg_stat_activity;

=======================wal configuration==========

postgres-#

postgres-# \l

List of databases

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

(4 rows)

postgres-# \c dbname2

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

dbname2-# show wal_level

dbname2-# exit

Use \q to quit.

dbname2-# \sq

invalid command \sq

Try \? for help.

dbname2-# \q

postgres@easytraining:~$ psql

psql (13.7 (Debian 13.7-0+deb11u1))

Type "help" for help.

postgres=#

postgres=# show wal_level;

wal_level

-----------

replica

(1 row)

postgres=# show max_wal_size;

max_wal_size

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

1GB

(1 row)

postgres=# show wal_keep_segments;

ERROR: unrecognized configuration parameter "wal_keep_segments"

postgres=#

postgres=# show wal_keep_segment;

ERROR: unrecognized configuration parameter "wal_keep_segment"

postgres=# show wal_keep_segments;

ERROR: unrecognized configuration parameter "wal_keep_segments"

postgres=# show archive_mode;

archive_mode

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

off

(1 row)

postgres=# show archive command;

ERROR: syntax error at or near "command"

LINE 1: show archive command;

postgres=# show archive_command;

archive_command

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

(disabled)

(1 row)

postgres=# show archive_timeout;

archive_timeout

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

(1 row)

postgres=#

postgres=# alter system set archive_mode=on;

ALTER SYSTEM

postgres=# \q

postgres@easytraining:~$ mkdir archive

postgres@easytraining:~$ chmod 775 archive

postgres@easytraining:~$ ls -ltr

total 12

drwxr-xr-x 3 postgres postgres 4096 Jun 10 06:28 13

drwx------ 3 postgres postgres 4096 Jun 10 06:35 tbs1

drwxrwxr-x 2 postgres postgres 4096 Jun 10 09:59 archive

postgres@easytraining:~$ pwd

/var/lib/postgresql

postgres@easytraining:~$ psql

psql (13.7 (Debian 13.7-0+deb11u1))

Type "help" for help.

postgres=# alter system set archive_command ='cp %p /var/lib/postgresql/archive/archive_wal%f';

ALTER SYSTEM

postgres=#

alter system set archive_timeout='1 h';

postgres=# alter system set archive_timeout='1 h';

ALTER SYSTEM

postgres=#

root@easytraining:~# ps -ef|grep post

postgres 3648 1 0 06:28 ? 00:00:00 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf

postgres 3650 3648 0 06:28 ? 00:00:00 postgres: 13/main: checkpointer

postgres 3651 3648 0 06:28 ? 00:00:00 postgres: 13/main: background writer

postgres 3652 3648 0 06:28 ? 00:00:00 postgres: 13/main: walwriter

postgres 3653 3648 0 06:28 ? 00:00:00 postgres: 13/main: autovacuum launcher

postgres 3654 3648 0 06:28 ? 00:00:00 postgres: 13/main: stats collector

postgres 3655 3648 0 06:28 ? 00:00:00 postgres: 13/main: logical replication launcher

root 5971 5967 0 09:53 pts/0 00:00:00 su - postgres

postgres 5972 5971 0 09:53 pts/0 00:00:00 -bash

postgres 6087 5972 0 10:05 pts/0 00:00:00 /usr/lib/postgresql/13/bin/psql

postgres 6095 3648 0 10:05 ? 00:00:00 postgres: 13/main: postgres postgres [local] idle

root 6134 6129 0 10:07 pts/1 00:00:00 grep post

root@easytraining:~# service postgresql restart

root@easytraining:~# ps -ef|grep post

root 5971 5967 0 09:53 pts/0 00:00:00 su - postgres

postgres 5972 5971 0 09:53 pts/0 00:00:00 -bash

postgres 6087 5972 0 10:05 pts/0 00:00:00 /usr/lib/postgresql/13/bin/psql

postgres 6149 1 0 10:07 ? 00:00:00 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf

postgres 6151 6149 0 10:07 ? 00:00:00 postgres: 13/main: checkpointer

postgres 6152 6149 0 10:07 ? 00:00:00 postgres: 13/main: background writer

postgres 6153 6149 0 10:07 ? 00:00:00 postgres: 13/main: walwriter

postgres 6154 6149 0 10:07 ? 00:00:00 postgres: 13/main: autovacuum launcher

postgres 6155 6149 0 10:07 ? 00:00:00 postgres: 13/main: archiver

postgres 6156 6149 0 10:07 ? 00:00:00 postgres: 13/main: stats collector

postgres 6157 6149 0 10:07 ? 00:00:00 postgres: 13/main: logical replication launcher

root 6170 6129 0 10:07 pts/1 00:00:00 grep post

root@easytraining:~#

postgres=# show archive_mode;

archive_mode

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

(1 row)

postgres=#

select name,setting from pg_settings where name like 'archive%' ;

postgres=# select name,setting from pg_settings where name like 'archive%' ;

name | setting

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

archive_cleanup_command |

archive_command | cp %p /var/lib/postgresql/archive/archive_wal%f

archive_mode | on

archive_timeout | 3600

(4 rows)

postgres=#

STEP 1: Verify archive mode is enabled or not using below command.

postgres=# show archive_mode;

archive_mode

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

off

(1 row)

postgres=# show archive_command;

archive_command

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

(disabled)

(1 row)

bash-4.1$ mkdir -p /scratch/postgres/backup/archive

bash-4.1$ chown postgres:postgres -R /scratch/postgres/backup/archive

STEP 3: Enable PostgreSQL archive_mode on using below command.

postgres=# ALTER SYSTEM SET archive_mode to 'ON';

ALTER SYSTEM

STEP 4: Set the PostgreSQL archive_command which copies WALs from pg_wal to archive location.

postgres=# ALTER SYSTEM SET archive_command TO 'cp %p /scratch/postgres/backup/archive/archive%f';

ALTER SYSTEM

Compress WAL Before Archiving

If archive storage size is a concern, you can use gzip to compress postgres archive logs:

archive_command = ‘gzip < %p > /archive_dir/%f’

restore_command = ‘gunzip < /mnt/server/archivedir/%f > %p’

STEP 5: Set wal_level, archive_timeout and max_wal_senders parameters.

postgres=# alter system set wal_level=replica;

ALTER SYSTEM

postgres=# alter system set archive_timeout to '900s';

ALTER SYSTEM

postgres=# alter system set max_wal_senders to 10;

ALTER SYSTEM1

wal_level = replica (Version PostgreSQL 10.x or later) and hot_standby (Version PostgreSQL 9.x or older).

archive_timeout: Forces a WAL switch after specified time interval and archives it even when WAL file is not fully filled due to less transaction in non peak time.

This is important because when there are less transactions and WAL file is not filled for long period of time, but still it has information of some transactions which will be lost in case of crash, if not archived)

max_wal_senders: This parameter is not mandatory for archiving, but for online backup. We can define the maximum number of wal sender process the PostgreSQL

server can spawn for Online Backup and Streaming Replication

STEP 6: Bounce the database server in order to changes come into effect.

bash-4.1$ export PGDATA=/scratch/postgres_db/db_home

bash-4.1$ /usr/pgsql-12/bin/pg_ctl restart

server started

STEP 7: Force log switch using pg_switch_wal and check whether archive is generating or not.

postgres=# select pg_switch_wal();

pg_switch_wal

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

0/1D392648

(1 row)

postgres=# select pg_switch_wal();

pg_switch_wal

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

0/1E000000

(1 row)

STEP 8: Now, verify archive mode settings.

bash-4.1$ ls -lrth /scratch/postgres/backup/archive

total 48M

-rwx------ 1 postgres postgres 16M Nov 7 09:05 archive000000010000000000000006

-rw------- 1 postgres postgres 16M Nov 7 09:06 archive000000010000000000000007

-rw------- 1 postgres postgres 16M Nov 7 09:07 archive000000010000000000000008

postgres=# show archive_mode;

archive_mode

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

(1 row)

postgres=# select name,setting from pg_settings where name like 'archive%';

name | setting

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

archive_cleanup_command |

archive_command | cp %p /scratch/postgres/backup/archive/archive%f

archive_mode | on

archive_timeout | 900

(4 rows)

postgres=# SELECT * FROM pg_stat_archiver;

STEPS to Disable Archive Mode in PostgreSQL

1: Set the archive_mode=off;

2: Restart the postgres server

postgres=# alter system set archive_mode=off;

ALTER SYSTEM

Hope, this will help you to enable archiving in postgres. After changing any configuration you can use pg_reload_conf() to reload the new changes as mention below.

SELECT pg_reload_conf();

=====================================================

Meta commands

Meta commands are a feature of psql that help you perform powerful operations without querying a database. There are many meta commands.

PostgreSQLIntermediate_SetupConfigure_code_Slide18.png

Each command supports adding + at the end for expanded information. These commands are useful for those who are unfamiliar with using catalog system tables or information schema tables to query. For more information on psql commands, see the PostgreSQL Client Applications documentation.

Interact with the OS

psql can also be used to interact with the OS. To learn more, select each of the following + icons.

Use \! to query the local client OS.

–

Use \! pwd to query the OS for the current working directory.

6PMbJkARRrgbQBBl-PostgreSQLIntermediate_SetupConfigure_code_Slide19.1.png

Change the working directory.

–

Use the \cd command to change your working directory.

zxqDsYSmIuT2syfH-PostgreSQLIntermediate_SetupConfigure_code_Slide19.2.png

Run a file.

–

Run a local file with psql using \i followed by the path to the file. Functions and permissions vary according to client OS.

(Note: You can also run a file without being logged in by using psql -h hostname -p port -d dbanme -U username -f script.sql.)

Rv0leqCSB3wpkgfo-PostgreSQLIntermediate_SetupConfigure_code_Slide19.3.png

Exit from the OS shell.

–

Exit to return to the psql client from the OS shell.

uQ8_yPgwORZUcAEn-psql%2520exit.png

================================

postgres=# \l

List of databases

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

(9 rows)

postgres=# \l+

List of databases

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

(9 rows)

postgres=# \c interndb

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

interndb=# create table departments ( department_id integer, name varchar(50));

CREATE TABLE

interndb=# insert into departments (department_id,name) values (1,'development');

INSERT 0 1

interndb=#

interndb=# create sequence department_id_seg;

CREATE SEQUENCE

interndb=# select * from departments;

department_id | name

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

1 | development

(1 row)

interndb=# update departments set name='DEVELOPMENT' where department_id =1;

UPDATE 1

interndb=# ^

interndb=# delete from departments where department_id=2;

DELETE 0

=============================

FDW

A PostgreSQL FDW is an installed extension that creates a link to another PostgreSQL database.

It can move data between databases. The postgres_fdw module provides the FDW postgres_fdw,

which can access data stored in external PostgreSQL servers.

Audio transcript

–

The CREATE EXTENSION command installs the PostgreSQL Foreign Data Wrapper, or FDW.

The CREATE SERVER command sets up the connection information to the remote server that is used by the FDW.

The CREATE USER MAPPING command sets up the credentials for the remote server.

interndb=# create extension postgres_fdw;

CREATE EXTENSION

interndb=#

interndb-# create server postgres_server foreign data wrapper postgres_fdw options (host 'localhost' ,port '5432', dbname 'easydb');

interndb=# create user mapping for public server postgres_server;

CREATE USER MAPPING

interndb=#

create schema dw

create foreign table dw.foo_stat(coll_sum int,coll_avg float)

server postgres_server options (table_name 'foo_stat',schema_name 'public');

==========================

There are many ways to move data in and out of PostgreSQL by using the COPY command.

You can also use common utilities such as FDWs, Amazon S3 import, and pgloader.

These built-in functions make moving data efficient.

Amazon S3 import

The Amazon S3 import process imports a file from Amazon S3 storage into a PostgreSQL table.

Amazon S3 is available to users to move data from a file to a PostgreSQL table. The user will need to have the data stored in an Amazon S3 storage file.

The file needs to conform to the syntax available to the COPY command, such as a comma delimited file or tab delimited file.

The function is available using the aws_s3 extension provided by Amazon RDS.

You can ensure secure access through AWS Identity and Access Management, or IAM, or use the native PostgreSQL security credentials.

create extension aws_s3 cascade;

Provide an IAM policy

–

Before you load data from an Amazon S3 file, you must give your DB instance permission. An IAM policy allows access to the S3 bucket.

For more detailed information, see the Amazon Web Services (AWS) IAM policies.

postgres=>select aws_s3.table_import_from_s3('cities'name',aws_commons.create_s3_url()

pgloader utility

The pgloader utility is a data loading tool that uses the PostgreSQL COPY protocol to stream data into the server.

It manages errors by filling a pair of reject.dat and reject.log files.

The default PostgreSQL behavior is transactional, which means that any erroneous line in the input data (file or remote database) will stop the entire load for the table.

The main advantage of using pgLoader instead of the COPY command or an FDW is its behavior during the transaction.

pgLoader keeps a separate file of rejected data during the copy process and continues trying to copy data over in the database.

The pgloader utility offers several advantages over using the COPY command.

pgloader

pgloader loads data from various sources into PostgreSQL.

It can transform the data it reads and simultaneously submit raw SQL before and after the loading.

It uses the COPY PostgreSQL protocol to stream the data into the server.

This example illustrates the resulting screen from a pgloader data import.

In the first step, you will LOAD the command file from the CSV file ~/us_cities_states_counties.csv with user credentials and Secure Sockets Layer (SSL) required.

The ~/ Unix command instructs pgloader where to find that CSV file.

The connection information to the target database is provided, with user credentials and an SSL connection requirement.

The target table and columns are specified.

======================

postgres=# select version();

version

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

PostgreSQL 13.7 (Debian 13.7-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

(1 row)

postgres=#

This code snippet contains the command string for encoding and collating a new database using template0.

create database easynew encoding 'SQL_ASCII' LC_COLLATE 'POSIX' LC_CTYPE 'POSIX' template 'template0';

postgres=# create database easynew encoding 'SQL_ASCII' LC_COLLATE 'POSIX' LC_CTYPE 'POSIX' template 'template0';

CREATE DATABASE

postgres=#

create database easynew1 with owner=postgres encoding 'UTF8' tablespace =pg_default' connection_limit=10 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' template 'template1';

create database easynew2 with connection limit =10 ;

postgres=# create database easynew2 with connection limit =10 ;

CREATE DATABASE

postgres=#

create database easynew3 with allow_connections =false ;

postgres=#

postgres=# create database easynew3 with allow_connections =false ;

CREATE DATABASE

postgres=#

====================

Dropping PostgreSQL databases

–

Remove a database using the DROP DATABASE command shown here. You must make sure there are no active connections to the database before you drop it.

If you attempt to drop a database that has an application or user connected, you will receive an error.

The error will indicate what action must be taken before the database can be dropped.

drop database easynew3;

postgres=# drop database easynew3;

DROP DATABASE

postgres=#

Altering PostgreSQL databases

–

You can change a database using the ALTER DATABASE command.

One example of using the ALTER command is to change the database name.

The following command structure is used to make changes.

Alter database easynew3 with connection limit=30;

Alter database easynew3 with allow_connections =true;

postgres=# Alter database easynew3 with connection limit=30;

ALTER DATABASE

postgres=#

postgres=# Alter database easynew3 with allow_connections =true;

ALTER DATABASE

postgres=#

Renaming databases

–

Use the ALTER DATABASE command string to rename the database.

This snippet shows the command for renaming the t2 database to testing2.

alter database easynew2 rename to easynew3;

postgres=# alter database easynew2 rename to easynew3;

ALTER DATABASE

postgres=#

Changing database owners

–

The ALTER DATABASE command can also change a database user if the one making the change has the privilege to make the change.

This snippet shows the command string for using ALTER DATABASE to change the owner.

In the first example, the user is changing the current d2 database owner to dbowner.

The second command-line demonstrates a user changing the owner of the d3 database to CURRENT_USER.

alter database easynew3 owner to dbowner;

Creating and Managing PostgreSQL Schemas

In PostgreSQL, a schema is a namespace that contains named database objects.

With schemas, users can group objects together in a database.

It is a logical collection of objects. Every object in a database belongs to a schema.

For example, you can group accounts in one part of a database and payments in another. A schema includes:

Tables

Views

Indexes

Data types

Functions

Stored procedures

Operators

Schemas logically group objects within a PostgreSQL database.

Every database object lives within a schema inside a PostgreSQL database.

postgres=# create schema easyschema;

CREATE SCHEMA

postgres=#

create schema easyschema1 create table accounts (id serial, name text) create index idx_accounts_id on accounts (id);

postgres=# create schema easyschema1 create table accounts (id serial, name text) create index idx_accounts_id on accounts (id);

CREATE SCHEMA

postgres=#

Renaming schemas

–

Schema names can be changed using the RENAME TO option of the ALTER SCHEMA command.

The snippet here illustrates using the ALTER SCHEMA command string to change the schema name from schemaA to schemaB.

alter schema easyschema1 rename to easyschema2;

drop schema easyschema cascade;

postgres=# alter schema easyschema1 rename to easyschema2;

ALTER SCHEMA

postgres=# drop schema easyschema cascade;

DROP SCHEMA

postgres=#

Parameter Groups

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

Amazon RDS manages database configuration through parameter groups. Every database has a default parameter group.

Amazon RDS provides a curated set of defaults based on your selected instance size and

PostgreSQL version to maintain consistent performance and availability

A parameter group is a container with all the configuration values applied in your DB instance.

A default DB parameter group is created when you create your DB instance. This default group contains database engine defaults and

Amazon RDS system defaults based on the engine, instance class, and allocated storage of the instance.

Default parameter groups cannot be modified. To set your own parameter group settings, you must create your own DB parameter group.

To create a new DB parameter group, you will clone it from one of the existing default groups.

Amazon RDS provides detailed documentation on Working with DB parameter groups in the Amazon RDS User Guide.

====================================

Ending Connections

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

DBAs must be able to cancel a query or stop a transaction. To do this, two PostgreSQL functions are built in:

pg_cancel_backend

pg_terminate_backend

These two commands can be used to send signals (SIGINT or SIGTERM, respectively) to backend processes identified by the process ID.

The process ID of an active process can be found in the pid column in the pg_stat_activity view or by listing the PostgreSQL processes on the server.

The role of an active backend can be found in the usename column in the pg_stat_activity view.

ManagingPostgreSQL_code_Slide33.png

This snippet shows the use of the pg_terminate_backend command to end a connection.

Ending a connection will launch a rollback for any transactions that are in process when the connection is closed.

====================================================

Most of the views provide cumulative server activity information. pg_stat_activity is different.

It provides a snapshot of all the database operations at that moment.

Views such as pg_stat_all_tables and pg_stat_user_tables are cumulative since the statistics counter was reset or from the point of inception.

The command pg_stat_progress_vacuum will read much like a status bar.

For example, if a DBA wanted to see the number of selects per minute, they would query pg_stat_user_tables every minute.

The user could then find the delta between what it was before

and what it is now to get the number of selects per minute on a table.

This list highlights some commonly used pg_stat commands. For the full list, see pg_stats.

pg_stat_activity

pg_stat_all_tables

pg_stat_user_tables

pg_stat_all_indexes

pg_stat_user_functions

pg_stat_xact_all_tables

pg_stat_xact_sys_tables

pg_stat_xact_user_functions

Monitoring connection count

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

select count(1) from pg_stat_activity

Monitoring process duration

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

select pid, now() - backend_start as connect_time

from pg_stat_activity

order by connect_time Desc

To review information about state monitoring, select each of the following tabs.

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

Active - The database is doing work. DBAs want to see active connections. For example, when a query is currently active in the database, there will be active connections.

IDLE - DBAs do not want to have too many idle connections. Having an idle status means the connections are not processing and are just sitting. Too many idle connections means that there is a high use of resources in the database resulting in unnecessary overhead.

IDLE IN TRANSACTION - Idle in transaction means that the query is in the middle of a transaction, but is not currently doing anything. An example would be if a user typed begin, and then walked away for lunch.

select state,count(*) from pg_stat_activity

order by state

group by count DESC

-To check wait event

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

select pid,wait_event_type,wait_event from pg_stat_activity where wait_event is NOT NULL;

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

select * from pg_stat_ssl;

==================

Query monitoring example 1

select * from pg_stat_activity;

Query monitoring example 2

This example shows more detail with a calculation of the query runtime: now () - query_start.

This query is to include information about runtime, pid, and user name.

A user can see the current state and wait events that have occurred and the runtime for each.

select now() -query_start as runtime, pid, username,state,waiting_query::varchar(40)

from pg_stat_activity limit 25;

Monitoring table usage

To view usage statistics for their database tables,

DBAs can use the SELECT * FROM pg_stat_user_tables query.

The results contain information and usage from the individual schema and the included tables.

Monitoring table usage example 2

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

select relid,schemaname,relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;

============================

\dt: This command will describe the table.

\dv: This command will describe the view.

=======================================

The table pg_database contains a row for every database in the cluster,

including the three that come as default in PostgreSQL: postgres, template0, and template1.

select datname,datcollate,datconnlimit,datacl from pg_database;

Rows in the system catalog tables contain a key called an OID.

OIDs uniquely identify each row in a system catalog table and can be used to join system catalog tables.

select oid,* from pg_namespace;

=================================

In this example, the user is gathering information to view the number of active

and idle connections when the connections were established.

select datname,pid,application_name,state,age(backend_start) from pg_stat_activity;

pg_locks can be joined to several different columns to gather specific data about how locks are being used.

For example, the pid column of pg_locks can be joined to the pid column of the pg_stat_activity view to get more information about the session holding or awaiting each lock.

select * from pg_locks pl left join pg_stat_activity psa on pl.pid=psa.pid;

==============================================================

pg_stat_database

collects statistics about each database in the cluster, including the number of connections (numbackends),

commits, rollbacks, and rows or tuples fetched and returned.

pg_stat_user_tables

displays statistics for each user table in a particular database. To query, specify a database and a user that has read access to that database.

The query will provide activity statistics for the database, broken down by each table.

pg_stat_user_indexes

shows users how often each index in any database is used. Querying this view can help you determine if any indexes are underused.

You can delete the underused indexes to make better use of resources.

pg_stat_bgwriter returns one row of data that shows the number of total checkpoints occurring across all databases in your cluster. It is broken down by the type of checkpoint (timed or requested),

how they were processed during a checkpoint process (buffers_checkpoint), by backends (buffers_backend), or by the background writer (buffers_clean).

pg_statio_user_tables

helps you analyze how often your queries are using the shared buffer cache.

As with pg_stat_user_tables, you will need to start a psql session and specify a database and user that has read access to the database.

This view displays a cumulative count of blocks read and information about the types of blocks read from each table.

=============================================

In this example, the query is focused on a system catalog informational function, get_user_count. The query has returned information about the following:

Language used by the function (prolang)

Estimated cost to run the function (procost)

Number of rows returned (prorows)

Data type of the return value (prorettype)

select proname,prolang,procost,prorows,prorettype from pg_proc where pronamespace=2200;

PostgreSQL provides database object size functions to help users track the amount of disk space used by their tables and indexes.

To return a value in bytes, users can query all_size_functions from pg_database

select datname,pg_database_size(oid) from pg_database;

You can use pg_size_pretty() to format the results of a query into a human-readable format.

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database;

You can customize these queries to gain more granular views into disk usage across tables and indexes in your databases.

For example, you can include indexes in your table_size metric by using pg_total_relation_size.

By default, table_size shows disk space used by the tables and excludes indexes.

select pg_relation_size(oid),pg_table_size(oid),pg_total_relation_size(oid)

from pg_class where relname='';

=========================

EasyReliableDBA

Thursday 23 June 2022

Postgresql Commands For Beginner

No comments:

Post a Comment

Search This Blog