psql cli tricks

-- show the connection info
# \conninfo

-- show IP address connected by the postgres client
# select inet_server_addr();

-- enable timing
# \timing
Timing is on.

-- enable expanded display
# \x
Expanded display is on.

-- run linux command inside psql command line
# \! date
Fri Sep  1 11:50:28 AM +03 2023

-- show the command history
# \s

-- prepare/set custom prompt:
# \set PROMPT1 '%M:%> user:%n database:%/ %l %x%# '
# \set PROMPT1 '%M:%> U:%n d:%/ %l %x%# '

PROMPT1 -> normal prompt
PROMPT2 -> if need to give more than one input command (extra prompt)
PROMPT3 -> if need to use "SQL COPY FROM STDIN" (extra prompt)

Ref: https://www.postgresql.org/docs/13/app-psql.html#APP-PSQL-PROMPTING

How to drop database/user on logical replicated database?

1st step
on 2nd node:
$ echo "DROP SUBSCRIPTION exampledb_node_2" | psql

on 1st node:
$ echo "DROP PUBLICATION exampledb_node_1" | psql

2nd step
on 1st node:
$ echo -e "DROP DATABASE exampledb \n" | psql
$ echo "DROP USER \"user-example\"" | psql

on 2nd node:
$ echo -e "DROP DATABASE exampledb \n" | psql
$ echo "DROP USER \"user-example\"" | psql

How to find/follow postgresql server logs?

-> Check log level from config file
# cat /var/lib/pgsql/data/postgresql.conf
e.g.
log_min_messages= 'ERROR' (default value)
(options; DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC)

-> Determine log directory name (via postgres user)
$ psql
postgres=# show log_directory;
e.g. pg_log

-> Determine data directory name (via postgres user)
$ psql
postgres=# show data_directory;
e.g. /var/lib/pgsql/data

-> Determine log file name (via postgres user)
$ psql
postgres=# show log_filename;
e.g. postgresql-%a.log (%a will be "day" of the week)
e.g. postgresql-%a-%H.log (%H will be "hour" of the day)

-> Check log rotation age of the log files
postgres=# show log_rotation_age;
e.g. 1d (1d means one day)
* The database system generate log files daily
e.g. 1h (1h means one hour)
* The database system generate log files hourly

Eventually, the log path could be like this;
e.g. 
# ls /var/lib/pgsql/data/pg_log/postgresql-Mon.log
# ls /var/lib/pgsql/data/pg_log/postgresql-Mon-07.log

-> To follow the logs by tail
e.g.
# tail -100f /var/lib/pgsql/data/pg_log/postgresql-Mon.log
# tail -100f /var/lib/pgsql/data/pg_log/postgresql-Mon-07.log

Ref: https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html

How to change service name?

e.g.
current service name: postgresql.service
desired service name: postgresql-9.1.service

Run following commands to change service name;

Step 1: Stop current service
# systemctl stop postgresql.service
# systemctl daemon-reload

Step 2: Change service name
# cd /usr/lib/systemd/system
# mv postgresql.service postgresql-9.1.service

Step 3: Start and enable new service
# systemctl start postgresql-9.1.service
# systemctl enable postgresql-9.1.service

Step 4: Check service status:
# systemctl status postgresql-9.1.service

Step 5: Make sure that service runs properly
# journalctl -u postgresql-9.1.service

How to run a SQL query/statement in a task?

You are able to run a SQL query/statement with "echo" command by shell module for the ansible task.

# an example task for sql query/statement
- name: set default schema for user
  shell: echo "ALTER ROLE alper SET SEARCH_PATH TO myschema" | psql
  become: yes
  become_user: postgres

"database "example" is being accessed by other users" solution

Action
postgres=# drop database example;

Error
ERROR:  database "example" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Solution
Run following sql code;

postgres=# 
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'example' AND pid <> pg_backend_pid();

All sessions should be terminated where belong to "example" database except backend sessions.

Lastly, try to drop it again.

e.g. truncate table cascade

Preparation of Test Data
test=# 
create table employees (id integer, name varchar, department_id integer);

test=# 
create table departments (id int, department_name varchar);

test=# 
ALTER TABLE DEPARTMENTS
ADD CONSTRAINT DEPT_PK 
PRIMARY KEY (ID);

test=# 
ALTER TABLE EMPLOYEES
ADD CONSTRAINT EMP_DEPT_FK 
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(ID);

test=# 
insert into departments
values (1,'IT');

test=# 
insert into departments
values (2,'Sales');

test=# 
insert into departments
values (3,'Marketing');

test=# 
insert into employees
values (1,'John',1);

test=# 
insert into employees
values (2,'Alex',1);

test=# 
insert into employees
values (3,'Guru',2);

test=# 
insert into employees
values (4,'Maximus',3);

test=# 
commit;

Data Check
-- select all rows of parent table 
test=# 
select * from departments;

-- select all rows of child table
test=# 
select * from employees;

Action
-- by using truncate table cascade, parent table and child table data will be removed. 
test=# 
truncate table departments cascade;

Last Data Check
-- select all rows of parent table 
test=# 
select * from departments;

-- select all rows of child table
test=# 
select * from employees;

Info: In Oracle 12c, you are able to use "cascade" clause with "truncate table" statement as well.

e.g. fetching JSON data

Preparation of Test Data
test=# 
create table T_JSON_DATA (id INTEGER, body JSON);

test=#
insert into T_JSON_DATA
values (1, '{ "name":"John", "age":30, "city":"New York" }');

test=#
insert into T_JSON_DATA
values (2, '{ "name":"Alex", "age":25, "city":"China" }');

test=#
insert into T_JSON_DATA
values (3, '{ "name":"Guru", "age":21, "city":"Russia" }');

test=#
insert into T_JSON_DATA
values (4, '{ "name":"Maximus", "age":25, "city":"Somewhere" }');

test=# commit;

Examples for populating test data
test=#
select * from T_JSON_DATA;

-- to retrieve specific field data
test=#
select body->>'name' from T_JSON_DATA;

-- with aliases
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA;

-- use with where clause
test=#
select body->'name' AS NAME, body->'city' AS CITY from T_JSON_DATA where id = 1;