How to set autocommit off in postgreSQL ?

 

postgres=# \set AUTOCOMMIT off

postgres=#

postgres=# \echo :AUTOCOMMIT

off

postgres=#

How to check autocommit in PostgreSQL ?

 

postgres=# \echo :AUTOCOMMIT

on

postgres=#

How to check command history in PostgreSQL ?

 

postgres=# \s

\d

\l

\l

\c

\c postgres

select pg_backend_pid();

alter user postgres password 'abcd12345';

\c demo

\c

\l

\c

\l

select * from pg_database;

show data_directory

show data_directory;

show data_directory;

select setting from pg_settings;

select * from pg_settings;

show data_directory;

\q

\conninfo

SELECT *

FROM pg_settings

WHERE name = 'port';

\c

\!

\! ls -lrt

\s

\d

\l

\s

postgres=#

How to execute OS command from psql prompt ?

 

postgres=#

postgres=# \!

bash-4.4$

bash-4.4$

bash-4.4$ ls -la

total 16

drwx------.  3 postgres postgres   79 Jan 15 08:33 .

drwxr-xr-x. 47 root     root     4096 Jan 10 12:04 ..

drwx------.  4 postgres postgres   51 Jan 10 12:05 16

-rw-------.  1 postgres postgres 1405 Jan 15 12:17 .bash_history

-rwx------.  1 postgres postgres  391 Jan 15 08:33 .bash_profile

-rw-------.  1 postgres postgres  352 Jan 15 10:00 .psql_history

bash-4.4$ 

postgres=#

postgres=#

postgres=#

postgres=# \! ls -lrt

total 0

drwx------. 4 postgres postgres 51 Jan 10 12:05 16

postgres=#

How to check database directory in PostgreSQL ?

 

postgres=# show data_directory;

     data_directory

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

 /var/lib/pgsql/16/data

(1 row)

postgres=#

How to check port in postgreSQL?

 

How to check which port running our postgreSQL database ?

[postgres@myserver ~]$ netstat -plunt |grep postgres

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

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

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

[postgres@myserver ~]$

[postgres@myserver ~]$

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".

postgres=#

postgres=#

postgres=#

postgres=# SELECT *

postgres-# FROM pg_settings

postgres-# WHERE name = 'port';

 name | setting | unit |                       category                       |                short_desc

     | extra_desc |  context   | vartype | source  | min_val | max_val | enumvals | boot_val | reset_val | sourcefil

e | sourceline | pending_restart

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

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

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

 port | 5432    |      | Connections and Authentication / Connection Settings | Sets the TCP port the server listens

 on. |            | postmaster | integer | default | 1       | 65535   |          | 5432     | 5432      |

  |            | f

(1 row)

postgres=#

How to connect to PostgreSQL database ?

 

[postgres@myserver ~]$ psql -p 5432 -U postgres -d postgres

Password for user postgres:

psql (16.6)

Type "help" for help.

postgres=# exit